目录

mybatis映射文件

官方映射文件介绍

概述

映射文件指导着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不支持;两种办法

  1. 在sql映射文件中, 取值时使用 #{email,jdbcType=NULL};
  2. 全局设置 <setting name="jdbcTypeForNull" value="NULL"/>
注意
jdbcTypeForNull=OTHER 或者 jdbcTypeForNull=NULL, mysql都支持

  • Select元素来定义查询操作。

  • Id:唯一标识符。

    用来引用这条语句,需要和接口的方法名一致

  • parameterType:参数类型

    可以不传,MyBatis会根据TypeHandler自动推断

  • resultType:返回值类型。

    别名或者全类名,如果返回的是集合,定义集合中元素的类型。不能和resultMap同时使用

https://gitee.com/lienhui68/picStore/raw/master/null/20201009125559.png

resultType

  • 基本类型或对象类型

    resultType = 基本类型或对象类型

  • 返回List类型

    resultType = List中元素的类型

  • Map类型

    • 单条记录Map<String, Object>

      resultType = map

    • 多条记录 Map<Integer, Entity>

      resultType = Map中value的类型, 不过key需要通过@MapKey("field")指定对象中一个属性名为key

返回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的查询语句

  1. 先按照员工id查询员工信息
  2. 根据查询员工信息中的d_id值去部门表查出部门信息
  3. 部门设置到员工中;

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>