Mybatis框架学习笔记

0 数据库准备

首先，进行数据库的准备工作，sql语句如下（为了操作简便，两个表不设置外键关系，实际上二者具有对应关系）：

DROP DATABASE IF EXISTS mybatis;
CREATE DATABASE IF NOT EXISTS mybatis; 

CREATE TABLE book(
book_id INT PRIMARY KEY AUTO_INCREMENT,
book_name VARCHAR(30),
book_price DOUBLE,
category_id INT
);
CREATE TABLE book_category(
category_id INT PRIMARY KEY,
description VARCHAR(50)
);

INSERT INTO book
(book_id, book_name, book_price, category_id)
VALUES
(1, 'Java编程思想（第四版）', 70.20, 1),
(2, '浪潮之巅（第三版）', 68.30, 1 ),
(3, '人间词话', 22.40, 2),
(4, '一只特立独行的猪', 24.20, 2);
SELECT * FROM book;

INSERT INTO book_category
(category_id, description)
VALUES
(1, '计算机'),
(2, '文学');
SELECT * FROM book_category;

1 HelloWorld

利用Mybatis进行数据库操作，主要分为以下3个过程。

1. 利用主配置文件创建一个会话（Session）。
2. 利用映射接口创建mapper。
3. 利用接口中的方法去定位并解析映射文件中的sql语句

1.1 项目搭建

创建一个maven项目Mybatis_book,，创建两个包（1）com.mmb.bean存储java实体类（即与数据库中表对应的类） （2）com.mmb.mapper存储映射接口

• 创建maven项目
• 编写pom.xml，引入所需要的jar包（可以从https://mvnrepository.com/进行依赖搜索，其中log4j包时程序运行时打印日志的包，需要加入log4j.xml文件）

  <dependencies>
      <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
      <dependency>
          <groupId>org.mybatis</groupId>
          <artifactId>mybatis</artifactId>
          <version>3.5.3</version>
      </dependency>
  
      <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>8.0.19</version>
      </dependency>
  
      <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
      <dependency>
          <groupId>org.apache.logging.log4j</groupId>
          <artifactId>log4j-core</artifactId>
          <version>2.12.1</version>
      </dependency>
      
      <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>4.12</version>
          <scope>compile</scope>
      </dependency>
      
  </dependencies>
  

• 创建实体类以及映射接口
  以book数据表为例（book_category表与之相对应）：

  package com.mmb.bean;
  
  public class Book {
  private Integer bookId;
  private String bookName;
  private Double bookPrice;
  private Integer categoryId;
  
  public Book() {
  }
  
  public Book(Integer bookId, String bookName, Double bookPrice, Integer categoryId) {
      this.bookId = bookId;
      this.bookName = bookName;
      this.bookPrice = bookPrice;
      this.categoryId = categoryId;
  }
  
  public Integer getBookId() {
      return bookId;
  }
  
  public void setBookId(Integer bookId) {
      this.bookId = bookId;
  }
  
  public String getBookName() {
      return bookName;
  }
  
  public void setBookName(String bookName) {
      this.bookName = bookName;
  }
  
  public Double getBookPrice() {
      return bookPrice;
  }
  
  public void setBookPrice(Double bookPrice) {
      this.bookPrice = bookPrice;
  }
  
  public Integer getCategoryId() {
      return categoryId;
  }
  
  public void setCategoryId(Integer categoryId) {
      this.categoryId = categoryId;
  }
  
  @Override
  public String toString() {
      return "Book{" +
              "bookId=" + bookId +
              ", bookName='" + bookName + '\'' +
              ", bookPrice=" + bookPrice +
              ", categoryId=" + categoryId +
              '}';
  	}
  }
  

  // 先写出映射接口，不写其中的方法
  package com.mmb.mapper;
  
  public interface BookMapper {
  }
  

  项目结构图：
  

1.2 编写主配置文件

注： 1. 配置文件中mapUnderscoreToCamelCase属性的设置是为了自动完成Mysql中下划线命名法到java中驼峰命名法的自动映射
2. mapper中需要添加主配置文件需要访问的映射文件

<?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"/>
    </settings>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="root123"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="BookMapper.xml"/>
    </mappers>
</configuration>

1.3 编写映射文件

编写映射文件（主要通过两个映射来定位SQL语句）之前，需要在BookMapper中先建立方法，以select（查询）为例。

//添加查询方法
package com.mmb.mapper;

import com.mmb.bean.Book;

public interface BookMapper {
	Book getBookById(Integer id);
}

<!-- 通过namespace（对应映射接口）、id（对应方法名）来定位sql语句 -->

<?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.mmb.mapper.BookMapper">

    <select id="getBookById" resultType="com.mmb.bean.Book">
        select * from book where id = #{id}
    </select>

</mapper>

1.4 log4j2.xml

1. 创建log4j2.xml文件（为了便于观察，可以将PatternLayout 中的%l 去掉），内容如下：

   <?xml version="1.0" encoding="UTF-8"?>
   <Configuration status="WARN">
       <Appenders>
           <Console name="Console" target="SYSTEM_OUT">
               <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %l %msg%n"/>
           </Console>
       </Appenders>
   
       <Loggers>
           <Root level="error">
               <AppenderRef ref="Console"/>
           </Root>
           <Logger name="com" level="DEBUG" additivity="false">
               <AppenderRef ref="Console"/>
           </Logger>
       </Loggers>
       
   </Configuration>
   
   

