Mybatis学习笔记-三、mybatis三种映射

最新推荐文章于 2025-07-13 13:00:00 发布

weixin_34755758

最新推荐文章于 2025-07-13 13:00:00 发布

阅读量790

点赞数

CC 4.0 BY-SA版权

分类专栏： Mybatis 文章标签： mybatis Spring

本文链接：https://blog.youkuaiyun.com/weixin_34755758/article/details/84193674

Mybatis 专栏收录该内容

6 篇文章

订阅专栏

本文详细介绍了Mybatis中三种映射关系：一对一、一对多和多对多的实现方式。包括使用嵌套结果ResultMap和嵌套查询select语句的映射方法，通过实例展示了如何在XML映射文件和Java代码中配置这些关系，以实现数据的高效关联查询。

三、mybatis三种映射

3.4 一对一映射

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

// An highlighted block
var foo = 'bar';

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>。

同时我们也可以使用定义内联的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>

在此方式中，元素的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
}

元素被用来将多行课程结果映射成一个课程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 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=1

select t.tutor_id, t.name as tutor_name, email, c.course_id, c.name, description, start_date, end_date 
from tutors t，addresses a，courses c
where t.addr_id = a.addr_id(+)
and   t.tutor_id = c.tutor_id(+)
and   t.tutor_id=1;

select t.tutor_id, t.name as tutor_name, email, c.course_id, c.name, description, start_date, end_date 
from tutors t，addresses a，courses c
where t.addr_id = a.addr_id
and   t.tutor_id = c.tutor_id
and   t.tutor_id=1;


<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语句获取讲师及其所教课程信息。元素的resultMap属性设置成了CourseResult，CourseResult包含了Course对象属性与表列名之间的映射。
如果同时也要查询到Address相关信息,可以按照上面一对一的方式,在配置中加入即可

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>

在这种方式中，元素的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 次）。对于大量数据而言，这会导致很差的性能问题。

// An highlighted block
var foo = 'bar';

3.5 多对多映射

对于在mybatis中的多对多的处理,其实我们可以参照一对多来解决
【注意】在这个例子中有三个字段都是一样的:id,这种情况一定要小心,要给列起别名的(上面的一对一和一对多中如果出现这种情况也是一样的处理方式)
建表语句:

drop table course;
drop table student;
drop table student_course;
//如果需要可以使用 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代码:

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:

<insert id="insertStudent" parameterType="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="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="Student">
	select id,name,gender,major,grade
	from student
	where id=#{id}
</select>

<select id="getCourseById" parameterType="int" resultType="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="Student">
	select *
	from student
	where id <![CDATA[ < ]]> #{id}
</select>

<!-- 
	 这里使用了嵌套结果ResultMap的方式进行级联查询 
	 当然也可以使用嵌套查询select 
-->
<!-- 映射一个基本的Student查询结果 -->
<resultMap id="StudentResult" type="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="Student" extends="StudentResult">
	<collection property="courses" resultMap="CourseResult"></collection>
</resultMap>
<!-- 这里特别要是的是column="cid" 这是和select语句中的 c.id as cid对应的 一定一定一定要对应起来 -->
<resultMap id="CourseResult" type="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中也有与这里对应的设置要特别特别注意
-->
<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 
	sc.course_id=c.id
</select>

测试代码:

@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一边开始操作是一样的,因为俩者的关系是多对多(对称的).
同时不论是一对一还是一对多还是多对多,都不能在mybatis中进行级联保存、更新、删除,我们需要使用sql语句控制每一步操作

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';

// An highlighted block
var foo = 'bar';