MyBatis

Starter

Github | Doc

DB Data

  1. pe_department
     CREATE TABLE `pe_department` (
     `id` INT(11) NOT NULL AUTO_INCREMENT,
     `name` VARCHAR(50) NOT NULL,
     `remark` VARCHAR(100) NULL DEFAULT NULL,
     PRIMARY KEY (`id`)
 )
 COLLATE='latin1_swedish_ci'
 ENGINE=InnoDB
 AUTO_INCREMENT=4
 ;
  2. pe_employee
     CREATE TABLE `pe_employee` (
     `id` INT(11) NOT NULL AUTO_INCREMENT,
     `department_id` INT(11) NOT NULL,
     `name` VARCHAR(50) NOT NULL DEFAULT '0',
     `remark` VARCHAR(100) NULL DEFAULT NULL,
     PRIMARY KEY (`id`),
     INDEX `FK__pe_department` (`department_id`),
     CONSTRAINT `FK__pe_department` FOREIGN KEY (`department_id`) REFERENCES `pe_department` (`id`)
 )
 COLLATE='latin1_swedish_ci'
 ENGINE=InnoDB
 AUTO_INCREMENT=10
 ;

  3. test data

     -- pe_department
 INSERT INTO pe_department(name,remark) VALUES('Dep-A','This is Department A');
 INSERT INTO pe_department(name,remark) VALUES('Dep-B','This is Department B');
 INSERT INTO pe_department(name,remark) VALUES('Dep-C','This is Department C');

 -- pe_employee
 INSERT INTO pe_employee(name,department_id,remark) VALUES ('Test1',1,'This is Test1');
 INSERT INTO pe_employee(name,department_id,remark) VALUES ('Test2',1,'This is Test2');
 INSERT INTO pe_employee(name,department_id,remark) VALUES ('Test3',1,'This is Test3');
 INSERT INTO pe_employee(name,department_id,remark) VALUES ('Test4',2,'This is Test4');
 INSERT INTO pe_employee(name,department_id,remark) VALUES ('Test5',2,'This is Test5');
 INSERT INTO pe_employee(name,department_id,remark) VALUES ('Test6',2,'This is Test6');
 INSERT INTO pe_employee(name,department_id,remark) VALUES ('Test7',3,'This is Test7');
 INSERT INTO pe_employee(name,department_id,remark) VALUES ('Test8',3,'This is Test8');
 INSERT INTO pe_employee(name,department_id,remark) VALUES ('Test9',3,'This is Test9');

HelloWorld

  1. dependency (pom.xml)
     <dependency>
     <groupId>org.mybatis</groupId>
     <artifactId>mybatis</artifactId>
     <version>3.5.3</version>
 </dependency>
 <dependency>
     <groupId>mysql</groupId>
     <artifactId>mysql-connector-java</artifactId>
     <version>8.0.18</version>
 </dependency>

  2. global config (mybatis-config.xml)

     <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE configuration
   PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
   "http://mybatis.org/dtd/mybatis-3-config.dtd">
 <configuration>
   <environments default="development">
     <environment id="development">
       <transactionManager type="JDBC"/>
       <dataSource type="POOLED">
         <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
         <property name="url" value="jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC"/>
         <property name="username" value="root"/>
         <property name="password" value="123456"/>
       </dataSource>
     </environment>
   </environments>

   <mappers>
     <mapper resource="mapper/EmployeeMapper.xml"/>
   </mappers>
 </configuration>

  3. sql config (mapper/EmployeeMapper.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.cj.mybatis.mapper.EmployeeMapper">
   <!-- id: 标识; resultType: 返回值类型; #{id}: 从传递过来的参数中取出id值 -->
   <select id="getEmployee" resultType="com.cj.mybatis.entity.Employee">
     select id,name,remark,department_id as departmentId from pe_employee where id = #{id}
   </select>
 </mapper>

  4. entity (com.cj.mybatis.entity.Employee.java)

     public class Employee {
     private Integer id;
     private String name;
     private String remark;
     private Integer departmentId;

     // getXxx,setXxx,toString
     // ...
 }

  5. Usage1

     @Test
 public void test1() throws IOException {
     // 1. 根据全局配置文件创建一个SqlSessionFactory对象
     String resource = "mybatis-config.xml";
     InputStream inputStream = Resources.getResourceAsStream(resource);
     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
             .build(inputStream);

     // 2. 获取SqlSession对象来执行映射的sql(一个SqlSession代表和数据库的一次会话，用完关闭)
     SqlSession session = sqlSessionFactory.openSession();
     try {
         // selectOne(namespace.id,parameter)
         Employee emp = session.selectOne("com.cj.mybatis.mapper.EmployeeMapper.getEmployee", 1);
         System.out.println(emp);
     }finally {
         session.close();
     }
 }

  6. Usage2

    • dao interface (com.cj.mybatis.mapper.EmployeeMapper.java)

        package com.cj.mybatis.mapper;
  import com.cj.mybatis.entity.Employee;
  public interface EmployeeMapper { 
      Employee getEmployee(Integer id);
  }

    • Test

        @Test
  public void test2() throws IOException {
      // 1. 根据全局配置文件创建一个SqlSessionFactory对象
      String resource = "mybatis-config.xml";
      InputStream inputStream = Resources.getResourceAsStream(resource);
      SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder()
              .build(inputStream);

      // 2. 获取SqlSession对象，执行映射的sql
      try (SqlSession session = sqlSessionFactory.openSession()) {
          // Method1: no EmployeeMapper.java
          // Employee emp = session.selectOne("com.cj.mybatis.mapper.EmployeeMapper.getEmployee", 1);

          // Method2: use EmployeeMapper.java
          // 注： interface EmployeeMapper 无实现类，mybatis会将此接口和sql配置进行绑定，生成一个代理对象执行
          EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
          Employee emp = employeeMapper.getEmployee(1);

          System.out.println(emp);
      }

Global Config

  • properties
  • settings
  • typeAliases
  • typeHandlers
  • objectFactory
  • plugins
  • evironments (evironment: transactionManager & dataSource)
  • databaseIdProvider
  • mappers

properties

Sample:

  1. mybatis-config.xml

     <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE configuration
   PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
   "http://mybatis.org/dtd/mybatis-3-config.dtd">
 <configuration>

   <!-- resource: 引入类路径下资源； url: 引入网络或磁盘路径下资源 -->
   <properties resource="db.properties">
       <!-- <property name="" value=""/> -->
   </properties>

   <environments default="development">
     <environment id="development">
       <transactionManager type="JDBC"/>
       <dataSource type="POOLED">
           <property name="driver" value="${jdbc.driver}"/>
           <property name="url" value="${jdbc.url}"/>
           <property name="username" value="${jdbc.username}"/>
           <property name="password" value="${jdbc.password}"/>
         </dataSource>
     </environment>
   </environments>
   <mappers>
     <mapper resource="mapper/EmployeeMapper.xml"/>
     <mapper resource="mapper/DepartmentMapper.xml"/>
   </mappers>
 </configuration>
  2. db.properties
     jdbc.driver=com.mysql.cj.jdbc.Driver
 jdbc.url=jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC
 jdbc.username=root
 jdbc.password=123456

settings

  • cacheEnable (default: true)
  • lazyLoadingEnabled (default: false)
  • useColumnLabel (default: true)
  • defaultStatementTimeout (default: null)
  • mapUnderscoreToCamelCase (default: false 默认不开启驼峰命名规则映射：a_column->aColumn)
  • jdbcTypeForNull (default:OTHER)
  • autoMappingBehavior (default: PARTIAL 开启自动映射，列名与javaBean属性名必须一致)
  • ...

Sample:

  1. mybatis-config.xml
     <settings>
     <!-- 打开驼峰命名规则映射-->
     <setting name="mapUnderscoreToCamelCase" value="true"/>
 </settings>
  2. mapper/EmployeeMapper.xml
     <mapper namespace="com.cj.mybatis.mapper.EmployeeMapper">
   <select id="getEmployee" resultType="com.cj.mybatis.entity.Employee">
     <!-- select id,name,remark,department_id as departmentId from pe_employee where id = #{id} -->
     select * from pe_employee where id = #{id}
     <!-- => setting mapUnderscoreToCamelCase:true后，可以匹配department_id到departmentId-->
   </select>
 </mapper>

typeAliases

  • 别名处理器，给java类型起别名（别名不区分大小写）
  • There are many built-in type aliases for common Java types,eg:
    • byte _byte
    • int _int
    • String string
    • Integer int/integer
    • Date date
    • Map map
    • ...

Sample:

  1. 单个配置别名（使用默认别名）

     <!-- mybatis-config.xml -->
 <typeAliases>
   <typeAlias type="com.cj.mybatis.entity.Employee"/> <!-- 默认别名=类名 -->
 </typeAliases>

 <!-- mapper/EmployeeMapper.xml -->
 <mapper namespace="com.cj.mybatis.mapper.EmployeeMapper">
   <select id="getEmployee" resultType="employee">  <!-- employee/Employee/com.cj.mybatis.entity.Employee 都可-->
     select * from pe_employee where id = #{id}
   </select>
 </mapper>

  2. 单个配置别名（指定别名）

     <!-- mybatis-config.xml -->
 <typeAliases>
   <typeAlias type="com.cj.mybatis.entity.Employee" alias="emp"/> <!-- 指定别名 -->
 </typeAliases>

 <!-- mapper/EmployeeMapper.xml -->
 <mapper namespace="com.cj.mybatis.mapper.EmployeeMapper">
   <select id="getEmployee" resultType="emp">  <!-- emp/Emp/com.cj.mybatis.entity.Employee 都可-->
     select * from pe_employee where id = #{id}
   </select>
 </mapper>

  3. 批量配置别名

     <!-- mybatis-config.xml -->
 <typeAliases>
     <!-- <typeAlias type="com.cj.mybatis.entity.Department"/>
     <typeAlias type="com.cj.mybatis.entity.Employee" alias="emp"/> -->

     <!-- 批量起别名（使用默认别名，即类名）
         在此基础上，可在类上加@Alias注解为某个类指定新的别名，eg：
         @Alias("emp")
         public Employee{
             //...
         }
      -->
     <package name="com.cj.mybatis.entity"/> 
 </typeAliases>

typeHandlers

类型处理器(XxxTypeHandler): value => javaType

<typeHandlers>
    <!-- method1: 配置具体的TypeHandler,eg:
    <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" 
        javaType="com.cj.mybatis.entity.UserTypeEnum"/>
    -->

    <!-- method2: 或者在XxxMapper.xml中具体处理某个字段时设置,eg:
        #{status,typeHandler=xxx} 

        <resultMap>
            <result column="" property="" typeHandler="xxx" />
        </resultMap>    

        注：若在位置参数中配置typeHandler，应保证insert/select用的TypeHandler是一样的
    -->
</typeHandlers>

plugins

Sample:

<plugins>
    <plugin interceptor="com.cj.mybatis.extend.MyFirstPlugin">
        <property name="testname" value="first-plugin"/>
    </plugin>
</plugins>

environments

Sample:

<environments default="development"> <!-- switch: development/product-->
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
          <property name="driver" value="${dev.jdbc.driver}"/>
          <property name="url" value="${dev.jdbc.url}"/>
          <property name="username" value="${dev.jdbc.username}"/>
          <property name="password" value="${dev.jdbc.password}"/>
        </dataSource>
    </environment>
    <environment id="product">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
          <property name="driver" value="${pro.jdbc.driver}"/>
          <property name="url" value="${pro.jdbc.url}"/>
          <property name="username" value="${pro.jdbc.username}"/>
          <property name="password" value="${pro.jdbc.password}"/>
        </dataSource>
    </environment>
  </environments>

databaseIdProvider

支持多数据库厂商(execute different statements depending on database vendor)

<!-- mybatis-config.xml -->
<!-- type="DB_VENDOR": VendorDatabaseIdProvider 得到数据库厂商的不同标识（驱动 Connection.getMetaData().getDatabaseProductName()） -->
<databaseIdProvider type="DB_VENDOR">
    <!-- 为不同数据库厂商标识起个别名 -->
    <property name="MySQL" value="mysql"/>
    <property name="Oracle" value="oracle"/>
    <property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>

<!-- mapper/EmployeeMapper.xml -->
<mapper namespace="com.cj.mybatis.mapper.EmployeeMapper">
  <!-- 1. default -->
  <select id="getEmployee" resultType="com.cj.mybatis.entity.Employee"> 
    select * from pe_employee where id = #{id}
  </select>
  <!-- 2. for mysql -->
  <select id="getEmployee" resultType="com.cj.mybatis.entity.Employee" databaseId="mysql"> 
    select * from pe_employee where id = #{id}
  </select>
  <!-- 3. for oracle -->
  <select id="getEmployee" resultType="com.cj.mybatis.entity.Employee" databaseId="oracle"> 
    select * from pe_employee where id = #{id}
  </select>
</mapper>

<!-- 
    => if current use MySQL datasource => load 1 & 2 => use 2
    => if current use Oracle datasource => load 1 & 3 => use 3
    => if current use any Other datasource => load 1 => use 1
-->

mappers

注册sql映射到global config中

  • <mapper> 单个注册:
    • url: 磁盘或网络文件（file:///var/mappers/AuthorMapper.xml）
    • resource: 类路径下文件 （mapper/EmployeeMapper.xml）
    • class: 接口
      • 方式一：接口和mapper文件同名同路径下
      • 方式二：接口上使用注解写sql（无需mapper文件）
  • <package> 批量注册
    • name: 接口包名（接口使用注解写sql）

Sample：三种方式

mybatis-config.xml

  1. 配置使用url/resource & mapper文件
     <mappers>
     <mapper resource="mapper/EmployeeMapper.xml"/>
 </mappers>

     package com.cj.mybatis.mapper;
 import com.cj.mybatis.entity.Employee;
 public interface EmployeeMapper {
     Employee getEmployee(Integer id);
 }
  2. 配置使用class & 接口上使用注解写sql（无需mapper文件）
     <mappers>
     <mapper class="com.cj.mybatis.mapper.EmployeeWithAnnotationMapper"/>
 </mappers>

     package com.cj.mybatis.mapper;
 import org.apache.ibatis.annotations.Select;
 import com.cj.mybatis.entity.Employee;
 public interface EmployeeWithAnnotationMapper {
     @Select("select * from pe_employee where id=#{id}")
     public Employee getEmployee(Integer id);
 }
  3. 批量注册（规则同class方式）
     <mappers>
     <package name="com.cj.mybatis.mapper"/>
 </mappers>

Sql Mapper Config: CRUD

  • <select>
  • <insert>
  • <update>
  • <delete>

select

<select>用来定义查询操作,属性：

  • 唯一标识: id
  • 参数
    • parameterType 参数类型（optional,mybatis会根据TypeHandler自动推断）
  • 结果集
    • resultType 返回值类型，别名或全类名（集合类则指定集合中的元素类型），注：不能和resultMap同时使用
    • resultMap
    • resultSets,resultSetType
    • resultOrdered
  • 缓存相关
    • flushCache
    • useCache
  • statementType: PREPARED,STATEMENT,CALLABLE
  • timeout
  • fetchSize
  • databaseId

  1. DepartmentMapper.xml

     <!-- public Department getDepartment(Integer id); -->
 <select id="getDepartment" resultType="com.cj.mybatis.entity.Department">
     select * from pe_department where id = #{id}
 </select>

 <!-- public List<Department> listDepartment(); -->
 <select id="listDepartment" resultType="com.cj.mybatis.entity.Department">
     select * from pe_department
 </select>

  2. test

     private SqlSessionFactory getSqlSessionFactory() throws IOException {
     String resource = "mybatis-config2.xml";
     InputStream inputStream = Resources.getResourceAsStream(resource);
     return new SqlSessionFactoryBuilder().build(inputStream);
 }

 @Test
 public void testQueryDepartment() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);

         System.out.println("------------listDepartment------------");
         List<Department> list = departmentMapper.listDepartment();
         System.out.println(list);

         System.out.println("------------getDepartment------------");
         Department dept = departmentMapper.getDepartment(1);
         System.out.println(dept);
     }
 }

