MyBatis——动态SQL

什么是动态SQL

---

动态SQL是MyBatis中的一种功能，允许你在XML映射文件中编写动态生成的SQL语句。主要用于解决SQL语句需要根据不同情况进行变化的问题。

例如，你可能需要根据用户输入的查询条件生成不同的where子句，或者可能需要根据程序运行时的状态切换不同的join子句等。

MyBatis提供了一套丰富的标签库来帮助你编写动态SQL，包括<if>、<choose>、<when>、<otherwise>、<foreach>等。

常用标签

---

<where>

可以动态生成 where

使用示例

<select id="testWhere" resultType="User">  
    select * from t_user  
    <where>  
        id = #{id}  
    </where>  
</select>

@Test  
public void testWhere() {  
    log.info("user: {}",sqlMapper.testWhere(null));  
}

输出结果：

2024-04-27 13:39:06 924 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testWhere - ==> Preparing: select * from t_user WHERE id = ?
2024-04-27 13:39:06 946 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testWhere - > Parameters: 1(Integer)
2024-04-27 13:39:07 008 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testWhere - < Total: 0
2024-04-27 13:39:07 023 [main] INFO com.zxb.mybatis.test.SqlMapperTest - user: null

<if>

常与<where>搭配使用，可以根据判断结果动态生成标签内的内容，符合时生成，不符合不生成，且自动检测前面是否要加and和or

使用示例

<select id="testWhere" resultType="User">  
    select * from t_user  
    <where>  
        <if test="id != null">  
            id = #{id}  
        </if>  
    </where>  
</select>

@Test  
public void testWhere() {  
    log.info("user: {}",sqlMapper.testWhere(null));  
}

输出结果：

2024-04-27 13:43:55 684 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testWhere - ==> Preparing: select * from t_user
2024-04-27 13:43:55 709 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testWhere - > Parameters:
2024-04-27 13:43:55 789 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testWhere - < Total: 25
2024-04-27 13:43:55 804 [main] INFO com.zxb.mybatis.test.SqlMapperTest - user: [User(id=4, username=王五, password=789, age=28, sex=男, email=789@qq.com), User(id=5, username=赵六, password=abc, age=31, sex=男, email=abc@qq.com), User(id=6, username=钱七, password=def, age=24, sex=女, email=def@qq.com), User(id=7, username=孙八, password=ghi, age=27, sex=男, email=ghi@qq.com), User(id=8, username=周九, password=jkl, age=30, sex=女, email=jkl@qq.com), User(id=9, username=吴十, password=mno, age=33, sex=男, email=mno@qq.com), User(id=10, username=郑十一, password=pqr, age=26, sex=女, email=pqr@qq.com), User(id=11, username=王十二, password=stu, age=29, sex=男, email=stu@qq.com), User(id=12, username=张十三, password=vwx, age=32, sex=女, email=vwx@qq.com), User(id=13, username=李十四, password=yz1, age=25, sex=男, email=yz1@qq.com), User(id=14, username=赵十五, password=234, age=28, sex=男, email=234@qq.com), User(id=15, username=钱十六, password=567, age=31, sex=女, email=567@qq.com), User(id=16, username=孙十七, password=890, age=24, sex=男, email=890@qq.com), User(id=17, username=周十八, password=abc, age=27, sex=女, email=abc@qq.com), User(id=18, username=吴十九, password=def, age=30, sex=男, email=def@qq.com), User(id=19, username=郑二十, password=ghi, age=33, sex=女, email=ghi@qq.com), User(id=20, username=王二十一, password=jkl, age=26, sex=男, email=jkl@qq.com), User(id=21, username=张二十二, password=mno, age=29, sex=女, email=mno@qq.com), User(id=22, username=李二十三, password=pqr, age=32, sex=男, email=pqr@qq.com), User(id=23, username=赵二十四, password=stu, age=25, sex=女, email=stu@qq.com), User(id=24, username=钱二十五, password=vwx, age=28, sex=男, email=vwx@qq.com), User(id=25, username=孙二十六, password=yz1, age=31, sex=男, email=yz1@qq.com), User(id=26, username=周二十七, password=234, age=24, sex=女, email=234@qq.com), User(id=29, username=王五, password=je2, age=19, sex=男, email=kk@qq.com), User(id=30, username=李四, password=123456, age=18, sex=男, email=lisi@qq.com)]

<trim>

---

当标签中有内容时，将 prefix 或 suffix 中的内容添加到对应位置， 去掉 suffixOverrides 或 prefixOverrides 对应位置的内容
当标签中没有内容时，trim没有作用

属性	作用
prefix 或 suffix	将trim标签中内容前面或后面添加指定内容
suffixOverrides 或 prefixOverrides	将trim标签中内容前面或后面去掉指定内容

<trim prefix="where">  
        <if test="eid != null">  
            eid = #{eid}  
        </if>  
        <if test="empName != null">  
            and emp_name = #{empName}  
        </if>  
        <if test="age != null">  
            and age = #{age}  
        </if>  
        <if test="sex != null">  
            and sex = #{sex}  
        </if>  
        <if test="email != null">  
            and email = #{email}  
        </if>  
    </trim>  
