在 Mybatis 的 Mapper 映射文件中,SQL 语句查询后返回的结果,会映射到配置标签的输出映射属性对应的 Java 类型。Mapper 的输出映射有两种配置,分别是 resultType 和 resultMap。
一、resultType
1、返回自定义包装类(JavaBean)
resultType 除了支持基本数据类型、基本数据包装类之外,也支持自定义包装类(如JavaBean)。关于自定义包装类,如果从数据库中查询出来的列名与包装类中的属性名完全都不一致,则不会创建包装类对象,如果数据库查询出来的列名与包装类中的属性名至少有一个一致,那么就会创建包装类对象。
<select id="getEmpById" resultType="com.example.pojo.Employee">
select * from tbl_employee where id = #{id}
</select>
resultType的值可以是包装类的别名或者全名。
2、返回 List 集合
如果返回的是集合,resultType的值需要设置为集合中元素的类型。
<select id="findAllEmp" resultType="employeecom.example.pojo.Employee">
select * from tbl_employee
</select>
上面的这条查询语句,查询出来的结果一定是一条或者多条数据,但是 resultType 仍需要配置为 Employee 类。在 Mybatis 中,不管输出的是 JavaBean 单个对象还是一个列表,在 Mapper 映射文件中 resultType 指定的类型是一样的。
3、返回 Map 集合
EmployeeMapper.xml
<select id="getEmpByGenderReturnMap" resultType="com.example.pojo.Employee">
select * from tbl_employee where gender='0'
</select>
接口 EmployeeMapper.java
@MapKey("id")
public Map<Integer, Employee> getEmpByGenderReturnMap(String gender);
测试方法:
@Test
public void test10() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map<Integer, Employee> map = mapper.getEmpByGenderReturnMap("0");
System.out.println(map);
} finally {
sqlSession.close();
}
}
控制台结果:
[main] [com.example.mapper.EmployeeMapper.getEmpByGenderReturnMap]-[DEBUG] ==> Preparing: select * from tbl_employee where gender='0'
[main] [com.example.mapper.EmployeeMapper.getEmpByGenderReturnMap]-[DEBUG] ==> Parameters:
[main] [com.example.mapper.EmployeeMapper.getEmpByGenderReturnMap]-[DEBUG] <== Total: 4
{1=Employee{id=1, lastName='tom', email='tom@123.com', gender='0', department=null}, 2=Employee{id=2, lastName='jerry', email='jerry@123.com', gender='0', department=null}, 3=Employee{id=3, lastName='jack', email='jack@123.com', gender='0', department=null}, 8=Employee{id=8, lastName='lisi', email='lisi@123.com', gender='0', department=null}}
如果需要将多条记录封装成map,需要通过 @MapKey 注解来告诉 Mybatis,在封装 map 的时要用哪个属性来作为 map 的 key。
二、resultMap
如果在 SQL 映射文件中配置的 SQL 语句返回的结果为多个值,且没有一个完全与返回结果值一一匹配的封装类去接收,或者此时希望使用一个容器来接收结果数据。为此,Mybatis 提供了一种 SQL 结果集输出映射类型,即 resultMap。可以通过定义一个 resultMap 在列名和 Java 包装类属性名之间创建映射关系。
情况一
EmployeeMapper.xml
<resultMap id="MyEmp" type="com.example.pojo.Employee">
<!--
指定主键列的封装规则
id 定义主键会在底层有优化, column 指定哪一列,property 指定对应的 javaBean 属性
-->
<id column="id" property="id"></id>
<!--定义普通列的封装规则-->
<result column="last_name" property="lastName"></result>
<!--其他不指定的列会自动封装:推荐全部写上-->
<result column="email" property="email"></result>
<result column="gender" property="gender"></result>
</resultMap>
<select id="getEmpByIdForResultMap" resultMap="MyEmp">
select * from tbl_employee where id = #{id}
</select>
接口 EmployeeMapper.java
public Employee getEmpByIdForResultMap(Integer id);
测试方法:
@Test
public void test11() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmpByIdForResultMap(2);
System.out.println(employee);
} finally {
sqlSession.close();
}
}
控制台结果:
[main] [com.example.mapper.EmployeeMapper.getEmpByIdForResultMap]-[DEBUG] ==> Preparing: select * from tbl_employee where id = ?
[main] [com.example.mapper.EmployeeMapper.getEmpByIdForResultMap]-[DEBUG] ==> Parameters: 2(Integer)
[main] [com.example.mapper.EmployeeMapper.getEmpByIdForResultMap]-[DEBUG] <== Total: 1
Employee{id=2, lastName='jerry', email='jerry@123.com', gender='0'}
情况二
创建 tbl_dept 表:
-- 创建部门表
create table tbl_dept(
id int(11) PRIMARY KEY auto_increment,
dept_name varchar(255)
);
-- 向部门表中插入数据
insert into tbl_dept (id, dept_name) values (1, '开发部');
insert into tbl_dept (id, dept_name) values (2, '产品部');
-- 为员工表新增部门id字段
alter table tbl_employee add column dept_id int(11);
-- 为员工表设置外键
alter table tbl_employee add CONSTRAINT fk_emp_dept
FOREIGN key (dept_id) REFERENCES tbl_dept(id);
-- 更新员工表信息
update tbl_employee set dept_id=1 where id < 4;
update tbl_employee set dept_id=2 where id > 4;
实体类:
Department.java
package com.example.pojo;
public class Department {
private Integer id;
private String deptName;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
@Override
public String toString() {
return "Department{" +
"id=" + id +
", deptName='" + deptName + '\'' +
'}';
}
}
Employee.java
package com.example.pojo;
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
private Department dept;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Department getDept() {
return dept;
}
public void setDept(Department dept) {
this.dept = dept;
}
@Override
public String toString() {
return "Employee{" +
"id=" + id +
", lastName='" + lastName + '\'' +
", email='" + email + '\'' +
", gender='" + gender + '\'' +
", department=" + dept +
'}';
}
}
EmployeeMapper.java
public Employee getEmpAndDept(Integer id);
测试方法:
@Test
public void test12() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmpAndDept(2);
System.out.println(employee);
} finally {
sqlSession.close();
}
}
EmployeeMapper.xml
<resultMap id="MyDifEmp" type="com.example.pojo.Employee">
<id column="id" property="id"></id>
<result column="last_name" property="lastName"></result>
<result column="gender" property="gender"></result>
<result column="email" property="email"></result>
<result column="dept_id" property="dept.id"></result>
<result column="dept_name" property="dept.deptName"></result>
</resultMap>
<select id="getEmpAndDept" resultMap="MyDifEmp">
select e.id id,e.last_name last_name, e.gender gender,
e.email email, e.dept_id dept_id, d.dept_name dept_name
from tbl_employee e
join tbl_dept d on e.dept_id=d.id
where e.id = #{id}
</select>
或者:
<resultMap id="MyDifEmp2" type="com.example.pojo.Employee">
<id column="id" property="id"></id>
<result column="last_name" property="lastName"></result>
<result column="gender" property="gender"></result>
<result column="email" property="email"></result>
<!--
association可以指定联合的 JavaBean 对象
property:指定哪个属性是联合的对象
javaType:制定这个属性对象的类型(不能省略)
-->
<association property="dept" javaType="com.example.pojo.Department">
<id column="dept_id" property="id"></id>
<result column="dept_name" property="deptName"></result>
</association>
</resultMap>
<select id="getEmpAndDept" resultMap="MyDifEmp2">
select e.id id,e.last_name last_name, e.gender gender,
e.email email, e.dept_id dept_id, d.dept_name dept_name
from tbl_employee e
join tbl_dept d on e.dept_id=d.id
where e.id = #{id}
</select>
控制台结果:
[main] [com.example.mapper.EmployeeMapper.getEmpAndDept]-[DEBUG] ==> Preparing: select e.id id,e.last_name last_name, e.gender gender, e.email email, e.dept_id dept_id, d.dept_name dept_name from tbl_employee e join tbl_dept d on e.dept_id=d.id where e.id = ?
[main] [com.example.mapper.EmployeeMapper.getEmpAndDept]-[DEBUG] ==> Parameters: 2(Integer)
[main] [com.example.mapper.EmployeeMapper.getEmpAndDept]-[DEBUG] <== Total: 1
Employee{id=2, lastName='jerry', email='jerry@123.com', gender='0', department=Department{id=1, deptName='开发部'}}
情况三:association 分步查询
1、创建 DepartmentMapper.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.example.mapper.DepartmentMapper">
<select id="getDeptById" resultType="com.example.pojo.Department">
select * from tbl_dept where id = #{id}
</select>
</mapper>
接口 DepartmentMapper.java
package com.example.mapper;
import com.example.pojo.Department;
public interface DepartmentMapper {
public Department getDeptById(Integer id);
}
EmployeeMapper.xml
<!--
association定义关联对象的封装规则
select:表明当前属性是调用 select 指定的方法查出的结果
column:指定将哪一列的值传给这个方法
流程:使用 select 指定的方法(传入 column 指定的这列参数的值)查出对象,并封装给 property 指定的属性
-->
<resultMap id="MyEmpByStep" type="com.example.pojo.Employee">
<id column="id" property="id"></id>
<result column="last_name" property="lastName"></result>
<result column="email" property="email"></result>
<result column="gender" property="gender"></result>
<association property="dept"
select="com.example.mapper.DepartmentMapper.getDeptById"
column="dept_id"></association>
</resultMap>
<select id="getEmpByIdStep" resultMap="MyEmpByStep">
select * from tbl_employee where id=#{id}
</select>
接口 EmployeeMapper.java
public Employee getEmpByIdStep(Integer id);
测试方法:
@Test
public void test13() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmpByIdStep(2);
System.out.println(employee);
} finally {
sqlSession.close();
}
}
控制台结果:
[main] [com.example.mapper.EmployeeMapper.getEmpByIdStep]-[DEBUG] ==> Preparing: select * from tbl_employee where id=?
[main] [com.example.mapper.EmployeeMapper.getEmpByIdStep]-[DEBUG] ==> Parameters: 2(Integer)
[main] [com.example.mapper.DepartmentMapper.getDeptById]-[DEBUG] ====> Preparing: select * from tbl_dept where id = ?
[main] [com.example.mapper.DepartmentMapper.getDeptById]-[DEBUG] ====> Parameters: 1(Integer)
[main] [com.example.mapper.DepartmentMapper.getDeptById]-[DEBUG] <==== Total: 1
[main] [com.example.mapper.EmployeeMapper.getEmpByIdStep]-[DEBUG] <== Total: 1
Employee{id=2, lastName='jerry', email='jerry@123.com', gender='0', department=Department{id=1, deptName='开发部'}}
2、分步查询的延迟加载
延迟加载,或者懒加载,按需加载,只有当调用到关联的数据时,才与数据库发生交互,否则不交互
在全局配置文件中进行如下配置:
<settings>
<!--
lazyLoadingEnabled 设置全局性懒加载,可设置的值为“true”和“false”。如果设为“false”,则所有相关联的数据都会被初始化加载,否噢则会延迟加载相关联的数据
-->
<setting name="lazyLoadingEnabled" value="true"/>
<!--
aggressiveLazyLoading侵入性延迟加载,控制具有懒加载特性的对象的属性的加载情况的。
true表示如果对具有懒加载特性的对象的任意调用会导致这个对象的完整加载,
false表示每种属性按照需要加载。-->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
测试代码:
@Test
public void test13() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmpByIdStep(2);
System.out.println(employee.getLastName());
} finally {
sqlSession.close();
}
}
上述代码中未调用 Department 对象中的方法,控制台结果:
[main] [com.example.mapper.EmployeeMapper.getEmpByIdStep]-[DEBUG] ==> Preparing: select * from tbl_employee where id=?
[main] [com.example.mapper.EmployeeMapper.getEmpByIdStep]-[DEBUG] ==> Parameters: 2(Integer)
[main] [com.example.mapper.EmployeeMapper.getEmpByIdStep]-[DEBUG] <== Total: 1
jerry
增加对 Department 对象的任意调用,如下:
@Test
public void test13() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = mapper.getEmpByIdStep(2);
System.out.println(employee.getLastName());
System.out.println(employee.getDept());
} finally {
sqlSession.close();
}
}
控制台结果:
[main] [com.example.mapper.EmployeeMapper.getEmpByIdStep]-[DEBUG] ==> Preparing: select * from tbl_employee where id=?
[main] [com.example.mapper.EmployeeMapper.getEmpByIdStep]-[DEBUG] ==> Parameters: 2(Integer)
[main] [com.example.mapper.EmployeeMapper.getEmpByIdStep]-[DEBUG] <== Total: 1
jerry
[main] [com.example.mapper.DepartmentMapper.getDeptById]-[DEBUG] ==> Preparing: select * from tbl_dept where id = ?
[main] [com.example.mapper.DepartmentMapper.getDeptById]-[DEBUG] ==> Parameters: 1(Integer)
[main] [com.example.mapper.DepartmentMapper.getDeptById]-[DEBUG] <== Total: 1
Department{id=1, deptName='开发部'}
情况四:collection 定义关联集合封装规则
resultMap 下的 collection 标签可以用来声明 List 集合中属性的类型。
案例:查询某个部门下的所有员工,属于一对多情况。
实体类Department.java
package com.example.pojo;
import java.util.*;
public class Department {
private Integer id;
private String deptName;
private List<Employee> list;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public List<Employee> getList() {
return list;
}
public void setList(List<Employee> list) {
this.list = list;
}
@Override
public String toString() {
return "Department{" +
"id=" + id +
", deptName='" + deptName + '\'' +
", list=" + list +
'}';
}
}
DepartmentMapper.xml
<resultMap id="getDeptInfo" type="com.example.pojo.Department">
<id column="id" property="id"></id>
<result column="dept_name" property="deptName"></result>
<collection property="list" ofType="com.example.pojo.Employee">
<id column="eid" property="id"></id>
<result column="last_name" property="lastName"></result>
<result column="email" property="email"></result>
<result column="gender" property="gender"></result>
</collection>
</resultMap>
<select id="getDeptByIdPlus" resultMap="getDeptInfo">
select d.id id, d.dept_name, e.id eid, e.last_name, e.email, e.gender
from tbl_dept d
left join tbl_employee e on d.id=e.dept_id
where d.id = #{id}
</select>
接口 DepartmentMapper.java
package com.example.mapper;
import com.example.pojo.Department;
public interface DepartmentMapper {
public Department getDeptByIdPlus(Integer id);
}
测试方法:
@Test
public void test14() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
Department dept = mapper.getDeptByIdPlus(1);
System.out.println(dept);
} finally {
sqlSession.close();
}
}
控制台结果:
[main] [com.example.mapper.DepartmentMapper.getDeptByIdPlus]-[DEBUG] ==> Preparing: select d.id id, d.dept_name, e.id eid, e.last_name, e.email, e.gender from tbl_dept d left join tbl_employee e on d.id=e.dept_id where d.id = ?
[main] [com.example.mapper.DepartmentMapper.getDeptByIdPlus]-[DEBUG] ==> Parameters: 1(Integer)
[main] [com.example.mapper.DepartmentMapper.getDeptByIdPlus]-[DEBUG] <== Total: 3
Department{id=1, deptName='开发部', list=[Employee{id=1, lastName='tom', email='tom@123.com', gender='0', department=null}, Employee{id=2, lastName='jerry', email='jerry@123.com', gender='0', department=null}, Employee{id=3, lastName='jack', email='jack@123.com', gender='0', department=null}]}
情况五:collection 分步查询以及延迟加载
1、分步查询
DepartmentMapper.xml
<resultMap id="MyDeptStep" type="com.example.pojo.Department">
<id column="id" property="id"></id>
<result column="dept_name" property="deptName"></result>
<collection property="list" select="com.example.mapper.EmployeeMapper.getAllEmpByDeptId" column="id">
<id column="id" property="id"></id>
<result column="last_name" property="lastName"></result>
<result column="email" property="email"></result>
<result column="gender" property="gender"></result>
</collection>
</resultMap>
<select id="getDeptByIdStep" resultMap="MyDeptStep">
select * from tbl_dept where id = #{id}
</select>
DepartmentMapper.java
package com.example.mapper;
import com.example.pojo.Department;
public interface DepartmentMapper {
public Department getDeptByIdStep(Integer id);
}
EmployeeMapper.xml
<select id="getAllEmpByDeptId" resultType="com.example.pojo.Employee">
select * from tbl_employee where dept_id = #{id}
</select>
package com.example.mapper;
import com.example.pojo.Employee;
import java.util.List;
public interface EmployeeMapper {
public List<Employee> getAllEmpByDeptId(Integer id);
}
测试方法:
@Test
public void test15() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
Department dept = mapper.getDeptByIdStep(1);
System.out.println(dept);
} finally {
sqlSession.close();
}
}
控制台结果:
[main] [com.example.mapper.DepartmentMapper.getDeptByIdStep]-[DEBUG] ==> Preparing: select * from tbl_dept where id = ?
[main] [com.example.mapper.DepartmentMapper.getDeptByIdStep]-[DEBUG] ==> Parameters: 1(Integer)
[main] [com.example.mapper.DepartmentMapper.getDeptByIdStep]-[DEBUG] <== Total: 1
[main] [com.example.mapper.EmployeeMapper.getAllEmpByDeptId]-[DEBUG] ==> Preparing: select * from tbl_employee where dept_id = ?
[main] [com.example.mapper.EmployeeMapper.getAllEmpByDeptId]-[DEBUG] ==> Parameters: 1(Integer)
[main] [com.example.mapper.EmployeeMapper.getAllEmpByDeptId]-[DEBUG] <== Total: 3
Department{id=1, deptName='开发部', list=[Employee{id=1, lastName='tom', email='tom@123.com', gender='0', department=null}, Employee{id=2, lastName='jerry', email='jerry@123.com', gender='0', department=null}, Employee{id=3, lastName='jack', email='jack@123.com', gender='0', department=null}]}
2、延迟加载
测试代码:
只输出部门名称,未输出人员相关信息
@Test
public void test15() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
Department dept = mapper.getDeptByIdStep(1);
System.out.println(dept.getDeptName());
} finally {
sqlSession.close();
}
}
控制台结果:
[main] [com.example.mapper.DepartmentMapper.getDeptByIdStep]-[DEBUG] ==> Preparing: select * from tbl_dept where id = ?
[main] [com.example.mapper.DepartmentMapper.getDeptByIdStep]-[DEBUG] ==> Parameters: 1(Integer)
[main] [com.example.mapper.DepartmentMapper.getDeptByIdStep]-[DEBUG] <== Total: 1
开发部
根据控制台结果可以看出,在未输出人员信息的情况下,只执行了部门相关的SQL语句。
输出人员相关信息
@Test
public void test15() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession sqlSession = sqlSessionFactory.openSession();
try{
DepartmentMapper mapper = sqlSession.getMapper(DepartmentMapper.class);
Department dept = mapper.getDeptByIdStep(1);
System.out.println(dept.getDeptName());
System.out.println(dept.getList());
} finally {
sqlSession.close();
}
}
控制台结果:
main] [com.example.mapper.DepartmentMapper.getDeptByIdStep]-[DEBUG] ==> Preparing: select * from tbl_dept where id = ?
[main] [com.example.mapper.DepartmentMapper.getDeptByIdStep]-[DEBUG] ==> Parameters: 1(Integer)
[main] [com.example.mapper.DepartmentMapper.getDeptByIdStep]-[DEBUG] <== Total: 1
开发部
[main] [com.example.mapper.EmployeeMapper.getAllEmpByDeptId]-[DEBUG] ==> Preparing: select * from tbl_employee where dept_id = ?
[main] [com.example.mapper.EmployeeMapper.getAllEmpByDeptId]-[DEBUG] ==> Parameters: 1(Integer)
[main] [com.example.mapper.EmployeeMapper.getAllEmpByDeptId]-[DEBUG] <== Total: 3
[Employee{id=1, lastName='tom', email='tom@123.com', gender='0', department=null}, Employee{id=2, lastName='jerry', email='jerry@123.com', gender='0', department=null}, Employee{id=3, lastName='jack', email='jack@123.com', gender='0', department=null}]
根据控制台结果可以看出,在部门信息及人员信息均输出的情况下,两条SQL都执行了。
association或者collection 标签补充知识
1、分步查询时,当有多列的值传递到 column 中,将多列的值封装进 map 中进行传递:column=“{key1=column1,key2=column2}”
2、association 或者 collection 标签的 fetchType=eager/lazy 可以覆盖全局的延迟加载策略,指定立即加载(eager)或者延迟加载(lazy)