第一章：简介

  • 动态SQL 是 Mybatis 强大特性之一。极大的简化我们拼装 SQL 的操作。
  • 动态SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。
  • Mybatis 采用功能强大的基于 OGNL 的表达式来简化操作。
    • if
    • choose（when、otherwise）
    • trim（where、set）
    • foreach

第二章：环境搭建

  • 导入相关 jar 包的 Maven 坐标：
  1. <dependency>
  2.     <groupId>org.mybatis</groupId>
  3.     <artifactId>mybatis</artifactId>
  4.     <version>3.4.1</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>
  • sql脚本
  1. DROP TABLE IF EXISTS `employee`;
  2. CREATE TABLE `employee`  (
  3.   `id` int(11) NOT NULL AUTO_INCREMENT,
  4.   `last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  5.   `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  6.   `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  7.   PRIMARY KEY (`id`) USING BTREE
  8. ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
  • db.propreties
  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 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. }
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. public interface EmployeeMapper {
  5. }
  • 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. </mapper>
  • mybatis-config.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.         <!-- 开启对jdbcType的NULL的支持 -->
  12.         <setting name="jdbcTypeForNull" value="NULL"/>
  13.         <!-- 开启延迟加载 -->
  14.         <setting name="lazyLoadingEnabled" value="true"/>
  15.         <!-- 开启按需加载 -->
  16.         <setting name="aggressiveLazyLoading" value="false"/>
  17.     </settings>
  19.     <environments default="development">
  20.         <environment id="development">
  21.             <transactionManager type="JDBC"/>
  22.             <dataSource type="POOLED">
  23.                 <property name="driver" value="${jdbc.driverClass}"/>
  24.                 <property name="url" value="${jdbc.url}"/>
  25.                 <property name="username" value="${jdbc.username}"/>
  26.                 <property name="password" value="${jdbc.password}"/>
  27.             </dataSource>
  28.         </environment>
  29.     </environments>
  31.     <mappers>
  32.         <package name="com.sunxiaping.mapper"/>
  33.     </mappers>
  35. </configuration>

第三章：if 判断

  • 示例：
  • 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> getEmpsByConditionIf(Employee employee);
  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">
  6.     <select id="getEmpsByConditionIf" resultType="com.sunxiaping.domain.Employee">
  7.         SELECT id as id,last_name as lastName,gender as gender,email as email
  8.         FROM employee
  9.         WHERE 1 = 1
  10.         <!--
  11.             test：条件判断表达式OGNL
  12.         -->
  13.         <if test="id != null">
  14.             AND id = #{id,jdbcType=INTEGER}
  15.         </if>
  16.         <if test="lastName != null and lastName != ''">
  17.             AND last_name LIKE #{lastName,jdbcType=VARCHAR}
  18.         </if>
  19.         <if test="gender != null and gender != ''">
  20.             AND gender = #{gender,jdbcType=VARCHAR}
  21.         </if>
  22.         <if test="email != null and email != ''">
  23.             AND email = #{email,jdbcType=VARCHAR}
  24.         </if>
  25.     </select>
  26. </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.List;
  18. public class EmployeeTest {
  19.     SqlSessionFactory sqlSessionFactory = null;
  20.     SqlSession sqlSession = null;
  21.     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);
  32.     }
  34.     @After
  35.     public void after() {
  36.         if (null != sqlSession) {
  37.             sqlSession.close();
  38.         }
  39.     }
  41.     @Test
  42.     public void testFindById() {
  43.         Employee example = new Employee();
  44.         example.setId(1);
  45.         example.setGender("男");
  46.         List<Employee> employeeList = employeeMapper.getEmpsByConditionIf(example);
  47.         System.out.println("employeeList = " + employeeList);
  48.     }
  50. }

第四章：where 查询条件

  • 如果查询条件中有多个 AND 或 OR 语句，Mybatis 推荐使用 where 标签，其内部会自动将多余的 AND 或 OR 去掉。当然，where 标签强制类似 AND xxx=xxx ，AND xxx=xxx 的方式。

  • 示例：

  • 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> getEmpsByConditionWhere(Employee employee);
  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">
  6.     <select id="getEmpsByConditionWhere" resultType="com.sunxiaping.domain.Employee">
  7.         SELECT id as id,last_name as lastName,gender as gender,email as email
  8.         FROM employee
  9.         <where>
  10.             <if test="id != null">
  11.                 id = #{id,jdbcType=INTEGER}
  12.             </if>
  13.             <if test="lastName != null and lastName != ''">
  14.                 AND last_name LIKE #{lastName,jdbcType=VARCHAR}
  15.             </if>
  16.             <if test="gender != null and gender != ''">
  17.                 AND gender = #{gender,jdbcType=VARCHAR}
  18.             </if>
  19.             <if test="email != null and email != ''">
  20.                 AND email = #{email,jdbcType=VARCHAR}
  21.             </if>
  22.         </where>
  23.     </select>
  24. </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.List;
  18. public class EmployeeTest {
  19.     SqlSessionFactory sqlSessionFactory = null;
  20.     SqlSession sqlSession = null;
  21.     EmployeeMapper employeeMapper = null;
  23.     @Before
  24.     public void before() throws IOException {
  25.         String resource = "mybatis-config.xml";
  26.         InputStream inputStream = Resources.getResourceAsStream(resource);
  27.         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  28.         sqlSession = sqlSessionFactory.openSession(true);
  29.         employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
  30.     }
  32.     @After
  33.     public void after() {
  34.         if (null != sqlSession) {
  35.             sqlSession.close();
  36.         }
  37.     }
  39.     @Test
  40.     public void testFindById() {
  41.         Employee example = new Employee();
  42.         example.setGender("男");
  43.         List<Employee> employeeList = employeeMapper.getEmpsByConditionWhere(example);
  44.         System.out.println("employeeList = " + employeeList);
  45.     }
  46. }

第五章：trim 自定义字符串截取

  • 上面的 where 标签有限制，一旦使用了类似 xxx=xxx AND,xxx=xxx AND 的情况，where 标签就失效了，这个时候可以使用 trim 自定义字符串截取，当前，trim 标签也可以模拟出 where 标签的效果。

  • 示例：

  • 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> getEmpsByConditionTrim(Employee employee);
  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">
  6.     <select id="getEmpsByConditionTrim" resultType="com.sunxiaping.domain.Employee">
  7.         SELECT id as id,last_name as lastName,gender as gender,email as email
  8.         FROM employee
  9.         <!--
  10.             trim标签 
  11.                 prefix：前缀，trim标签体中是整个字符串拼串后的结果，prefix就是给整个字符串加一个前缀
  12.                 prefixOverrides：前缀覆盖。去掉整个字符串前面多余的字符
  13.                 suffix：后缀，prefix就是给整个字符串加一个后缀
  14.                 suffixOverrides：后缀覆盖。去掉整个字符串后面多余的字符
  15.         -->
  16.         <trim prefix="WHERE" suffixOverrides="AND">
  17.             <if test="id != null">
  18.                 id = #{id,jdbcType=INTEGER} AND
  19.             </if>
  20.             <if test="lastName != null and lastName != ''">
  21.                  last_name LIKE #{lastName,jdbcType=VARCHAR} AND
  22.             </if>
  23.             <if test="gender != null and gender != ''">
  24.                  gender = #{gender,jdbcType=VARCHAR} AND
  25.             </if>
  26.             <if test="email != null and email != ''">
  27.                  email = #{email,jdbcType=VARCHAR}
  28.             </if>
  29.         </trim>
  30.     </select>
  31. </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.List;
  18. public class EmployeeTest {
  19.     SqlSessionFactory sqlSessionFactory = null;
  20.     SqlSession sqlSession = null;
  21.     EmployeeMapper employeeMapper = null;
  23.     @Before
  24.     public void before() throws IOException {
  25.         String resource = "mybatis-config.xml";
  26.         InputStream inputStream = Resources.getResourceAsStream(resource);
  27.         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  28.         sqlSession = sqlSessionFactory.openSession(true);
  29.         employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
  30.     }
  32.     @After
  33.     public void after() {
  34.         if (null != sqlSession) {
  35.             sqlSession.close();
  36.         }
  37.     }
  39.     @Test
  40.     public void testFindById() {
  41.         Employee example = new Employee();
  42.         example.setGender("男");
  43.         List<Employee> employeeList = employeeMapper.getEmpsByConditionTrim(example);
  44.         System.out.println("employeeList = " + employeeList);
  45.     }
  47. }

第六章：choose 分支选择

  • 有点类似 Java 中的 switch-case 语句。

  • 示例：如果查询条件中有 id ，就用 id 查询；如果查询条件中有 lastName ，就用 lastName 查询；如果查询条件中有 gender，就用 gener 查询；否则，用 emial 查询。

  • 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> getEmpsByConditionChoose(Employee employee);
  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">
  6.     <select id="getEmpsByConditionChoose" resultType="com.sunxiaping.domain.Employee">
  7.         SELECT id as id,last_name as lastName,gender as gender,email as email
  8.         FROM employee
  9.         <where>
  10.             <!--
  11.                 choose标签：分支选择，有点类似Java中的switch-case
  12.             -->
  13.             <choose>
  14.                 <when test="id != null">
  15.                     id = #{id,jdbcType=INTEGER}
  16.                 </when>
  17.                 <when test="lastName != null and lastName != ''">
  18.                     last_name = #{lastName,jdbcType=VARCHAR}
  19.                 </when>
  20.                 <when test="gender != null and gender != ''">
  21.                     gender = #{gender,jdbcType=VARCHAR}
  22.                 </when>
  23.                 <otherwise>
  24.                     email = #{email,jdbcType=VARCHAR}
  25.                 </otherwise>
  26.             </choose>
  27.         </where>
  28.     </select>
  29. </mapper>

第七章：set 更新

  • set 标签一般结合 if 标签实现动态更新。Mybatis 中的 set 标签会自动将最右边多出的 , 去掉。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  5. public interface EmployeeMapper {
  7.     void updateEmployee(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.     <update id="updateEmployee">
  8.         UPDATE employee
  9.         <set>
  10.             <if test="lastName != null and lastName != ''">
  11.                 last_name = #{lastName,jdbcType=VARCHAR},
  12.             </if>
  13.             <if test="gender != null and gender != ''">
  14.                 gender = #{gender,jdbcType=VARCHAR},
  15.             </if>
  16.             <if test="email != null and email != ''">
  17.                 email = #{email,jdbcType=VARCHAR}
  18.             </if>
  19.         </set>
  20.         <where>
  21.             <if test="id != null">
  22.                 id = #{id,jdbcType=INTEGER}
  23.             </if>
  24.         </where>
  26.     </update>
  27. </mapper>

第八章：foreach 遍历

  • foreach 标签类似于 jstl 中的 <c:forEach> 标签，都是迭代一个集合中的对象。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  4. import org.apache.ibatis.annotations.Param;
  6. import java.util.List;
  8. public interface EmployeeMapper {
  10.     List<Employee> findEmpsByConditionForeach(@Param("ids") List<Integer> ids);
  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="findEmpsByConditionForeach" resultType="com.sunxiaping.domain.Employee">
  8.         SELECT id as id,last_name as lastName,gender as gender,email as email
  9.         FROM employee
  10.         <!--
  11.             foreach 遍历集合
  12.                 collection：指定要遍历的集合
  13.                 item：将当前遍历的元素赋值给指定的变量
  14.                 separator：每个元素之间的分隔符
  15.                 open：遍历出所有的结果拼接一个开始的字符
  16.                 close：遍历出所有的结果拼接一个结束的字符
  17.                 index：
  18.                     如果遍历的是List，那么index就是索引，item就是当前的值。
  19.                     如果遍历的是Map，那么index就是Map的key，item就是Map中的值。
  20.         -->   
  21.         <foreach collection="ids" item="id" open="WHERE id IN (" separator="," close=")">
  22.             #{id,jdbcType=INTEGER}
  23.         </foreach>
  24.     </select>
  25. </mapper>

第九章：MySQL 下的 foreach 批量插入的两种方式

  • 第一种批量插入数据的 SQL 方式如下：
  1. INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...),(值1,值2,...);
  • 示例：
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  4. import org.apache.ibatis.annotations.Param;
  6. import java.util.List;
  8. public interface EmployeeMapper {
  10.     void batchInsertEmps(@Param("emps") List<Employee> employees);
  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">
  6.     <insert id="batchInsertEmps">
  7.         <foreach collection="emps" item="emp" open="INSERT INTO employee (last_name,gender,email) VALUES" separator=",">
  8.              (#{emp.lastName,jdbcType=VARCHAR},#{emp.gender,jdbcType=VARCHAR},#{emp.email,jdbcType=VARCHAR})
  9.         </foreach>
  10.     </insert>
  11. </mapper>
  • 第二种批量插入数据的 SQL 方式如下：
  1. INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);
  2. INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);

  • 但是，在 JDBC 操纵 MySQL 的时候，需要在 jdbc.url 上加上“allowMultiQueries=true”参数，当然这个参数也可以用于批量删除、批量更新。

  • 示例：

  • 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&allowMultiQueries=true
  2. jdbc.driverClass=com.mysql.cj.jdbc.Driver
  3. jdbc.username=root
  4. jdbc.password=123456
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  4. import org.apache.ibatis.annotations.Param;
  6. import java.util.List;
  8. public interface EmployeeMapper {
  10.     void batchInsertEmps(@Param("emps") List<Employee> employees);
  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">
  6.     <insert id="batchInsertEmps">
  7.         <foreach collection="emps" item="emp" separator=";">
  8.             INSERT INTO employee (last_name,gender,email) VALUES (#{emp.lastName,jdbcType=VARCHAR},#{emp.gender,jdbcType=VARCHAR},#{emp.email,jdbcType=VARCHAR})
  9.         </foreach>
  10.     </insert>
  11. </mapper>

第十章：Oracle 下的 foreach 批量插入的两种方式

  • 第一种批量插入数据的 SQL 方式如下：
  1. begin
  2.     INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);
  3.     INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);
  4. end;
  • 示例：
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  4. import org.apache.ibatis.annotations.Param;
  6. import java.util.List;
  8. public interface EmployeeMapper {
  10.     void batchInsertEmps(@Param("emps") List<Employee> employees);
  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">
  6.     <insert id="batchInsertEmps">
  7.         <foreach collection="emps" item="emp" open="begin" close="end;" >
  8.             INSERT INTO employee (id,last_name,gender,email) VALUES (#{employee_seq.nextval},#{emp.lastName,jdbcType=VARCHAR},#{emp.gender,jdbcType=VARCHAR},#{emp.email,jdbcType=VARCHAR}); 
  9.         </foreach>
  10.     </insert>
  11. </mapper>
  • 第二种批量插入数据的 SQL 方式如下：
  1. INSERT INTO 表名(字段1,字段2,...) 
  2.     SELECT 别名1,别名2,... FROM (
  3.         SELECT 值1 别名1,值2 别名2,... FROM dual
  4.            UNION
  5.         SELECT 值1 别名1,值2 别名2<?xml version="1.0" encoding="UTF-8" ?>
  6. <!DOCTYPE mapper
  7.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  8.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  9. <mapper namespace="com.sunxiaping.mapper.EmployeeMapper">
  10.     <insert id="batchInsertEmps">
  11.         <foreach collection="emps" item="emp" open="INSERT INTO employee (id,last_name,gender,email) SELECT employee.nextval,lastName,gender,email FROM (" close=")" separator="UNION">
  12.             SELECT #{emp.lastName,jdbcType=VARCHAR} lastName,#{emp.gender,jdbcType=VARCHAR} gender,#{emp.email,jdbcType=VARCHAR} email FROM dual
  13.         </foreach>
  14.     </insert>
  15. </mapper>,... FROM dual
  16.     )
  • 示例：
  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  4. import org.apache.ibatis.annotations.Param;
  6. import java.util.List;
  8. public interface EmployeeMapper {
  10.     void batchInsertEmps(@Param("emps") List<Employee> employees);
  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">
  6.     <insert id="batchInsertEmps">
  7.         <foreach collection="emps" item="emp" open="INSERT INTO employee (id,last_name,gender,email) SELECT employee.nextval,lastName,gender,email FROM (" close=")" separator="UNION">
  8.             SELECT #{emp.lastName,jdbcType=VARCHAR} lastName,#{emp.gender,jdbcType=VARCHAR} gender,#{emp.email,jdbcType=VARCHAR} email FROM dual
  9.         </foreach>
  10.     </insert>
  11. </mapper>

第十一章：内置参数

  • Mybatis 不只是方法传递过来的参数可以用来判断，取值，Mybatis默认还有两个默认的参数：_parameter 和 _databaseId 。
  • _parameter：代表整个参数。
    • 如果是单个参数：_parameter 就是这个参数。
    • 如果是多个参数：参数会封装到一个 Map 中，_parameter 就是代表这个 Map 。

  • _databaseId：如果在全局配置文件中配置了 databaseIdProvider 标签，那么 _databaseId 就是代表当前数据库的别名。

  • 示例：

  • mybatis-config.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.         <!-- 开启对jdbcType的NULL的支持 -->
  12.         <setting name="jdbcTypeForNull" value="NULL"/>
  13.         <!-- 开启延迟加载 -->
  14.         <setting name="lazyLoadingEnabled" value="true"/>
  15.         <!-- 开启按需加载 -->
  16.         <setting name="aggressiveLazyLoading" value="false"/>
  17.     </settings>
  19.     <environments default="development">
  20.         <environment id="development">
  21.             <transactionManager type="JDBC"/>
  22.             <dataSource type="POOLED">
  23.                 <property name="driver" value="${jdbc.driverClass}"/>
  24.                 <property name="url" value="${jdbc.url}"/>
  25.                 <property name="username" value="${jdbc.username}"/>
  26.                 <property name="password" value="${jdbc.password}"/>
  27.             </dataSource>
  28.         </environment>
  29.     </environments>
  31.     <databaseIdProvider type="DB_VENDOR">
  32.         <!--
  33.             为不同的数据库厂商起别名
  34.         -->
  35.         <property name="MySQL" value="mysql"/>
  36.         <property name="Oracle" value="oracle"/>
  37.         <property name="SQL Server" value="sqlserver"/>
  38.     </databaseIdProvider>
  39.     <mappers>
  40.         <package name="com.sunxiaping.mapper"/>
  41.     </mappers>
  43. </configuration>
  • 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> findByBuiltParameter();
  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.     <select id="findByBuiltParameter" resultType="com.sunxiaping.domain.Employee">
  8.         <if test="_databaseId == 'mysql'">
  9.             SELECT id as id,last_name as lastName,email as email ,gender as gender
  10.             FROM employee
  11.             LIMIT 0,5
  12.         </if>
  13.         <if test="_databaseId == 'oracle'">
  14.             SELECT * FROM
  15.             (SELECT e.*,rownum as r1 FROM employee e WHERE rownum &lt; 5)
  16.             WHERE r1 &gt; 1
  17.         </if>
  20.     </select>
  21. </mapper>

第十二章：bind 绑定

  • bind 标签可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  4. import org.apache.ibatis.annotations.Param;
  6. import java.util.List;
  8. public interface EmployeeMapper {
  10.    List<Employee> findByLastNameLike(@Param("lastName") String lastName);
  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="findByLastNameLike" resultType="com.sunxiaping.domain.Employee">
  8.         <bind name="_lastName" value="'%' + lastName + '%'"></bind>
  9.         SELECT id as id,last_name as lastName,email as email ,gender as gender
  10.         FROM employee
  11.         WHERE last_name like #{_lastName,jdbcType=VARCHAR}
  12.     </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.List;
  18. public class EmployeeTest {
  19.     SqlSessionFactory sqlSessionFactory = null;
  20.     SqlSession sqlSession = null;
  21.     EmployeeMapper employeeMapper = null;
  23.     @Before
  24.     public void before() throws IOException {
  25.         String resource = "mybatis-config.xml";
  26.         InputStream inputStream = Resources.getResourceAsStream(resource);
  27.         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
  28.         sqlSession = sqlSessionFactory.openSession(true);
  29.         employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
  30.     }
  32.     @After
  33.     public void after() {
  34.         if (null != sqlSession) {
  35.             sqlSession.close();
  36.         }
  37.     }
  39.     @Test
  40.     public void testFindById() {
  42.         List<Employee> employeeList = employeeMapper.findByLastNameLike("a");
  43.         System.out.println("employeeList = " + employeeList);
  44.     }
  46. }

第十三章：抽取可重用的 SQL 片段

  • sql 标签可以抽取可重用的 SQL 片段，方便后面引用。

  • 示例：

  • EmployeeMapper.java
  1. package com.sunxiaping.mapper;
  3. import com.sunxiaping.domain.Employee;
  4. import org.apache.ibatis.annotations.Param;
  6. import java.util.List;
  8. public interface EmployeeMapper {
  10.    List<Employee> findByLastNameLike(@Param("lastName") String lastName);
  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">
  6.     <!--
  7.         抽取可重用的SQL片段，方便后面引用
  8.     -->
  9.     <sql id="emp_sql" >
  10.         id as id,last_name as lastName,email as email ,gender as gender
  11.     </sql>
  13.     <select id="findByLastNameLike" resultType="com.sunxiaping.domain.Employee">
  14.         <bind name="_lastName" value="'%' + lastName + '%'"></bind>
  15.         <!--
  16.             include标签，引用外部定义的可重用的SQL片段
  17.         -->
  18.         SELECT <include refid="emp_sql"/>
  19.         FROM employee
  20.         WHERE last_name like #{_lastName,jdbcType=VARCHAR}
  21.     </select>
  22. </mapper>