本文介绍了Mybatis中Mapper映射文件的一对一和一对多映射关系。针对一对一映射，讲解了普通方式封装ResultMap的不足，并详细阐述了如何通过resultMap的嵌套结果和select的嵌套查询来实现一对一关联映射。对于一对多映射，提到了使用内嵌结果的ResultMap和嵌套查询的Select方法，并提醒在处理多张表查询时注意字段别名以避免映射冲突。最后，讨论了多对多映射的处理策略，强调了在处理外键时的顺序以及列名重复时的注意事项。
一、一对一映射关系
-
举例：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), dob date , phone varchar2(15), 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); -
映射xml文件查询结果普通方式封装resultMap：
<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, dob, phone, 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=#{id} </select> //接口定义 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());- 如果select语句单独封装成Address对象，需要单独重新配置封装Address对象
-
为了解决普通封装对象的关联映射需要重新配置封装Address对象，提供了一对一关联映射的另外两种方式：
- resultMap，嵌套结果：一个是映射结果中引用其他映射结果
- select，嵌套查询：将级联查询分解为多个简单查询，使用简单查询的结果当作另一查询条件，完成级联查询
-
使用【嵌套结果】ResultMap，实现一对一关系映射
使用嵌套结果ResultMap方式来获取Student及其Address信息，代码如下：
<!-- 独立的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>
<!-- Student封装映射，里面关联上Address的封装映射 -->
<resultMap type="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id" />
<result property="name" column="name" />
<result property="email" column="email" />
<result property="dob" column="dob" />
<result property="phone" column="phone" />
<association property="address" resultMap="AddressResult" />
</resultMap>
<select id="selectStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
select stud_id, name, email,dob,phone, 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=#{id}
</select>
-
注:是关联的意思,常被用来表示(has-a)类型的关联。就是对象1里面关联另一个对象2
同时我们也可以使用<association> 定义【内联】的resultMap，代码如下所示：（了解即可，不常用） <!-- 相当于把Student映射和Address映射又合并在一起写了，还是使用<association>标签 --> <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> -
使用【嵌套查询】select，实现一对一关系映射
使用嵌套查询select来获取Student及其Address信息，代码如下： <!-- 独立的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> <!-- 独立的select查询，专门查询Address --> <select id="findAddressById" parameterType="int" resultMap="AddressResult"> select * from addresses where addr_id=#{id} </select> <!-- Student封装映射，里面关联了查询address使用的select语句，并指定数据库表中的这个关联的外键列的名字，这里是addr_id --> <resultMap type="Student" id="StudentWithAddressResult"> <id property="studId" column="stud_id" /> <result property="name" column="name" /> <result property="email" column="email" /> <result property="dob" column="dob" /> <result property="phone" column="phone" /> <association property="address" column="addr_id" select="findAddressById" /> </resultMap> <!-- 查询Student的select语句，这里不用写多表查询，因为对于address的关联查询，已经在上边定义好了，并且在结果映射中关联进来了 --> <!-- 将多表查询变为简单的一个表的查询，需要写出association标签关联的select语句 --> <select id="findAddressById" parameterType="int" resultMap="AddressResult"> select * from addresses where addr_id=#{id} </select> <select id="selectStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult"> select * from students where stud_id=#{id} </select> 在此方式中，<association>元素的select属性被设置成了id为findAddressById的语句。 两个分开的SQL语句将会在数据库中分别执行，第一个通过id查询student信息，而第二个调用findAddressById来加载address信息。 addr_id列的值将会被作为输入参数传递给selectAddressById语句作为参数进行条件查询。 调用selectStudentWithAddress方法测试： @Test public void test_selectStudentWithAddress() { SqlSession sqlSession = null; try { sqlSession = MyBatisSqlSessionFactory.openSession(); //Student stu = sqlSession.selectOne("com.briup.mappers.OneToOneMapper.selectStudentWithAddress", 1); OneToOneMapper mapper = sqlSession.getMapper(OneToOneMapper.class); Student stu = mapper.selectStudentWithAddress(1); System.out.println(stu.toString()); } catch (Exception e) { e.printStackTrace(); } finally { sqlSession.close(); } }
二、一对多映射
例如，一个讲师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 表的样例数据如下：
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 2019-01-10 2019-02-10 1
2 JavaEE JavaEE 2019-01-10 2019-03-10 2
3 MyBatis MyBatis 2019-01-10 2019-02-20 2
insert into
courses(course_id,name,description,start_date,end_date,tutor_id)
values(1,'JavaSE','JavaSE',to_date('2019-01-10','yyyy-mm-dd'),to_date('2019-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('2019-01-10','yyyy-mm-dd'),to_date('2019-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('2019-01-10','yyyy-mm-dd'),to_date('2019-02-20','yyyy-mm-dd'),1);
在上述的表数据中，zs 讲师教授一个课程，而 ls 讲师教授两个课程
java代码:
public class Course{
private Integer courseId;
private String name;
private String description;
private Date startDate;
private Date endDate;
get/set
}
public class Tutor{
private Integer tutorId;
private String name;
private String email;
private PhoneNumber phone;
private Address address;
private List<Course> courses;
get/set
}
- 使用【内嵌结果】ResultMap 实现一对多映射
<collection>标签可以用来将多行课程结果映射成一个课程Course对象的集合。和之前的一对一映射一样，可以使用【嵌套结果】ResultMap和【嵌套查询】Select语句两种方式映射实现一对多映射。
使用【嵌套结果】resultMap方式获得讲师及其课程信息，代码如下：
<!-- 独立的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>
<!-- 独立的Course封装映射 -->
<resultMap type="Course" id="CourseResult">
<id column="course_id" property="courseId" />
<result column="courseName" property="name" />
<result column="description" property="description" />
<result column="start_date" property="startDate" />
<result column="end_date" property="endDate" />
</resultMap>
<!-- Tutor封装映射，里面是有嵌套结果的方式关联一个Addres和多个Course，分别使用association标签和collection标签 -->
<resultMap type="Tutor" id="TutorResult">
<id column="tutor_id" property="tutorId" />
<result column="tutorName" property="name" />
<result column="email" property="email" />
<result column="phone" property="phone" />
<association property="address" resultMap="AddressResult" />
<collection property="courses" resultMap="CourseResult" />
</resultMap>
<!-- 查询Tutor的select语句，多表连接查询，把查询的结果给个上面的映射结果统一进行封装 -->
<select id="findTutorById" parameterType="int" resultMap="TutorResult">
select
t.tutor_id, t.name tutorName, t.email,t.phone,
c.course_id, c.name courseName, c.description, c.start_date, c.end_date,
a.addr_id,a.street,a.city,a.state,a.zip,a.country
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>
- 注意,这些查询了三张表，如果表中有字段和其他表中的字段名字一样，那么一定要起别名进行区别，否则封装映射结果的时候会出冲突问题，将其他对象的字段值封装到不对应的对象属性值中。
- 使用【嵌套查询】Select，实现一对多映射
使用【嵌套查询】Select方式获得讲师及其课程信息，代码如下：
<!-- 独立的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>
<!-- 独立的Course封装映射 -->
<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>
<!-- Tutor封装映射，里分别使用association和collection标签来进行关联查询，直接把对应的select查询语句管理进行 -->
<resultMap type="Tutor" id="TutorResult">
<id column="tutor_id" property="tutorId" />
<result column="tutor_name" property="name" />
<result column="email" property="email" />
<result column="phone" property="phone" />
<!-- 在涉及select嵌套的时候，要注意是将查询结果的列名作为子查询的查询条件，association和collection的column的值必须和表中查询条件的列名相同 -->
<!-- 把addr_id列的值当做参数传给findAddressById进行查询 -->
<association property="address" column="addr_id" select="findAddressById"></association>
<!-- 把tutor_id列的值当做参数传给findCoursesByTutor进行查询 -->
<collection property="courses" column="tutor_id" select="findCoursesByTutor" />
</resultMap>
<!-- 单独的Tutor查询语句 -->
<select id="findTutorById" parameterType="int" resultMap="TutorResult">
select *
from tutors
where tutor_id=#{tutor_id}
</select>
<!-- 单独的Address查询语句 -->
<select id="findAddressById" parameterType="int" resultMap="AddressResult">
select *
from addresses
where addr_id = #{addr_id}
</select>
<!-- 单独的Course查询语句 -->
<select id="findCoursesByTutor" parameterType="int" resultMap="CourseResult">
select *
from courses
where tutor_id=#{tutor_id}
</select>
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();
courses.foreach(System.out::println);
【注意】嵌套查询Select语句查询会导致性能问题。首先，主查询将会执行（1 次,这1次select可能会查出多条数据），对于主查询返回的每一行数据，另外一个查询将会被执行（主查询 N 行，则此查询 N 次）。对于大量数据而言，这会导致整体效率降低。
-
这时候如果需要编写一个Course的插入功能，该如果操作？注意外键列在Course对应的表中，需要先插入tutor数据在插入course数据
-
多对多映射：对于在mybatis中的多对多的处理,可以参照一对多来解决
- 【注意】在这个例子中有三个字段都是一样的:id,这种情况一定要小心,要给列起别名的
建表语句:
drop table student_course;
drop table course;
drop table student;
如果需要可以使用 cascade constraints;
create table course (
id number primary key,
course_code varchar2(30) not null,
course_name varchar2(30) not null
);
create table student (
id number primary key,
name varchar2(10) not null,
gender varchar2(10) ,
major varchar2(10) ,
grade varchar2(10)
);
create table student_course (
id number primary key,
student_id number references student(id),
course_id number references course(id)
);
java代码:
新建包 com.briup.many2many:
public class Course {
private Integer id;
private String courseCode; // 课程编号
private String courseName;// 课程名称
private List<Student> students;// 选课学生
get/set
}
public class Student {
private Integer id;
private String name; // 姓名
private String gender; // 性别
private String major; // 专业
private String grade; // 年级
private List<Course> courses;// 所选的课程
get/set
}
Many2ManyMapper.java:
public interface Many2ManyMapper {
//插入student数据
public void insertStudent(Student student);
//插入course数据
public void insertCourse(Course course);
//通过id查询学生
public Student getStudentById(Integer id);
//通过id查询课程
public Course getCourseById(Integer id);
//学生x选课y
public void studentSelectCourse(Student student, Course course);
//查询比指定id值小的学生信息
public List<Student> getStudentByIdOnCondition(Integer id);
//查询student级联查询出所选的course并且组装成完整的对象
public Student getStudentByIdWithCourses(Integer id);
}
Many2ManyMapper.xml:
注意，为了避免和之前类型的别名冲突，这里全都使用的是类型的全限定名
<?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">
<mapper namespace="com.briup.mappers.Many2ManyMapper">
<insert id="insertStudent" parameterType="com.briup.many2many.Student">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select my_seq.nextval from dual
</selectKey>
insert into
student(id,name,gender,major,grade)
values
(#{id},#{name},#{gender},#{major},#{grade})
</insert>
<insert id="insertCourse" parameterType="com.briup.many2many.Course">
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select my_seq.nextval from dual
</selectKey>
insert into
course(id,course_code,course_name)
values
(#{id},#{courseCode},#{courseName})
</insert>
<select id="getStudentById" parameterType="int"
resultType="com.briup.many2many.Student">
select id,name,gender,major,grade
from student
where id=#{id}
</select>
<select id="getCourseById" parameterType="int"
resultType="com.briup.many2many.Course">
select id,course_code as courseCode,course_name as courseName
from course
where id=#{id}
</select>
<!-- param1代表方法中第一个参数 以此类推 -->
<insert id="studentSelectCourse">
insert into
student_course(id,student_id,course_id)
values
(my_seq.nextval,#{param1.id},#{param2.id})
</insert>
<!-- 如果有特殊符号的话 需要用 <![CDATA[ 特殊符号 ]]> 例如 < & 等等 -->
<select id="getStudentByIdOnCondition" parameterType="int"
resultType="com.briup.many2many.Student">
select *
from student
where id <![CDATA[ < ]]>
#{id}
</select>
<!-- 这里使用了嵌套结果ResultMap的方式进行级联查询 当然也可以使用嵌套查询select -->
<!-- 映射一个基本的Student查询结果 -->
<resultMap id="StudentResult" type="com.briup.many2many.Student">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="gender" column="gender" />
<result property="major" column="major" />
<result property="grade" column="grade" />
</resultMap>
<!-- 继承上面那个基本的映射,再扩展出级联查询 -->
<resultMap id="StudentResultWithCourses" type="com.briup.many2many.Student"
extends="StudentResult">
<collection property="courses" resultMap="CourseResult"></collection>
</resultMap>
<!-- 这里特别注意的是column="cid" 这是和select语句中的 c.id as cid对应的 -->
<resultMap id="CourseResult" type="com.briup.many2many.Course">
<id property="id" column="cid" />
<result property="courseCode" column="course_code" />
<result property="courseName" column="course_name" />
</resultMap>
<!-- 注意:查询语句的中的c.id as cid这个地方,避免名字冲突，将数据封装到对象中错误-->
<!--同时在id="CourseResult"的resultMap中对象属性名和property相同，column和查询结果的列名相同 -->
<select id="getStudentByIdWithCourses" parameterType="int"
resultMap="StudentResultWithCourses">
select s.id,s.name,s.gender,s.major,s.grade,c.id as
cid,c.course_code,c.course_name,sc.id,sc.student_id,sc.course_id
from student s,course c,student_course sc
where
s.id=#{id}
and
s.id = sc.student_id
and
c.id = sc.course_id
</select>
</mapper>
测试代码:Many2ManyMapperTest.java
@Test
public void test_insertStudent(){
SqlSession session = null;
try {
session = MyBatisSqlSessionFactory.openSession();
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
mapper.insertStudent(new Student("张三","男","计算机","大四"));
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally {
if(session!=null)session.close();
}
}
@Test
public void test_insertCourse(){
SqlSession session = null;
try {
session = MyBatisSqlSessionFactory.openSession();
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
mapper.insertCourse(new Course("001","corejava"));
mapper.insertCourse(new Course("002","oracle"));
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally {
if(session!=null)session.close();
}
}
@Test
public void test_studentSelectCourse(){
SqlSession session = null;
try {
session = MyBatisSqlSessionFactory.openSession();
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
Student student = mapper.getStudentById(58);
Course course = mapper.getCourseById(59);
mapper.studentSelectCourse(student, course);
session.commit();
} catch (Exception e) {
e.printStackTrace();
session.rollback();
}finally {
if(session!=null)session.close();
}
}
@Test
public void test_getStudentByIdOnCondition(){
SqlSession session = null;
try {
session = MyBatisSqlSessionFactory.openSession();
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
List<Student> list = mapper.getStudentByIdOnCondition(100);
for(Student s:list){
System.out.println(s);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
if(session!=null)session.close();
}
}
@Test
public void test_getStudentByIdWithCourses(){
SqlSession session = null;
try {
session = MyBatisSqlSessionFactory.openSession();
Many2ManyMapper mapper = session.getMapper(Many2ManyMapper.class);
Student student = mapper.getStudentByIdWithCourses(58);
System.out.println(student);
} catch (Exception e) {
e.printStackTrace();
}finally {
if(session!=null)session.close();
}
}
- 注意：这是从student这边出发所做的一些操作,从course一边开始操作是一样的,因为俩者的关系是多对多(对称的).
扫一扫
123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
