第一章：增删改查

  • 示例：
  • 导入相关 jar 包的 Maven 坐标：
  1. <dependency>
  2.     <groupId>org.mybatis</groupId>
  3.     <artifactId>mybatis</artifactId>
  4.     <version>3.5.5</version>
  5. </dependency>
  6. <dependency>
  7.     <groupId>log4j</groupId>
  8.     <artifactId>log4j</artifactId>
  9.     <version>1.2.17</version>
  10. </dependency>
  11. <dependency>
  12.     <groupId>mysql</groupId>
  13.     <artifactId>mysql-connector-java</artifactId>
  14.     <version>8.0.21</version>
  15. </dependency>
  16. <dependency>
  17.     <groupId>junit</groupId>
  18.     <artifactId>junit</artifactId>
  19.     <version>4.13</version>
  20.     <scope>test</scope>
  21. </dependency>
  • db.properties
  1. jdbc.url=jdbc:mysql://192.168.134.100:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
  2. jdbc.driverClass=com.mysql.cj.jdbc.Driver
  3. jdbc.username=root
  4. jdbc.password=123456
  • log4j.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
  4. <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
  6.  <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
  7.    <param name="Encoding" value="UTF-8" />
  8.    <layout class="org.apache.log4j.PatternLayout">
  9.     <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) \n" />
  10.    </layout>
  11.  </appender>
  12.  <logger name="java.sql">
  13.    <level value="debug" />
  14.  </logger>
  15.  <logger name="org.apache.ibatis">
  16.    <level value="info" />
  17.  </logger>
  18.  <root>
  19.    <level value="debug" />
  20.    <appender-ref ref="STDOUT" />
  21.  </root>
  22. </log4j:configuration>
  • Employee.java
  1. package com.sunxiaping.domain;
  3. import org.apache.ibatis.type.Alias;
  5. @Alias("emp")
  6. public class Employee {
  8.     private Integer id;
  10.     private String lastName;
  12.     private String email;
  14.     private String gender;
  16.     public Employee() {
  17.     }
  19.     public Employee(Integer id, String lastName, String email, String gender) {
  20.         this.id = id;
  21.         this.lastName = lastName;
  22.         this.email = email;
  23.         this.gender = gender;
  24.     }
  26.     public Integer getId() {
  27.         return id;
  28.     }
  30.     public void setId(Integer id) {
  31.         this.id = id;
  32.     }
  34.     public String getLastName() {
  35.         return lastName;
  36.     }
  38.     public void setLastName(String lastName) {
  39.         this.lastName = lastName;
  40.     }
  42.     public String getEmail() {
  43.         return email;
  44.     }
  46.     public void setEmail(String email) {
  47.         this.email = email;
  48.     }
  50.     public String getGender() {
  51.         return gender;
  52.     }
  54.     public void setGender(String gender) {
  55.         this.gender = gender;
  56.     }
  58.     @Override
  59.     public String toString() {
  60.         return "Employee{" +
  61.                 "id=" + id +
  62.                 ", lastName='" + lastName + '\'' +
  63.                 ", email='" + email + '\'' +
  64.                 ", gender='" + gender + '\'' +
  65.                 '}';
  66.     }
  67. }
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. public interface EmployeeMapper {
  7.     /**
  8.      * 根据id查询员工的信息
  9.      *
  10.      * @param id
  11.      * @return
  12.      */
  13.     Employee findById(Integer id);
  15.     /**
  16.      * 增加员工信息
  17.      *
  18.      * @param employee
  19.      */
  20.     void addEmp(Employee employee);
  22.     /**
  23.      * 修改员工信息
  24.      *
  25.      * @param employee
  26.      */
  27.     void updateEmp(Employee employee);
  30.     /**
  31.      * 根据id删除员工信息
  32.      *
  33.      * @param id
  34.      */
  35.     void deleteEmpById(Integer id);
  37. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  6.     <select id="findById" resultType="com.sunxiaping.domain.Employee">
  7.         SELECT id,last_name as lastName,email,gender
  8.         FROM employee
  9.         WHERE id = #{id,jdbcType=INTEGER}
  10.     </select>
  12.     <insert id="addEmp">
  13.         INSERT INTO employee (last_name,email,gender)
  14.         VALUES (#{lastName,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{gender,jdbcType=VARCHAR})
  15.     </insert>
  17.     <update id="updateEmp">
  18.         UPDATE employee
  19.         SET last_name = #{lastName,jdbcType=VARCHAR},email = #{email,jdbcType=VARCHAR},gender=#{gender,jdbcType=VARCHAR}
  20.         WHERE id = #{id,jdbcType=INTEGER}
  21.     </update>
  22.     <delete id="deleteEmpById">
  23.         DELETE FROM employee
  24.         WHERE id = #{id,jdbcType=INTEGER}
  25.     </delete>
  26. </mapper>
  • mybatis-copnfig.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE configuration
  3.         PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-config.dtd">
  5. <configuration>
  7.     <properties resource="db.properties"></properties>
  8.     <settings>
  9.         <!-- 开启自动驼峰命名规则映射 -->
  10.         <setting name="mapUnderscoreToCamelCase" value="true"/>
  11.     </settings>
  13.     <environments default="development">
  14.         <environment id="development">
  15.             <transactionManager type="JDBC"/>
  16.             <dataSource type="POOLED">
  17.                 <!-- 使用${}引入db.properties属性文件中的内容 -->
  18.                 <property name="driver" value="${jdbc.driverClass}"/>
  19.                 <property name="url" value="${jdbc.url}"/>
  20.                 <property name="username" value="${jdbc.username}"/>
  21.                 <property name="password" value="${jdbc.password}"/>
  22.             </dataSource>
  23.         </environment>
  24.     </environments>
  26.     <mappers>
  27.         <package name="com.sunxiaping.mapper"/>
  28.     </mappers>
  30. </configuration>
  • 测试：
  1. package com.sunxiaping;
  3. import com.sunxiaping.domain.Employee;
  4. import com.sunxiaping.mapper.EmployeeMapper;
  5. import org.apache.ibatis.io.Resources;
  6. import org.apache.ibatis.session.SqlSession;
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  9. import org.junit.After;
  10. import org.junit.Before;
  11. import org.junit.Test;
  13. import java.io.IOException;
  14. import java.io.InputStream;
  17. public class EmployeeTest {
  18.     SqlSessionFactory sqlSessionFactory = null;
  19.     SqlSession sqlSession = null;
  20.     EmployeeMapper employeeMapper = null;
  22.     @Before
  23.     public void before() throws IOException {
  24.         String resource = "mybatis-config.xml";
  25.         InputStream inputStream = Resources.getResourceAsStream(resource);
  26.         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  27.         sqlSession = sqlSessionFactory.openSession(true);
  28.         employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
  29.     }
  31.     @After
  32.     public void after() {
  33.         if (null != sqlSession) {
  34.             sqlSession.close();
  35.         }
  36.     }
  38.     @Test
  39.     public void testFindById() {
  41.         Employee employee = employeeMapper.findById(1);
  42.         System.out.println("employee = " + employee);
  43.     }
  45.     @Test
  46.     public void testAddEmp() {
  47.         Employee employee = new Employee();
  48.         employee.setEmail("jerry@qq.com");
  49.         employee.setGender("男");
  50.         employee.setLastName("jerry");
  52.         employeeMapper.addEmp(employee);
  53.     }
  55.     @Test
  56.     public void testUpdateEmp(){
  58.         Employee employee = employeeMapper.findById(2);
  59.         employee.setGender("女");
  61.         employeeMapper.updateEmp(employee);
  62.     }
  64.     @Test
  65.     public void testDeleteEmpById(){
  66.         employeeMapper.deleteEmpById(2);
  68.     }
  70. }

第二章：INSERT 获取自增主键的值

  • MySQL 等数据库支持自增主键，自增主键的值是通过 statement.getGenerateKeys() 方法获取的。
  • 通过使用 useGeneratedKeys="true" 开启自增主键获取主键值的策略。

  • 通过使用 keyProperty="id" 指定对应的主键属性，Mybatis 获取主键值以后，将这个值封装给 JavaBean 的哪个属性。

  • 示例：

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  6.     <!--
  7.         MySQL支持自增主键，自增主键值的获取，Mybatis是利用statement.getGeneratedKeys()
  8.             useGeneratedKeys="true"：使用自增主键获取主键值策略
  9.             keyProperty="id"：指定对应的主键属性，Mybatis获取主键值以后，将这个值封装给JavaBean的哪个属性
  10.     -->
  11.     <insert id="addEmp" useGeneratedKeys="true" keyProperty="id" parameterType="com.sunxiaping.domain.Employee">
  12.         INSERT INTO employee (last_name,email,gender)
  13.         VALUES (#{lastName,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{gender,jdbcType=VARCHAR})
  14.     </insert>
  15. </mapper>

第三章：INSERT 获取非自增主键的值

  • Oracle 不支持自增主键，Oracle 是使用序列来模拟自增的。

  • 每次插入的数据的主键都是从序列中拿到的值。

  • 示例：

  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  6.     <!--
  7.         Oracle不支持自增主键，Oracle是使用序列来模拟自增的。
  8.         每次插入的数据的主键都是从序列中拿到的值。
  9.     -->
  10.     <insert id="addEmp" useGeneratedKeys="true" keyProperty="id" parameterType="com.sunxiaping.domain.Employee">
  11.         <!--
  12.             插入时的主键是从序列中拿到的
  13.                 keyProperty：查询的主键封装给JavaBean的哪个属性
  14.                 order：
  15.                     - BEFORE：当前的SQL在插入SQL之前运行
  16.                         - 运行顺序：先运行selectKey中的SQL语句，查询出id，然后将id封装给JavaBean中的id属性，在运行插入SQL的时候，就可以取出id属性对应的值
  17.                     - AFTER: 先运行插入SQL，再运行当前的SQL 
  18.                         - 运行顺序：先运行插入的SQL（会从序列中取出新值作为id），再运行selectKey查询id，然后将id封装给JavaBean的id属性，并发情况下可能有问题。
  19.                 resultType：查出的数据的返回值类型
  20.         -->
  21.         <selectKey keyProperty="id" order="BEFORE" resultType="java.lang.Integer">
  22.             <!--
  23.                 编写查询主键的SQL语句
  24.             -->
  25.             SELECT employee_seq.nextval FROM dual
  26.         </selectKey>
  28.         INSERT INTO employee (id,last_name,email,gender)
  29.         VALUES (#{id,jdbcType=INTEGER},#{lastName,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{gender,jdbcType=VARCHAR})
  31.     </insert>
  32. </mapper>
  • 示例：
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  6.     <!--
  7.         Oracle不支持自增主键，Oracle是使用序列来模拟自增的。
  8.         每次插入的数据的主键都是从序列中拿到的值。
  9.     -->
  10.     <insert id="addEmp" useGeneratedKeys="true" keyProperty="id" parameterType="com.sunxiaping.domain.Employee">
  11.         <!--
  12.             插入时的主键是从序列中拿到的
  13.                 keyProperty：查询的主键封装给JavaBean的哪个属性
  14.                 order：
  15.                     - BEFORE：当前的SQL在插入SQL之前运行
  16.                         - 运行顺序：先运行selectKey中的SQL语句，查询出id，然后将id封装给JavaBean中的id属性，在运行插入SQL的时候，就可以取出id属性对应的值
  17.                     - AFTER: 先运行插入SQL，再运行当前的SQL
  18.                         - 运行顺序：先运行插入的SQL（会从序列中取出新值作为id），再运行selectKey查询id，然后将id封装给JavaBean的id属性，并发情况下可能有问题。
  19.                 resultType：查出的数据的返回值类型
  20.         -->
  21.         <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
  22.             <!--
  23.                 编写查询主键的SQL语句
  24.             -->
  25.             SELECT employee_seq.currval FROM dual
  26.         </selectKey>
  28.         INSERT INTO employee (id,last_name,email,gender)
  29.         VALUES (employee_seq.nextval},#{lastName,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR},#{gender,jdbcType=VARCHAR})
  31.     </insert>
  32. </mapper>

第四章：参数处理

4.1 单个参数

  • Mybatis 不需要经过任何处理，Mybatis 可以直接使用 #{参数名} 取出参数值。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. public interface EmployeeMapper {
  7.     /**
  8.      * 根据id查询员工的信息
  9.      *
  10.      * @param id
  11.      * @return
  12.      */
  13.     Employee findById(Integer id);
  15. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <select id="findById" resultType="com.sunxiaping.domain.Employee">
  8.         SELECT id,last_name as lastName,email,gender
  9.         FROM employee
  10.         WHERE id = #{abc,jdbcType=INTEGER}
  11.     </select>
  12. </mapper>

4.2 多个参数

  • 任意多个参数，都会被 Mybatis 重新包装成一个 Map 传入。Map 中的 key 就是 param1, param2 ,…，value 是传入的参数值。

  • #{} 就是从 map 中获取指定的 key 的值。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. public interface EmployeeMapper {
  7.     /**
  8.      * 根据id和lastName查询员工信息
  9.      *
  10.      * @param id
  11.      * @param lastName
  12.      * @return
  13.      */
  14.     Employee findByIdAndLastName(Integer id, String lastName);
  16. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <select id="findByIdAndLastName" resultType="com.sunxiaping.domain.Employee">
  8.         SELECT id,last_name as lastName,email,gender
  9.         FROM employee
  10.         WHERE id = #{param1,jdbcType=INTEGER} AND last_name = #{param2,jdbcType=VARCHAR}
  11.     </select>
  13. </mapper>

4.3 命名参数

  • 当多个参数的时候，可以使用 @Param 注解明确指定 Mybatis 中传入参数 Map 中的 key ，Map 中的 key 就是使用 @Param 注解指定的值，value 就是传入的参数值。

  • #{指定的key} 就可以取出对应的参数值。

  • 示例：

  • Employee.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  4. import org.apache.ibatis.annotations.Param;
  6. public interface EmployeeMapper {
  8.     /**
  9.      * 根据id和lastName查询员工信息
  10.      *
  11.      * @param id
  12.      * @param lastName
  13.      * @return
  14.      */
  15.     Employee findByIdAndLastName(@Param("id") Integer id,@Param("lastName") String lastName);
  17. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <select id="findByIdAndLastName" resultType="com.sunxiaping.domain.Employee">
  8.         SELECT id,last_name as lastName,email,gender
  9.         FROM employee
  10.         WHERE id = #{id,jdbcType=INTEGER} AND last_name = #{lastName,jdbcType=VARCHAR}
  11.     </select>
  13. </mapper>

4.4 POJO

  • 如果多个参数正好是业务逻辑的数据模型，我们就可以直接传入 POJO 。#{属性名} 取出传入 POJO 的属性值。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. public interface EmployeeMapper {
  7.     Employee findByPOJO(Employee employee);
  9. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <select id="findByPOJO" resultType="com.sunxiaping.domain.Employee">
  8.         SELECT id,last_name as lastName,email,gender
  9.         FROM employee
  10.         WHERE id = #{id,jdbcType=INTEGER} AND last_name = #{lastName,jdbcType=VARCHAR}
  11.     </select>
  13. </mapper>

4.5 Map

  • 如果多个参数不是业务模型的数据，没有对应的 POJO ，不经常使用，为了方便，我们可以传入 Map 。

  • #{key} 取出传入 map 中对应的值。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. import java.util.Map;
  7. public interface EmployeeMapper {
  9.     Employee findByMap(Map<String, Object> map);
  11. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <select id="findByMap" resultType="com.sunxiaping.domain.Employee">
  8.         SELECT id,last_name as lastName,email,gender
  9.         FROM employee
  10.         WHERE id = #{id,jdbcType=INTEGER} AND last_name = #{lastName,jdbcType=VARCHAR}
  11.     </select>
  13. </mapper>

4.6 TO

  • 如果多个参数不是业务模型中的数据，但是经常使用，推荐编写一个 TO（Transfer Object）数据传输对象。

  • 示例：

  • Page.java
  1. package com.sunxiaping.to;
  3. /**
  4.  * 分页的参数
  5.  */
  6. public class Page {
  8.     /**
  9.      * 每页显示条数
  10.      */
  11.     private Integer pageSize;
  13.     /**
  14.      * 起始页码
  15.      */
  16.     private Integer pageNum;
  18.     public Integer getPageSize() {
  19.         return pageSize;
  20.     }
  22.     public void setPageSize(Integer pageSize) {
  23.         this.pageSize = pageSize;
  24.     }
  26.     public Integer getPageNum() {
  27.         return pageNum;
  28.     }
  30.     public void setPageNum(Integer pageNum) {
  31.         this.pageNum = pageNum;
  32.     }
  34.     @Override
  35.     public String toString() {
  36.         return "Page{" +
  37.                 "pageSize=" + pageSize +
  38.                 ", pageNum=" + pageNum +
  39.                 '}';
  40.     }
  41. }
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  4. import com.sunxiaping.to.Page;
  6. import java.util.List;
  8. public interface EmployeeMapper {
  10.     List<Employee> findByPage(Page page);
  12. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  8.     <select id="findByPage" resultType="com.sunxiaping.domain.Employee">
  10.         SELECT id,last_name as lastName,email,gender
  11.         FROM employee
  12.         LIMIT #{pageNum},#{pageSize}
  14.     </select>
  15. </mapper>

4.7 Collection 类型(List类型、Set类型)数组类型

  • 如果传入的参数是 Collection 类型(List 类型、Set 类型)或数组类型，那么 Mybatis 会针对这些类型进行特殊处理：如果是 Collection 类型，那么 Mybatis 内部封装参数的 Map 的 key 就是 collection ；如果是 List 类型，那么 Mybatis 内部封装参数的 Map 的 key 可以是 list ；而如果是数组类型, Mybatis 内部封装参数的 Map 的 key 是 array 。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. import java.util.List;
  6. import java.util.Set;
  8. public interface EmployeeMapper {
  10.    List<Employee> findBySet(Set<Integer> id);
  12. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <select id="findBySet" resultType="com.sunxiaping.domain.Employee">
  8.         SELECT id,last_name as lastName,email,gender
  9.         FROM employee
  10.         WHERE id = #{collection}
  11.     </select>
  12. </mapper>

4.8 # 和 $ 取值区别

  • #{} 获取参数的值，预编译到 SQL 中，安全。
  • ${} 获取参数的值，拼接到 SQL 中，有 SQL 注入问题。
  • 注意：
  • 大多数情况下，我们取参数的值的时候使用 #{} 。
  • 原生 JDBC 不支持占位符的地方，我们就可以使用 ${} 。
    • 比如：排序
    • 比如：财务系统在设计之初按照年份分表，查询的时候应该这样写：
  1. Order BY ${name}
  1. SELECT * FROM ${year}_salary WHERE xxx;
  • 示例：
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. import java.util.Map;
  7. public interface EmployeeMapper {
  9.    Employee findByMap(Map<String,Object> map);
  11. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <select id="findByMap" resultType="com.sunxiaping.domain.Employee" parameterType="java.util.Map">
  8.         SELECT id,last_name as lastName,email,gender
  9.         FROM ${tableName}
  10.         WHERE id = #{id}
  11.     </select>
  13. </mapper>
  • 测试：
  1. package com.sunxiaping;
  3. import com.sunxiaping.domain.Employee;
  4. import com.sunxiaping.mapper.EmployeeMapper;
  5. import org.apache.ibatis.io.Resources;
  6. import org.apache.ibatis.session.SqlSession;
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.apache.ibatis.session.SqlSessionFactoryBuilder;
  9. import org.junit.After;
  10. import org.junit.Before;
  11. import org.junit.Test;
  13. import java.io.IOException;
  14. import java.io.InputStream;
  15. import java.util.HashMap;
  16. import java.util.Map;
  19. public class EmployeeTest {
  20.     SqlSessionFactory sqlSessionFactory = null;
  21.     SqlSession sqlSession = null;
  22.     EmployeeMapper employeeMapper = null;
  24.     @Before
  25.     public void before() throws IOException {
  26.         String resource = "mybatis-config.xml";
  27.         InputStream inputStream = Resources.getResourceAsStream(resource);
  28.         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  29.         sqlSession = sqlSessionFactory.openSession(true);
  30.         employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
  31.     }
  33.     @After
  34.     public void after() {
  35.         if (null != sqlSession) {
  36.             sqlSession.close();
  37.         }
  38.     }
  40.     @Test
  41.     public void testFindById() {
  42.         Map<String, Object> map = new HashMap<>();
  43.         map.put("id",1);
  44.         map.put("tableName","employee");
  46.         Employee employee = employeeMapper.findByMap(map);
  47.         System.out.println("employee = " + employee);
  49.     }   
  50. }

4.9 #取值时指定参数的相关规则

  • Mybatis 在参数位置支持如下的属性：
    • javaType
    • jdbcType
    • mode（用于存储过程）
    • numericScale
    • resultMap
    • typeHandler
    • jdbcTypeName
    • expression

  • 在某种特定的条件下，jdbcType 通常需要被设置。

    • 在我们的数据为 null 的时候，有些数据库可能不能识别 Mybatis 对 null 的默认处理，比如 Oracle 。
    • Mybatis对 null 的默认处理时的 jdbcType 是 OTHER ，这种类型 MySQL 能正确识别，但是 Oracle 不能。
      • ① 我们在使用 Oracle 保存 null 的时候，需要设置 jdbcType 为 NULL 。
      • ② 在全局配置文件 mybatis-config.xml 中设置 jdbcTypeForNull 的值是 NULL（推荐这种，Oracle 和 MySQL 都支持）。

  • 示例：修改 Mybatis 对 Oracle 的 NULL 值的默认处理

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. public interface EmployeeMapper {
  7.    int addEmp(Employee employee);
  8. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <insert id="addEmp" useGeneratedKeys="true" keyProperty="id" parameterType="com.sunxiaping.domain.Employee">
  8.         <selectKey>
  9.             SELECT employee_seq.nextval FROM dual
  10.         </selectKey>
  12.         INSERT INTO employee (id,last_name,email,gender)
  13.         <!--
  14.             在Oracle数据库中，如果是null值，需要设置jdbcType=NULL
  15.         -->
  16.         VALUES (#{id,jdbcType=INTEGER},#{lastName,jdbcType=VARCHAR},#{email,jdbcType=NULL},#{gender,jdbcType=VARCHAR})
  17.     </insert>
  18. </mapper>

第五章：select 元素

5.1 概述

  • select 元素用于定义查询操作。
  • id：唯一标识符。用来引用这条语句，需要和接口的方法名一致。
  • parameterType：参数类型。可以不传，Mybatis 会根据 TypeHandler 自动推断。
  • resultType：返回值类型。别名或者全类名，如果返回的是集合，定义集合中元素的类型。不能和 resultMap 同时使用。
  • resultMap：实现高级结果集映射。不能和 resultType 一起使用。

5.2 resultType

5.2.1 返回 List

  • 对于 resultType 来说，如果返回的是一个集合，需要写集合中的类型。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. import java.util.List;
  7. public interface EmployeeMapper {
  9.    List<Employee> findByGender(String gender);
  10. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <!--
  8.         resultType：如果返回的是一个集合，需要写集合里面的类型
  9.     -->
  10.     <select id="findByGender" resultType="com.sunxiaping.domain.Employee">
  11.         SELECT * FROM employee
  12.         WHERE gender = #{gender,jdbcType=VARCHAR}
  13.     </select>
  15. </mapper>

5.2.2 返回 Map

  • 返回一条记录封装 Map ，key 就是列名，值就是对应的值。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import java.util.Map;
  5. public interface EmployeeMapper {
  7.    Map<String, Object> findById(Integer id);
  9. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <select id="findById" resultType="java.util.Map">
  8.         SELECT * FROM employee
  9.         WHERE id = #{id,jdbcType=INTEGER}
  10.     </select>
  12. </mapper>

  • 返回多条记录将其封装为 Map<主键类型,对象类型>，key 是这条记录的主键，值是这条记录。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  4. import org.apache.ibatis.annotations.MapKey;
  6. import java.util.Map;
  8. public interface EmployeeMapper {
  10.    @MapKey("id")
  11.    Map<Integer, Employee> findById(Integer id);
  13. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <select id="findById" resultType="java.util.Map">
  8.         SELECT id,last_name as lastName,gender,email FROM employee
  9.         WHERE id = #{id,jdbcType=INTEGER}
  10.     </select>
  12. </mapper>

5.3 resultMap

5.3.1 自定义结果映射规则

  • 示例：
  • EmplyeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. public interface EmployeeMapper {
  7.   Employee findById(Integer id);
  9. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <!--
  8.         resultMap 自定义关系结果映射规则
  9.             id：唯一方便引用的id
  10.             type：自定义规则的JavaBean类型
  11.     -->
  12.     <resultMap id="emp" type="com.sunxiaping.domain.Employee">
  13.         <!--
  14.             id：指定主键列的封装规则
  15.                 column：指定哪一列
  16.                 property:指定column对应的JavaBean属性
  17.          -->
  18.         <id property="id" column="id"/>
  19.         <!--
  20.             result：定义普通列的封装规则
  21.                column：指定哪一列
  22.                property:指定column对应的JavaBean属性
  23.         -->
  24.         <result property="lastName" column="last_name"/>
  25.         <!-- 其它不指定的列会自动封装 -->
  26.         <result property="email" column="email"/>
  27.         <result property="gender" column="gender"/>
  28.     </resultMap>
  30.     <select id="findById" resultMap="emp">
  31.         SELECT * FROM employee
  32.         WHERE id = #{id,jdbcType=INTEGER}
  33.     </select>
  35. </mapper>

5.3.2 关联查询

5.3.2.1 环境搭建

  • SQL 脚本：
  1. DROP TABLE IF EXISTS `department`;
  2. CREATE TABLE `department`  (
  3.   `id` int(11) NOT NULL AUTO_INCREMENT,
  4.   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  5.   PRIMARY KEY (`id`) USING BTREE
  6. ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  8. INSERT INTO `department` VALUES (1, '开发部');
  9. INSERT INTO `department` VALUES (2, '测试部');
  11. DROP TABLE IF EXISTS `employee`;
  12. CREATE TABLE `employee`  (
  13.   `id` int(11) NOT NULL AUTO_INCREMENT,
  14.   `last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  15.   `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  16.   `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  17.   `department_id` int(11) NULL DEFAULT NULL,
  18.   PRIMARY KEY (`id`) USING BTREE,
  19.   INDEX `fk_department_id`(`department_id`) USING BTREE,
  20.   CONSTRAINT `fk_department_id` FOREIGN KEY (`department_id`) REFERENCES `department` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
  21. ) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  23. INSERT INTO `employee` VALUES (1, 'tom', '男', 'tom@qq.com', 1);
  24. INSERT INTO `employee` VALUES (2, 'aa', '女', NULL, 2);

5.3.2.2 级联属性封装查询结果

  • 示例：查询一个员工信息以及其所在部门信息
  • Department.java
  1. package com.sunxiaping.domain;
  3. public class Department {
  5.     private Integer id;
  7.     private String name;
  9.     public Integer getId() {
  10.         return id;
  11.     }
  13.     public void setId(Integer id) {
  14.         this.id = id;
  15.     }
  17.     public String getName() {
  18.         return name;
  19.     }
  21.     public void setName(String name) {
  22.         this.name = name;
  23.     }
  25.     @Override
  26.     public String toString() {
  27.         return "Department{" +
  28.                 "id=" + id +
  29.                 ", name='" + name + '\'' +
  30.                 '}';
  31.     }
  32. }
  • Employee.java
  1. package com.sunxiaping.domain;
  3. import org.apache.ibatis.type.Alias;
  5. @Alias("emp")
  6. public class Employee {
  8.     private Integer id;
  10.     private String lastName;
  12.     private String email;
  14.     private String gender;
  16.     private Department department;
  18.     public Integer getId() {
  19.         return id;
  20.     }
  22.     public void setId(Integer id) {
  23.         this.id = id;
  24.     }
  26.     public String getLastName() {
  27.         return lastName;
  28.     }
  30.     public void setLastName(String lastName) {
  31.         this.lastName = lastName;
  32.     }
  34.     public String getEmail() {
  35.         return email;
  36.     }
  38.     public void setEmail(String email) {
  39.         this.email = email;
  40.     }
  42.     public String getGender() {
  43.         return gender;
  44.     }
  46.     public void setGender(String gender) {
  47.         this.gender = gender;
  48.     }
  50.     @Override
  51.     public String toString() {
  52.         return "Employee{" +
  53.                 "id=" + id +
  54.                 ", lastName='" + lastName + '\'' +
  55.                 ", email='" + email + '\'' +
  56.                 ", gender='" + gender + '\'' +
  57.                 ", department=" + department +
  58.                 '}';
  59.     }
  60. }
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. public interface EmployeeMapper {
  7.  Employee findEmpAndDeptById(Integer id);
  9. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <resultMap id="emp" type="com.sunxiaping.domain.Employee">
  8.         <id property="id" column="id"/>
  9.         <result property="lastName" column="last_name"/>
  10.         <result property="email" column="email"/>
  11.         <result property="gender" column="gender"/>
  12.         <result property="department.id" column="did"/>
  13.         <result property="department.name" column="dept_name"/>
  14.     </resultMap>
  16.     <select id="findEmpAndDeptById" resultMap="emp">
  17.         SELECT e.id as id,e.last_name as last_name,e.gender as gender,e.email as email,d.id as did,d.name as dept_name
  18.         FROM employee e INNER JOIN department d
  19.         ON e.department_id = d.id
  20.         WHERE e.id = #{id,jdbcType=INTEGER}
  21.     </select>
  23. </mapper>

5.3.2.3 association 定义关联对象

  • 示例：查询一个员工信息以及其所在部门信息
  • Department.java
  1. package com.sunxiaping.domain;
  3. public class Department {
  5.     private Integer id;
  7.     private String name;
  9.     public Integer getId() {
  10.         return id;
  11.     }
  13.     public void setId(Integer id) {
  14.         this.id = id;
  15.     }
  17.     public String getName() {
  18.         return name;
  19.     }
  21.     public void setName(String name) {
  22.         this.name = name;
  23.     }
  25.     @Override
  26.     public String toString() {
  27.         return "Department{" +
  28.                 "id=" + id +
  29.                 ", name='" + name + '\'' +
  30.                 '}';
  31.     }
  32. }
  • Employee.java
  1. package com.sunxiaping.domain;
  3. import org.apache.ibatis.type.Alias;
  5. @Alias("emp")
  6. public class Employee {
  8.     private Integer id;
  10.     private String lastName;
  12.     private String email;
  14.     private String gender;
  16.     private Department department;
  18.     public Integer getId() {
  19.         return id;
  20.     }
  22.     public void setId(Integer id) {
  23.         this.id = id;
  24.     }
  26.     public String getLastName() {
  27.         return lastName;
  28.     }
  30.     public void setLastName(String lastName) {
  31.         this.lastName = lastName;
  32.     }
  34.     public String getEmail() {
  35.         return email;
  36.     }
  38.     public void setEmail(String email) {
  39.         this.email = email;
  40.     }
  42.     public String getGender() {
  43.         return gender;
  44.     }
  46.     public void setGender(String gender) {
  47.         this.gender = gender;
  48.     }
  50.     @Override
  51.     public String toString() {
  52.         return "Employee{" +
  53.                 "id=" + id +
  54.                 ", lastName='" + lastName + '\'' +
  55.                 ", email='" + email + '\'' +
  56.                 ", gender='" + gender + '\'' +
  57.                 ", department=" + department +
  58.                 '}';
  59.     }
  60. }
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. public interface EmployeeMapper {
  7.  Employee findEmpAndDeptById(Integer id);
  9. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <resultMap id="emp" type="com.sunxiaping.domain.Employee">
  8.         <id property="id" column="id"/>
  9.         <result property="lastName" column="last_name"/>
  10.         <result property="email" column="email"/>
  11.         <result property="gender" column="gender"/>
  12.         <!--
  13.             association：可以指定联合的JavaBean对象
  14.                 property：指定哪个属性是联合的对象
  15.                 javaType：指定联合对象的类型
  16.         -->
  17.         <association property="department" javaType="com.sunxiaping.domain.Department">
  18.             <id property="id" column="did"/>
  19.             <id property="name" column="dept_name"/>
  20.         </association>
  21.     </resultMap>
  23.     <select id="findEmpAndDeptById" resultMap="emp">
  24.         SELECT e.id as id,e.last_name as last_name,e.gender as gender,e.email as email,d.id as did,d.name as dept_name
  25.         FROM employee e INNER JOIN department d
  26.         ON e.department_id = d.id
  27.         WHERE e.id = #{id,jdbcType=INTEGER}
  28.     </select>
  29. </mapper>

5.3.2.4 association 分步查询

  • 示例：查询一个员工信息以及其所在部门信息
  • Department.java
  1. package com.sunxiaping.domain;
  3. public class Department {
  5.     private Integer id;
  7.     private String name;
  9.     public Integer getId() {
  10.         return id;
  11.     }
  13.     public void setId(Integer id) {
  14.         this.id = id;
  15.     }
  17.     public String getName() {
  18.         return name;
  19.     }
  21.     public void setName(String name) {
  22.         this.name = name;
  23.     }
  25.     @Override
  26.     public String toString() {
  27.         return "Department{" +
  28.                 "id=" + id +
  29.                 ", name='" + name + '\'' +
  30.                 '}';
  31.     }
  32. }
  • Employee.java
  1. package com.sunxiaping.domain;
  3. import org.apache.ibatis.type.Alias;
  5. @Alias("emp")
  6. public class Employee {
  8.     private Integer id;
  10.     private String lastName;
  12.     private String email;
  14.     private String gender;
  16.     private Department department;
  18.     public Integer getId() {
  19.         return id;
  20.     }
  22.     public void setId(Integer id) {
  23.         this.id = id;
  24.     }
  26.     public String getLastName() {
  27.         return lastName;
  28.     }
  30.     public void setLastName(String lastName) {
  31.         this.lastName = lastName;
  32.     }
  34.     public String getEmail() {
  35.         return email;
  36.     }
  38.     public void setEmail(String email) {
  39.         this.email = email;
  40.     }
  42.     public String getGender() {
  43.         return gender;
  44.     }
  46.     public void setGender(String gender) {
  47.         this.gender = gender;
  48.     }
  50.     @Override
  51.     public String toString() {
  52.         return "Employee{" +
  53.                 "id=" + id +
  54.                 ", lastName='" + lastName + '\'' +
  55.                 ", email='" + email + '\'' +
  56.                 ", gender='" + gender + '\'' +
  57.                 ", department=" + department +
  58.                 '}';
  59.     }
  60. }
  • DepartmentMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Department;
  5. public interface DepartmentMapper {
  7.     Department findById(Integer id);
  9. }
  • DepartmentMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.DepartmentMapper">
  7.     <select id="findById" resultType="com.sunxiaping.domain.Department">
  8.         SELECT *
  9.         FROM department
  10.         WHERE id = #{id,jdbcType=INTEGER}
  11.     </select>
  12. </mapper>
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. public interface EmployeeMapper {
  7.     Employee findEmpAndDeptById(Integer id);
  9. }
  • EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  7.     <resultMap id="emp" type="com.sunxiaping.domain.Employee">
  8.         <!--
  9.             分步查询：
  10.                 ①按照员工id查询员工信息
  11.                 ②根据查询员工信息中的department_id去部门表中查询部门信息
  12.                 ③部门设置到员工中
  13.         -->
  14.         <id property="id" column="id"/>
  15.         <result property="lastName" column="last_name"/>
  16.         <result property="email" column="email"/>
  17.         <result property="gender" column="gender"/>
  18.         <!--
  19.             association 定义关联对象的封装规则
  20.                 property：关联对象的属性
  21.                 select：表明当前属性是调用select查询的结果
  22.                 column：指定将那一列的值传给这个方法
  24.              流程：使用select指定的方法（传入column指定的这列参数的值）查出对象，并封装给property指定的关联对象的属性
  25.         -->
  26.         <association property="department" select="com.sunxiaping.mapper.DepartmentMapper.findById" column="did" />
  27.     </resultMap>
  29.     <select id="findEmpAndDeptById" resultMap="emp">
  30.         SELECT e.id as id,e.last_name as last_name,e.gender as gender,e.email as email,e.department_id as did
  31.         FROM employee e
  32.         WHERE e.id = #{id,jdbcType=INTEGER}
  33.     </select>
  34. </mapper>

分步查询中使用延迟加载：需要在 mybatis-config.xml 中配置如下的全局设置

  1. <settings>
  2.     <!-- 开启自动驼峰命名规则映射 -->
  3.     <setting name="mapUnderscoreToCamelCase" value="true"/>
  4.     <!-- 开启对jdbcType的NULL的支持 -->
  5.     <setting name="jdbcTypeForNull" value="NULL"/>
  6.     <!-- 开启延迟加载 -->
  7.     <setting name="lazyLoadingEnabled" value="true"/>
  8.     <!-- 开启按需加载 -->
  9.     <setting name="aggressiveLazyLoading" value="false"/>
  10. </settings>

5.3.2.5 collection 定义关联集合封装规则

  • 示例：查询一个部门信息的同时并查询下面的所有员工信息
  • Employee.java
  1. package com.sunxiaping.domain;
  3. import org.apache.ibatis.type.Alias;
  5. @Alias("emp")
  6. public class Employee {
  8.     private Integer id;
  10.     private String lastName;
  12.     private String email;
  14.     private String gender;
  16.     public Integer getId() {
  17.         return id;
  18.     }
  20.     public void setId(Integer id) {
  21.         this.id = id;
  22.     }
  24.     public String getLastName() {
  25.         return lastName;
  26.     }
  28.     public void setLastName(String lastName) {
  29.         this.lastName = lastName;
  30.     }
  32.     public String getEmail() {
  33.         return email;
  34.     }
  36.     public void setEmail(String email) {
  37.         this.email = email;
  38.     }
  40.     public String getGender() {
  41.         return gender;
  42.     }
  44.     public void setGender(String gender) {
  45.         this.gender = gender;
  46.     }
  48.     @Override
  49.     public String toString() {
  50.         return "Employee{" +
  51.                 "id=" + id +
  52.                 ", lastName='" + lastName + '\'' +
  53.                 ", email='" + email + '\'' +
  54.                 ", gender='" + gender + '\'' +
  55.                 '}';
  56.     }
  57. }
  • Department.java
  1. package com.sunxiaping.domain;
  3. import java.util.List;
  5. public class Department {
  7.     private Integer id;
  9.     private String name;
  11.     private List<Employee> employees;
  13.     public Integer getId() {
  14.         return id;
  15.     }
  17.     public void setId(Integer id) {
  18.         this.id = id;
  19.     }
  21.     public String getName() {
  22.         return name;
  23.     }
  25.     public void setName(String name) {
  26.         this.name = name;
  27.     }
  29.     public List<Employee> getEmployees() {
  30.         return employees;
  31.     }
  33.     public void setEmployees(List<Employee> employees) {
  34.         this.employees = employees;
  35.     }
  37.     @Override
  38.     public String toString() {
  39.         return "Department{" +
  40.                 "id=" + id +
  41.                 ", name='" + name + '\'' +
  42.                 ", employees=" + employees +
  43.                 '}';
  44.     }
  45. }
  • DepartmentMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Department;
  5. public interface DepartmentMapper {
  7.     Department findById(Integer id);
  9. }
  • DepartmentMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.mapper.DepartmentMapper">
  7.     <resultMap id="dept" type="com.sunxiaping.domain.Department">
  8.         <id property="id" column="id"/>
  9.         <result property="name" column="name"/>
  10.          <!--
  11.             collection 定义关联集合类型的属性的封装规则
  12.                 ofType 定义集合中元素的类型
  13.          -->
  14.         <collection property="employees" ofType="com.sunxiaping.domain.Employee">
  15.             <!-- 定义这个集合中元素的封装规则 -->
  16.             <id property="id" column="eid"/>
  17.             <result property="lastName" column="lastName"/>
  18.             <result property="gender" column="gender"/>
  19.             <result property="email" column="email"/>
  20.         </collection>
  21.     </resultMap>
  23.     <select id="findById" resultMap="dept">
  24.         SELECT d.id as id,d.name as name,e.id as eid,e.last_name as lastName,e.email as email,e.gender as gender
  25.         FROM department d
  26.         INNER JOIN employee e
  27.         ON d.id = e.department_id
  28.         WHERE d.id = #{id,jdbcType=INTEGER}
  29.     </select>
  30. </mapper>

5.3.2.6 collection 定义分步查询

  • 示例：查询一个部门信息的同时并查询下面的所有员工信息
  • Employee.java
  1. package com.sunxiaping.domain;
  3. import org.apache.ibatis.type.Alias;
  5. @Alias("emp")
  6. public class Employee {
  8.     private Integer id;
  10.     private String lastName;
  12.     private String email;
  14.     private String gender;
  16.     public Integer getId() {
  17.         return id;
  18.     }
  20.     public void setId(Integer id) {
  21.         this.id = id;
  22.     }
  24.     public String getLastName() {
  25.         return lastName;
  26.     }
  28.     public void setLastName(String lastName) {
  29.         this.lastName = lastName;
  30.     }
  32.     public String getEmail() {
  33.         return email;
  34.     }
  36.     public void setEmail(String email) {
  37.         this.email = email;
  38.     }
  40.     public String getGender() {
  41.         return gender;
  42.     }
  44.     public void setGender(String gender) {
  45.         this.gender = gender;
  46.     }
  48.     @Override
  49.     public String toString() {
  50.         return "Employee{" +
  51.                 "id=" + id +
  52.                 ", lastName='" + lastName + '\'' +
  53.                 ", email='" + email + '\'' +
  54.                 ", gender='" + gender + '\'' +
  55.                 '}';
  56.     }
  57. }
  • Department.java
  1. package com.sunxiaping.domain;
  3. import java.util.List;
  5. public class Department {
  7.     private Integer id;
  9.     private String name;
  11.     private List<Employee> employees;
  13.     public Integer getId() {
  14.         return id;
  15.     }
  17.     public void setId(Integer id) {
  18.         this.id = id;
  19.     }
  21.     public String getName() {
  22.         return name;
  23.     }
  25.     public void setName(String name) {
  26.         this.name = name;
  27.     }
  29.     public List<Employee> getEmployees() {
  30.         return employees;
  31.     }
  33.     public void setEmployees(List<Employee> employees) {
  34.         this.employees = employees;
  35.     }
  37.     @Override
  38.     public String toString() {
  39.         return "Department{" +
  40.                 "id=" + id +
  41.                 ", name='" + name + '\'' +
  42.                 ", employees=" + employees +
  43.                 '}';
  44.     }
  45. }
  • EmployeeMapper.java
package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

    List<Employee> findByDepatmentId(Integer departmentId);

}
  • 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.sunxiaping.mapper.EmployeeMapper">

    <resultMap id="emp" type="com.sunxiaping.domain.Employee">

        <id property="id" column="id"/>
        <result property="lastName" column="last_name"/>
        <result property="email" column="email"/>
        <result property="gender" column="gender"/>
    </resultMap>

    <select id="findByDepatmentId" resultMap="emp">
        SELECT e.id as id,e.last_name as last_name,e.gender as gender,e.email as email,e.department_id as did
        FROM employee e
        WHERE e.department_id = #{departmentId,jdbcType=INTEGER}
    </select>
</mapper>
  • DepartmentMapper.java
package com.sunxiaping.mapper;

import com.sunxiaping.domain.Department;

public interface DepartmentMapper {

    Department findById(Integer id);

}
  • 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.sunxiaping.mapper.DepartmentMapper">

    <resultMap id="dept" type="com.sunxiaping.domain.Department">
        <id property="id" column="id"/>
        <result property="name" column="name"/>

        <collection property="employees" select="com.sunxiaping.mapper.EmployeeMapper.findByDepatmentId" column="{id}">
        </collection>
    </resultMap>

    <select id="findById" resultMap="dept">
        SELECT d.id as id,d.name as name
        FROM department d
        WHERE d.id = #{id,jdbcType=INTEGER}
    </select>
</mapper>

5.2.3.7 分步查询传递多列值

  • 有的时候，分步查询不是传递一个值，而是传递多列值，这个时候我们需要使用 {key1=column1,key2=clomn2…} 的形式来多列值封装 map 传递。

  • 示例：

  • 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.sunxiaping.mapper.DepartmentMapper">

    <resultMap id="dept" type="com.sunxiaping.domain.Department">
        <id property="id" column="id"/>
        <result property="name" column="name"/>

        <collection property="employees" select="com.sunxiaping.mapper.EmployeeMapper.findByDepatmentId" column="{departmentId=id}">
        </collection>
    </resultMap>

    <select id="findById" resultMap="dept">
        SELECT d.id as id,d.name as name
        FROM department d
        WHERE d.id = #{id,jdbcType=INTEGER}
    </select>
</mapper>

5.2.3.8 fetchType

  • 在 collection 和 association 标签的 fetchType 属性有 eager 和 lazy 属性，其中 eager 属性表示立即加载，而 lazy 属性表示延迟加载。一旦指定了 fetchType 可以覆盖全局的延迟加载策略。

5.2.3.9 discriminator 鉴别器

  • Mybatis 可以使用 discriminator 判断某列的值，然后根据某列的值改变封装行为。

  • 示例：判断 Employee，如果查出的是女生，就把部门信息查询出来，否则不查询；如果查询出来的是男生，就把 last_name 这一列的值赋值给 email

  • Department.java
package com.sunxiaping.domain;

public class Department {

    private Integer id;

    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Department{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }
}
  • Employee.java
package com.sunxiaping.domain;

import org.apache.ibatis.type.Alias;

@Alias("emp")
public class Employee {

    private Integer id;

    private String lastName;

    private String email;

    private String gender;

    private Department department;


    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender='" + gender + '\'' +
                ", department=" + department +
                '}';
    }
}
  • DepartmentMapper.java
package com.sunxiaping.mapper;

import com.sunxiaping.domain.Department;

public interface DepartmentMapper {

    Department findById(Integer id);

}
  • 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.sunxiaping.mapper.DepartmentMapper">

    <resultMap id="dept" type="com.sunxiaping.domain.Department">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
    </resultMap>

    <select id="findById" resultMap="dept">
        SELECT d.id as id,d.name as name
        FROM department d
        WHERE d.id = #{id,jdbcType=INTEGER}
    </select>
</mapper>
  • EmployeeMapper.java
package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

public interface EmployeeMapper {

    Employee findById(Integer id);

}
  • 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.sunxiaping.mapper.EmployeeMapper">

    <resultMap id="emp" type="com.sunxiaping.domain.Employee">

        <id property="id" column="id"/>
        <result property="lastName" column="last_name"/>
        <result property="email" column="email"/>
        <result property="gender" column="gender"/>
        <!--
            discriminator 鉴别器
             column：指定要判断的列名
             javaType：指定要判断的列对应的java类型
        -->
        <discriminator javaType="string" column="gender">
            <case value="女" resultType="com.sunxiaping.domain.Employee">
                <association property="department" javaType="java.lang.Integer" select="com.sunxiaping.mapper.DepartmentMapper.findById" column="did"/>
            </case>
            <case value="男" resultType="com.sunxiaping.domain.Employee">
                <id property="id" column="id"/>
                <result property="lastName" column="last_name"/>
                <result property="email" column="last_name"/>
                <result property="gender" column="gender"/>
            </case>
        </discriminator>

    </resultMap>


    <select id="findById" resultMap="emp">
        SELECT e.id as id,e.last_name as last_name,e.gender as gender,e.email as email,e.department_id as did
        FROM employee e
        WHERE e.department_id = #{departmentId,jdbcType=INTEGER}
    </select>
</mapper>