官方映射文件介绍
概述
映射文件指导着MyBatis如何进行数据库增删改查, 有着非常重要的意义
- cache –命名空间的二级缓存配置
- cache-ref – 其他命名空间缓存配置的引用。
- resultMap – 自定义结果集映射
- parameterMap – 已废弃!老式风格的参数映射
- sql –抽取可重用语句块。
- insert – 映射插入语句
- update – 映射更新语句
- delete – 映射删除语句
- select – 映射查询语句
增删改查
Employee.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
package com.eh.eden.mybatis.orm.bean;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Employee {
private Integer id;
private String lastName;
private String gender;
private String email;
}
|
EmployeeMapper.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
package com.eh.eden.mybatis.orm.dao;
import com.eh.eden.mybatis.orm.bean.Employee;
public interface EmployeeMapper {
void insertEmployee(Employee employee);
void deleteEmployee(Integer id);
void updateEmployeeById(Employee employee);
Employee getEmployeeById(Integer id);
}
|
EmployeeMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
<?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.eh.eden.mybatis.orm.dao.EmployeeMapper">
<!--parameterType可省略,建议把参数类型和结果类型都写上-->
<insert id="insertEmployee" parameterType="com.eh.eden.mybatis.orm.bean.Employee">
insert into tbl_employee(last_name, gender, email) values(#{lastName}, #{gender}, #{email});
</insert>
<delete id="deleteEmployee">
delete from tbl_employee where id = #{id}
</delete>
<update id="updateEmployeeById">
update tbl_employee
set last_name = #{lastName},
gender = #{gender},
email = #{email}
where id = #{id}
</update>
<select id="getEmployeeById" resultType="com.eh.eden.mybatis.orm.bean.Employee">
select id, last_name, gender, email from tbl_employee where id = #{id}
</select>
</mapper>
|
测试类:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
/**
* 测试增删改
* 1. mybatis允许增删改直接定义以下类型返回值
* Integer、Long、Boolean
* 2. 需要手动提交数据
* sqlSessionFactory.openSession(true) 自动提交
*/
@Test
public void test1() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
// openSession默认参数是false,获取的session不会自动提交
try (SqlSession session = sqlSessionFactory.openSession()) {
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
// 增
employeeMapper.insertEmployee(new Employee(null, "张三", "0", "zs@gmail.com"));
// 删
// employeeMapper.deleteEmployee(3);
// 改
// employeeMapper.updateEmployeeById(new Employee(1, "王五", "1", "w5@gmail.com"));
// 查
// System.out.println(employeeMapper.getEmployeeById(1));
// 手动提交
session.commit();
}
|
插入时获取自增主键的值
EmployeeMapper.xml
1
2
3
4
5
6
7
8
9
|
<!--
获取自增主键的值:
mysql支持自增主键,自增主键值的获取,mybatis也是利用statement.getGeneratedKeys();
useGeneratedKeys="true";使用自增主键获取主键值策略
keyProperty;指定对应的主键属性,也就是mybatis获取到主键值以后,将这个值封装给javaBean的哪个属性
-->
<insert id="insertEmployee" useGeneratedKeys="true" keyProperty="id">
insert into tbl_employee(last_name, gender, email) values(#{lastName}, #{gender}, #{email});
</insert>
|
测试类:
1
2
3
4
5
6
7
8
9
10
11
|
@Test
public void test1() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
try (SqlSession session = sqlSessionFactory.openSession(true)) {
EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
Employee employee = new Employee(null, "张三", "0", "zs@gmail.com");
employeeMapper.insertEmployee(employee);
System.out.println(employee.getId());
}
}
|
扩展:Oracle中不支持自增,Oracle使用序列来模拟自增;每次插入数据的主键是从序列中拿到的值,如下所示:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
<!--
获取非自增主键的值:
Oracle不支持自增;Oracle使用序列来模拟自增;
每次插入的数据的主键是从序列中拿到的值;如何获取到这个值;
-->
<insert id="addEmp" databaseId=" ">
<!--
keyProperty:查出的主键值封装给javaBean的哪个属性
order="BEFORE":当前sql在插入sql之前运行
AFTER:当前sql在插入sql之后运行
resultType:查出的数据的返回值类型
BEFORE运行顺序:
先运行selectKey查询id的sql;查出id值封装给javaBean的id属性
在运行插入的sql;就可以取出id属性对应的值
AFTER运行顺序:
先运行插入的sql(从序列中取出新值作为id);
再运行selectKey查询当前id的sql;
-->
<selectKey keyProperty="id" order="BEFORE" resultType="Integer">
<!-- 编写查询主键的sql语句 -->
<!-- BEFORE-->
select EMPLOYEES_SEQ.nextval from dual
<!-- AFTER:
select EMPLOYEES_SEQ.currval from dual -->
</selectKey>
<!-- 插入时的主键是从序列中拿到的 -->
<!-- BEFORE:-->
insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL)
values(#{id},#{lastName},#{email<!-- ,jdbcType=NULL -->})
<!-- AFTER:
insert into employees(EMPLOYEE_ID,LAST_NAME,EMAIL)
values(employees_seq.nextval,#{lastName},#{email}) -->
</insert>
|
注:AFTER可能会存在问题,因为拿的是运行之后序列里的值,可能一次插入很多条,拿到的值并不一定就是自己插入时用的序列值。所以主要是BEFORE方式。
参数处理
单个参数
mybatis不会做特殊处理; #{参数名/任意名}
:取出参数值。例:#{id}
1
2
3
|
<select id="getEmployeeById" resultType="com.eh.eden.mybatis.orm.bean.Employee">
select id, last_name, gender, email from tbl_employee where id = #{idabc}
</select>
|
多个参数
1
2
3
4
5
6
|
<!--
Employee getEmployeeByIdAndName(Integer id, String name);
-->
<select id="getEmployeeByIdAndName" resultType="com.eh.eden.mybatis.orm.bean.Employee">
select id, last_name, gender, email from tbl_employee where id = #{id} and last_name = #{lastName}
</select>
|
多个参数:mybatis会做特殊处理,多个参数会被封装成 一个map
key:param1...paramN,或者参数的索引也可以 value:传入的参数值
如果还按照单个参数的写法#{id},#{lastName}
就会抛异常(如下代码)
1
2
3
4
5
6
7
|
异常:
org.apache.ibatis.binding.BindingException:
Parameter 'id' not found.
Available parameters are [1, 0, param1, param2]
操作:
方法:public Employee getEmpByIdAndLastName(Integer id,String lastName);
取值:#{id},#{lastName}
|
可以使用#{param1}、#{para2}进行取值,或者参数的索引也可以
1
2
3
|
<select id="getEmployeeByIdAndName" resultType="com.eh.eden.mybatis.orm.bean.Employee">
select id, last_name, gender, email from tbl_employee where id = #{param1} and last_name = #{param2}
</select>
|
当然,上面这种做法可读性差,不建议。
这里多个参数取值就需要使用命名参数来解决问题,例子如下。
命名参数:明确指定封装参数时map的key:@Param(“id”), 多个参数会被封装成一个map
key:使用@Param注解指定的值 value:参数值 #{指定的key}取出对应的参数值
1
|
Employee getEmployeeByIdAndName(@Param("id") Integer id, @Param("lastName") String lastName);
|
1
2
3
|
<select id="getEmployeeByIdAndName" resultType="com.eh.eden.mybatis.orm.bean.Employee">
select id, last_name, gender, email from tbl_employee where id = #{id} and last_name = #{lastName}
</select>
|
POJO
若多个参数正好是我们业务逻辑的数据模型,我们就可以直接传入pojo;#{属性名}:取出传入的pojo的属性值
Map
若多个参数不是业务模型中的数据,没有对应的pojo,不经常使用,为了方便,我们也可以传入map
#{key}:取出map中对应的值
1
|
Employee getEmployeeByMap(Map<String, Object> map);
|
1
|
System.out.println(employeeMapper.getEmployeeByMap(ImmutableMap.of("id", 1, "lastName", "王五")));
|
1
2
3
|
<select id="getEmployeeByMap" resultType="com.eh.eden.mybatis.orm.bean.Employee">
select id, last_name, gender, email from tbl_employee where id = #{id} and last_name = #{lastName}
</select>
|
TO
如果多个参数不是业务模型中的数据,但是要经常使用,推荐来写一个TO(Transfer Object)数据传输对象。eg:Page分页对象
细节
细节一
1
|
Employee getEmp(@Param("id") Integer id, String lastName);
|
取值:id => #{id/param1} lastName => #{param2}
细节二
1
|
Employee getEmp(Integer id, @Param("e") Employee emp);
|
取值:id => #{param1} lastName => #{e.lastName / param2.lastName}
细节三
特别注意,如果是Collection(List、Set)类型或者数组,也会特殊处理。也是把传入的List或者数组封装在Map中。
key: Collection(collection),如果是List还可以使用这个key(list),数组(array)
1
|
Employee getEmps(List<Integer> ids);
|
取第一个值,#{list[0]}
参数处理源码分析
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
// 将传入参数转为sql映射文件中可执行参数 convertArgsToSqlCommandParam下方法
args : [1, "王五"]
names: sortedMap {0->"id", 1->"lastName"}
public Object getNamedParams(Object[] args) {
final int paramCount = names.size();
// 1. 参数为null直接返回
if (args == null || paramCount == 0) {
return null;
// 2. 如果只有一个元素并且没有注解,args[0]
} else if (!hasParamAnnotation && paramCount == 1) {
Object value = args[names.firstKey()];
// 如果只有一个参数,还需要区分处理集合、数组,如果只是普通单个参数直接返回
return wrapToMapIfCollection(value, useActualParamName ? names.get(0) : null);
// 3. 多个元素或者有param注解
} else {
final Map<String, Object> param = new ParamMap<>();
int i = 0;
// 遍历names集合 {0->"id", 1->"lastName"}
for (Map.Entry<Integer, String> entry : names.entrySet()) {
// names集合的value作为key => id,key作为取值的参考args[0] => 1
param.put(entry.getValue(), args[entry.getKey()]);
// add generic param names (param1, param2, ...)
// GENERIC_NAME_PREFIX => param
final String genericParamName = GENERIC_NAME_PREFIX + (i + 1);
// 额外的将每一个参数也保存到map中,使用新的key:param1...param2,也就是param这个map同时存在{id=>1,param1=>1}
// 所以最终效果:有Param注解可以#{id},或者#{param1}
// ensure not to overwrite parameter named with @Param
if (!names.containsValue(genericParamName)) {
param.put(genericParamName, args[entry.getKey()]);
}
i++;
}
return param;
}
}
|
参数值的获取方式
#{}
和${}
-
两者都可以获取map中的值或者pojo中属性的值
-
区别:
#{}
:是以预编译的形式,将参数设置到sql语句中;PreparedStatement;防止sql注入
${}
:取出的值直接拼装在sql语句中;会有安全问题;
-
大多情况下,我们去参数的值都应该去使用#{}
;
-
原生jdbc不支持占位符的地方我们就可以使用${}
进行取值 比如分表、排序。。。;
select * from tbl_employee order by ${f_name} ${order}
按照年份分表拆分
select * from ${year}_salary where xxx;
1
2
3
|
<select id="getEmployeeByIdAndName" resultType="com.eh.eden.mybatis.orm.bean.Employee">
select id, last_name, gender, email from tbl_employee where id = ${id} and last_name = #{lastName}
</select>
|
日志:
1
2
3
4
5
6
7
8
9
|
20201008 23:21:35 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection
20201008 23:21:35 [main] DEBUG o.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 1169474473.
20201008 23:21:35 [main] DEBUG c.e.e.m.o.d.EmployeeMapper.getEmployeeByIdAndName - ==> Preparing: select id, last_name, gender, email from tbl_employee where id = 1 and last_name = ?
20201008 23:21:35 [main] DEBUG c.e.e.m.o.d.EmployeeMapper.getEmployeeByIdAndName - ==> Parameters: 王五(String)
20201008 23:21:35 [main] TRACE c.e.e.m.o.d.EmployeeMapper.getEmployeeByIdAndName - <== Columns: id, last_name, gender, email
20201008 23:21:35 [main] TRACE c.e.e.m.o.d.EmployeeMapper.getEmployeeByIdAndName - <== Row: 1, 王五, 1, w5@gmail.com
20201008 23:21:35 [main] DEBUG c.e.e.m.o.d.EmployeeMapper.getEmployeeByIdAndName - <== Total: 1
20201008 23:21:35 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@45b4c3a9]
20201008 23:21:35 [main] DEBUG o.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 1169474473 to pool.
|
演示
查询语句中的表名设置为动态取值
1
2
3
|
System.out.println(employeeMapper.
getEmployeeByDynamicTable(ImmutableMap.
of("tableName", "tbl_employee", "id", 1, "lastName", "王五")));
|
表名取值用#{}
会报错,换成${}
则ok
1
2
3
|
<select id="getEmployeeByDynamicTable" resultType="com.eh.eden.mybatis.orm.bean.Employee">
select id, last_name, gender, email from ${tableName} where id = #{id} and last_name = #{lastName}
</select>
|
#{}
更丰富的用法
规定参数的一些规则:
javaType、 jdbcType、 mode(存储过程)、 numericScale、resultMap、 typeHandler、 jdbcTypeName、 expression(未来准备支持的功能);
jdbcType通常需要在某种特定的条件下被设置:
在我们数据为null的时候,有些数据库可能不能识别mybatis对null的默认处理。比如Oracle(报错): JdbcType OTHER:无效的类型;
因为mybatis对所有的null都映射的是原生Jdbc的OTHER类型,oracle不能正确处理;由于全局配置中:jdbcTypeForNull=OTHER;oracle不支持;两种办法
- 在sql映射文件中, 取值时使用
#{email,jdbcType=NULL}
;
- 全局设置
<setting name="jdbcTypeForNull" value="NULL"/>
注意
jdbcTypeForNull=OTHER 或者 jdbcTypeForNull=NULL, mysql都支持
查

resultType
-
基本类型或对象类型
resultType = 基本类型或对象类型
-
返回List类型
resultType = List中元素的类型
-
Map类型
返回List
resultType使用集合里元素的类型
1
2
3
4
|
<!--List<Employee> getEmployeeByLastNameLike(String lastNameLike);-->
<select id="getEmployeeByLastNameLike" resultType="com.eh.eden.mybatis.orm.bean.Employee">
select id, last_name, gender, email from tbl_employee where last_name like #{lastName}
</select>
|
返回Map
单条记录
1
|
Map<String, Object> getSingleMapById(Integer id);
|
1
2
3
|
<select id="getSingleMapById" resultType="map">
select * from tbl_employee where id = #{id}
</select>
|
1
|
{gender=1, last_name=王五, id=1, email=w5@gmail.com}
|
多条记录
1
2
|
@MapKey("id")
Map<Integer, Employee> getMultiMapByLastNameLike(String lastNameLike);
|
1
2
3
|
<select id="getMultiMapByLastNameLike" resultType="map">
select * from tbl_employee where last_name like #{lastNameLike}
</select>
|
1
|
{1={gender=1, last_name=王五, id=1, email=w5@gmail.com}}
|
resultMap
自定义结果集映射
使用自定义结果集映射可以取消驼峰法命名设置。
EmployeeMapperPlus.java
1
2
3
4
5
6
7
8
|
package com.eh.eden.mybatis.orm.dao;
import com.eh.eden.mybatis.orm.bean.Employee;
public interface EmployeeMapperPlus {
Employee getEmployeeById(Integer id);
}
|
EmployeeMapperPlus.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
<?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.eh.eden.mybatis.orm.dao.EmployeeMapperPlus">
<!--自定义某个javaBean的封装规则
type:自定义规则的Java类型
id:唯一id方便引用
-->
<resultMap id="employee" type="com.eh.eden.mybatis.orm.bean.Employee">
<!--指定主键列的封装规则
id定义主键会底层有优化;
column:指定哪一列
property:指定对应的javaBean属性
-->
<id column="id" property="id"/>
<!-- 定义普通列封装规则 -->
<result column="last_name" property="lastName"/>
<!-- 其他不指定的列会自动封装:我们只要写resultMap就把全部的映射规则都写上。 -->
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</resultMap>
<!-- resultMap:自定义结果集映射规则; -->
<select id="getEmployeeById" resultMap="employee">
select * from tbl_employee where id = #{id}
</select>
</mapper>
|
测试类:
1
2
3
4
5
6
7
8
9
10
|
@Test
public void test1() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
try (SqlSession session = sqlSessionFactory.openSession(true)) {
EmployeeMapperPlus employeeMapper = session.getMapper(EmployeeMapperPlus.class);
Employee employee = employeeMapper.getEmployeeById(1);
System.out.println(employee);
}
}
|
关联查询
注意
场景一:
查询Employee的同时查询员工对应的部门
Employee===Department
数据准备
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
create table tbl_dept(
id int(11) PRIMARY KEY auto_increment,
dept_name VARCHAR(255)
);
insert into tbl_dept(dept_name) values('开发部');
insert into tbl_dept(dept_name) values('测试部');
-- 员工表增加部门id列
alter table tbl_employee add COLUMN dept_id int(11);
-- 员工表部门id列增加外键约束
alter table tbl_employee add CONSTRAINT fk_emp_dept
FOREIGN KEY(dept_id) REFERENCES tbl_dept(id);
select e.*, d.id d_id, d.dept_name d_name from tbl_employee e, tbl_dept d
where e.dept_id = d.id
and e.id = 1;
|
级联属性封装结果
Dept.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
package com.eh.eden.mybatis.orm.bean;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Dept {
private Integer id;
private String name;
}
|
Employee.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
package com.eh.eden.mybatis.orm.bean;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Employee {
private Integer id;
private String lastName;
private String gender;
private String email;
private Dept dept;
}
|
EmployeeMapperPlus.java
1
2
3
4
5
6
7
8
|
package com.eh.eden.mybatis.orm.dao;
import com.eh.eden.mybatis.orm.bean.Employee;
public interface EmployeeMapperPlus {
Employee getUnionEmployeeById(Integer id);
}
|
EmployeeMapperPlus.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
<resultMap id="unionEmployee" type="com.eh.eden.mybatis.orm.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<result column="d_id" property="dept.id"/>
<result column="d_name" property="dept.name"/>
</resultMap>
<select id="getUnionEmployeeById" resultMap="unionEmployee">
select e.*, d.id d_id, d.dept_name d_name from tbl_employee e, tbl_dept d
where e.dept_id = d.id
and e.id = #{id};
</select>
|
测试类:
1
2
3
4
5
6
7
8
9
10
11
12
|
@Test
public void test1() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
try (SqlSession session = sqlSessionFactory.openSession(true)) {
EmployeeMapperPlus employeeMapper = session.getMapper(EmployeeMapperPlus.class);
Employee employee = employeeMapper.getUnionEmployeeById(1);
System.out.println(employee);
}
}
// 运行结果
Employee(id=1, lastName=王五, gender=1, email=w5@gmail.com, dept=Dept(id=1, name=开发部))
|
使用association定义关联的单个对象的封装规则
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
<resultMap id="unionEmployee" type="com.eh.eden.mybatis.orm.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>
<!-- association可以指定联合的javaBean对象
property="dept":指定哪个属性是联合的对象
javaType:指定这个属性对象的类型[可以省略,省略之后id会报红字但不影响正常运行]
-->
<association property="dept" javaType="com.eh.eden.mybatis.orm.bean.Dept">
<id column="d_id" property="id"/>
<result column="d_name" property="name"/>
</association>
</resultMap>
|
使用association进行分步查询
分布查询的受益者是数据库,如果连接查询,会发生Nested Loop
实际开发中dept的查询已经写好了, 可以直接使用dept的查询语句
- 先按照员工id查询员工信息
- 根据查询员工信息中的d_id值去部门表查出部门信息
- 部门设置到员工中;
Dept.java
1
2
3
4
5
6
7
8
9
|
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Dept {
private Integer id;
private String deptName;
}
|
DeptMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
|
<?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.eh.eden.mybatis.orm.dao.DeptMapper">
<select id="getDeptById" resultType="com.eh.eden.mybatis.orm.bean.Dept">
select id, dept_name deptName from tbl_dept where id = #{id}
</select>
</mapper>
|
EmployeeMapperPlus.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
<resultMap id="stepEmployee" type="com.eh.eden.mybatis.orm.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
<!-- association定义关联对象的封装规则
select:表明当前属性是调用select指定的方法查出的结果
column:指定将哪一列的值传给这个方法
流程:使用select指定的方法(传入column指定的这列参数的值)查出对象,并封装给property指定的属性
-->
<association property="dept"
select="com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById"
column="d_id">
</association>
</resultMap>
<select id="getEmployeeByIdStep" resultMap="stepEmployee">
select e.*, d.id d_id, d.dept_name d_name from tbl_employee e, tbl_dept d
where e.dept_id = d.id
and e.id = #{id};
</select>
|
分布查询可以使用延迟加载(又叫懒加载、按需加载)
我们每次查询Employee对象的时候,都将部门信息一起查询出来,可以让部门信息在我们使用的时候再去查询;
只需要在上述分布查询的基础上增加两个全局配置:
1
2
3
4
5
|
<settings>
<!--显示的指定每个我们需要更改的配置的值,即使他是默认的。防止版本更新带来的问题-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
</settings>
|
测试类:
1
2
3
4
5
6
7
8
9
10
11
12
|
@Test
public void test1() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
try (SqlSession session = sqlSessionFactory.openSession(true)) {
EmployeeMapperPlus employeeMapper = session.getMapper(EmployeeMapperPlus.class);
Employee employee = employeeMapper.getEmployeeByIdStep(1);
System.out.println(employee.getLastName());
Dept dept = employee.getDept();
System.out.println(dept.getDeptName());
}
}
|
没加延迟加载设置之前
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
20201009 14:27:57 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection
20201009 14:27:57 [main] DEBUG o.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 2109798150.
20201009 14:27:57 [main] DEBUG c.e.e.m.o.d.EmployeeMapperPlus.getEmployeeByIdStep - ==> Preparing: select e.*, d.id d_id, d.dept_name d_name from tbl_employee e, tbl_dept d where e.dept_id = d.id and e.id = ?;
20201009 14:27:57 [main] DEBUG c.e.e.m.o.d.EmployeeMapperPlus.getEmployeeByIdStep - ==> Parameters: 1(Integer)
20201009 14:27:57 [main] TRACE c.e.e.m.o.d.EmployeeMapperPlus.getEmployeeByIdStep - <== Columns: id, last_name, gender, email, dept_id, d_id, d_name
20201009 14:27:57 [main] TRACE c.e.e.m.o.d.EmployeeMapperPlus.getEmployeeByIdStep - <== Row: 1, 王五, 1, w5@gmail.com, 1, 1, 开发部
20201009 14:27:57 [main] DEBUG com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById - ====> Preparing: select id, dept_name deptName from tbl_dept where id = ?
20201009 14:27:57 [main] DEBUG com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById - ====> Parameters: 1(Integer)
20201009 14:27:57 [main] TRACE com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById - <==== Columns: id, deptName
20201009 14:27:57 [main] TRACE com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById - <==== Row: 1, 开发部
20201009 14:27:57 [main] DEBUG com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById - <==== Total: 1
20201009 14:27:57 [main] DEBUG c.e.e.m.o.d.EmployeeMapperPlus.getEmployeeByIdStep - <== Total: 1
王五
开发部
20201009 14:27:57 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@7dc0f706]
20201009 14:27:57 [main] DEBUG o.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 2109798150 to pool.
|
增加延迟加载之后:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
20201009 14:28:52 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection
20201009 14:28:52 [main] DEBUG o.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 1665620686.
20201009 14:28:52 [main] DEBUG c.e.e.m.o.d.EmployeeMapperPlus.getEmployeeByIdStep - ==> Preparing: select e.*, d.id d_id, d.dept_name d_name from tbl_employee e, tbl_dept d where e.dept_id = d.id and e.id = ?;
20201009 14:28:52 [main] DEBUG c.e.e.m.o.d.EmployeeMapperPlus.getEmployeeByIdStep - ==> Parameters: 1(Integer)
20201009 14:28:52 [main] TRACE c.e.e.m.o.d.EmployeeMapperPlus.getEmployeeByIdStep - <== Columns: id, last_name, gender, email, dept_id, d_id, d_name
20201009 14:28:52 [main] TRACE c.e.e.m.o.d.EmployeeMapperPlus.getEmployeeByIdStep - <== Row: 1, 王五, 1, w5@gmail.com, 1, 1, 开发部
20201009 14:28:52 [main] DEBUG c.e.e.m.o.d.EmployeeMapperPlus.getEmployeeByIdStep - <== Total: 1
王五
20201009 14:28:52 [main] DEBUG com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById - ==> Preparing: select id, dept_name deptName from tbl_dept where id = ?
20201009 14:28:52 [main] DEBUG com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById - ==> Parameters: 1(Integer)
20201009 14:28:52 [main] TRACE com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById - <== Columns: id, deptName
20201009 14:28:52 [main] TRACE com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById - <== Row: 1, 开发部
20201009 14:28:52 [main] DEBUG com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById - <== Total: 1
开发部
20201009 14:28:52 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@63475ace]
20201009 14:28:52 [main] DEBUG o.apache.ibatis.datasource.pooled.PooledDataSource - Returned connection 1665620686 to pool.
|
可以看到先打印了王五,然后使用dept属性的时候才会去查数据库。
关联查询之collection
注意
场景二:
查询部门的时候将部门对应的所有员工信息也查询出来:注释在DeptMapper.xml中
Dept.java
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
package com.eh.eden.mybatis.orm.bean;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Dept {
private Integer id;
private String deptName;
private List<Employee> employees;
}
|
DeptMapper.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
<!--嵌套结果集的方式,使用collection标签定义关联的集合类型的属性封装规则 -->
<!--
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Dept {
private Integer id;
private String deptName;
private List<Employee> employees;
}//集合类型得使用collection
-->
<resultMap id="deptEmpCollection" type="com.eh.eden.mybatis.orm.bean.Dept">
<id column="id" property="id"/>
<result column="dept_name" property="deptName"/>
<!--
collection定义关联集合类型的属性的封装规则
ofType:指定集合里面元素的类型
-->
<collection property="employees" ofType="com.eh.eden.mybatis.orm.bean.Employee">
<!-- 定义这个集合中元素的封装规则 -->
<id column="e_id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</collection>
</resultMap>
<select id="getDeptEmpCollectionById" resultMap="deptEmpCollection">
SELECT d.*,
e.id e_id,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>
|
关联查询之collection之分布查询
EmployeeMapperPlus.xml
1
2
3
|
<select id="getEmployeesByDeptId" resultType="com.eh.eden.mybatis.orm.bean.Employee">
select * from tbl_employee where dept_id = #{deptId}
</select>
|
DeptMapper.xml
1
2
3
4
5
6
7
8
9
10
11
|
<resultMap id="deptEmpCollectionStep" type="com.eh.eden.mybatis.orm.bean.Dept">
<id column="id" property="id"/>
<result column="dept_name" property="deptName"/>
<collection property="employees"
select="com.eh.eden.mybatis.orm.dao.EmployeeMapperPlus.getEmployeesByDeptId"
column="id">
</collection>
</resultMap>
<select id="getDeptEmpCollectionByIdStep" resultMap="deptEmpCollectionStep">
select * from tbl_dept where id = #{id}
</select>
|
测试类:
1
2
3
4
5
6
7
8
9
10
11
12
|
@Test
public void test2() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
try (SqlSession session = sqlSessionFactory.openSession(true)) {
DeptMapper deptMapper = session.getMapper(DeptMapper.class);
Dept dept = deptMapper.getDeptEmpCollectionByIdStep(1);
System.out.println(dept.getDeptName());
List<Employee> employees = dept.getEmployees();
System.out.println(employees.size());
}
}
|
1
2
3
4
5
6
7
8
9
10
11
12
|
20201009 15:43:59 [main] DEBUG c.e.e.m.o.d.D.getDeptEmpCollectionByIdStep - ==> Preparing: select * from tbl_dept where id = ?
20201009 15:43:59 [main] DEBUG c.e.e.m.o.d.D.getDeptEmpCollectionByIdStep - ==> Parameters: 1(Integer)
20201009 15:43:59 [main] TRACE c.e.e.m.o.d.D.getDeptEmpCollectionByIdStep - <== Columns: id, dept_name
20201009 15:43:59 [main] TRACE c.e.e.m.o.d.D.getDeptEmpCollectionByIdStep - <== Row: 1, 开发部
20201009 15:43:59 [main] DEBUG c.e.e.m.o.d.D.getDeptEmpCollectionByIdStep - <== Total: 1
开发部
20201009 15:43:59 [main] DEBUG c.e.e.m.o.d.E.getEmployeesByDeptId - ==> Preparing: select * from tbl_employee where dept_id = ?
20201009 15:43:59 [main] DEBUG c.e.e.m.o.d.E.getEmployeesByDeptId - ==> Parameters: 1(Integer)
20201009 15:43:59 [main] TRACE c.e.e.m.o.d.E.getEmployeesByDeptId - <== Columns: id, last_name, gender, email, dept_id
20201009 15:43:59 [main] TRACE c.e.e.m.o.d.E.getEmployeesByDeptId - <== Row: 1, 王五, 1, w5@gmail.com, 1
20201009 15:43:59 [main] DEBUG c.e.e.m.o.d.E.getEmployeesByDeptId - <== Total: 1
1
|
可以看到关联查询之collection之分布查询也支持懒加载。
扩展
多列分步查询
assocation和collection 的分步查询都使用了column属性,如果使用多列进行分步查询该怎么办?
将多列的值封装成map传递,如下:
column="{key1=column1,key2=column2}",注意key1是属性名称,是要设置给第二个查询语句的java对象条件属性
例如上面例子中sql映射文件
1
2
3
4
5
6
7
8
|
<resultMap id="deptEmpCollectionStep" type="com.eh.eden.mybatis.orm.bean.Dept">
<id column="id" property="id"/>
<result column="dept_name" property="deptName"/>
<collection property="employees"
select="com.eh.eden.mybatis.orm.dao.EmployeeMapperPlus.getEmployeesByDeptId"
column="id">
</collection>
</resultMap>
|
等价于
1
2
3
4
5
6
7
8
|
<resultMap id="deptEmpCollectionStep" type="com.eh.eden.mybatis.orm.bean.Dept">
<id column="id" property="id"/>
<result column="dept_name" property="deptName"/>
<collection property="employees"
select="com.eh.eden.mybatis.orm.dao.EmployeeMapperPlus.getEmployeesByDeptId"
column="{deptId=id}">
</collection>
</resultMap>
|
fetchType
会覆盖全局设置中延迟加载相关的设置
- lazy 表示使用延迟加载
- eager 不使用延迟加载,立即查询
使用演示
关联查询中不使用延迟加载
1
2
3
4
5
6
7
8
|
<resultMap id="deptEmpCollectionStep" type="com.eh.eden.mybatis.orm.bean.Dept">
<id column="id" property="id"/>
<result column="dept_name" property="deptName"/>
<collection property="employees"
select="com.eh.eden.mybatis.orm.dao.EmployeeMapperPlus.getEmployeesByDeptId"
column="{deptId=id}" fetchType="eager">
</collection>
</resultMap>
|
关联查询中使用延迟加载
1
2
3
4
5
6
7
8
|
<resultMap id="deptEmpCollectionStep" type="com.eh.eden.mybatis.orm.bean.Dept">
<id column="id" property="id"/>
<result column="dept_name" property="deptName"/>
<collection property="employees"
select="com.eh.eden.mybatis.orm.dao.EmployeeMapperPlus.getEmployeesByDeptId"
column="{deptId=id}" fetchType="lazy">
</collection>
</resultMap>
|
discriminator 鉴别器
用的非常少,了解即可。
鉴别器:mybatis可以使用discriminator判断某列的值,然后根据某列的值改变封装行为
注意
封装Employee:
如果查出的是女生:就把部门信息查询出来,否则不查询;
如果是男生,把last_name这一列的值赋值给email;
演示
EmployeeMapperPlus.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
<!--
column:指定判定的列名
javaType:列值对应的java类型 -->
<discriminator javaType="string" column="gender">
<!--女生 resultType:指定封装的结果类型;不能缺少。和resultMap二选一 -->
<case value="0" resultType="com.eh.eden.mybatis.orm.bean.Employee">
<association property="dept"
javaType="com.eh.eden.mybatis.orm.bean.Dept"
select="com.eh.eden.mybatis.orm.dao.DeptMapper.getDeptById"
column="d_id"/>
</case>
<!--男生 把last_name这一列的值赋值给email; -->
<case value="1" resultType="com.eh.eden.mybatis.orm.bean.Employee">
<id column="id" property="id"/>
<result column="last_name" property="lastName"/>
<result column="last_name" property="email"/>
<result column="gender" property="gender"/>
</case>
</discriminator>
</resultMap>
<select id="getEmployeeByIdStepDis" resultMap="stepEmployeeDis">
select e.*, d.id d_id, d.dept_name d_name from tbl_employee e, tbl_dept d
where e.dept_id = d.id
and e.id = #{id};
</select>
|