Mybatis中一对多映射详解
Mybatis中一对一映射详解
1、建表
一个讲师tutors可以教授一个或者多个课程course。这意味着讲师和课程之间存在一对多的映射关系。
注意:在一对多关系中,数据库建表的时候外键一定是在多的那一方建立.建表语句:
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','zs@briup.com','123-456-7890',1);
insert into tutors(tutor_id,name,email,phone,addr_id)values(2,'ls','ls@briup.com','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;
}
}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;
}
}<?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>最终部署完成后结果如下图所示，部分操作在一对一映射中详细说明，在这里就不在一一重复，可参见以下连接，Mybatis中一对一映射详解：
3、实现一对多操作
<collection>元素被用来将多行课程结果映射成一个课程Course对象的一个集合。和一对一映射一样，我们可以使用【嵌套结果ResultMap】和【嵌套查询Select】语句两种方式映射实现一对多映射。
我们可以使用嵌套结果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>如果同时也要查询到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=zs@briup.com, 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]]]
执行完毕
从测试结果我们能够看出红色标记的是用于查询的sql语句，蓝色标记的是传递的参数，紫色标记的是返回的结果数，绿色标记的是查询到的结果
我们可以使用嵌套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>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=zs@briup.com, 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]]]
执行完毕
由结果，我们能够看到程序进行了三次查询(蓝色)，一次最终查询并且返回一条数据，其内容为教师的基本资料，住址信息和所教授的两门课程