insert

  1. DepartmentMapper.xml

     <!-- public Integer insertDepartment(Department department); -->
 <insert id="insertDepartment">
     insert into pe_department(name,remark) values (#{name},#{remark})
 </insert>

 <!-- public Integer insertDepartmentAndReturnId(Department department); -->
 <!-- 
     useGeneratedKeys=false/true 是否使用产生的主键； 
     keyProperty: 配置获取到的主键注入javaBean的哪个属性中 
 -->
 <insert id="insertDepartmentAndReturnId" useGeneratedKeys="true" keyProperty="id">
     insert into pe_department(name,remark) values (#{name},#{remark})
 </insert>

 <!-- public Integer insertDepartmentByNextSeq(Department department); -->
 <insert id="insertDepartmentByNextSeq">
     <!-- order="BEFORE"/"AFTER" 在sql执行前/后执行 -->
     <!-- <selectKey keyProperty="id" order="BEFORE" resultType="Integer">
         select max(id)+1 from pe_department
     </selectKey> -->
     <selectKey keyProperty="id" order="BEFORE" resultType="Integer">
         <!-- 注意加allowMultiQueries=true这里才可执行多条（ jdbc:mysql://localhost:3306/demo?allowMultiQueries=true） -->
         update pe_generator set next=next+1 where segment_name='dept_seq';
         select next from pe_generator where segment_name='dept_seq'
     </selectKey>
     insert into pe_department(id,name,remark) values (#{id},#{name},#{remark})
 </insert>

  2. test

     /*
 Note:
     CUD 可返回类型：Integer/Long/Boolean 受影响的行数/是否成功
     CUD 需提交数据： factory.openSession(true) 自动commit / factory.openSession() + session.commit() 手动commit
 */

 @Test
 public void testInsertDepartment() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);

         System.out.println("------------insertDepartment------------");
         Department dept = new Department("Dep-test01","This is department test01");
         Integer result = departmentMapper.insertDepartment(dept);
         System.out.println(result);
         System.out.println(dept);           // => dept.getId() is null

         session.commit();                   // note: commit
     }
 }

 @Test
 public void testInsertDepartmentAndReturnId() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);

         System.out.println("------------insertDepartmentAndReturnId------------");
         Department dept = new Department("Dep-test01","This is department test01");
         Integer result = departmentMapper.insertDepartmentAndReturnId(dept);
         System.out.println(result);
         System.out.println(dept);           // => dept.getId() is not null

         session.commit();                   // note: commit
     }
 }

 @Test
 public void testInsertDepartmentByNextSeq() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);

         System.out.println("------------insertDepartmentAndReturnId------------");
         Department dept = new Department("Dep-test01","This is department test01");
         Integer result = departmentMapper.insertDepartmentByNextSeq(dept);
         System.out.println(result);
         System.out.println(dept);           // => dept.getId() is not null

         session.commit();                   // note: commit
     }
 }

update

  1. DepartmentMapper.xml

     <!-- public Integer updateDepartment(Department department); -->
 <update id="updateDepartment">
     update pe_department set name=#{name},remark=#{remark} where id=#{id}
 </update>

  2. test

     @Test
 public void testUpdateDepartment() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);

         System.out.println("------------updateDepartment------------");
         Integer result = departmentMapper.updateDepartment(new Department(5,"Dep-test02","This is department test02"));
         System.out.println(result);

         session.commit();                   // note: commits
     }
 }

delete

  1. DepartmentMapper.xml

     <!-- public Integer deleteDepartment(Integer id); -->
 <delete id="deleteDepartment">
     delete from pe_department where id=#{id}
 </delete>

  2. test

     @Test
 public void testDeleteDepartment() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);

         System.out.println("------------deleteDepartment------------");
         Integer result = departmentMapper.deleteDepartment(5);
         System.out.println(result);

         session.commit();                   // note: commits
     }
 }

ParameterHandler

Parameters

  • 单个参数(mybatis一般不做特殊处理)

      <!-- 传入基础类型Integer: public Department getDepartment(Integer id);  -->
  <select id="getDepartment" resultType="com.cj.mybatis.entity.Department">
      select * from pe_department where id = #{id}
  </select>

  <!-- 传入POJO : public List<Department> listByExample(Department department); -->
  <select id="listByExample" resultType="com.cj.mybatis.entity.Department">
      select * from pe_department where name like #{name} or remark like #{remark}
  </select>

  <!-- 传入Map : public List<Department> listByMap(Map params);  -->
  <select id="listByMap" resultType="com.cj.mybatis.entity.Department">
      select * from pe_department where name like #{name} or remark like #{remark}
  </select>

  <!-- 
      传入Collection/List/Set/List/Array : 会做特殊处理（封装到Map中）
          Collection  => Map<collection,Object>
          List        => Map<list,Object>
          Set         => Map<set,Object>
          Array       => Map<array,Object>
      public List<Department> listByIds(List<Integer> ids);
  -->
  <select id="listByIds" resultType="com.cj.mybatis.entity.Department">
      select * from pe_department where id in (#{list[0]})
  </select>

  • 多个参数(mybatis会做特殊处理) => 会被封装成一个Map<String,Object> (key: param1,..,paramN),#{}即从map中取值

      <!-- 
     public List<Department> listByParams1(String name,String remark); 
     => available parameters are [arg0,arg1,param1,param2]
  -->
  <select id="listByParams1" resultType="com.cj.mybatis.entity.Department">
      <!-- select * from pe_department where name like #{arg0} or remark like #{arg1} -->
      select * from pe_department where name like #{param1} or remark like #{param2}
  </select>

  <!--   
     使用@Param注解命名参数
     public List<Department> listByParams2(@Param("name")String name,@Param("remark")String remark); 
     => available parameters are [name,remark,param1,param2]
  -->
  <select id="listByParams2" resultType="com.cj.mybatis.entity.Department">
      <!-- select * from pe_department where name like #{param1} or remark like #{param2} -->
      select * from pe_department where name like #{name} or remark like #{remark}
  </select>

#{},${}

  • #{} vs ${}

      <!-- 
      ${} : 直接拼接到sql语句中
      => select * from pe_department where id = 2 
  -->
  <select id="getDepartment" resultType="com.cj.mybatis.entity.Department">
      select * from pe_department where id = ${id}
      <!-- 更多应用场景（在sql中不支持占位符的地方使用）： 
          select * from ${year}_salary
          select * from pe_employee order by ${orderColumn}
      -->
  </select>

  <!-- 
      #{} : 预编译模式注入参数（PreparedStatement 占位符)，防止sql注入
      => select * from pe_department where id = ? 
  -->
  <select id="getDepartment" resultType="com.cj.mybatis.entity.Department">
      select * from pe_department where id = #{id}
  </select>

  • #{}中可设置参数规则

    • javaType 指定参数类型（通常可根据参数对象自动确定）
    • jdbcType (eg: #{remark,jdbcType=NULL})
    • mode
    • numericScale
    • resultMap
    • typeHandler
    • jdbcTypeName
    • expression

ResultSetHandler

resultType

Sample: DepartmentMapper.xml

  1. get single result

     <!-- 
     public Department getDepartment(Integer id); 
     resultType: Department
 -->
 <select id="getDepartment" resultType="com.cj.mybatis.entity.Department">
     select * from pe_department where id = #{id}
 </select>

 <!-- 
     public Map<String,Object> getDepartmentMap(Integer id); 
     => resultType: map
 -->
 <select id="getDepartmentMap" resultType="map">
     select * from pe_department where id = #{id}
 </select>

     @Test
 public void testReturnSingle() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);

         // return POJO (set resultType="com.cj.mybatis.entity.Department")
         System.out.println("------------getDepartment------------");
         Department dept = departmentMapper.getDepartment(1);
         System.out.println(dept);

         // return Map (set resultType="map")
         System.out.println("------------getDepartmentMap------------");
         Map<String,Object> deptMap = departmentMapper.getDepartmentMap(1);
         System.out.println(deptMap);
     }
 }

  2. get multiple results

     <!-- 
     public List<Department> listDepartment();
     => resultType : Department (集合类则指定集合中的元素类型) 
 -->
 <select id="listDepartment" resultType="com.cj.mybatis.entity.Department">
     select * from pe_department
 </select>

 <!-- 
    @MapKey("id")  // 将结果集封装成Map
    public Map<Integer,Department> listAndReturnMap(); 
    => resultType : Department (集合类则指定集合中的元素类型) 
 -->
 <select id="listDepartmentMap" resultType="com.cj.mybatis.entity.Department">
     select * from pe_department;
 </select>

     @Test
 public void testReturnMultiple() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);

         // return List<Department> (set resultType="com.cj.mybatis.entity.Department")
         System.out.println("------------listDepartment------------");
         List<Department> list = departmentMapper.listDepartment();
         System.out.println(list);

         // return Map<Integer,Department> (set resultType="com.cj.mybatis.entity.Department")
         // + @MapKey("") 注解 => 将结果集封装成Map
         System.out.println("------------listDepartmentMap------------");
         Map<Integer,Department> map = departmentMapper.listDepartmentMap();
         System.out.println(map);
     }
 }

