Mybatis一对多映射详解 - 极悦
首页 课程 师资 教程 报名
首页 > Hot资讯 > 正文

Mybatis一对多映射详解

  • 2021-09-14 10:08:43
  • 1378次 极悦

1.建表

注意:在一对多关系中,数据库建表的时候外键一定是在多的那一方建立.

建表语句:

drop table courses;

drop table tutors;

如果需要可以使用 cascade constraints;

create table tutors(tutor_id number primary key, name varchar2(50) not null,email varchar2(50),phone varchar2(15), addr_id number(11) references addresses (addr_id));

create table courses(course_id number primary key,name varchar2(100) not null,description varchar2(512),start_date date ,end_date date ,tutor_id number references tutors (tutor_id));

tutors 表的样例数据如下：

insert into tutors(tutor_id,name,email,phone,addr_id)values(1,'zs','[email protected]','123-456-7890',1);

insert into tutors(tutor_id,name,email,phone,addr_id)values(2,'ls','[email protected]','111-222-3333',2);

course 表的样例数据如下：

insert into courses(course_id,name,description,start_date,end_date,tutor_id) values(1,'JavaSE','JavaSE',to_date('2015-09-10','yyyy-mm-dd'),to_date('2016-02-10','yyyy-mm-dd'),1);

insert into courses(course_id,name,description,start_date,end_date,tutor_id) values(2,'JavaEE','JavaEE',to_date('2015-09-10','yyyy-mm-dd'),to_date('2016-03-10','yyyy-mm-dd'),2);

insert into courses(course_id,name,description,start_date,end_date,tutor_id) values(3,'MyBatis','MyBatis',to_date('2015-09-10','yyyy-mm-dd'),to_date('2016-02-20','yyyy-mm-dd'),1);

在上述的表数据中，zs 讲师教授一个课程，而 ls 讲师教授两个课程

2.实现pojo java类:

Tutor类

package com.mybatis.pojo;
import java.util.List;
 public class Tutor{
            private Integer tutorId;  
            private String name;  
            private String email;  
            private PhoneNumber phone;
            private Address address;  
            private List<Course> courses;
			public Tutor(Integer tutorId, String name, String email,
					PhoneNumber phone, Address address, List<Course> courses) {
				super();
				this.tutorId = tutorId;
				this.name = name;
				this.email = email;
				this.phone = phone;
				this.address = address;
				this.courses = courses;
			}
			public Tutor() {
				super();
			}
			@Override
			public String toString() {
				return "Tutor [tutorId=" + tutorId + ", name=" + name
						+ ", email=" + email + ", phone=" + phone
						+ ", address=" + address + ", courses=" + courses + "]";
			}
			public Integer getTutorId() {
				return tutorId;
			}
			public void setTutorId(Integer tutorId) {
				this.tutorId = tutorId;
			}
			public String getName() {
				return name;
			}
			public void setName(String name) {
				this.name = name;
			}
			public String getEmail() {
				return email;
			}
			public void setEmail(String email) {
				this.email = email;
			}
			public PhoneNumber getPhone() {
				return phone;
			}
			public void setPhone(PhoneNumber phone) {
				this.phone = phone;
			}
			public Address getAddress() {
				return address;
			}
			public void setAddress(Address address) {
				this.address = address;
			}
			public List<Course> getCourses() {
				return courses;
			}
			public void setCourses(List<Course> courses) {
				this.courses = courses;
			}             
        }

Course类实现：

