动态SQL

李广妮2023/3/13

动态SQL

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

IF

作用

if标签可通过test属性（即传递过来的数据）的表达式进行判断，若表达式的结果为true，则标签中的内容会执行；反之标签中的内容不会执行

示例

<select id="getEmpByCondition" resultType="com.lgn.mybatis.pojo.Emp">
    select * from t_emp where 1=1
    <if test="empName != null and empName !=''">
        and emp_name = #{empName}
    </if>
    <if test="age != null and age !=''">
        and age = #{age}
    </if>
    <if test="sex != null and sex !=''">
        and sex = #{sex}
    </if>
    <if test="email != null and email !=''">
        and email = #{email}
    </if>
    <if test="dept != null and dept.deptId != ''">
        and dept_id = #{dept.deptId}
    </if>
</select>

注意:

当第一个if条件为空时，会导致sql语句中出现WHERE AND type = ? ...的情况，造成SQL语句异常。

此时会在where后面添加一个恒成立条件1=1

这个恒成立条件并不会影响查询的结果

例如：当empName为null时

如果不加上恒成立条件，则SQL语句为select * from t_emp where and age = ? and sex = ? and email = ?，此时where会与and连用，SQL语句会报错
如果加上一个恒成立条件，则SQL语句为select * from t_emp where 1= 1 and age = ? and sex = ? and email = ?，此时不报错

测试

@Test
public void testCondition(){
    Emp emp = new Emp();
    emp.setAge(23);
    emp.setEmpName("张三");
    emp.setDept(new Dept(1, "A"));
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    mapper.getEmpByCondition(emp);
}

WHERE

作用

where和if一般结合使用：

若where标签中的if条件都不满足，则where标签没有任何功能，即不会添加where关键字
若where标签中的if条件满足，则where标签会自动添加where关键字，并将条件最前方多余的and/or去掉

示例

<select id="getEmpByCondition2" resultType="Emp">
    select * from t_emp
    <where>
        <if test="empName != null and empName !=''">
            emp_name = #{empName}
        </if>
        <if test="sex != null and sex !=''">
            and sex = #{sex}
        </if>
        <if test="dept != null and dept.deptId != ''">
            and dept_id = #{dept.deptId}
        </if>
    </where>
</select>

注意：where标签不能去掉条件后多余的and/or

<!--这种用法是错误的，只能去掉条件前面的and/or，条件后面的不行-->
<if test="empName != null and empName !=''">
    emp_name = #{empName} and
</if>
<if test="age != null and age !=''">
    age = #{age}
</if>

测试

@Test
public void testCondition2(){
    Emp emp = new Emp();
    emp.setAge(23);
    emp.setEmpName("张三");
    emp.setDept(new Dept(1, "A"));
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    mapper.getEmpByCondition2(emp);
}

trim

作用

trim用于去掉或添加标签中的内容

若trim中的标签都不满足条件，则trim标签没有任何效果，也就是只剩下select * from t_emp

常用属性

prefix：在trim标签中的内容的前面添加某些内容
suffix：在trim标签中的内容的后面添加某些内容
prefixOverrides：在trim标签中的内容的前面去掉某些内容
suffixOverrides：在trim标签中的内容的后面去掉某些内容

示例

<select id="getEmpByCondition3" resultType="com.lgn.mybatis.pojo.Emp">
    select * from t_emp
    <trim prefix="where" suffixOverrides="and|or">
        <if test="empName != null and empName !=''">
            emp_name = #{empName} and
        </if>
        <if test="age != null and age !=''">
            age = #{age} and
        </if>
        <if test="sex != null and sex !=''">
            sex = #{sex} and
        </if>
        <if test="email != null and email !=''">
            email = #{email} and
        </if>
        <if test="dept != null and dept.deptId != ''">
            dept_id = #{dept.deptId} and
        </if>
    </trim>
</select>

测试
image-20230313133832240

choose、when、otherwise

作用

choose、when、otherwise相当于if...else if..else
when至少要有一个，otherwise至多只有一个

示例

<select id="getEmpByChoose" resultType="Emp">
	select * from t_emp
	<where>
		<choose>
			<when test="empName != null and empName != ''">
				emp_name = #{empName}
			</when>
			<when test="age != null and age != ''">
				age = #{age}
			</when>
			<when test="sex != null and sex != ''">
				sex = #{sex}
			</when>
			<when test="email != null and email != ''">
				email = #{email}
			</when>
			<otherwise>
				did = 1
			</otherwise>
		</choose>
	</where>
</select>

相当于if a else if b else if c else d，只会执行其中一个

foreach

作用

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。

它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符，看它多智能！

你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

常用属性

collection：设置要循环的数组或集合
item：表示集合或数组中的每一个数据
separator：设置循环体之间的分隔符，分隔符前后默认有一个空格，如,
open：设置foreach标签中的内容的开始符
close：设置foreach标签中的内容的结束符

示例

批量删除

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

@Test
public void deleteMoreByArray() {
    SqlSession sqlSession = SqlSessionUtils.getSqlSession();
    DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
    int result = mapper.deleteMoreByArray(new Integer[]{9, 10, 11, 12});
    System.err.println(result);
}

批量添加

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

@Test
public void insertMoreByList() {
	SqlSession sqlSession = SqlSessionUtils.getSqlSession();
	DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
	Emp emp1 = new Emp(null,"a",1,"男","123@321.com",null);
	Emp emp2 = new Emp(null,"b",1,"男","123@321.com",null);
	Emp emp3 = new Emp(null,"c",1,"男","123@321.com",null);
	List<Emp> emps = Arrays.asList(emp1, emp2, emp3);
	int result = mapper.insertMoreByList(emps);
	System.out.println(result);
}

SQL片段

作用

我们可能会将一些功能的部分抽取出来，在使用的地方通过include标签进行引入，方便复用！

示例

示例1

<!-- 使用SQL标签抽取公共部分可 -->
<sql id="if-title-author">
    <if test="title!=null">
        title = #{title}
    </if>
    <if test="author!=null">
        and author = #{author}
    </if>
</sql>

<!-- 在需要使用的地方使用Include标签引用即可 -->
<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <include refid="if-title-author"></include>
    </where>
</select>

示例2

<sql id="selectArchAlldbSearchVo">
	select id, datatype, archivetype, dirorvolumn, dataid, directorynum, titlename, studentname, studentid, createunit, securitelevel, vicetitlename, responsiblename, retention, startdate, enddate, createtime, titlenum, serialnum, pagenum, attachnum, deleteflag, deletetime, yeararchive, ordernum, state, campus, fondnum from arch_alldb_search
</sql>

<select id="selectArchAlldbSearchList" parameterType="ArchAlldbSearch" resultMap="ArchAlldbSearchResult">
	<include refid="selectArchAlldbSearchVo"/>
	<where>
		<if test="titlename != null  and titlename != ''"> and titlename like concat('%', #{titlename}, '%')</if>
		<if test="titlename1 != null  and titlename1 != ''"> and titlename like concat('%', #{titlename1}, '%')</if>
    </where>
</select>