resultMap

自定义resultMap,实现高级结果集映射

<resultMap>

  • properties:
    • id: 唯一标识
    • type: javaBean
  • children:
    • <id column="" property=""/> 定义主键列映射规则，有利底层优化 (column:列名，property:javaBean属性名)
    • <result column="" property=""/> 定义其他普通列映射规则
    • <association> 定义关联单个对象的映射规则
      • <association property="" javaType="" autoMapping="" columnPrefix="" />
      • <association property="" select="" column="" fetchType="" autoMapping="" columnPrefix="" /> 分段查询
    • <collection> 定义关联多个对象的映射规则
      • <collection property="" ofType="" autoMapping="" columnPrefix="" /> (ofType 指定集合里面的元素类型)
      • <collection property="" select="" column="" fetchType="" autoMapping="" columnPrefix="" /> 分段查询
    • 注分段查询中：
      • select 指定当前属性通过调用哪个方法获取
      • column 指定将那几个列值传递给这个方法作为参数（单列：column="xxx"; 多列: column={key=column1,key=column2}）
      • fetchType: eager/lazy
      • 即使用select指定的方法（传入column指定的列参数值）查出结果，封装给property指定的javaBean属性

resultMap:association

OneToOne

Sample:EmployeeWithDepartmentMapper.xml

  1. Pojo: EmployeeWithDepartment
     @Data
 public class EmployeeWithDepartment {
     private Integer id;
     private String name;
     private String remark;
     private Department department;
 }

  2. mapper: EmployeeWithDepartmentMapper.xml

     <mapper namespace="com.cj.mybatis.mapper.EmployeeWithDepartmentMapper">
    <!-- <resultMap type="com.cj.mybatis.entity.EmployeeWithDepartment" id="EmpWithDeptMap">
     <id column="id" property="id"/>
     <result column="name" property="name"/>
     <result column="remark" property="remark"/>
     <association property="department" javaType="com.cj.mybatis.entity.Department" >
         <id column="dept_id" property="id"/>
         <result column="dept_name" property="name"/>
         <result column="dept_remark" property="remark" />
     </association>
   </resultMap> -->
   <resultMap id="EmpWithDeptMap" type="com.cj.mybatis.entity.EmployeeWithDepartment" autoMapping="true">
     <association property="department" 
         javaType="com.cj.mybatis.entity.Department" 
         columnPrefix="dept_" 
         autoMapping="true" />
   </resultMap>
   <select id="getEmployee" resultMap="EmpWithDeptMap">
     select 
         a.id id,a.name name,a.remark remark,
         b.id dept_id,b.name dept_name,b.remark dept_remark
     from pe_employee a left join pe_department b on a.department_id=b.id where a.id = #{id}
   </select>

   <resultMap type="com.cj.mybatis.entity.EmployeeWithDepartment" id="EmpWithDeptLazyMap">
     <id column="id" property="id"/>
     <result column="name" property="name"/>
     <result column="remark" property="remark"/>
     <association property="department" 
         select="com.cj.mybatis.mapper.DepartmentMapper.getDepartment" 
         column="department_id"
         fetchType="lazy">
     </association>
   </resultMap>
   <select id="getEmployeeLazy" resultMap="EmpWithDeptLazyMap">
     select * from pe_employee where id = #{id}
   </select>
 </mapper>

  3. test

     @Test
 public void testEmployeeWithDepartment() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         EmployeeWithDepartmentMapper edMapper = session.getMapper(EmployeeWithDepartmentMapper.class);

         System.out.println("------------getEmployee------------");
         EmployeeWithDepartment emp = edMapper.getEmployee(1);
         System.out.println(emp);

         System.out.println("------------getEmployeeLazy------------");
         emp = edMapper.getEmployeeLazy(1);
         System.out.println(emp.getName());
         System.out.println(emp.getDepartment());
         System.out.println(emp);
     }
 }
  4. result
     ------------getEmployee------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select a.id id,a.name name,a.remark remark, b.id dept_id,b.name dept_name,b.remark dept_remark from pe_employee a left join pe_department b on a.department_id=b.id where a.id = ? 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 1(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 1
 EmployeeWithDepartment [id=1, name=Test1, remark=This is Test1, department=Department [id=1, name=Dep-A, remark=This is Department A]]
 ------------getEmployeeLazy------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_employee where id = ? 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 1(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 1
 Test1
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_department where id = ? 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 1(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 1
 Department [id=1, name=Dep-A, remark=This is Department A]
 EmployeeWithDepartment [id=1, name=Test1, remark=This is Test1, department=Department [id=1, name=Dep-A, remark=This is Department A]]

resultMap: collection

OneToMany

Sample:DepartmentWithEmployeeMapper.xml

  1. Pojo: DepartmentWithEmployees
     @Data
 public class DepartmentWithEmployees {
     private Integer id;
     private String name;
     private String remark; 
     private List<Employee> employees;
 }

  2. mapper: DepartmentWithEmployees.xml

     <mapper namespace="com.cj.mybatis.mapper.DepartmentWithEmployeesMapper">

    <!-- <resultMap type="com.cj.mybatis.entity.DepartmentWithEmployees" id="DeptWithEmpsMap">
     <id column="id" property="id"/>
     <result column="name" property="name"/>
     <result column="remark" property="remark"/>
     <collection property="employees" ofType="com.cj.mybatis.entity.Employee">
         <id column="emp_id" property="id"/>
         <result column="emp_name" property="name"/>
         <result column="emp_remark" property="remark" />
         <result column="emp_deptartment_id" property="departmentId" />
     </collection>
   </resultMap> -->
   <resultMap type="com.cj.mybatis.entity.DepartmentWithEmployees" 
     id="DeptWithEmpsMap" autoMapping="true">
     <id column="id" property="id"/> <!-- must set! -->
     <collection property="employees" ofType="com.cj.mybatis.entity.Employee" 
         columnPrefix="emp_" autoMapping="true">
     </collection>
   </resultMap>
   <select id="getDepartment" resultMap="DeptWithEmpsMap">
     select
         a.id id,a.name name,a.remark remark,
         b.id emp_id,b.name emp_name,b.remark emp_remark,b.department_id emp_department_id
     from pe_department a 
     left join pe_employee b on a.id=b.department_id 
     where a.id=#{id}
   </select>

   <resultMap type="com.cj.mybatis.entity.DepartmentWithEmployees" id="DeptWithEmpsLazyMap">
     <id column="id" property="id"/>
     <result column="name" property="name"/>
     <result column="remark" property="remark"/>
     <collection property="employees" 
         select="com.cj.mybatis.mapper.EmployeeMapper.listEmployeesByDept"
         column="id" fetchType="lazy">
     </collection>
   </resultMap>
   <select id="getDepartmentLazy" resultMap="DeptWithEmpsLazyMap">
     select * from pe_department where id = #{id}
   </select>

 </mapper>

  3. test

     @Test
 public void testDepartmentWithEmployees() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         DepartmentWithEmployeesMapper deMapper = session.getMapper(DepartmentWithEmployeesMapper.class);

         System.out.println("------------getDepartment------------");
         DepartmentWithEmployees dept = deMapper.getDepartment(1);
         System.out.println(dept);

         System.out.println("------------getDepartmentLazy------------");
         dept = deMapper.getDepartmentLazy(1);
         System.out.println(dept.getName());
         System.out.println(dept.getEmployees());
     }
 }
  4. result
     ------------getDepartment------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select a.id id,a.name name,a.remark remark, b.id emp_id,b.name emp_name,b.remark emp_remark,b.department_id emp_department_id from pe_department a left join pe_employee b on a.id=b.department_id where a.id=? 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 1(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 3
 DepartmentWithEmployees [id=1, name=Dep-A, remark=This is Department A, employees=[Employee [id=1, name=Test1, remark=This is Test1, departmentId=1], Employee [id=2, name=Test2, remark=This is Test2, departmentId=1], Employee [id=3, name=Test3, remark=This is Test3, departmentId=1]]]
 ------------getDepartmentLazy------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_department where id = ? 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 1(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 1
 Dep-A
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_employee where department_id=? 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 1(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 3
 [Employee [id=1, name=Test1, remark=This is Test1, departmentId=1], Employee [id=2, name=Test2, remark=This is Test2, departmentId=1], Employee [id=3, name=Test3, remark=This is Test3, departmentId=1]]

resultMap: discriminator

  1. Pojo
     @Data
 public class UserRole {
     private Integer id;
     private Integer userId;
     private String userType;
     private String roleName;
 }

  2. mapper: UserRoleMapper.xml

     <mapper namespace="com.cj.mybatis.mapper.UserRoleMapper">

   <select id="listUserRoles" resultType="UserRole">
     select * from pe_role
   </select>

   <resultMap id="userRoleMap" type="com.cj.mybatis.entity.UserRole">
     <id column="id" property="id"/>
     <result column="role_name" property="roleName" />
     <result column="user_id" property="userId" />
     <result column="user_type" property="userType" />
     <discriminator javaType="string" column="user_type">
         <case value="Employee">
             <result column="emp_name" property="name" />
             <result column="emp_remark" property="remark" />
         </case>
         <case value="Department">
             <result column="dept_name" property="name" />
             <result column="dept_remark" property="remark" />
         </case>
     </discriminator>
   </resultMap>
   <select id="listUserRoleWithDetails" resultMap="userRoleMap">
     select 
         a.id id,a.role_name role_name,a.user_id user_id,a.user_type user_type,
         b.id emp_id,b.name emp_name,b.remark emp_remark,b.department_id emp_dept_id,
         c.id dept_id,c.name dept_name,c.remark dept_remark 
     from pe_role a 
     left join pe_employee b on a.user_id=b.id and a.user_type='Employee'
     left join pe_department c on a.user_id=c.id and a.user_type='Department';
   </select>
 </mapper>

  3. test

     @Test
 public void testUserRole() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         UserRoleMapper urMapper = session.getMapper(UserRoleMapper.class);

         System.out.println("------------listUserRole------------");
         List<UserRole>  list = urMapper.listUserRoles();
         System.out.println(list);

         System.out.println("------------listUserRoleWithDetails------------");
         list = urMapper.listUserRoleWithDetails();
         for(UserRole ur : list) {
             System.out.println(ur);
         }
     }
 }
  4. result
     ------------listUserRole------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_role 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 6
 [UserRole [id=1, userId=1, userType=Employee, roleName=manager, name=null, remark=null], UserRole [id=2, userId=2, userType=Employee, roleName=leader, name=null, remark=null], UserRole [id=3, userId=3, userType=Employee, roleName=temporary, name=null, remark=null], UserRole [id=4, userId=1, userType=Department, roleName=IT-01, name=null, remark=null], UserRole [id=5, userId=2, userType=Department, roleName=IT-02, name=null, remark=null], UserRole [id=6, userId=3, userType=Department, roleName=Finance-01, name=null, remark=null]]
 ------------listUserRoleWithDetails------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select a.id id,a.role_name role_name,a.user_id user_id,a.user_type user_type, b.id emp_id,b.name emp_name,b.remark emp_remark,b.department_id emp_dept_id, c.id dept_id,c.name dept_name,c.remark dept_remark from pe_role a left join pe_employee b on a.user_id=b.id and a.user_type='Employee' left join pe_department c on a.user_id=c.id and a.user_type='Department'; 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 6
 UserRole [id=1, userId=1, userType=Employee, roleName=manager, name=Test1, remark=This is Test1]
 UserRole [id=2, userId=2, userType=Employee, roleName=leader, name=Test2, remark=This is Test2]
 UserRole [id=3, userId=3, userType=Employee, roleName=temporary, name=Test3, remark=This is Test3]
 UserRole [id=4, userId=1, userType=Department, roleName=IT-01, name=Dep-A, remark=This is Department A]
 UserRole [id=5, userId=2, userType=Department, roleName=IT-02, name=Dep-B, remark=This is Department B]
 UserRole [id=6, userId=3, userType=Department, roleName=Finance-01, name=Dep-C, remark=This is Department C]

TypeHandler

Mybatis类型转换介绍

在整个过程中，进行数据库类型和JavaBean类型的映射 (Table column => JavaBean property)

  • DefaultParameterHandler typeHandler.setParameter(ps,i+1,value,jdbcType);
  • DefaultResultSetHandler typeHandler.getResult(rs,column);

配置

  • Method 1: 全局配置(mybatis-config.xml) => 配置<typeHandlers>,<typeHandler>

      <typeHandlers>
      <typeHandler handler="com.cj.mybatis.extend.MyEnumCodeTypeHandler" javaType="com.cj.mybatis.entity.EmployeeStatusEnum"/>
      <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.cj.mybatis.entity.UserTypeEnum"/>
  </typeHandlers>

      <!-- then in XxxMapper.xml : -->
  <insert id="insertOnEnumOrdinal" useGeneratedKeys="true" keyProperty="id" >
      insert into pe_role2(role_name,user_id,user_type)
      values(
          #{roleName},
          #{userId},
          #{userType}
      )
  </insert>
  <select id="listOnEnumOrdinal" resultType="UserRole">
      select * from pe_role2
  </select>

  • Method 2: 局部配置(XxxMapper.xml) => 处理某个字段时设置 eg: #{status,typeHandler=xxx},<resultMap> <result column="" property="" typeHandler="xxx">

      <insert id="insertOnEnumOrdinal" useGeneratedKeys="true" keyProperty="id" 
      parameterType="com.cj.mybatis.entity.UserRole"> must set parameterType!
      insert into pe_role2(role_name,user_id,user_type)
      values(
          #{roleName},
          #{userId},
          #{userType,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}
      )
  </insert>

  <resultMap type="com.cj.mybatis.entity.UserRole" id="userRoleEnumOrdinalMap" 
      autoMapping="true">
      <id column="id" property="id"/>
      <result column="user_type" property="userType" 
          typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>
  </resultMap>
  <select id="listOnEnumOrdinal" resultMap="userRoleEnumOrdinalMap">
      select * from pe_role2
  </select>

  • Method 3: 在自定义TypeHandler类上添加注解 @MappedTypes(value={Xxx.class}) / @MappedJdbcTypes(...) 注册 (eg: @MappedTypes({EmployeeStatusEnum.class}))

处理Enum

处理枚举对象:

  • 默认使用EnumTypeHandler，即操作的是枚举的名字 (=> select/insert: use enum name)
  • 可改变使用EnumOrdinalTypeHandler,即操作的是枚举的ordinal()值(eg: 0,1,2,... => select/insert: use enum ordinal )

Sample: EnumTypeHandler

=> column: user_type(Employee,Department)

  1. enum
     public enum UserTypeEnum {
     Employee,Department
 }
  2. entity
     @Data
 public class UserRole {
     private Integer id;
     private Integer userId;
     private UserTypeEnum userType;
     private String roleName;
 }

  3. mapper

     <select id="listUserRoles" resultType="UserRole">
     select * from pe_role
 </select>

 <insert id="insertUserRole" useGeneratedKeys="true" keyProperty="id">
     insert into pe_role(role_name,user_id,user_type)
     values(#{roleName},#{userId},#{userType})
 </insert>

  4. test

     @Test
 public void testInsert() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         UserRoleMapper userRoleMapper = session.getMapper(UserRoleMapper.class);

         UserRole userRole=new UserRole();
         userRole.setRoleName("PE-01");
         userRole.setUserId(4);
         userRole.setUserType(UserTypeEnum.Employee);

         Integer result = userRoleMapper.insertUserRole(userRole);
         System.out.println(result);
         System.out.println(userRole.getId());

         session.commit();
     }
 }
 /*
  Result Sample:
  [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: insert into pe_role(role_name,user_id,user_type) values(?,?,?) 
  [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: PE-01(String), 4(Integer), Employee(String)
  [QC] DEBUG BaseJdbcLogger.debug | <==    Updates: 1
  1
  8
  */

 @Test
 public void testList() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         UserRoleMapper urMapper = session.getMapper(UserRoleMapper.class);

         System.out.println("------------listUserRole------------");
         List<UserRole>  list = urMapper.listUserRoles();
         for(UserRole ur : list) {
             System.out.println(ur);
         }
     }
 }
 /*
 Result Sample:
 ------------listUserRole------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_role 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 7
 UserRole [id=1, userId=1, userType=Employee, roleName=manager, name=null, remark=null]
 UserRole [id=2, userId=2, userType=Employee, roleName=leader, name=null, remark=null]
 UserRole [id=3, userId=3, userType=Employee, roleName=temporary, name=null, remark=null]
 UserRole [id=4, userId=1, userType=Department, roleName=IT-01, name=null, remark=null]
 UserRole [id=5, userId=2, userType=Department, roleName=IT-02, name=null, remark=null]
 UserRole [id=6, userId=3, userType=Department, roleName=Finance-01, name=null, remark=null]
 UserRole [id=8, userId=4, userType=Employee, roleName=PE-01, name=null, remark=null]
 */