package com.mybatis.pojo;
import java.util.Date;
 public class Course{
            private Integer courseId;  
            private String name;  
            private String description;  
            private Date startDate;  
            private Date endDate;
			public Course(Integer courseId, String name, String description,
					Date startDate, Date endDate) {
				super();
				this.courseId = courseId;
				this.name = name;
				this.description = description;
				this.startDate = startDate;
				this.endDate = endDate;
			}
			public Course() {
				super();
			}
			@Override
			public String toString() {
				return "Course [courseId=" + courseId + ", name=" + name
						+ ", description=" + description + ", startDate="
						+ startDate + ", endDate=" + endDate + "]";
			}
			public Integer getCourseId() {
				return courseId;
			}
			public void setCourseId(Integer courseId) {
				this.courseId = courseId;
			}
			public String getName() {
				return name;
			}
			public void setName(String name) {
				this.name = name;
			}
			public String getDescription() {
				return description;
			}
			public void setDescription(String description) {
				this.description = description;
			}
			public Date getStartDate() {
				return startDate;
			}
			public void setStartDate(Date startDate) {
				this.startDate = startDate;
			}
			public Date getEndDate() {
				return endDate;
			}
			public void setEndDate(Date endDate) {
				this.endDate = endDate;
			}  
        }

配置完以上内容之后，我们需要进行配置文件中注册必要的config文件：

<?xml version="1.0" encoding="UTF-8"?>
 <!-- 进行dtd约束，其中-//mybatis.org//DTD Config 3.0//EN为公共约束，
 http://mybatis.org/dtd/mybatis-3-config.dtd为获取网络中提供的dtd约束 -->
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"  
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
	<typeAliases>
	<!-- 给pojo类起别名 -->
		<typeAlias type="com.mybatis.pojo.Address" alias="Address" />
		<typeAlias type="com.mybatis.pojo.Course" alias="Course" />
		<typeAlias type="com.mybatis.pojo.Tutor" alias="Tutor" />
	</typeAliases>
	<typeHandlers>
	<typeHandler handler="com.mybatis.handlers.PhoneNumberHandlers"/>
	</typeHandlers>
	<!-- 配置数据库环境其中development为默认的数据库名称事务管理器transactionManager类型为JDBC类型，数据源dataSource使用连接池的方式 -->
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC"></transactionManager>
			<dataSource type="POOLED">
			<!-- 配置数据库信息这里使用oracle数据库 -->
				<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
				<property name="url" value="jdbc:oracle:thin:@127.0.0.1:1521:orcl" />
				<property name="username" value="briup" />
				<property name="password" value="briup" />
			</dataSource>
		</environment>
	</environments>
	<!-- 配置xml文件映射路径，在这里可以进行sql的操作 -->
	<mappers>
		<mapper resource="com/mybatis/mappers/One2ManyMapper.xml" />
	</mappers>
</configuration>

3.实现一对多操作

<collection>元素被用来将多行课程结果映射成一个课程Course对象的一个集合。和一对一映射一样，我们可以使用【嵌套结果ResultMap】和【嵌套查询Select】语句两种方式映射实现一对多映射。

(1)使用内嵌结果 ResultMap 实现一对多映射

我们可以使用嵌套结果resultMap方式获得课程信息，代码如下：

<?xml version="1.0" encoding="UTF-8"?>
            <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- com.mybatis.mappers.StudentMapper是我们定义接口的全限定名字 这样就可以使用接口调用映射的SQL语句了 这个名字一定要和接口对应上 -->
<mapper namespace="com.mybatis.mappers.One2ManyMapper">
	   <resultMap type="Address" id="AddressResult">  
          <id property="addrId" column="addr_id" />  
          <result property="street" column="street" />  
          <result property="city" column="city" />  
          <result property="state" column="state" />  
          <result property="zip" column="zip" />  
          <result property="country" column="country" />  
        </resultMap>
       <resultMap type="Course" id="CourseResult">
            <id property="courseId" column="course_id"/>
            <result property="name" column="name"/>
            <result property="description" column="description"/>
            <result property="startDate" column="start_Date"/>
            <result property="endDate" column="end_Date"/>
       </resultMap>    
       <resultMap type="Tutor" id="TutorWithCoursesResult">
            <id property="tutorId" column="tutor_id"/>
            <result property="name" column="name"/>
            <result property="email" column="email"/>
            <result property="phone" column="phone"/>
            <association property="address"  resultMap="AddressResult"/>
            <collection property="courses" resultMap="CourseResult"/>
       </resultMap>
        <select id="selectTutorWithCourses" parameterType="int" resultMap="TutorWithCoursesResult">  
            select t.tutor_id,t.name,t.email,t.phone,a.addr_id,a.street,a.city,a.state,a.zip,a.country,c.course_id,c.name,c.description,c.start_Date,c.end_Date 
            from tutors t left outer join addresses a on t.addr_id=a.addr_id
            left outer join courses c on t.tutor_id=c.tutor_id
            where t.tutor_id=#{id}                              
        </select>