2. 项目结构：
   

1.5 测试查询单条数据

1. 测试代码：

   package com.mmb.mapper;
   
   import com.mmb.bean.Book;
   import org.apache.ibatis.io.Resources;
   import org.apache.ibatis.session.SqlSession;
   import org.apache.ibatis.session.SqlSessionFactory;
   import org.apache.ibatis.session.SqlSessionFactoryBuilder;
   import org.junit.Test;
   
   import java.io.IOException;
   import java.io.InputStream;
   
   public class TestMybatis {
   
   @Test
   public void test() throws IOException {
       InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
       SqlSessionFactory sqlSessionFactory =
               new SqlSessionFactoryBuilder().build(inputStream);
       SqlSession sqlSession = sqlSessionFactory.openSession(true);
       BookMapper mapper = sqlSession.getMapper(BookMapper.class);
       Book book = mapper.getBookById(3);
       System.out.println(book);
   	}
   }
   

2. 测试结果

   Book{bookId=3, bookName='人间词话', bookPrice=22.4, categoryId=2}
   

3. 打印日志（SQL语句）

   	20:45:20.288 [main] DEBUG  ==>  Preparing: select * from book where book_id = ? 
   	20:45:20.316 [main] DEBUG  ==> Parameters: 3(Integer)
   	20:45:20.341 [main] DEBUG  <==      Total: 1
   

2 增删改查（CRUD）

2.1 CRUD操作

类型	含义
create	增加
retrieve	查询
update	更新
delete	删除

2.2 SQL示例

1. 在BookMapper里添加如下四个方法：

   List<Book> getAllBook(); //查询多条数据
   
   void addBook(Book book); //添加数据
   
   void updateInfo(Book book); //更新数据
   
   void deleteBookById(Integer id); //删除数据
   