Sample: EnumOrdinalTypeHandler

=> column: user_type (0,1)

  1. enum
     public enum UserTypeEnum {
     Employee,Department
 }
  2. entity
     @Data
 public class UserRole {
     private Integer id;
     private Integer userId;
     private UserTypeEnum userType;
     private String roleName;
 }

  3. mapper

     <!-- method 1 -->
 <insert id="insertOnEnumOrdinal" useGeneratedKeys="true" keyProperty="id" 
     parameterType="com.cj.mybatis.entity.UserRole"> <!-- must set parameterType! -->
     insert into pe_role2(role_name,user_id,user_type)
     values(
         #{roleName},
         #{userId},
         #{userType,typeHandler=org.apache.ibatis.type.EnumOrdinalTypeHandler}
     )
   </insert>

 <resultMap type="com.cj.mybatis.entity.UserRole" id="userRoleEnumOrdinalMap" 
     autoMapping="true">
     <id column="id" property="id"/>
     <result column="user_type" property="userType" 
         typeHandler="org.apache.ibatis.type.EnumOrdinalTypeHandler"/>
 </resultMap>
 <select id="listOnEnumOrdinal" resultMap="userRoleEnumOrdinalMap">
     select * from pe_role2
 </select>

 <!-- method 2 
     1. mybatis-config.xml
     <typeHandlers>
         <typeHandler handler="org.apache.ibatis.type.EnumOrdinalTypeHandler" javaType="com.cj.mybatis.entity.UserTypeEnum"/>
     </typeHandlers>

     2. XxxMapper.xml
     <insert id="insertOnEnumOrdinal" useGeneratedKeys="true" keyProperty="id" >
         insert into pe_role2(role_name,user_id,user_type)
         values(
             #{roleName},
             #{userId},
             #{userType}
         )
     </insert>
     <select id="listOnEnumOrdinal" resultType="UserRole">
         select * from pe_role2
     </select>
 -->

  4. test

     @Test
 public void testInsertOnEnumOrdinalTypeHandler() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         UserRoleMapper userRoleMapper = session.getMapper(UserRoleMapper.class);

         UserRole userRole=new UserRole();
         userRole.setRoleName("PE-02");
         userRole.setUserId(4);
         userRole.setUserType(UserTypeEnum.Department);

         Integer result = userRoleMapper.insertOnEnumOrdinal(userRole);
         System.out.println(result);
         System.out.println(userRole.getId());

         session.commit();
     }
 }
 /*
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: insert into pe_role2(role_name,user_id,user_type) values( ?, ?, ? ) 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: PE-02(String), 4(Integer), 1(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==    Updates: 1
 1
 4
 */

 @Test
 public void testListOnEnumOrinalTypeHandler() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         UserRoleMapper urMapper = session.getMapper(UserRoleMapper.class);

         System.out.println("------------listUserRole------------");
         List<UserRole>  list = urMapper.listOnEnumOrdinal();
         for(UserRole ur : list) {
             System.out.println(ur);
         }
     }
 }
 /*
  ------------listUserRole------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_role2 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 3
 UserRole [id=1, userId=1, userType=Department, roleName=PE-00, name=null, remark=null]
 UserRole [id=3, userId=4, userType=Department, roleName=PE-01, name=null, remark=null]
 UserRole [id=4, userId=4, userType=Department, roleName=PE-02, name=null, remark=null]
 */

处理Enum: 自定义TypeHandler

=> column: status(100,200,300)

步骤:

  • 实现TypeHandler接口或者继承BaseTypeHandler
  • 注册TypeHandler:
    • method1: 使用@MappedTypes定义处理的java类型(使用@MappedJdbcTypes定义jdbcType类型)
    • method2: 在全局配置TypeHandler要处理的javaType
    • method3: 在自定义结果集标签(<resultMap>)或者参数处理(@{xx,typeHandler=xxx})时声明使用自定义TypeHandler进行处理