</mapper>

这里我们使用了一个简单的使用了JOINS连接的Select语句获取课程信息。<collection>元素的resultMap属性设置成了CourseResult，CourseResult包含了Course对象属性与表列名之间的映射。

如果同时也要查询到Address相关信息,可以按照上面一对一的方式,在配置中加入<association>即可

实现接口：

package com.mybatis.mappers;
import com.mybatis.pojo.Tutor;
public interface One2ManyMapper {
	Tutor selectTutorWithCourses(Integer id); 
}

测试类实现：

package com.mybatis.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.mybatis.mappers.One2ManyMapper;
import com.mybatis.pojo.Tutor;
import com.mybatis.utils.MyBatisSqlSessionFactory;
public class One2ManyMapperTest {	
	@Test
	public void test_selectTutorWithCourses()
	{
		SqlSession session=null;
		session = MyBatisSqlSessionFactory.openSession();//使用封装之后的类
		// 使用sqlsession获得映射接口的实现类对象,接口的引用指向实现类的对象
		One2ManyMapper mapper = session.getMapper(One2ManyMapper.class);
		Tutor tutor = mapper.selectTutorWithCourses(1);
		System.out.println(tutor);
		System.out.println("执行完毕");
	}
}

测试结果：

2016-10-23 14:14:26,785 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - ==> Preparing: select t.tutor_id,t.name,t.email,t.phone,a.addr_id,a.street,a.city,a.state,a.zip,a.country,c.course_id,c.name,c.description,c.start_Date,c.end_Date from tutors t left outer join addresses a on t.addr_id=a.addr_id left outer join courses c on t.tutor_id=c.tutor_id where t.tutor_id=?

2016-10-23 14:14:27,007 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - ==> Parameters: 1(Integer)

2016-10-23 14:14:27,165 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - <== Total: 2

Tutor [tutorId=1, name=zs, [email protected], phone=123-456-7890, address=Address [addrId=1, street=redSt, city=kunshan, state=W, zip=12345, country=china], courses=[Course [courseId=1, name=zs, description=JavaSE, startDate=Thu Sep 10 00:00:00 CST 2015, endDate=Wed Feb 10 00:00:00 CST 2016], Course [courseId=3, name=zs, description=MyBatis, startDate=Thu Sep 10 00:00:00 CST 2015, endDate=Sat Feb 20 00:00:00 CST 2016]]]

执行完毕

(2)使用嵌套Select语句实现一对多映射

我们可以使用嵌套Select语句方式获得课程信息，代码如下：

