Mybatis 一对一，一对多
2018-10-31 本文已影响0人
Zebraaa
一对一与一对多：
一对一映射
Student和Address是一个【一对一】关系
建表语言:
drop table students;
drop table addresses;
如果需要可以使用 cascade constraints;
create table addresses(
addr_id number primary key,
street varchar2(50) not null,
city varchar2(50) not null,
state varchar2(50) not null,
zip varchar2(10),
country varchar2(50)
);
create table students(
stud_id number primary key,
name varchar2(50) not null,
email varchar2(50),
phone varchar2(15),
dob date ,
addr_id number references addresses(addr_id)
);
java类:
public class PhoneNumber {
private String countryCode;
private String stateCode;
private String number;
get/set
}
public class Address{
private Integer addrId;
private String street;
private String city;
private String state;
private String zip;
private String country;
get/set
}
public class Student {
private Integer studId;
private String name;
private String email;
private Date dob;
private PhoneNumber phone;
private Address address;
get/set
}
addresses 表的样例输入如下所示：
addr_id street city state zip country
1 redSt kunshan W 12345 china
2 blueST kunshan W 12345 china
insert into addresses(addr_id,street,city,state,zip,country) values(1,'redSt','kunshan','W','12345','china');
insert into addresses(addr_id,street,city,state,zip,country) values(2,'blueST','kunshan','W','12345','china');
students 表的样例数据如下所示：
stud_id name email phone addr_id
1 John john@gmail.com 123-456-7890 1
2 Paul paul@gmail.com 111-222-3333 2
insert into students(stud_id,name,email,phone,addr_id) values(1,'John','john@gmail.com','123-456-7890',1);
insert into students(stud_id,name,email,phone,addr_id) values(2,'Paul','paul@gmail.com','111-222-3333',2);
mapper XML:
<resultMap type="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id" />
<result property="name" column="name" />
<result property="email" column="email" />
<result property="phone" column="phone" />
<result property="address.addrId" column="addr_id" />
<result property="address.street" column="street" />
<result property="address.city" column="city" />
<result property="address.state" column="state" />
<result property="address.zip" column="zip" />
<result property="address.country" column="country" />
</resultMap>
<select id="selectStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
select stud_id, name, email, a.addr_id, street, city, state, zip, country
from students s left outer join addresses a on
s.addr_id=a.addr_id
where stud_id=#{studid}
</select>
我们可以使用(对象.属性名)的方式为内嵌的对象的属性赋值。在上述的resultMap中，Student的address属性使用该方式被赋上了 address 对应列的值。同样地，我们可以访问【任意深度】的内嵌对象的属性。
//接口定义
public interface Student Mapper{
Student selectStudentWithAddress(int studId);
}
//方法调用
int studId = 1;
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
Student student = studentMapper.selectStudentWithAddress(studId);
System.out.println("Student :" + student);
System.out.println("Address :" + student.getAddress());
上面展示了一对一关联映射的一种方法。然而，使用这种方式映射，如果address结果需要在其他的SELECT映射语句中映射成Address对象，我们需要为每一个语句重复这种映射关系。MyBatis提供了更好地实现一对一关联映射的方法：【嵌套结果】ResultMap和【嵌套查询】select语句。接下来，我们将讨论这两种方式。
3.4.1 使用嵌套结果ResultMap实现一对一关系映射
我们可以使用一个嵌套结果ResultMap方式来获取Student及其Address信息，代码如下：
<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="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id" />
<result property="name" column="name" />
<result property="email" column="email" />
<association property="address" resultMap="AddressResult" />
</resultMap>
<select id="findStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
select stud_id, name, email, a.addr_id, street, city, state,
zip, country
from students s left outer join addresses a on
s.addr_id=a.addr_id
where stud_id=#{studid}
</select>
注:association是关联的意思
元素<association>被用来导入“有一个”(has-one)类型的关联。在上述的例子中，我们使用了<association>元素引用了另外的在同一个XML文件中定义的<resultMap>。
同时我们也可以使用<association> 定义内联的resultMap，代码如下所示：
<resultMap type="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id" />
<result property="name" column="name" />
<result property="email" column="email" />
<association property="address" javaType="Address">
<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" />
</association>
</resultMap>
3.4.2 使用嵌套查询实现一对一关系映射
我们可以通过使用嵌套select查询来获取Student及其Address信息，代码如下：
<resultMap id="AddressResult" type="Address">
<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="findAddressById" parameterType="int" resultMap="AddressResult">
select * from addresses where addr_id=#{id}
</select>
<resultMap id="StudentWithAddressResult" type="Student">
<id property="studId" column="stud_id" />
<result property="name" column="name" />
<result property="email" column="email" />
<association property="address" column="addr_id" select="findAddressById" />
</resultMap>
<select id="findStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
select * from students where stud_id=#{id}
</select>
在此方式中，<association>元素的select属性被设置成了id为findAddressById的语句。这里，两个分开的SQL语句将会在数据库中分别执行，第一个调用findStudentById加载student信息，而第二个调用findAddressById来加载address信息。
addr_id列的值将会被作为输入参数传递给selectAddressById语句。
我们可以如下调用findStudentWithAddress映射语句：
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
Student student = mapper.selectStudentWithAddress(studId);
System.out.println(student);
System.out.println(student.getAddress());
3.5 一对多映射
一个讲师tutors可以教授一个或者多个课程course。这意味着讲师和课程之间存在一对多的映射关系。
注意:在一对多关系中,数据库建表的时候外键一定是在多的那一方建立.
建表语句:
drop table tutors;
drop table courses;
如果需要可以使用 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 表的样例数据如下：
tutor_id name email phone addr_id
1 zs zs@briup.com 123-456-7890 1
2 ls ls@briup.com 111-222-3333 2
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 表的样例数据如下：
course_id name description start_date end_date tutor_id
1 JavaSE JavaSE 2015-09-10 2016-02-10 1
2 JavaEE JavaEE 2015-09-10 2016-03-10 2
3 MyBatis MyBatis 2015-09-10 2016-02-20 2
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 讲师教授两个课程
java代码:
public class Tutor{
private Integer tutorId;
private String name;
private String email;
private PhoneNumber phone;
private Address address;
private List<Course> courses;
get/set
}
public class Course{
private Integer courseId;
private String name;
private String description;
private Date startDate;
private Date endDate;
get/set
}
<collection>元素被用来将多行课程结果映射成一个课程Course对象的一个集合。和一对一映射一样，我们可以使用【嵌套结果ResultMap】和【嵌套查询Select】语句两种方式映射实现一对多映射。
3.5.1 使用内嵌结果 ResultMap 实现一对多映射
我们可以使用嵌套结果resultMap方式获得讲师及其课程信息，代码如下：
<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 column="course_id" property="courseId" />
<result column="name" property="name" />
<result column="description" property="description" />
<result column="start_date" property="startDate" />
<result column="end_date" property="endDate" />
</resultMap>
<resultMap type="Tutor" id="TutorResult">
<id column="tutor_id" property="tutorId" />
<result column="name" property="name" />
<result column="email" property="email" />
<result column="phone" property="phone" />
<association property="address" resultMap="AddressResult" />
<collection property="courses" resultMap="CourseResult" />
</resultMap>
<select id="findTutorById" parameterType="int" resultMap="TutorResult">
select t.tutor_id, t.name as tutor_name, email, c.course_id, c.name, description, start_date, 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=#{tutorid}
</select>
这里我们使用了一个简单的使用了JOINS连接的Select语句获取讲师及其所教课程信息。<collection>元素的resultMap属性设置成了CourseResult，CourseResult包含了Course对象属性与表列名之间的映射。
如果同时也要查询到Address相关信息,可以按照上面一对一的方式,在配置中加入<association>即可
3.5.2 使用嵌套Select语句实现一对多映射
我们可以使用嵌套Select语句方式获得讲师及其课程信息，代码如下：
<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 column="course_id" property="courseId" />
<result column="name" property="name" />
<result column="description" property="description" />
<result column="start_date" property="startDate" />
<result column="end_date" property="endDate" />
</resultMap>
<resultMap type="Tutor" id="TutorResult">
<id column="tutor_id" property="tutorId" />
<result column="tutor_name" property="name" />
<result column="email" property="email" />
<association property="address" column="addr_id" select="findAddressById"></association>
<!-- 这里要注意:是把当前tutor_id表中列的值当做参数去执行findCoursesByTutor这个查询语句,最后把查询结果封装到Tutor类中的courses属性中 -->
<collection property="courses" column="tutor_id" select="findCoursesByTutor" />
</resultMap>
<select id="findTutorById" parameterType="int" resultMap="TutorResult">
select *
from tutors
where tutor_id=#{tutor_id}
</select>
<select id="findAddressById" parameterType="int" resultMap="AddressResult">
select *
from addresses
where addr_id = #{addr_id}
</select>
<select id="findCoursesByTutor" parameterType="int" resultMap="CourseResult">
select *
from courses
where tutor_id=#{tutor_id}
</select>
在这种方式中，<aossication>元素的select属性被设置为id为findCourseByTutor的语句，用来触发单独的SQL查询加载课程信息。tutor_id这一列值将会作为输入参数传递给 findCouresByTutor语句。
mapper接口代码:
public interface TutorMapper{
Tutor findTutorById(int tutorId);
}
//方法调用
TutorMapper mapper = sqlSession.getMapper(TutorMapper.class);
Tutor tutor = mapper.findTutorById(tutor Id);
System.out.println(tutor);
List<Course> courses = tutor.getCourses();
for (Course course : courses){
System.out.println(course);
}
【注意】嵌套查询Select语句查询会导致1+N选择问题。首先，主查询将会执行（1 次），对于主查询返回的每一行，另外一个查询将会被执行（主查询 N 行，则此查询 N 次）。对于大量数据而言，这会导致很差的性能问题。