Sample:自定义TypeHandler处理枚举：在设置参数和取出结果集时自定义参数封装策略

  1. enum

     public enum EmployeeStatusEnum{

     NORMAL(100,"正常"),CANCEL(200,"注销"),DELETE(300,"删除");

     private Integer code;
     private String msg;

     private EmployeeStatusEnum(Integer code,String msg) {
         this.code=code;
         this.msg=msg;
     }

     public Integer getCode() {
         return code;
     }
     public String getMsg() {
         return msg;
     }
 }
  2. entity
     @Data
 public class Employee {
     private Integer id;
     private String name;
     private String remark;
     private Integer departmentId;
     private EmployeeStatusEnum status;
 }

  3. self defined TypeHandler: MyEnumCodeTypeHandler (实现TypeHandler接口，或者继承BaseTypeHandler)

     public class MyEnumCodeTypeHandler extends BaseTypeHandler<EmployeeStatusEnum>{

     private final Class<EmployeeStatusEnum> type;
     private final Map<Integer,EmployeeStatusEnum> enumMap;
     private final EmployeeStatusEnum[] enums;

     public MyEnumCodeTypeHandler(Class<EmployeeStatusEnum> type) {
         if (type == null) {
               throw new IllegalArgumentException("Type argument cannot be null");
         }
         this.type = type;
         this.enums = type.getEnumConstants();
         if (this.enums == null) {
           throw new IllegalArgumentException(type.getSimpleName() + " does not represent an enum type.");
         }
         enumMap=new HashMap<Integer,EmployeeStatusEnum>();
         for(EmployeeStatusEnum e:enums) {
             enumMap.put(e.getCode(), e);
         }
     }

     @Override
     public void setNonNullParameter(PreparedStatement ps, int i, EmployeeStatusEnum parameter, JdbcType jdbcType) throws SQLException {
         ps.setInt(i, parameter.getCode());
     }

     @Override
     public EmployeeStatusEnum getNullableResult(ResultSet rs, String columnName) throws SQLException {
         int code = rs.getInt(columnName);
         if (code == 0 && rs.wasNull()) {
           return null;
         }
         return toCodeEnum(code);
     }

     @Override
     public EmployeeStatusEnum getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
         int code = rs.getInt(columnIndex);
         if (code == 0 && rs.wasNull()) {
           return null;
         }
         return toCodeEnum(code);
     }

     @Override
     public EmployeeStatusEnum getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
         int code = cs.getInt(columnIndex);
         if (code == 0 && cs.wasNull()) {
           return null;
         }
         return toCodeEnum(code);
     }

     private EmployeeStatusEnum toCodeEnum(int code) {
         try {
           return this.enumMap.get(code);
         } catch (Exception ex) {
           throw new IllegalArgumentException("Cannot convert " + code + " to " + type.getSimpleName() + " by code value.", ex);
         }
     }
 }

  4. mapper

     <!-- Method 1 -->
 <insert id="insertEmployeeOnMyEnum" useGeneratedKeys="true" keyProperty="id" 
     parameterType="Employee">  <!-- must set parameterType! -->
     insert into pe_employee(name,remark,department_id,status)
     values (#{name},#{remark},#{departmentId},#{status,typeHandler=com.cj.mybatis.extend.MyEnumCodeTypeHandler})
 </insert>

 <resultMap type="Employee" id="employeesOnMyEnumMap" autoMapping="true">
     <id column="id" property="id"/>
     <result column="status" property="status" 
         typeHandler="com.cj.mybatis.extend.MyEnumCodeTypeHandler"/>
 </resultMap>
 <select id="listEmployeesOnMyEnum" resultMap="employeesOnMyEnumMap">
     select * from pe_employee
 </select>

 <!-- Method 2 
     1. mybatis-config.xml
         <typeHandlers>
             <typeHandler handler="com.cj.mybatis.extend.MyEnumCodeTypeHandler" javaType="com.cj.mybatis.entity.EmployeeStatusEnum"/>
         </typeHandlers>

     2. XxxMapper.xml
         <insert id="insertEmployeeOnMyEnum" useGeneratedKeys="true" keyProperty="id"> 
             insert into pe_employee(name,remark,department_id,status)
             values (#{name},#{remark},#{departmentId},#{status})
         </insert>

         <select id="listEmployeesOnMyEnum" resultType="Employee">
             select * from pe_employee
         </select>
 -->

  5. test

     @Test
 public void testInsertOnMyEnumTypeHandler() throws Exception{
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
         System.out.println("------------insertEmployeeOnMyEnum------------");
         Employee emp = new Employee();
         emp.setName("QA-02");
         emp.setRemark("This is Employee QA-02");
         emp.setDepartmentId(15);
         emp.setStatus(EmployeeStatusEnum.CANCEL);
         Integer result = employeeMapper.insertEmployeeOnMyEnum(emp);
         System.out.println(result);
         System.out.println(emp.getId());
         session.commit();
     }
 }
 /*
 ------------insertEmployeeOnMyEnum------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: insert into pe_employee(name,remark,department_id,status) values (?,?,?,?) 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: QA-02(String), This is Employee QA-02(String), 15(Integer), 200(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==    Updates: 1
 1
 72
 */

 @Test
 public void testListOnMyEnumTypeHandler() throws Exception{
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
         System.out.println("------------listEmployees------------");
         List<Employee>  list = employeeMapper.listEmployeesOnMyEnum();
         for(Employee ur : list) {
             System.out.println(ur);
         }
     }
 }
 /*
  ------------listEmployees------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_employee 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 38
 Employee [id=1, name=Test1, remark=This is Test1, depar
 Employee [id=68, name=Bat-19, remark=This is Employee Bat-19, departmentId=3, status=null]
 Employee [id=71, name=QA-01, remark=This is Employee QA-01, departmentId=15, status=NORMAL]
 Employee [id=72, name=QA-02, remark=This is Employee QA-02, departmentId=15, status=CANCEL]
 */

Dynamic Sql

  • Doc
  • 基于OGNL表达式简化SQL拼装
  • <if>
  • <choose> (<when>, <otherwise>)
  • <trim>,<where>, <set>
  • <foreach>
  • <bind>: create a variable out of an OGNL expression and bind it to the context
    • configured variables for dynamic code: _databaseId,_parameter
    • _databaseId: 代表当前数据库的别名（depending on database vendor, 配置databaseIdProvider标签）
    • _parameter: 代表整个参数（单个参数：_parameter即是这个参数；多个参数：参数会被封装成一个map，_parameter即代表这个map）
  • <sql> 可重用sql片段,<include> 引用定义的sql片段(里面可使用<property name="" value=""/>自定义参数和值，传给sql片段)
    • 注：<sql>中取include中property定义的属性，只能使用${xxx},不能使用#{xxx}

OGNL

Object Graph Navigation Language 对象图导航语言 (表达式语言，类似EL)

person.name
person.getName()
new com.cj.Persion('admin').name

调用静态方法

@java.lang.Math@PI
@java.util.UUID@randomUUID()
  • 运算符：+,-,*,/,%
  • 逻辑运算符： in,not in,>,>=,<,<=,==,!=
  • 注意：xml中特殊符号，如<,>等需要使用转义字符

Sample: Dynamic Filter select,if

<!-- 
Employee sample = new Employee();
sample.setName("Test1");
List<Employee> employees = employeeMapper.listEmployeesByExample(sample);
-->
<select id="listEmployeesByExample" resultType="Employee">
    select * from pe_employee 
    where 1=1
    <if test="id!=null"> and id=#{id} </if>
    <if test="name!=null"> and name=#{name} </if>
    <if test="remark!=null"> and remark=#{remark} </if>
</select>

Sample: Dynamic Update: update,set,if

<!--
Employee sample = new Employee();
sample.setId(9);
sample.setName("TestAB9");
sample.setRemark("This is Employee TestAB9");
Integer result= employeeMapper.updateEmployee(sample);
-->
<update id="updateEmployee">
    update pe_employee
    <set>
        <if test="name!=null">name=#{name},</if>
        <if test="remark!=null">remark=#{remark},</if>
        <if test="departmentId!=null">department_id=#{departmentId}</if>
    </set>
    where id=#{id}
</update>

Sample: foreach

<!-- List<Employee> employees= employeeMapper.listEmployeesByDeptIds(Arrays.asList(1,3,5)); -->
<select id="listEmployeesByDeptIds" resultType="Employee">
    select * from pe_employee where department_id in 
    <foreach collection="list" item="deptId" separator="," open="(" close=")">
        #{deptId}
    </foreach>
</select>

<!-- 批量插入：
    Mysql: 
        method1: insert into T(...) values (),(),...
        method2:
                insert into T(...) values ()
                insert into T(...) values ()
                ....

    Oracle:
        method1: insert into T(...) select ... from ...
        method2:
            begin
                insert into T(...) values ();
                insert into T(...) values ();
                ...
            end
 -->
