MyBatis | 06 - Dynamic SQL

「这是我参与2022首次更文挑战的第28天，活动详情查看：2022首次更文挑战」

一、动态SQL

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

• if
• choose (when, otherwise)
• trim (where, set)
• foreach

新建数据库表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_teacher
-- ----------------------------
DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `class_name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of t_teacher
-- ----------------------------
BEGIN;
INSERT INTO `t_teacher` VALUES (1, 'stark', '三年二班', '浦东', '2022-02-13');
INSERT INTO `t_teacher` VALUES (2, 'steve', '三年三班', '静安', '2022-02-12');
INSERT INTO `t_teacher` VALUES (3, 'clint', '三年四班', '黄埔', '2022-02-11');
INSERT INTO `t_teacher` VALUES (4, 'banner', '三年二班', '静安', '2022-02-16');
INSERT INTO `t_teacher` VALUES (5, 'thor', '三年四班', '浦东', '2022-02-16');
INSERT INTO `t_teacher` VALUES (6, 'strange', '三年三班', '黄埔', '2022-02-16');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

工程搭建

创建一个maven项目mybatis-dynamic-sql，加入相关依赖

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.7</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.16</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-classic</artifactId>
        <version>1.2.3</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.16.18</version>
        <scope>provided</scope>
    </dependency>
</dependencies>

在resource目录下新增数据库信息配置文件db.properties

jdbc_driver=com.mysql.cj.jdbc.Driver
jdbc_url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai
jdbc_username=root
jdbc_password=root

在resource目录下新增MyBatis全局配置文件mybatis-config.xml，开启驼峰命名规则

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

    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!--设置默认指向的数据库-->
    <environments default="dev">
        <!--配置环境，不同的环境不同的id名字-->
        <environment id="dev">
            <!-- 采用JDBC方式对数据库事务进行commit/rollback -->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc_driver}"/>
                <property name="url" value="${jdbc_url}"/>
                <property name="username" value="${jdbc_username}"/>
                <property name="password" value="${jdbc_password}"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

在resource目录下新增logback.xml日志配置文件

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
   <appender name="console" class="ch.qos.logback.core.ConsoleAppender">
       <encoder>
           <pattern>[%thread] %d{HH:mm:ss.SSS} %-5level %logger{36} - %msg%n</pattern>
       </encoder>
   </appender>
    <root level="debug">
        <appender-ref ref="console"/>
    </root>
</configuration>

新建entity包，增加Teacher实体类，对应t_teacher表

@Data
public class Teacher {

    private Integer id;
    private String teacherName;
    private String className;
    private String address;
    private Date birthDate;
}

在mapper包中新增TeacherMapper接口，并增加一个方法

public interface TeacherMapper {

    Teacher getTeacherById(Integer id);
}

在resources目录下新建mappers文件夹，新增SQL映射文件 TeacherMapper.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.citi.mapper.TeacherMapper">

    <select id="getTeacherById" resultType="com.citi.entity.Teacher">
        select * from t_teacher where id = #{id}
    </select>
</mapper>

在MyBatis全局配置文件mybatis-config.xml中注册TeacherMapper.xml

<mappers>
    <mapper resource="mappers/TeacherMapper.xml"/>
</mappers>

在test包下新增TeacherMapperTest类，对getTeacherById方法测试

public class TeacherMapperTest {

    SqlSessionFactory sqlSessionFactory = null;
    SqlSession openSession = null;

    @Before
    public void setUp() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        openSession = sqlSessionFactory.openSession();

    }

    @Test
    public void getTeacherById() {
        TeacherMapper teacherMapper = openSession.getMapper(TeacherMapper.class);
        Teacher teacher = teacherMapper.getTeacherById(1);
        System.out.println(teacher);
    }
}

执行该测试方法

if，判断条件

if标签可以对WHERE关键字后面的条件进行判断，通过test属性进行条件判断

在TeacherMapper中新增一个方法，根据条件获取Teacher列表

List<Teacher> getTeacherList(Teacher teacher);

在TeacherMapper.xml中新增SQL语句

<select id="getTeacherList" resultType="com.citi.entity.Teacher">
    select * from t_teacher where
    <!--test="",编写判断条件 id!= null，取出传入的JavaBean属性中ID的值，判断是否为空-->
    <if test="id!=null">
        id > #{id} and
    </if>
    <if test="className!=null">
        class_name like #{className} and
    </if>
    <if test="birthDate!=null">
        birth_date &lt; #{birthDate}
    </if>
</select>

如果判断的test=true则加入if标签内的查询条件，test=false就忽略该if标签下的查询条件。一些判断的字符如大于、小于、&&等可以参考HTML ISO 8859-1

在TeacherMapperTest中新增测试方法

@Test
public void getTeacherList() {
    TeacherMapper teacherMapper = openSession.getMapper(TeacherMapper.class);
    Teacher teacher = new Teacher();
    teacher.setId(1);
    teacher.setClassName("三年%");
    teacher.setBirthDate(new Date());
    List<Teacher> teacherList = teacherMapper.getTeacherList(teacher);
    System.out.println(teacherList);
}

执行测试，查看控制台输出的SQL语句

where，自动去除SQL语句中多余的and

在SQL语句中含有多个if判断条件一旦某一个if判断不满足条件，那么这个SQL语句会多出来一个and，这样就会导致SQL执行报错。

将测试方法中给birthDate赋值的代码注销，再次执行测试

这种情况下可以使用where标签，自动去除多余的and，修改SQL语句为

<select id="getTeacherList" resultType="com.citi.entity.Teacher">
    select * from t_teacher
    <where>
        <!--test="",编写判断条件 id!= null，取出传入的JavaBean属性中ID的值，判断是否为空-->
        <if test="id!=null">
            id > #{id}
        </if>
        <if test="className!=null">
            and class_name like #{className}
        </if>
        <if test="birthDate!=null">
            and birth_date &lt; #{birthDate}
        </if>
    </where>

</select>

再次执行测试

注销id赋值代码，执行测试

trim，截取字符串

去掉后面的and，判断条件可能条件前或者条件后出现多余的and，条件前出现多余的and可以使用where标签去除，条件后出现的对于的and where标签无能为力，这时可以使用trim标签

修改SQL语句

<select id="getTeacherList" resultType="com.citi.entity.Teacher">
    select * from t_teacher
    <where>
        <!--test="",编写判断条件 id!= null，取出传入的JavaBean属性中ID的值，判断是否为空-->
        <if test="id!=null">
            id > #{id} and
        </if>
        <if test="className!=null">
            class_name like #{className} and
        </if>
        <if test="birthDate!=null">
            birth_date &lt; #{birthDate} and
        </if>
    </where>

</select>

注销测试代码中id和birthDate的赋值代码，执行测试 修改SQL语句

<select id="getTeacherList" resultType="com.citi.entity.Teacher">
    select * from t_teacher
    <trim prefix="where" suffixOverrides="and">
        <!--test="",编写判断条件 id!= null，取出传入的JavaBean属性中ID的值，判断是否为空-->
        <if test="id!=null">
            id > #{id} and
        </if>
        <if test="className!=null">
            class_name like #{className} and
        </if>
        <if test="birthDate!=null">
            birth_date &lt; #{birthDate} and
        </if>
    </trim>
</select>

• prefix=""：前缀，为trim标签包裹的SQL增加一个前缀
• prefixOverrides=""：去除整体字符串前多余的字符
• suffix=""：为整体添加一个后缀
• suffixOverrides=""：去除整体字符串后面多余的字符 再次执行测试

建议查询条件就放在where标签中，and关键字放在条件前面，一旦条件判断为false，where会自动去除多余的and关键字

foreach，遍历集合

TeacherMapper接口中新增一个方法，根据传入的ids集合查询所有的Teacher

List<Teacher> getTeacherListByIds(@Param("ids")List<Integer> ids);

在TeacmerMapper.xml中增加方法的SQL语句

<select id="getTeacherListByIds" resultType="com.citi.entity.Teacher">
    select * from t_teacher where id IN
    <foreach collection="ids" item="id_item" separator="," open="(" close=")">
        #{id_item}
    </foreach>
</select>

• collection：指要遍历的集合的key
• open：以什么开始
• close：以什么结束
• separator：遍历元素的分隔符
• item：遍历的元素的变量名
• index：索引
  • 遍历List:
    • index：保存了当前元素的索引
    • item：保存了当前元素的值
  • 遍历Map:
    • index：保存当前元素的key
    • item：保存当前元素的值

新增测试方法

@Test
public void getTeacherListByIds() {
    TeacherMapper teacherMapper = openSession.getMapper(TeacherMapper.class);
    List<Integer> ids = Arrays.asList(1,2,3);
    List<Teacher> teacherList = teacherMapper.getTeacherListByIds(ids);
    System.out.println(teacherList);
}

执行测试

choose，分支选择，相当于if-else

TeacherMapper接口中新增一个方法

List<Teacher> getTeacherListByChoose(Teacher teacher);

在TeacherMapper.xml中增加SQL语句

<select id="getTeacherListByChoose" resultType="com.citi.entity.Teacher">
    select * from t_teacher
    <where>
        <choose>
            <when test="id!=null">
                id=#{id}
            </when>
            <when test="className!=null and !className.equals(&quot;&quot;)">
                class_name = #{className}
            </when>
            <when test="birthDate!=null">
                birth_date = #{birthDate}
            </when>
            <otherwise>
                1=1
            </otherwise>
        </choose>
    </where>
</select>

@Test
public void getTeacherListByChoose() {
    TeacherMapper teacherMapper = openSession.getMapper(TeacherMapper.class);
    Teacher teacher = new Teacher();
    teacher.setId(1);
    teacher.setClassName("三年二班");
    // teacher.setBirthDate(new Date());
    List<Teacher> teacherList = teacherMapper.getTeacherListByChoose(teacher);
    System.out.println(teacherList);
}

执行测试

id、className、birthDate赋值代码全部注销

choose可以实现条件查询条件的分支选择，if可以实现查询条件的叠加

set，实现动态更新

实现动态更新，自动去除逗号“,”

TeacherMapper中新增方法

int updateTeacher(Teacher teacher);

在TeacherMapper.xml中新增SQL语句

<update id="updateTeacher">
    update t_teacher
    <set>
        <if test="teacherName!=null and !teacherName.equals(&quot;&quot;)">
            teacher_name = #{teacherName},
        </if>
        <if test="className!=null and !className.equals(&quot;&quot;)">
            class_name = #{className},
        </if>
        <if test="address!=null and !address.equals(&quot;&quot;)">
            address = #{address},
        </if>
        <if test="birthDate!=null">
            birth_date = #{birthDate}
        </if>
    </set>
    <where>
        id = #{id}
    </where>
</update>

增加测试方法

@Test
public void updateTeacher() {
    TeacherMapper teacherMapper = openSession.getMapper(TeacherMapper.class);
    Teacher teacher = new Teacher();
    teacher.setId(1);
    teacher.setClassName("三年三班");
    teacher.setTeacherName("Tony Stark");
    teacher.setBirthDate(new Date());
    teacherMapper.updateTeacher(teacher);
    Teacher updateTeacher = teacherMapper.getTeacherById(1);
    System.out.println(updateTeacher);
}

执行测试

include与sql，抽取可重用的语句

以getTeacherListByIds方法的SQL映射语句为例

<!--抽取的可重复使用的SQL-->
<sql id="select*">select * from t_teacher</sql>

<select id="getTeacherListByIds" resultType="com.citi.entity.Teacher">
    <!--引用抽取的SQL-->
    <include refid="select*"></include> where id IN
    <foreach collection="ids" item="id_item" separator="," open="(" close=")">
        #{id_item}
    </foreach>
</select>