2. BookMapper.xml文件中添加对应SQL语句

   <select id="getAllBook" resultType="com.mmb.bean.Book">
       select * from book
   </select>
   
   <insert id="addBook" parameterType="com.mmb.bean.Book">
       insert into book values(#{bookId}, #{bookName}, #{bookPrice}, #{categoryId})
   </insert>
   
   <update id="updateInfo" parameterType="com.mmb.bean.Book">
       update book set book_price = #{bookPrice} where book_id = #{bookId}
   </update>
   
   <delete id="deleteBookById" parameterType="java.lang.Integer">
       delete from book where book_id = #{id}
   </delete>
   

2.3 测试结果

2.3.1 查询多条数据

1. 结果(利用foreach()对List进行遍历)

   Book{bookId=1, bookName='Java编程思想（第四版）', bookPrice=70.2, categoryId=1}
   Book{bookId=2, bookName='浪潮之巅（第三版）', bookPrice=68.3, categoryId=1}
   Book{bookId=3, bookName='人间词话', bookPrice=22.4, categoryId=2}
   Book{bookId=4, bookName='一只特立独行的猪', bookPrice=24.2, categoryId=2}
   

2. SQL语句

   21:30:33.953 [main] DEBUG ==>  Preparing: select * from book 
   21:30:33.980 [main] DEBUG ==> Parameters: 
   21:30:34.005 [main] DEBUG <==      Total: 4
   

2.3.2 更新数据

1. 结果（利用查询所有数据进行查看，由于数据库中book_id为自增主键，所以id为5）

   Book{bookId=1, bookName='Java编程思想（第四版）', bookPrice=70.2, categoryId=1}
   Book{bookId=2, bookName='浪潮之巅（第三版）', bookPrice=68.3, categoryId=1}
   Book{bookId=3, bookName='人间词话', bookPrice=22.4, categoryId=2}
   Book{bookId=4, bookName='一只特立独行的猪', bookPrice=24.2, categoryId=2}
   Book{bookId=5, bookName='文化苦旅', bookPrice=19.0, categoryId=2}
   

2. SQL语句

   21:37:49.549 [main] DEBUG ==>  Preparing: insert into book values(?, ?, ?, ?) 
   21:37:49.577 [main] DEBUG ==> Parameters: null, 文化苦旅(String), 19.0(Double), 2(Integer)
   21:37:49.748 [main] DEBUG <==    Updates: 1
   

2.3.3 添加数据（可获得自增主键值）

1. 结果（利用查询所有数据进行查看）

   Book{bookId=1, bookName='Java编程思想（第四版）', bookPrice=70.2, categoryId=1}
   Book{bookId=2, bookName='浪潮之巅（第三版）', bookPrice=68.3, categoryId=1}
   Book{bookId=3, bookName='人间词话', bookPrice=22.4, categoryId=2}
   Book{bookId=4, bookName='一只特立独行的猪', bookPrice=24.2, categoryId=2}
   Book{bookId=5, bookName='文化苦旅', bookPrice=38.0, categoryId=2}
   

2. SQL语句

   21:46:14.294 [main] DEBUG ==>  Preparing: update book set book_price = ? where book_id = ? 
   21:46:14.322 [main] DEBUG ==> Parameters: 38.0(Double), 5(Integer)
   21:46:14.423 [main] DEBUG <==    Updates: 1
   

3. 插入时获取自增主键值：
   sql语句：

   <insert id="addBook" parameterType="com.mmb.bean.Book" useGeneratedKeys="true" keyProperty="bookId">
       insert into book values(#{bookId}, #{bookName}, #{bookPrice}, #{categoryId})
   </insert>
   

   结果：

   22:07:33.645 [main] DEBUG ==>  Preparing: insert into book values(?, ?, ?, ?) 
   22:07:33.646 [main] DEBUG ==> Parameters: null, 啊哈C语言(String), 38.7(Double), 1(Integer)
   22:07:33.726 [main] DEBUG <==    Updates: 1
   bookId = 6
   

2.3.4 删除数据

1. 结果（利用查询所有数据进行查看）

   Book{bookId=1, bookName='Java编程思想（第四版）', bookPrice=70.2, categoryId=1}
   Book{bookId=2, bookName='浪潮之巅（第三版）', bookPrice=68.3, categoryId=1}
   Book{bookId=4, bookName='一只特立独行的猪', bookPrice=24.2, categoryId=2}
   Book{bookId=5, bookName='文化苦旅', bookPrice=38.0, categoryId=2}
   

2. SQL语句

   21:52:17.427 [main] DEBUG ==>  Preparing: delete from book where book_id = ? 
   21:52:17.457 [main] DEBUG ==> Parameters: 3(Integer)
   21:52:17.612 [main] DEBUG <==    Updates: 1
   

3 全局配置文件

3.1 properties标签

可以引入外部资源文件，比如db.propeties.可以通过resource（引入类路径下资源）/url（引入磁盘文件或网络资源）设置文件路径。

注： （与spring框架整合后，该标签基本不使用）；
如果同一属性在不同地方设置，则按照下列顺序进行读取（覆盖）

1. property标签
2. 引入的资源文件
3. 作为方法参数传递的属性值

也可以为属性设定默认值（可查询官方文档）

<properties resource="db.properties"></properties>

//db.properties
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC
jdbc.username=root
jdbc.password=root123

3.2 settings标签

有许多属性可以设置，比如下划线命名法到驼峰命名法的映射关系、延迟加载、缓存等。

配置完整的seetings标签：link_mybatis官方文档.

<settings>
  <setting name="cacheEnabled" value="true"/>
  <setting name="lazyLoadingEnabled" value="true"/>
  <setting name="multipleResultSetsEnabled" value="true"/>
  <setting name="useColumnLabel" value="true"/>
  <setting name="useGeneratedKeys" value="false"/>
  <setting name="autoMappingBehavior" value="PARTIAL"/>
  <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
  <setting name="defaultExecutorType" value="SIMPLE"/>
  <setting name="defaultStatementTimeout" value="25"/>
  <setting name="defaultFetchSize" value="100"/>
  <setting name="safeRowBoundsEnabled" value="false"/>
  <setting name="mapUnderscoreToCamelCase" value="false"/>
  <setting name="localCacheScope" value="SESSION"/>
  <setting name="jdbcTypeForNull" value="OTHER"/>
  <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>

3.3 typeAliases标签

如果嫌弃全限定名麻烦，可以为其起一个别名，在需要使用全限定名的地方可以以别名代替；

基本数据类型及其包装类已经取好别名（首字母小写类名），可查阅文档；

可以通过package对整个包下的类起别名，在没有注解的情况下，会使用 Bean 的首字母小写的非限定类名来作为它的别名；

若子包中出现重名类，可采用给类加注解@Alias("")进行区分。

<typeAliases>
        <typeAlias type="com.mmb.bean.Book" alias="book"></typeAlias>
</typeAliases>

<select id="getAllBook" resultType="book">
    select * from book
</select>

@Alias("bookMapper")
public class BookMapper {
    ...
}

3.4 environments标签

可以通过环境标签快速切换数据库环境，应对开发、测试不同的应用场景；

尽管可以配置多个环境，但每个 SqlSessionFactory 实例只能选择一种环境。

environments default="mysql">
        <!--配置mysql的环境-->
        <environment id="mysql">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                ···
            </dataSource>
        </environment>
        <!--配置oracle的环境-->
        <environment id="oracle">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
				···
			</dataSource>
        </environment>
</environments>

3.5 mappers 标签

1. 引入所需要的映射文件（如BookMapper.xml，可以利用resource或url两个属性值）
2. 使用映射器接口实现类的完全限定类名
3. 将包内的映射器接口实现全部注册为映射器

<mappers>
    <mapper resource="bookMapper.xml"/>
    <mapper class="com.mmb.bean.BookCategory"></mapper>
    <package name="com.mmb.bean"/>
</mappers>

3.6 其他标签

• typeHandlers 类处理器标签：设置了数据库类型到Java类型的映射规则（在结果集中将值转换成合适的类），也可自定义规则
• objectFactory 对象工厂标签：每次 MyBatis 创建结果对象的新实例时，它都会使用一个对象工厂（ObjectFactory）实例来完成实例化工作。 默认的对象工厂需要做的仅仅是实例化目标类，要么通过默认无参构造方法，要么通过存在的参数映射来调用带有参数的构造方法。 如果想覆盖对象工厂的默认行为，可以通过创建自己的对象工厂来实现
• plugins 插件标签：MyBatis 允许你在映射语句执行过程中的某一点进行拦截调用
• databaseIdProvider 数据库厂商标识标签：可以通过设置不同数据库厂商来执行不同的sql语句

4 映射文件

MyBatis 的基本原则之一是：在每个插入、更新或删除操作之间，通常会执行多个查询操作。

因此，MyBatis 在查询和结果映射做了相当多的改进。

4.1 基本属性

• id属性：作为命名空间（映射接口）中方法的唯一标识
• parameterType属性：设置传入的参数类型（全限定名或别名）；默认为unset，因为可以由TypeHandler自动推断
• resultType：从结果集中映射出的类型（要查询到的数据类型，集合类使用集合内存储的类型）
• resultMap：自定义映射关系（最为强大的特性），与resultType只能二选其一

4.2 SQL标签

可以利用sql标签来存储经常被使用的SQL语句，当需要时用include标签调用

<select id="getBookById" resultType="com.mmb.bean.Book">
    <include refid="def"></include>
</select>

<sql id="def">
   select book_name, book_price from book where book_id = #{id}
</sql>

20:12:12.173 [main] DEBUG ==>  Preparing: select book_name, book_price from book where book_id = ? 
20:12:12.207 [main] DEBUG ==> Parameters: 5(Integer)
20:12:12.406 [main] DEBUG <==      Total: 1
Book{bookId=null, bookName='文化苦旅', bookPrice=38.0, categoryId=null}

4.3 参数

4.3.1 #{ } 与 ${ }

xml中获取参数由两种方式，#{} 和 ${}
其中，#{}对应PreparedStatedment，可以防止SQL注入
${} 则是直接进行替换（拼接）

• 当参数列表只有一个参数时，可以利用 #{任意参数名} 来获取
• 有多个参数时，参数被封装成map，键按param1，param2依次存储

  <select id="getBookByIdAndName" resultType="com.mmb.bean.Book">
      select * from book where book_id = #{param1} and book_name = #{param2}
  </select>
  

  //测试类代码
  Book book = mapper.getBookByIdAndName(5, "文化苦旅");
  System.out.println(book);
  //console print
  11:34:14.327 [main] DEBUG ==>  Preparing: select * from book where book_id = ? and book_name = ? 
  11:34:14.357 [main] DEBUG ==> Parameters: 5(Integer), 文化苦旅(String)
  11:34:14.383 [main] DEBUG <==      Total: 1
  Book{bookId=5, bookName='文化苦旅', bookPrice=38.0, categoryId=2}
  

4.4 结果映射（resultMap)

一般情况下，一般会选用javaBean或POJO来进行存储结果：MyBatis 会在幕后自动创建一个 ResultMap，再根据属性名来映射列到 JavaBean 的属性上。如果列名和属性名不能匹配上，可以在 SELECT 语句中设置列别名（这是一个基本的 SQL 特性）来完成匹配。
也可以进行手动显式的配置ResultMap( id / result 从column（数据库中的列名）到property（javaBean中的字段）进行映射)：

<resultMap id="defMap" type="com.mmb.bean.Book">
    <id column="book_id" property="bookId"></id>
    <result column="book_name" property="bookName"></result>
</resultMap>

<select id="getAllBook" resultMap="defMap">
    select * from book
</select>

23:15:57.221 [main] DEBUG ==>  Preparing: select * from book 
23:15:57.251 [main] DEBUG ==> Parameters: 
23:15:57.326 [main] DEBUG <==      Total: 5
Book{bookId=1, bookName='Java编程思想（第四版）', bookPrice=70.2, categoryId=1}
Book{bookId=2, bookName='浪潮之巅（第三版）', bookPrice=68.3, categoryId=1}
Book{bookId=4, bookName='一只特立独行的猪', bookPrice=24.2, categoryId=2}
Book{bookId=5, bookName='文化苦旅', bookPrice=38.0, categoryId=2}
Book{bookId=6, bookName='啊哈C语言', bookPrice=38.7, categoryId=1}

java对象的创建也可以通过反射利用构造方法来进行创建（constructor标签），可以通过idArg 或 arg 属性进行详细设置；

// 为Book类添包含两个参数（bookId、bookName）的构造方法
public Book(Integer bookId, String bookName) {
   this.bookId = bookId;
   this.bookName = bookName;
}

<constructor>
   <idArg column="book_id" javaType="int" name="bookId" />
   <arg column="book_name" javaType="string" name="bookName" />
</constructor>

association标签：复杂的类型关联

更改Book类，使其有一个自定义类的字段category，对应数据库中的book_category表

1. 级联嵌套

private Integer bookId;
private String bookName;
private Double bookPrice;
private BookCategory category;

<select id="getBookById" resultMap="def">
   SELECT b.book_id, b.book_name, b.book_price, c.category_id, c.description
   FROM book AS b LEFT JOIN book_category AS c
   ON b.category_id = c.category_id
   WHERE book_id = #{id}
</select>

<resultMap id="def" type="com.mmb.bean.Book">
   <id column="book_id" property="bookId"></id>
   <result column="book_name" property="bookName"></result>
   <result column="book_price" property="bookPrice"></result>
   <association property="category" javaType="com.mmb.bean.BookCategory">
       <id column="category_id" property="categoryId"></id>
       <result column="description" property="description"></result>
   </association>
</resultMap>

11:02:03.915 [main] DEBUG ==>  Preparing: SELECT b.book_id, b.book_name, b.book_price, c.category_id, c.description FROM book AS b LEFT JOIN book_category AS c ON b.category_id = c.category_id WHERE book_id = ? 
11:02:03.942 [main] DEBUG ==> Parameters: 5(Integer)
11:02:03.959 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id, description
11:02:03.960 [main] TRACE <==        Row: 5, 文化苦旅, 38.0, 2, 文学
11:02:03.961 [main] DEBUG <==      Total: 1
Book{bookId=5, bookName='文化苦旅', bookPrice=38.0, category=BookCategory{categoryId=2, description='文学'}}

2. select标签：进行分步查询

<!-- 需要设置另一个select语句com.mmb.mapper.BookCategoryMapper.getCategoryById -->
<select id="getBookById" resultMap="def">
   SELECT b.book_id, b.book_name, b.book_price, b.category_id FROM book b where book_id = #{id}
</select>

<resultMap id="def" type="com.mmb.bean.Book">
    <id column="book_id" property="bookId"></id>
    <result column="book_name" property="bookName"></result>
    <result column="book_price" property="bookPrice"></result>
    <association property="category" select="com.mmb.mapper.BookCategoryMapper.getCategoryById" column="category_id"></association>
</resultMap>

<mapper namespace="com.mmb.mapper.BookCategoryMapper">
  	<select id="getCategoryById" resultType="com.mmb.bean.BookCategory">
     	select * from book_category where category_id = #{id}
 	</select>
</mapper>

12:04:51.471 [main] DEBUG ==>  Preparing: SELECT b.book_id, b.book_name, b.book_price, b.category_id FROM book b where book_id = ? 
12:04:51.504 [main] DEBUG ==> Parameters: 5(Integer)
12:04:51.527 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id
12:04:51.527 [main] TRACE <==        Row: 5, 文化苦旅, 38.0, 2
12:04:51.544 [main] DEBUG ====>  Preparing: select * from book_category where category_id = ? 
12:04:51.545 [main] DEBUG ====> Parameters: 2(Integer)
12:04:51.548 [main] TRACE <====    Columns: category_id, description
12:04:51.548 [main] TRACE <====        Row: 2, 文学
12:04:51.549 [main] DEBUG <====      Total: 1
12:04:51.551 [main] DEBUG <==      Total: 1
Book{bookId=5, bookName='文化苦旅', bookPrice=38.0, category=BookCategory{categoryId=2, description='文学'}}

这种方式虽然很简单，但在大型数据集或大型数据表上表现不佳。
MyBatis 能够对这样的查询进行延迟加载，因此可以将大量语句同时运行的开销分散开来。

3. 结果集嵌套

<select id="getBookById" resultMap="def">
    SELECT b.book_id, b.book_name, b.book_price, c.category_id, c.description
    FROM book AS b LEFT JOIN book_category AS c
    ON b.category_id = c.category_id
    WHERE book_id = #{id}
    </select>

<resultMap id="def" type="com.mmb.bean.Book">
    <id column="book_id" property="bookId"></id>
    <result column="book_name" property="bookName"></result>
    <result column="book_price" property="bookPrice"></result>
    <association property="category" column="category_id" resultMap="def2"></association>
</resultMap>

<resultMap id="def2" type="com.mmb.bean.BookCategory">
    <id column="category_id" property="categoryId"></id>
    <result column="description" property="description"></result>
</resultMap>

12:25:41.708 [main] DEBUG ==>  Preparing: SELECT b.book_id, b.book_name, b.book_price, c.category_id, c.description FROM book AS b LEFT JOIN book_category AS c ON b.category_id = c.category_id WHERE book_id = ? 
12:25:41.736 [main] DEBUG ==> Parameters: 5(Integer)
12:25:41.761 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id, description
12:25:41.761 [main] TRACE <==        Row: 5, 文化苦旅, 38.0, 2, 文学
12:25:41.763 [main] DEBUG <==      Total: 1
Book{bookId=5, bookName='文化苦旅', bookPrice=38.0, category=BookCategory{categoryId=2, description='文学'}}

4.3.3 集合Collection

更改BookCategory类，为其设置一个字段：books代表该类书所含有的书；

private List<Book> books;

<select id="getCategoryById" resultMap="def">
   select * from book b left join book_category c on b.category_id = c.category_id  where b.category_id = #{id}
</select>

<resultMap id="def" type="com.mmb.bean.BookCategory">
   <id column="category_id" property="categoryId"></id>
   <id column="description" property="description"></id>
   <collection property="books" ofType="com.mmb.bean.Book">
       <id column="book_id" property="bookId"></id>
       <result column="book_name" property="bookName"></result>
       <result column="book_price" property="bookPrice"></result>
   </collection>
</resultMap>

13:02:11.667 [main] DEBUG ==>  Preparing: select * from book b left join book_category c on b.category_id = c.category_id where b.category_id = ? 
13:02:11.718 [main] DEBUG ==> Parameters: 1(Integer)
13:02:11.784 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id, category_id, description
13:02:11.785 [main] TRACE <==        Row: 1, Java编程思想（第四版）, 70.2, 1, 1, 计算机
13:02:11.787 [main] TRACE <==        Row: 2, 浪潮之巅（第三版）, 68.3, 1, 1, 计算机
13:02:11.787 [main] TRACE <==        Row: 6, 啊哈C语言, 38.7, 1, 1, 计算机
13:02:11.788 [main] DEBUG <==      Total: 3
BookCategory{categoryId=1, description='计算机', books=[Book{bookId=1, bookName='Java编程思想（第四版）', bookPrice=70.2, category=null}, Book{bookId=2, bookName='浪潮之巅（第三版）', bookPrice=68.3, category=null}, Book{bookId=6, bookName='啊哈C语言', bookPrice=38.7, category=null}]}

集合也可以进行结果集嵌套以及分步查询：

<mapper namespace="com.mmb.mapper.BookMapper">
    <select id="getBooksBySteps" resultMap="def">
        select * from book where category_id = #{id}
    </select>

    <resultMap id="def" type="com.mmb.bean.Book">
        <id column="book_id" property="bookId"></id>
        <result column="book_name" property="bookName"></result>
        <result column="book_price" property="bookPrice"></result>
    </resultMap>
</mapper>

<!--bookCategoryMapper.xml -->
<select id="getCategoryBySteps" resultMap="def1">
   select * from book_category where category_id = #{id}
</select>

<resultMap id="def1" type="com.mmb.bean.BookCategory">
    <id column="category_id" property="categoryId"></id>
    <id column="description" property="description"></id>
    <collection property="books" column="category_id" select="com.mmb.mapper.BookMapper.getBooksBySteps"></collection>
</resultMap>

13:24:04.744 [main] DEBUG ==>  Preparing: select * from book_category where category_id = ? 
13:24:04.776 [main] DEBUG ==> Parameters: 1(Integer)
13:24:04.795 [main] TRACE <==    Columns: category_id, description
13:24:04.796 [main] TRACE <==        Row: 1, 计算机
13:24:04.798 [main] DEBUG ====>  Preparing: select * from book where category_id = ? 
13:24:04.798 [main] DEBUG ====> Parameters: 1(Integer)
13:24:04.800 [main] TRACE <====    Columns: book_id, book_name, book_price, category_id
13:24:04.800 [main] TRACE <====        Row: 1, Java编程思想（第四版）, 70.2, 1
13:24:04.801 [main] TRACE <====        Row: 2, 浪潮之巅（第三版）, 68.3, 1
13:24:04.801 [main] TRACE <====        Row: 6, 啊哈C语言, 38.7, 1
13:24:04.801 [main] DEBUG <====      Total: 3
13:24:04.803 [main] DEBUG <==      Total: 1
BookCategory{categoryId=1, description='计算机', books=[Book{bookId=1, bookName='Java编程思想（第四版）', bookPrice=70.2, category=null}, Book{bookId=2, bookName='浪潮之巅（第三版）', bookPrice=68.3, category=null}, Book{bookId=6, bookName='啊哈C语言', bookPrice=38.7, category=null}]}

集合的结果集嵌套：

<select id="getCategoryById" resultMap="def">
    select * from book b left join book_category c on b.category_id = c.category_id  where b.category_id = #{id}
</select>

<resultMap id="def" type="com.mmb.bean.BookCategory">
    <id column="category_id" property="categoryId"></id>
    <id column="description" property="description"></id>
    <collection property="books" column="category_id" resultMap="def2"></collection>
</resultMap>
<resultMap id="def2" type="com.mmb.bean.Book">
    <id column="book_id" property="bookId"></id>
    <result column="book_name" property="bookName"></result>
    <result column="book_price" property="bookPrice"></result>
</resultMap>

13:30:23.190 [main] DEBUG ==>  Preparing: select * from book b left join book_category c on b.category_id = c.category_id where b.category_id = ? 
13:30:23.218 [main] DEBUG ==> Parameters: 2(Integer)
13:30:23.241 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id, category_id, description
13:30:23.242 [main] TRACE <==        Row: 4, 一只特立独行的猪, 24.2, 2, 2, 文学
13:30:23.243 [main] TRACE <==        Row: 5, 文化苦旅, 38.0, 2, 2, 文学
13:30:23.244 [main] DEBUG <==      Total: 2
BookCategory{categoryId=2, description='文学', books=[Book{bookId=4, bookName='一只特立独行的猪', bookPrice=24.2, category=null}, Book{bookId=5, bookName='文化苦旅', bookPrice=38.0, category=null}]}

可以通过鉴别器（Discriminator标签）做出不同的设置；

例如，有一张动物表，根据其种类编号的不同可以映射不同的javaBean；

<discriminator javaType="int" column="animal_type">
	<case value="1" resultMap="rabbit"/>
	<case value="2" resultMap="cow"/>
	<case value="3" resultMap="duck"/>
	<case value="4" resultMap="fox"/>
</discriminator>

5 缓存机制

默认情况下，缓存级别为会话级别；
可以通过在SQL映射文件中设添加<cache/>来开启二级缓存（映射文件级别，需要在SQLSession关闭或提交后起作用）；

二级缓存效果：

1. 映射语句文件中的所有 select 语句的结果将会被缓存。
2. 映射语句文件中的所有 insert、update 和 delete 语句会刷新缓存。
3. 缓存会使用最近最少使用算法（LRU, Least Recently Used）算法来清除不需要的缓存。
4. 缓存不会定时进行刷新（也就是说，没有刷新间隔）。
5. 缓存会保存列表或对象（无论查询方法返回哪种）的 1024 个引用。
6. 缓存会被视为读/写缓存，这意味着获取到的对象并不是共享的，可以安全地被调用者修改，而不干扰其他调用者或线程所做的潜在修改。

可以通过缓存的设置来进行配置参数：

<cache eviction="FIFO" flushInterval="60000" size="512" readOnly="true"/>

刷新策略：

• LRU – 最近最少使用：移除最长时间不被使用的对象。
• FIFO – 先进先出：按对象进入缓存的顺序来移除它们。
• SOFT –软引用：基于垃圾回收器状态和软引用规则移除对象。
• WEAK – 弱引用：更积极地基于垃圾收集器状态和弱引用规则移除对象。

注：二级缓存是事务性的。这意味着，当 SqlSession 完成并提交时，或是完成并回滚，但没有执行 flushCache=true 的 insert/delete/update 语句时，缓存会获得更新。

15:52:38.083 [main] DEBUG Cache Hit Ratio [com.mmb.mapper.BookMapper]: 0.0
15:52:39.244 [main] DEBUG ==>  Preparing: select * from book where book_id = ? 
15:52:39.274 [main] DEBUG ==> Parameters: 5(Integer)
15:52:39.297 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id
15:52:39.297 [main] TRACE <==        Row: 5, 文化苦旅, 38.0, 2
15:52:39.299 [main] DEBUG <==      Total: 1
Book{bookId=5, bookName='文化苦旅', bookPrice=38.0}
---------------------------------------------------------------------------------------------
15:52:39.301 [main] DEBUG Cache Hit Ratio [com.mmb.mapper.BookMapper]: 0.5
Book{bookId=5, bookName='文化苦旅', bookPrice=38.0}

6 动态SQL

动态sql可以解决语句拼接麻烦的问题。

6.1 if 标签

当进行条件查询时，可以利用if标签：当传入的参数为null时，该条件不参加筛选

Book getBookByCondition(@Param("id") Integer id, @Param("name") String name);

<select id="getBookByCondition" resultType="com.mmb.bean.Book">
    select * from book where 1=1
    <if test="id != null">
        and book_id = #{id}
    </if>
    <if test="name != null">
        and book_name = #{name}
    </if>
</select>

17:41:17.389 [main] DEBUG Cache Hit Ratio [com.mmb.mapper.BookMapper]: 0.0
17:41:18.207 [main] DEBUG ==>  Preparing: select * from book where 1=1 and book_id = ? 
17:41:18.233 [main] DEBUG ==> Parameters: 1(Integer)
17:41:18.262 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id
17:41:18.262 [main] TRACE <==        Row: 1, Java编程思想（第四版）, 70.2, 1
17:41:18.265 [main] DEBUG <==      Total: 1
Book{bookId=1, bookName='Java编程思想（第四版）', bookPrice=70.2}

17:49:24.171 [main] DEBUG Cache Hit Ratio [com.mmb.mapper.BookMapper]: 0.0
17:49:25.479 [main] DEBUG ==>  Preparing: select * from book where 1=1 and book_name = ? 
17:49:25.507 [main] DEBUG ==> Parameters: 文化苦旅(String)
17:49:25.565 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id
17:49:25.566 [main] TRACE <==        Row: 5, 文化苦旅, 38.0, 2
17:49:25.569 [main] DEBUG <==      Total: 1
Book{bookId=5, bookName='文化苦旅', bookPrice=38.0}

6.2 choose/when/otherwise

可以认为与switch、case、default相对应；只是从多个条件中选择一个使用；

<select id="getBookByCondition" resultType="com.mmb.bean.Book">
    select * from book where 1=1
    <choose>
        <when test="id != null">
            AND book_id like #{id}
        </when>
        <when test="name != null">
            AND book_name like #{name}
        </when>
        <otherwise>
            AND 1=2
        </otherwise>
    </choose>
</select>

Book book = mapper.getBookByCondition(5,"文化苦旅");
    System.out.println(book);
//即使传入了两个参数，也只按最先匹配的进行筛选
18:08:02.037 [main] DEBUG Cache Hit Ratio [com.mmb.mapper.BookMapper]: 0.0
18:08:02.923 [main] DEBUG ==>  Preparing: select * from book where 1=1 AND book_id like ? 
18:08:02.954 [main] DEBUG ==> Parameters: 5(Integer)
18:08:03.039 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id
18:08:03.039 [main] TRACE <==        Row: 5, 文化苦旅, 38.0, 2
18:08:03.043 [main] DEBUG <==      Total: 1
Book{bookId=5, bookName='文化苦旅', bookPrice=38.0}

6.3 trim、where、set

对于if 标签中如果去掉 1=1 的条件，当传入的id值不为null时，SQL语句会变为：
select * from book where and book_id = ?
显然，and在此处应该被被删除；
可以将内容放置在where标签中， 能够起到两个作用：

• 存在可用条件时，在SQL语句中拼接where
• 若语句开头为and或or，会被自动删除

<select id="getBookByCondition" resultType="com.mmb.bean.Book">
    select * from book
    <where>
        <if test="id != null">
            and book_id = #{id}
        </if>
        <if test="name != null">
            and book_name = #{name}
        </if>
    </where>
</select>

// 传入的参数为：4，“文化苦旅”，没有符合条件的数据
18:27:56.890 [main] DEBUG Cache Hit Ratio [com.mmb.mapper.BookMapper]: 0.0
18:27:57.691 [main] DEBUG ==>  Preparing: select * from book WHERE book_id = ? and book_name = ? 
18:27:57.718 [main] DEBUG ==> Parameters: 4(Integer), 文化苦旅(String)
18:27:57.738 [main] DEBUG <==      Total: 0
null

如果where标签中的内容不能如愿，也可以通过trim（等价于where）的四个属性值进行自定义：

用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。

<update id="updateBookIfNecessary">
    update book
    <set>
        <if test="name!= null">book_name=#{name},</if>
        <if test="price!= null">book_price=#{price}</if>
    </set>
    where book_id=#{id}
</update>

18:39:07.347 [main] DEBUG ==>  Preparing: update book SET book_price=? where book_id=? 
18:39:07.379 [main] DEBUG ==> Parameters: 19.0(Double), 5(Integer)
18:39:07.382 [main] DEBUG <==    Updates: 1
---------------------------------------------------------------------------------------------
18:39:07.384 [main] DEBUG Cache Hit Ratio [com.mmb.mapper.BookMapper]: 0.0
18:39:07.385 [main] DEBUG ==>  Preparing: select * from book where book_id = ? 
18:39:07.385 [main] DEBUG ==> Parameters: 5(Integer)
18:39:07.406 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id
18:39:07.406 [main] TRACE <==        Row: 5, 文化苦旅, 19.0, 2
18:39:07.408 [main] DEBUG <==      Total: 1
Book{bookId=5, bookName='文化苦旅', bookPrice=19.0}

6.4 foreach 批量操作

动态 SQL 的另一个常见使用场景是对集合进行遍历；

<select id="getBooksByIds" resultType="com.mmb.bean.Book">
    select * from book
    where book_id in
    <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
        #{item}
    </foreach>
</select>

18:59:06.163 [main] DEBUG Cache Hit Ratio [com.mmb.mapper.BookMapper]: 0.0
18:59:06.974 [main] DEBUG ==>  Preparing: select * from book where book_id in ( ? , ? , ? , ? ) 
18:59:06.999 [main] DEBUG ==> Parameters: 1(Integer), 3(Integer), 5(Integer), 7(Integer)
18:59:07.019 [main] TRACE <==    Columns: book_id, book_name, book_price, category_id
18:59:07.019 [main] TRACE <==        Row: 1, Java编程思想（第四版）, 70.2, 1
18:59:07.022 [main] TRACE <==        Row: 5, 文化苦旅, 19.0, 2
18:59:07.022 [main] DEBUG <==      Total: 2
[Book{bookId=1, bookName='Java编程思想（第四版）', bookPrice=70.2}, Book{bookId=5, bookName='文化苦旅', bookPrice=19.0}]

你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。

当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。

当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

7 逆向工程 (code generator)

利用数据库来生成Mybatis适合的实体、Mapper接口和映射文件；

1. 引入jar包（数据库连接驱动，mybatis-connection-core包）

    <!-- https://mvnrepository.com/artifact/org.mybatis.generator/mybatis-generator-core -->
    <dependency>
        <groupId>org.mybatis.generator</groupId>
        <artifactId>mybatis-generator-core</artifactId>
        <version>1.4.0</version>
    </dependency>
    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
       <dependency>
           <groupId>mysql</groupId>
           <artifactId>mysql-connector-java</artifactId>
           <version>8.0.19</version>
       </dependency>
   

2. 配置生成规则
   主目录下创建xml文件

   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE generatorConfiguration
       PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
       "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
   <generatorConfiguration>
     <context id="DB2Tables" targetRuntime="MyBatis3">
       <jdbcConnection driverClass="com.mysql.jdbc.Driver"
                       connectionURL="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"
                       userId="root"
                       password="root123">
       </jdbcConnection>
   
       <javaTypeResolver >
           <property name="forceBigDecimals" value="false" />
       </javaTypeResolver>
   	<!--实体类-->
       <javaModelGenerator targetPackage="com.mmb.model" targetProject=".\src\main\java">
           <property name="enableSubPackages" value="true" />
           <property name="trimStrings" value="true" />
       </javaModelGenerator>
   	<!-- 映射文件-->
       <sqlMapGenerator targetPackage="com.mmb.xml"  targetProject=".\src\main\resources">
           <property name="enableSubPackages" value="true" />
       </sqlMapGenerator>
   	<!--映射接口-->
       <javaClientGenerator type="XMLMAPPER" targetPackage="com.mmb.dao"  targetProject=".\src\main\java">
           <property name="enableSubPackages" value="true" />
       </javaClientGenerator>
   	<!--需要生成代码的表-->
       <table tableName="book" domainObjectName="Book"></table>
   </context>
   </generatorConfiguration>
   

3. 运行

   @org.junit.Test
   public void test1() throws Exception{
       List<String> warnings = new ArrayList<String>();
       boolean overwrite = true;
       File configFile = new File("generatorConfig.xml");
       ConfigurationParser cp = new ConfigurationParser(warnings);
       Configuration config = cp.parseConfiguration(configFile);
       DefaultShellCallback callback = new DefaultShellCallback(overwrite);
       MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
       myBatisGenerator.generate(null);
   }
   

4. 目录结构
   

---

References:

link_mybatis_generator
link_mybatis官方文档.