<!-- public Integer insertMultipleEmployees(@param("emps")List<Employee> emps)-->
<insert id="insertMultipleEmployees">
   insert into pe_employees(name,remark,department_id) values
   <foreach collection="emps" item='emp' seperator=",">
       (#{emp.name},#{emp.remark},#{emp.departmentId})
   </foreach>
</insert>

<!-- 
    注：这种方式数据库连接属性需加上 allowMultiQueries=true 
    （ jdbc:mysql://localhost:3306/demo?allowMultiQueries=true ）
-->
<!-- 
<insert id="insertMultipleEmployees">
   <foreach collection="emps" item='emp' seperator=";">
       insert into pe_employees(name,remark,department_id) values
       (#{emp.name},#{emp.remark},#{emp.departmentId})
   </foreach>
</insert>
-->

Sample: bind

<!-- List<Employee> employees= employeeMapper.listEmployeesByNameLike("Test"); -->
<select id="listEmployeesByNameLike" resultType="Employee">
    <bind name="namePatten" value="'%' + name + '%'" />
    select * from pe_employee where name like #{namePatten}
</select>

Sample: _parameter,_databaseId

  1. _parameter

     <!-- 单参：listByName(String name)-->
 <select id="listByName" resultType="Employee">
     select * fro pe_employees 
     <if test="_parameter!=null">
         where last_name = #{_paramter}
     </if>
 </select>

 <!-- 多参：ListBySample(Employee emp)-->
 <select id="ListBySample" resultType="Employee">
     select * fro pe_employees 
     <if test="_parameter!=null">
         where last_name = #{_paramter.lastname}
     </if>
 </select>

  2. _databaseId

     <!-- 
 mybatis-config.xml:
 <databaseIdProvider type="DB_VENDOR"> 
     <property name="MySQL" value="mysql"/>
     <property name="Oracle" value="oracle"/>
     <property name="SQL Server" value="sqlserver"/>
   </databaseIdProvider>
 -->
 <!-- XxxMapper.xml-->
 <if test="_databaseId=='oracle'">
     select seq_employee.nextval from dual; 
 </if>

Sample: sql,include

<update id="updateEmployee">
    <!-- update pe_employee
    <set>
        <if test="name!=null">name=#{name},</if>
        <if test="remark!=null">remark=#{remark},</if>
        <if test="departmentId!=null">department_id=#{departmentId}</if>
    </set>
    where id=#{id} -->
    update pe_employee
    <set>
        <include refid="empUpdateSet"/>
    </set>
    where id=#{id}
</update>

<sql id="empUpdateSet">
    <if test="name!=null">name=#{name},</if>
    <if test="remark!=null">remark=#{remark},</if>
    <if test="departmentId!=null">department_id=#{departmentId}</if>
</sql>

Plugin

原理

  • 拦截器接口Interceptor,插件即拦截器实现类(implements Interceptor)
      package org.apache.ibatis.plugin;
  import java.util.Properties;
  public interface Interceptor {
    Object intercept(Invocation invocation) throws Throwable;
    default Object plugin(Object target) {
      return Plugin.wrap(target, this);
    }
    default void setProperties(Properties properties) {
      // NOP
    }
  }
  • 插件原理：
    • 四大对象每个创建的时候都有一个interceptorChain.pluginAll(target)
    • => 即插件介入：用拦截器们，通过动态代理机制一层层的包装目标对象，返回目标对象的代理对象，从而实现在目标对象执行目标方法之前进行拦截的效果(最终此代理对象可以拦截到四大对象的每一个执行)
    • eg:Configuration#newParameterHandler#interceptorChain.pluginAll(parameterHandler)
      public Object pluginAll(Object target) {
  for (Interceptor interceptor : interceptors) {
    target = interceptor.plugin(target);
  }
  return target;
}
  • 四大对象：
    • Executor (update, query, flushStatements, commit, rollback, getTransaction, close, isClosed)
    • ParameterHandler (getParameterObject, setParameters)
    • ResultSetHandler (handleResultSets, handleOutputParameters)
    • StatementHandler (prepare, parameterize, batch, update, query)
  • 插件会产生目标对象的代理对象，多个插件就会产生多层代理 => 创建动态代理的时候，按照插件配置顺序创建层层代理对象, 执行目标方法后，按照逆向顺序执行

自定义插件

  • 编写Interceptor实现类
  • 使用@Intercepts({@Signature(type=Xxx.class,method="",args=[])})注解完成插件签名

  • 将写好的插件注册到全局配置文件中<plugins> -> <plugin>

  • MyFirstPlugin.java

     @Intercepts({
     @Signature(type=StatementHandler.class,method="parameterize",args=Statement.class)
 })
 public class MyFirstPlugin implements Interceptor{
     @Override
     public Object intercept(Invocation invocation) throws Throwable {
         System.out.println("MyFirstPlugin#intercept:"+invocation.getMethod());

         // test: change sql parameter value
         Object target = invocation.getTarget();
         // 拿到target的元数据
         MetaObject metaObject = SystemMetaObject.forObject(target);
         // StatementHandler -> ParameterHandler -> parameterObject
         Object value = metaObject.getValue("parameterHandler.parameterObject");
         System.out.println("Get Sql Parameter Value:" + value);
         metaObject.setValue("parameterHandler.parameterObject", 11);

         Object proceed = invocation.proceed();
         return proceed;
     }

     @Override
     public Object plugin(Object target) {
         // 使用当前Interceptor封装目标对象，返回一个新的代理对象
         Object wrap = Plugin.wrap(target, this);
         System.out.println("MyFirstPlugin#plugin wrap:"+target);
         return wrap;
     }

     @Override
     public void setProperties(Properties properties) {
         // 获取Plugin注册时（mybatis-config.xml <plugins> <plugin>）设置的property
         System.out.println("MyFirstPlugin#setProperties:"+properties);
     }
 }
  • mybatis-config.xml
     <plugins>
     <plugin interceptor="com.cj.mybatis.extend.MyFirstPlugin">
         <property name="testname" value="first-plugin"/>
     </plugin>
 </plugins>
  • test
         @Test
 public void testGet() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
         Employee emp = employeeMapper.getEmployee(1);
         System.out.println(emp);
     }
 }

  • result

     MyFirstPlugin#setProperties:{testname=first-plugin}
 MyFirstPlugin#plugin wrap:org.apache.ibatis.executor.CachingExecutor@569cfc36
 MyFirstPlugin#plugin wrap:org.apache.ibatis.scripting.defaults.DefaultParameterHandler@6eceb130
 MyFirstPlugin#plugin wrap:org.apache.ibatis.executor.resultset.DefaultResultSetHandler@5c072e3f
 MyFirstPlugin#plugin wrap:org.apache.ibatis.executor.statement.RoutingStatementHandler@954b04f
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_employee where id = ? 
 MyFirstPlugin#intercept:public abstract void org.apache.ibatis.executor.statement.StatementHandler.parameterize(java.sql.Statement) throws java.sql.SQLException
 Get Sql Parameter Value:1
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 11(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 1
 Employee [id=11, name=Test-CC, remark=This is Employee CC, departmentId=3]

多个插件

  1. mybatis-config.xml
     <plugins>
     <plugin interceptor="com.cj.mybatis.extend.MyFirstPlugin">
         <property name="testname" value="first-plugin"/>
     </plugin>
     <plugin interceptor="com.cj.mybatis.extend.MySecondPlugin">
         <property name="testname" value="second-plugin"/>
     </plugin>
   </plugins>
  2. result ( First wrap,Second wrap -> Target execute -> Second intercept,First intercept )
     MyFirstPlugin#setProperties:{testname=first-plugin}
 MySecondPlugin#setProperties:{testname=second-plugin}
 MyFirstPlugin#plugin wrap:org.apache.ibatis.executor.CachingExecutor@569cfc36
 MySecondPlugin#plugin wrap:org.apache.ibatis.executor.CachingExecutor@569cfc36
 MyFirstPlugin#plugin wrap:org.apache.ibatis.scripting.defaults.DefaultParameterHandler@6eceb130
 MySecondPlugin#plugin wrap:org.apache.ibatis.scripting.defaults.DefaultParameterHandler@6eceb130
 MyFirstPlugin#plugin wrap:org.apache.ibatis.executor.resultset.DefaultResultSetHandler@5c072e3f
 MySecondPlugin#plugin wrap:org.apache.ibatis.executor.resultset.DefaultResultSetHandler@5c072e3f
 MyFirstPlugin#plugin wrap:org.apache.ibatis.executor.statement.RoutingStatementHandler@954b04f
 MySecondPlugin#plugin wrap:org.apache.ibatis.executor.statement.RoutingStatementHandler@954b04f
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_employee where id = ? 
 MySecondPlugin#intercept:public abstract void org.apache.ibatis.executor.statement.StatementHandler.parameterize(java.sql.Statement) throws java.sql.SQLException
 MyFirstPlugin#intercept:public abstract void org.apache.ibatis.executor.statement.StatementHandler.parameterize(java.sql.Statement) throws java.sql.SQLException
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 1(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 1
 Employee [id=1, name=Test1, remark=This is Test1, departmentId=1]

第三方插件: PageHelper

Doc

  1. dependency
     <dependency>
     <groupId>com.github.pagehelper</groupId>
     <artifactId>pagehelper</artifactId>
     <version>5.1.10</version>
 </dependency>
  2. mybatis-config.xml
     <!-- typeAliases ... -->
 <plugins>
      <plugin interceptor="com.github.pagehelper.PageInterceptor">
         <property name="rowBoundsWithCount" value="true"/>
     </plugin>
 </plugins>
 <!-- environments ... -->

  3. test: PageHelper.startPage(pageNum,pageSize)

     @Test
 public void testPage01() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);

         System.out.println("------------listEmployees------------");
         Page<Object> page = PageHelper.startPage(1, 4);
         List<Employee> employees = employeeMapper.listEmployees();
         for(Employee emp:employees) {
             System.out.println(emp);
         }
         System.out.println("Current Page:\t"+page.getPageNum());
         System.out.println("Page Size:\t"+page.getPageSize());
         System.out.println("Total Pages:\t"+page.getPages());
         System.out.println("Total Records:\t"+page.getTotal());
     }
 }

     ------------listEmployees------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: SELECT count(0) FROM pe_employee 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 1
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_employee LIMIT ? 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 4(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 4
 Employee [id=1, name=Test1, remark=This is Test1, departmentId=1]
 Employee [id=2, name=Test2, remark=This is Test2, departmentId=1]
 Employee [id=3, name=Test3, remark=This is Test3, departmentId=1]
 Employee [id=4, name=Dev4, remark=This is Dev4, departmentId=2]
 Current Page:   1
 Page Size:  4
 Total Pages:    4
 Total Records:  15

  4. test: new PageInfo<>(List<T> list)

     @Test
 public void testPage02() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);

         System.out.println("------------listEmployees------------");
         PageHelper.startPage(1, 4);
         List<Employee> employees = employeeMapper.listEmployees();
         PageInfo<Employee> pageInfo = new PageInfo<Employee>(employees);

         System.out.println("Current Page:\t"+pageInfo.getPageNum());
         System.out.println("Page Size:\t"+pageInfo.getPageSize());
         System.out.println("Total Pages:\t"+pageInfo.getPages());
         System.out.println("Total Records:\t"+pageInfo.getTotal());

         System.out.print("List Nav Pages:\t");
         for(int i : pageInfo.getNavigatepageNums()) {
             System.out.print(i+" ");
         }
         System.out.println();

         System.out.println("List Records:");
         List<Employee> results=pageInfo.getList();
         for(Employee emp : results) {
             System.out.println(emp);
         }
         System.out.println(results);
     }
 }

     ------------listEmployees------------
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: SELECT count(0) FROM pe_employee 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 1
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: select * from pe_employee LIMIT ? 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 4(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 4
 Current Page:   1
 Page Size:  4
 Total Pages:    4
 Total Records:  15
 List Nav Pages: 1 2 3 4 
 List Records:
 Employee [id=1, name=Test1, remark=This is Test1, departmentId=1]
 Employee [id=2, name=Test2, remark=This is Test2, departmentId=1]
 Employee [id=3, name=Test3, remark=This is Test3, departmentId=1]
 Employee [id=4, name=Dev4, remark=This is Dev4, departmentId=2]
 Page{count=true, pageNum=1, pageSize=4, startRow=0, endRow=4, total=15, pages=4, reasonable=false, pageSizeZero=false}[Employee [id=1, name=Test1, remark=This is Test1, departmentId=1], Employee [id=2, name=Test2, remark=This is Test2, departmentId=1], Employee [id=3, name=Test3, remark=This is Test3, departmentId=1], Employee [id=4, name=Dev4, remark=This is Dev4, departmentId=2]]

Cache

一级缓存

  • 本地缓存，sqlSession级别（默认开启，一个Map）
  • 与数据库同一次session查询到的数据会放在本地缓存中,以后获取相同数据，会直接从缓存中取而不用再去DB查询
  • 一级缓存失效情况（会再向DB发出查询）
    • 不同sqlSession
    • 同一sqlSeesion
      • 查询条件不同（一级缓存中还没有这个数据）
      • 两次查询中间执行了CUD操作（因为这次CUD可鞥会对当前数据有影响）
      • 手动清除了一级缓存（缓存清空）

二级缓存

  • 全局缓存，namespace级别（不同namespace查出的数据会放在自己对应的缓存中，需手动开启和配置）
  • 一个会话中查询数据放入一级缓存，会话提交或关闭后，一级缓存中数据保存到二级缓存中，新的会话查询信息，可参照二级缓存
  • 使用步骤：
    1. 开启全局二级缓存配置 <setting name="cacheEnabled" value="true"/>
    2. mapper中配置使用二级缓存 <cache/>
      • eviction 缓存回收策略 LRU(最近最少使用的，默认),FIFO(先进先出),SOFT(软引用),WEAK(弱引用)
      • flushInterval 缓存刷新间隔 （毫秒，多长时间清空一次，默认不清空）
      • readOnly 是否只读（true则认为只读，会将数据在缓存中的引用交给用户，不安全，速度快；false则会利用序列化反序列技术克隆一份给用户，安全，速度慢）
      • size 缓存存放多少元素
      • type 指定自定义缓存的全类名（实现Cache接口的自定义类全名）
    3. POJO实现序列化接口

缓存相关配置

  1. 全局二级缓存开关 <setting name="cacheEnabled" value="true/false" />
  2. mapper中<select>配置是否使用二级缓存 <select useCache="true/false" ... >
  3. 参照缓存：若想在命名空间中共享相同的缓存配置和实例，可使用<cache-ref namespace="...xxxMapper" />引用另一个缓存
  4. mapper中sql标签上配置sql执行后是否清除缓存（包括一级二级）
    • 增删改标签上的flushCache默认为true
    • 查询标签上的flushCache默认为false
  5. 清除当前session的一级缓存：程序执行sqlSession.clearCache()
  6. localCacheScope 本地缓存作用域 SESSION(一级缓存)/STATEMENT(禁用一级缓存)
  7. 注：
    • 在某一个作用域（一级缓存session/二级缓存namespace)进行了CUD后，默认该作用域下的所有select缓存将被clear
    • Mybatis提供了缓存接口Cache，供扩展实现自定义的二级缓存