</select>

<choose>、<when>、<otherwise>

choose、when、otherwise 可以看作java的 if ... else if ... other ，

例如，在XSLT中：

<xsl:choose>
  <xsl:when test="condition1">
    <!-- Some actions here --> 
  </xsl:when>
  <xsl:when test="condition2">
    <!-- Some actions here --> 
  </xsl:when>
  <xsl:otherwise>
    <!-- Some actions here --> 
  </xsl:otherwise>
</xsl:choose>

以上代码与下面的Java代码执行相同的逻辑：

if (condition1) {
    // Some actions here
} else if (condition2) {
    // Some actions here
} else {
    // Some actions here
}

使用示例

<select id="testChoose" resultType="com.zxb.mybatis.pojo.User">  
    select * from t_user  
    <where>  
        <choose>  
            <when test="id != null">id = #{id}</when>  
            <when test="age != null">age = #{age}</when>  
            <otherwise>id = 4</otherwise>  
        </choose>  
    </where>  
</select>

@Test  
public void testChoose() {  
    log.info("id result:{}", sqlMapper.testChoose(new User(5, null, null, null, null, null)));  
    log.info("age result: {}", sqlMapper.testChoose(new User(null, null, null, 18, null, null)));  
    log.info("other result: {}",sqlMapper.testChoose(null));  
}

输出结果：

2024-04-27 14:00:53 277 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testChoose - ==> Preparing: select * from t_user WHERE id = ?
2024-04-27 14:00:53 300 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testChoose - > Parameters: 5(Integer)
2024-04-27 14:00:53 374 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testChoose - < Total: 1
2024-04-27 14:00:53 390 [main] INFO com.zxb.mybatis.test.SqlMapperTest - id result:[User(id=5, username=赵六, password=abc, age=31, sex=男, email=abc@qq.com)]
2024-04-27 14:00:53 391 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testChoose - ==> Preparing: select * from t_user WHERE age = ?
2024-04-27 14:00:53 391 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testChoose - > Parameters: 18(Integer)
2024-04-27 14:00:53 448 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testChoose - < Total: 1
2024-04-27 14:00:53 448 [main] INFO com.zxb.mybatis.test.SqlMapperTest - age result: [User(id=30, username=李四, password=123456, age=18, sex=男, email=lisi@qq.com)]
2024-04-27 14:00:53 448 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testChoose - ==> Preparing: select * from t_user WHERE id = 4
2024-04-27 14:00:53 449 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testChoose - > Parameters:
2024-04-27 14:00:53 505 [main] DEBUG com.zxb.mybatis.mapper.SqlMapper.testChoose - < Total: 1
2024-04-27 14:00:53 505 [main] INFO com.zxb.mybatis.test.SqlMapperTest - other result: [User(id=4, username=王五, password=789, age=28, sex=男, email=789@qq.com)]

<foreach>

<foreach>的功能和java中的foreach一致，将数组中的元素遍历到 item

属性	作用
collection	表示接收的集合
item	每次遍历的元素
open	以什么开头
close	以什么结尾（加在foreach结束之后）
separator	名称遍历元素后面的分隔符（最后一个元素不会添加）

注：传入映射的元素记得加 @param 不然会报错

使用 <foreach>实现批量删除

<delete id="deleteBatch">  
    delete from t_emp  
    where eid in    
    <foreach collection="eids" item="eid" open="(" close=")" separator=",">  
        #{eid}  
    </foreach>  
</delete>

也可以写成：

<delete id="deleteBatch">  
    delete from t_emp  
    where eid where    
    <foreach collection="eids" item="eid" separator="or">  
        #{eid}  
    </foreach>  
</delete>

test:

@Test  
public void testDeleteBatch() {  
    Integer[] eids = new Integer[]{1, 2, 3};  
    dynamicSQLMapper.deleteBatch(eids);  
}

使用 <foreach>实现批量添加

<insert id="insertBatch">  
    insert into t_emp values  
    <foreach collection="emps" item="emp" separator=",">  
        (null, #{emp.empName}, #{emp.age}, #{emp.sex}, #{emp.email}, null)  
    </foreach>  
</insert>

test:

@Test  
public void testInsertBatch() {  
    List<Emp> empList = new ArrayList<>();  
    empList.add(new Emp(null, "zhangsan", 18, "男", "zhangsan@qq.com", null));  
    empList.add(new Emp(null, "lisi", 19, "女", "lisi@qq.com", null));  
    empList.add(new Emp(null, "wangwu", 20, "男", "wangwu@qq.com", null));  
    dynamicSQLMapper.insertBatch(empList);  
}

<sql>标签

作用：将一段重复sql进行记录，在需要的地方使用 <include> 标签导入

<sql id="emp"> null, #{emp.empName}, #{emp.age}, #{emp.sex}, #{emp.email}, null</sql>  
<insert id="insertBatch">  
    insert into t_emp values  
    <foreach collection="emps" item="emp" separator=",">  
        (<include refid="emp"/>)  
    </foreach>  
</insert>