<mapper namespace="com.mybatis.mappers.One2ManyMapper">
         <resultMap type="Address" id="AddressResult">  
          <id property="addrId" column="addr_id" />  
          <result property="street" column="street" />  
          <result property="city" column="city" />  
          <result property="state" column="state" />  
          <result property="zip" column="zip" />  
          <result property="country" column="country" />  
        </resultMap>
        <select id="selectAddress" parameterType="int" resultMap="AddressResult">
           select * from addresses where addr_id=#{id}
        </select>
       <resultMap type="Course" id="CourseResult">
            <id property="courseId" column="course_id"/>
            <result property="name" column="name"/>
            <result property="description" column="description"/>
            <result property="startDate" column="start_Date"/>
            <result property="endDate" column="end_Date"/>
       </resultMap>
       <select id="selectCourse" parameterType="int" resultMap="CourseResult">
       select * from courses where tutor_id=#{id}
       </select> 
       <resultMap type="Tutor" id="TutorWithCoursesResult">
            <id property="tutorId" column="tutor_id"/>
            <result property="name" column="name"/>
            <result property="email" column="email"/>
            <result property="phone" column="phone"/>
            <association property="address" column="addr_id" select="selectAddress"/>
  <!-- 这里要注意:是把当前tutor_id表中列的值当做参数去执行selectCourse这个查询语句,最后把查询结果封装到Tutor类中的courses属性中 -->
            <collection property="courses" column="tutor_id" select="selectCourse"/>
       </resultMap>
        <select id="selectTutorWithCourses" parameterType="int" resultMap="TutorWithCoursesResult">  
            select t.tutor_id,t.name,t.email,t.phone,t.addr_id
            from tutors t
            where t.tutor_id=#{id}                              
        </select>
</mapper>

在这种方式中，<assoication>元素的select属性被设置为id为selectAddress的语句，用来触发单独的SQL查询加载课程信息。tutor_id这一列值将会作为输入参数传递给selectCourse语句。

mapper接口和程序调用与resultMap中的方法一致，查询后结果显示如下所示：

2016-10-23 15:38:53,709 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - ==> Preparing: select t.tutor_id,t.name,t.email,t.phone,t.addr_id from tutors t where t.tutor_id=?

2016-10-23 15:38:54,029 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - ==> Parameters: 1(Integer)

2016-10-23 15:38:54,187 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectAddress - ====> Preparing: select * from addresses where addr_id=?

2016-10-23 15:38:54,188 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectAddress - ====> Parameters: 1(Integer)

2016-10-23 15:38:54,193 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectAddress - <==== Total: 1

2016-10-23 15:38:54,202 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectCourse - ====> Preparing: select * from courses where tutor_id=?

2016-10-23 15:38:54,203 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectCourse - ====> Parameters: 1(Integer)

2016-10-23 15:38:54,224 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectCourse - <==== Total: 2

2016-10-23 15:38:54,225 [DEBUG] com.mybatis.mappers.One2ManyMapper.selectTutorWithCourses - <== Total: 1

Tutor [tutorId=1, name=zs, [email protected], phone=123-456-7890, address=Address [addrId=1, street=redSt, city=kunshan, state=W, zip=12345, country=china], courses=[Course [courseId=1, name=JavaSE, description=JavaSE, startDate=Thu Sep 10 00:00:00 CST 2015, endDate=Wed Feb 10 00:00:00 CST 2016], Course [courseId=3, name=MyBatis, description=MyBatis, startDate=Thu Sep 10 00:00:00 CST 2015, endDate=Sat Feb 20 00:00:00 CST 2016]]]

执行完毕

【注意】嵌套查询Select语句查询会导致1+N问题。首先，主查询将会执行(1 次)，对于主查询返回的每一行，另外一个查询将会被执行(主查询 N 行，则此查询 N 次)。对于大量数据而言，这会导致很差的性能问题。

通过以上介绍相信大家对"Mybatis一对多映射详解"已经有所了解，如果有朋友想了解更多Java相关知识，可以关注极悦的Java视频页面，里面的视频教程都是可以免费下载学习的，希望对大家能够有所帮助。

上一篇：16种常用的Java工具类下一篇：Mybatis循环List

选你想看

首页

报班选班

免费测评

视频中心

技术文档

Java资讯

常见问题

hot资讯

学院新闻

热门专题

你适合学Java吗？4大专业测评方法

代码逻辑 吸收能力 技术学习能力 综合素质

先测评确定适合在学习

在线申请免费测试名额
价值1998元实验班免费学
姓名
手机
提交
在线咨询 免费试学