第三方缓存整合

EhCache

  • 纯Java进程内缓存框架，是Hibernate中默认的CacheProvider
  • 使用步骤：
    1. 导入ehcache包，整合包(mybatis-echcache)，日志包
    2. 编写ehcache.xml配置文件
    3. mapper中配置<cache type="org.mybatis.caches.ehcache.EhcacheCache"/>标签

与SpringBoot整合

dependencies

<dependencies>
    <!-- springboot -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>

    <!-- mybatis -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.1</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>com.github.pagehelper</groupId>
        <artifactId>pagehelper</artifactId>
        <version>5.1.10</version>
    </dependency>

    <!-- druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.1.21</version>
    </dependency>

    <!-- for @ConfigurationProperties : optional ! -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-configuration-processor</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

druid

Github: druid | druid-spring-boot-starter

  1. application.yml

     server:
   port: 8080
   servlet:
     context-path: /demo

 spring:
   datasource:
     druid:
       url: jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true
       username: root
       password: 123456
       driver-class-name: com.mysql.cj.jdbc.Driver
 #      type: com.alibaba.druid.pool.DruidDataSource
       initial-size: 8
       min-idle: 1
       max-active: 20
       max-wait: 60000
       time-between-eviction-runsMillis: 60000
       min-evictable-idle-timeMillis: 300000
       validation-query: select 'x'
       test-while-idle: true
       test-on-borrow: false
       test-on-return: false
       pool-prepared-statements: false
       max-open-prepared-statements: 20
       max-pool-prepared-statement-per-connection-size: 20
       filters: stat,wall
       use-global-data-source-stat: true
       connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

  2. Configuration

     @Configuration
 public class DruidConfig {
     //doc: https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

     // inject configed druid properties
     @ConfigurationProperties(prefix = "spring.datasource.druid")
     @Bean
     public DataSource druidDataSource(){
         return new DruidDataSource();
     }

     //配置Druid的监控
     //1、配置一个管理后台的Servlet
     @Bean
     public ServletRegistrationBean<StatViewServlet> statViewServlet(){
         ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<StatViewServlet>(new StatViewServlet(), "/druid/*");
         Map<String,String> initParams = new HashMap<>();
         initParams.put("loginUsername","admin");
         initParams.put("loginPassword","123456");
         //initParams.put("allow","");//默认就是允许所有访问
         //initParams.put("deny","192.168.15.21");
         bean.setInitParameters(initParams);
         return bean;
     }

     //2、配置一个web监控的filter
     @Bean
     public FilterRegistrationBean<WebStatFilter> webStatFilter(){
         FilterRegistrationBean<WebStatFilter> bean = new FilterRegistrationBean<WebStatFilter>();
         bean.setFilter(new WebStatFilter());
         Map<String,String> initParams = new HashMap<>();
         initParams.put("exclusions","*.js,*.css,/druid/*");
         bean.setInitParameters(initParams);
         //bean.setUrlPatterns(Arrays.asList("/*"));
         return  bean;
     }
 }
  3. start application,then visit: http://localhost:8080/demo/druid to login

Druid Monitor

MyBatis(单数据源)

  1. application.yml

     server:
   port: 8080
   servlet:
     context-path: /demo

 spring:
   datasource:
     druid:
       url: jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true
       username: root
       password: 123456
       driver-class-name: com.mysql.cj.jdbc.Driver
       # type: com.alibaba.druid.pool.DruidDataSource
       initial-size: 8
       min-idle: 1
       max-active: 20
       max-wait: 60000
       time-between-eviction-runsMillis: 60000
       min-evictable-idle-timeMillis: 300000
       validation-query: select 'x'
       test-while-idle: true
       test-on-borrow: false
       test-on-return: false
       pool-prepared-statements: false
       max-open-prepared-statements: 20
       max-pool-prepared-statement-per-connection-size: 20
       filters: stat,wall
       use-global-data-source-stat: true
       connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

 mybatis:
   mapper-locations: classpath:mybatis/mapper/*Mapper.xml
   config-location:  classpath:mybatis-config.xml

  2. Druid Configuration: DruidConfig.java

     @Configuration
 public class DruidConfig {

     //https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

     @ConfigurationProperties(prefix = "spring.datasource.druid")
     @Bean
     public DataSource druidDataSource(){
         return new DruidDataSource();
     }

     // 配置Druid的监控 & 监控的Filter
     // ...
 }

  3. Global Config: mybatis-config.xml

     <?xml version="1.0" encoding="UTF-8" ?>
 <!DOCTYPE configuration
   PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
   "http://mybatis.org/dtd/mybatis-3-config.dtd">
 <configuration>
   <settings>
     <setting name="mapUnderscoreToCamelCase" value="true"/>
     <!-- <setting name="logImpl" value="STDOUT_LOGGING" /> -->
   </settings>

   <typeAliases>
     <package name="com.cj.mybatis.entity"/> 
   </typeAliases>

   <plugins>
      <plugin interceptor="com.github.pagehelper.PageInterceptor">
         <property name="rowBoundsWithCount" value="true"/>
     </plugin>
   </plugins>
 </configuration>

  4. XxxMapper.xml: mybatis/mapper/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.cj.mybatis.mapper.DepartmentMapper">

   <select id="listAll" resultType="Department">
     select * from pe_department
   </select>

 </mapper>

  5. Dao Interface: com/cj/mybatis/mapper/DepartmentMapper.java

     public interface DepartmentMapper {
     public List<Department> listAll();
 }

  6. Entity: com/cj/mybatis/entity/Department.java

     @Data
 public class Department {  
     private Integer id;
     private String name;
     private String remark;
 }

  7. Service: com/cj/mybatis/service/DepartmentService.java

     @Service
 public class DepartmentService {

     @Autowired
     private DepartmentMapper departmentMapper;

     public List<Department> listAll(){
         return this.departmentMapper.listAll();
     }
 }

  8. Controller

     @RestController
 public class DepartmentController {

     @Autowired
     DepartmentService departmentService;

     @GetMapping("/departments")
     public Object listAll() {
         return ResponseUtil.ok(departmentService.listAll());
     }
 }

  9. util/ResponseUtil.java

     public class ResponseUtil {

     private boolean success;
     private Object data;

     public ResponseUtil(boolean success, Object data) {
         this.success = success;
         this.data = data;
     }

     public static ResponseUtil result(boolean success,Object data) {
         return new ResponseUtil(success, data);
     }

     public static ResponseUtil ok(Object data) {
         return new ResponseUtil(true,data);
     }

     public static ResponseUtil fail(Object data) {
         return new ResponseUtil(false,data);
     }

     // Getter & Setter ...
     // ....

 }

MyBatis(多数据源)

  1. application.yml

     server:
   port: 8080
   servlet:
     context-path: /demo

 spring:
    datasource:
       first:
          url: jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
          # type: com.alibaba.druid.pool.DruidDataSource
          initial-size: 8
          min-idle: 1
          max-active: 20
          max-wait: 60000
          time-between-eviction-runsMillis: 60000
          min-evictable-idle-timeMillis: 300000
          validation-query: select 'x' FROM DUAL
          test-while-idle: true
          test-on-borrow: false
          test-on-return: false
          pool-prepared-statements: false
          max-open-prepared-statements: 20
          max-pool-prepared-statement-per-connection-size: 20
          filters: stat,wall
          use-global-data-source-stat: true
          connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
       second:
          url: jdbc:mysql://localhost:3306/demo2?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowMultiQueries=true
          username: root
          password: 123456
          driver-class-name: com.mysql.cj.jdbc.Driver
          # type: com.alibaba.druid.pool.DruidDataSource
          initial-size: 8
          min-idle: 1
          max-active: 20
          max-wait: 60000
          time-between-eviction-runsMillis: 60000
          min-evictable-idle-timeMillis: 300000
          validation-query: select 'x' FROM DUAL
          test-while-idle: true
          test-on-borrow: false
          test-on-return: false
          pool-prepared-statements: false
          max-open-prepared-statements: 20
          max-pool-prepared-statement-per-connection-size: 20
          filters: stat,wall
          use-global-data-source-stat: true
          connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

 # config sqlSessionFactory bean,this would be useless!         
 # mybatis:
 #   mapper-locations: classpath:mybatis/mapper/*Mapper.xml
 #   config-location:  classpath:mybatis-config.xml

  2. Configuration

     @Configuration
 @MapperScan(basePackages="com.cj.mybatis.dao.first",sqlSessionTemplateRef="firstSqlSessionTemplate")
 public class FirstDruidConfig {

 //  @Primary
     @Bean(name="firstDataSource")
     @ConfigurationProperties(prefix="spring.datasource.first")
     public DataSource firstDataSource() {
         return DruidDataSourceBuilder.create().build();
     }

 //  @Primary
     @Bean(name="firstSqlSessionFactory")
     public SqlSessionFactory firstSqlSessionFactory(@Qualifier("firstDataSource") DataSource dataSource) 
             throws Exception {
         SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
         factoryBean.setDataSource(dataSource);

         //factoryBean.setTypeAliasesPackage("com.cj.mybatis.entity");
         factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                 .getResources("classpath:mybatis/mapper/first/*Mapper.xml"));
         factoryBean.setConfigLocation(new PathMatchingResourcePatternResolver()
                 .getResource("classpath:mybatis-config.xml"));

         return factoryBean.getObject(); 
     }

 //  @Primary
     @Bean(name="firstTransactionManager")
     public DataSourceTransactionManager firstDataSourceTransactionManager(@Qualifier("firstDataSource") DataSource dataSource) {
         return new DataSourceTransactionManager(dataSource);
     }

 //  @Primary
     @Bean(name="firstSqlSessionTemplate")
     public SqlSessionTemplate firstSqlSessionTemplate(@Qualifier("firstSqlSessionFactory") SqlSessionFactory sqlSessionFactory) 
             throws Exception {
         return new SqlSessionTemplate(sqlSessionFactory);
     }
 }

     @Configuration
 @MapperScan(basePackages="com.cj.mybatis.dao.second",sqlSessionTemplateRef="secondSqlSessionTemplate")
 public class SecondDruidConfig {

     @Bean(name="secondDataSource")
     @ConfigurationProperties(prefix="spring.datasource.second")
     public DataSource secondDataSource() {
         return DruidDataSourceBuilder.create().build();
     }

     @Bean(name="secondSqlSessionFactory")
     public SqlSessionFactory secondSqlSessionFactory(@Qualifier("secondDataSource") DataSource dataSource) 
             throws Exception {
         SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
         factoryBean.setDataSource(dataSource);

         //factoryBean.setTypeAliasesPackage("com.cj.mybatis.entity");
         factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver()
                 .getResources("classpath:mybatis/mapper/second/*Mapper.xml"));
         factoryBean.setConfigLocation(new PathMatchingResourcePatternResolver()
                 .getResource("classpath:mybatis-config.xml"));
         return factoryBean.getObject();
     }

     @Bean(name="secondTransactionManager")
     public DataSourceTransactionManager secondDataSourceTransactionManager(@Qualifier("secondDataSource") DataSource dataSource) {
         return new DataSourceTransactionManager(dataSource);
     }

     @Bean(name="secondSqlSessionTemplate")
     public SqlSessionTemplate secondSqlSessionTemplate(@Qualifier("secondSqlSessionFactory") SqlSessionFactory sqlSessionFactory) 
             throws Exception {
         return new SqlSessionTemplate(sqlSessionFactory);
     }
 }

     // Druid Monitor 控制台
 @Configuration
 public class DruidConfig {

     @Bean
     public ServletRegistrationBean<StatViewServlet> statViewServlet(){
         ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<StatViewServlet>(new StatViewServlet(), "/druid/*");
         Map<String,String> initParams = new HashMap<>();
         initParams.put("loginUsername","admin");
         initParams.put("loginPassword","123456");
         bean.setInitParameters(initParams);
         return bean;
     }

     @Bean
     public FilterRegistrationBean<WebStatFilter> webStatFilter(){
         FilterRegistrationBean<WebStatFilter> bean = new FilterRegistrationBean<WebStatFilter>();
         bean.setFilter(new WebStatFilter());
         Map<String,String> initParams = new HashMap<>();
         initParams.put("exclusions","*.js,*.css,/druid/*");
         bean.setInitParameters(initParams);
         bean.setUrlPatterns(Arrays.asList("/*"));
         return  bean;
     }
 }

  3. 目录划分：

    • resources:
      • mybatis/mapper/first/*Mapper.xml
      • mybatis/mapper/second/*Mapper.xml
    • java:
      • com.cj.mybatis.dao.first: *Mapper.java
      • com.cj.mybatis.dao.second: *Mapper.java

  4. Service: 注意事务时指定具体事务名@Transactional(xxx)

     @Service
 public class EmployeeService {

     @Autowired
     private EmployeeMapper employeeMapper;

     @Transactional("firstTransactionManager")
     public Integer updateEmployee(Employee emp) {
         return employeeMapper.updateEmployee(emp);
     }
 }

     @Service
 public class DepartmentService {

     @Autowired
     private DepartmentMapper departmentMapper;

     @Transactional("secondTransactionManager")
     public List<Integer> insertMultipleDepartments(int count) {
         List<Integer> results=new ArrayList<>();
         for(int i=0;i<count;i++) {
             Department dept = new Department(null,"TDept-"+i,"This is TDept-"+i);
             results.add(departmentMapper.insert(dept));
         }
         return results;
     }
 }

高级

运行原理

  1. SqlSessionFactoryBuilder#build => 创建SqlSessionFactory （默认实现类：DefaultSqlSessionFactory）
    • Configuration对象，封装了所有配置文件（全局，sql映射配置文件）的详细信息
    • 注：会解析XxxMapper.xml中增删改查标签的每一个标签每一个属性，封装成一个MappedStatement(一个MappedStatement就代表一个CRUD标签的详细信息)
  2. SqlSessionFactory#openSession => 创建一次连接会话SqlSession对象 (默认实现类:DefaultSqlSession)
    • 里面包括Configuration对象和根据全局配置文件中的defaultExecutorType创建出对应的Executor
  3. sqlSession#getMapper => 返回mapper接口对应的代理对象MapperProxy（implements XxxMapper,里面包括SqlSession对象）

  4. 使用返回的代理对象MapperProxy调用接口，执行配置的CRUD方法

    1. 调用SqlSession的CRUD（Executor） 2) 创建StatementHandler对象（同时也会创建出ParameterHandler，ResultSetHandler） 3) 调用StatementHandler预编译参数及设置参数值，使用ParameterHandler处理预编译参数 4) 调用StatementHandler的CRUD方法 => JDBC:Statement,PreparedStatement,... 5) 使用ResultSetHandler封装结果集

  5. 总结(主要对象):

    • Executor (update,query,flushStatements,commit,rollback,getTransaction,close,isClosed) 执行sql
    • ParameterHandler (getParameterObject,setParameters) 处理预编译参数
    • ResultSetHandler (handleResultSet,handleOutputParameters) 处理结果集
    • StatemnetHandler (prepare,parameterize,batch,update,query) 处理Sql语句预编译，设置参数等
    • TypeHandler 用来在整个过程中，处理Table column => JavaBean property 映射
      • DefaultParameterHandler typeHandler.setParameter(ps,i+1,value,jdbcType);
      • DefaultResultSetHandler typeHandler.getResult(rs,column);
    • 注： 四大对象(Executor,ParameterHandler,ResultSetHandler,StatementHandler)每个创建的时候都会执行interceptorChain.pluginAll(parameterHandler) 操作，执行插件拦截器

存储过程

  1. mysql procedure:

     -- 创建
 delimiter $
 create procedure get_page_result(
     IN p_pageNum INTEGER,
     IN p_pageSize INTEGER,
     OUT total Integer
 )
 begin
     declare offsetNum int default 0;
     if p_pageNum is null then
         set p_pageNum=0;
     end if;
     if p_pageSize is null then
         set p_pageSize=0;
     end if;
     set offsetNum = (p_pageNum-1) * p_pageSize;
     select count(1) into total from pe_employee ;
     select * from pe_employee limit offsetNum,p_pageSize;
 end $

 -- 测试
 set @total=0;
 call get_page_result(2,3,@total);
 select @total;

 -- 删除
 drop procedure get_page_result;

  2. XxxMapper.xml

     <!-- 
 public List<Employee> getPageResult(MyPageInfo page);

 @Data
 public class MyPageInfo {
     private Integer pageNum;
     private Integer pageSize;
     private Integer total;
 }
 -->
 <select id="getPageResult" statementType="CALLABLE" resultType="Employee">
     {call get_page_result(
         #{pageNum,mode=IN},
         #{pageSize,mode=IN},
         #{total,mode=OUT,jdbcType=INTEGER}
     )}
 </select>

  3. test

     @Test
 public void testProcedure() throws IOException {
     SqlSessionFactory factory = getSqlSessionFactory();
     try(SqlSession session=factory.openSession()){
         EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);

         MyPageInfo page = new MyPageInfo();
         page.setPageNum(13);
         page.setPageSize(3);
         page.setTotal(0);
         List<Employee> list = employeeMapper.getPageResult(page);
         for(Employee emp:list) {
             System.out.println(emp);
         }
         System.out.println(page.getTotal());
     }
 }
 /*
 [QC] DEBUG BaseJdbcLogger.debug | ==>  Preparing: {call get_page_result( ?, ?, ? )} 
 [QC] DEBUG BaseJdbcLogger.debug | ==> Parameters: 13(Integer), 3(Integer)
 [QC] DEBUG BaseJdbcLogger.debug | <==      Total: 3
 [QC] DEBUG BaseJdbcLogger.debug | <==    Updates: 0
 Employee [id=71, name=QA-01, remark=This is Employee QA-01, departmentId=15, status=NORMAL]
 Employee [id=72, name=QA-02, remark=This is Employee QA-02, departmentId=15, status=CANCEL]
 Employee [id=73, name=QA-02, remark=This is Employee QA-02, departmentId=15, status=CANCEL]
 43
  */

批量操作

  • 非批量：预编译sql & 设置参数 => 执行10000次 SqlSession session=factory.openSession()
  • 批量：预编译sql & 设置参数 10000次 => 执行1次 SqlSession session=factory.openSession(ExecutorType.BATCH)
  • 注： springboot中可以注入sqlSessionFactory.openSession(ExecutorType.BATCH)来获取可以执行批量操作的sqlSession

Sample:

@SpringBootTest
@RunWith(SpringJUnit4ClassRunner.class)
public class BathExecutorTest {

    @Autowired
    private SqlSessionFactory sqlSessionFactory;

    /*
    EmployeeMapper.xml:
    <insert id="insertEmployee" useGeneratedKeys="true" keyProperty="id">
        insert into pe_employee(name,remark,department_id) values (#{name},#{remark},#{departmentId})
    </insert>
    */
    @Test
    public void testBatchExecutor() {
        try(SqlSession session=sqlSessionFactory.openSession(ExecutorType.BATCH)){
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
            for(int i=0;i<5;i++) {
                Employee emp = new Employee(null,"Test-"+i,"This is Test-"+i,3);
                Integer result = employeeMapper.insertEmployee(emp);
                System.out.println(result+","+emp.getId());
            }
            System.out.println("Done");
        }
    }
}
/*
==>  Preparing: insert into pe_employee(name,remark,department_id) values (?,?,?) 
==> Parameters: Test-0(String), This is Test-0(String), 3(Integer)
-2147482646,null
==> Parameters: Test-1(String), This is Test-1(String), 3(Integer)
-2147482646,null
==> Parameters: Test-2(String), This is Test-2(String), 3(Integer)
-2147482646,null
==> Parameters: Test-3(String), This is Test-3(String), 3(Integer)
-2147482646,null
==> Parameters: Test-4(String), This is Test-4(String), 3(Integer)
-2147482646,null
*/

More

显示sql语句

  • method 1: mybatis-config.xml
      <settings>
      <setting name="mapUnderscoreToCamelCase" value="true"/>
      <setting name="logImpl" value="STDOUT_LOGGING" /> <!-- 控制台显示sql语句-->
  </settings>

  • method 2: log4j.properties

      log4j.rootLogger=error,Stdout
  # 控制台显示sql语句
  log4j.logger.com.cj.mybatis=debug

  # 控制台
  log4j.appender.Stdout=org.apache.log4j.ConsoleAppender
  log4j.appender.Stdout.layout=org.apache.log4j.PatternLayout
  log4j.appender.Stdout.layout.ConversionPattern=[QC] %p %C{1}.%M | %m%n
  log4j.appender.Stdout.Target=System.err

  • 与springboot结合: application.yml

      logging:
    level:
       root: warn
       com.cj.mybatis.mapper: debug

logback日志

  • application.yml

      # 默认就使用logback-spring.xml，可不配置
  logging:
    config: classpath:logback-spring.xml

  • logback-spring.xml

      <?xml version="1.0" encoding="UTF-8"?>
  <configuration>
      <conversionRule conversionWord="mycolor" converterClass="com.cj.mybatis.util.LogColor" />
      <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
          <layout class="ch.qos.logback.classic.PatternLayout">
              <Pattern>
                  %mycolor(%msg%n)
              </Pattern>
          </layout>
      </appender>

      <!-- 显示sql语句 -->
      <logger name="com.cj.mybatis" level="DEBUG" />
      <root level="info">
          <appender-ref ref="STDOUT" />
      </root>
  </configuration>

  • 彩色输出(自定义实现类：com.cj.mybatis.util.LogColor.java)

      package com.cj.mybatis.util;

  import ch.qos.logback.classic.Level;
  import ch.qos.logback.classic.spi.ILoggingEvent;
  import ch.qos.logback.core.pattern.color.ANSIConstants;
  import ch.qos.logback.core.pattern.color.ForegroundCompositeConverterBase;

  public class LogColor extends ForegroundCompositeConverterBase<ILoggingEvent>{

      @Override
      protected String getForegroundColorCode(ILoggingEvent event) {
          Level level = event.getLevel();
          switch(level.toInt()) {
              case Level.ERROR_INT:
                  return ANSIConstants.RED_FG;
              case Level.WARN_INT:
                  return ANSIConstants.YELLOW_FG;
              case Level.INFO_INT:
                  return ANSIConstants.BLUE_FG;
              case Level.DEBUG_INT:
                  return ANSIConstants.GREEN_FG;
              case Level.TRACE_INT:
                  return ANSIConstants.DEFAULT_FG;
              default:
                  return ANSIConstants.DEFAULT_FG;
          }
      }
  }

  • Logger使用示例：

      @RestControllerAdvice
  public class ExceptionControllerAdvice {

      private static final Logger LOGGER = LoggerFactory.getLogger(ExceptionControllerAdvice.class);

      @ExceptionHandler(Throwable.class)
      public ResponseUtil onException(Throwable ex,HttpServletRequest request){
          LOGGER.error("url: {}, msg: {}", request.getRequestURL(), ex.getMessage());
          return ResponseUtil.fail("get exception:"+ex.getMessage());
      }
  }