MyBatis中动态SQL的使用和注意事项说明

Fon |

已于 2023-05-21 21:39:13 修改

阅读量1.5k

点赞数

分类专栏： MyBatis｜From zero to hero 文章标签： mybatis sql 数据库

于 2023-05-21 21:33:18 首次发布

本文链接：https://blog.csdn.net/qq_41860497/article/details/130796303

版权

MyBatis｜From zero to hero 专栏收录该内容

15 篇文章 2 订阅

订阅专栏

文章目录

0、前言
1、if
2、where
3、trim
4、choose-when-otherwise
5、foreach
- 应用场景1：通过数组实现批量删除
- 应用场景2：通过list集合实现批量添加
6、include抽取公共SQL片段

0、前言

MyBatis框架动态SQL技术是根据特定的条件拼接SQL语句的功能，存在的意义是为了解决拼接SQL语句字符串痛点问题。

对于动态SQL不是什么新的东西，我们之前写的SQL是直接写死在xml配置文件中的，对于很多的代码判断，我们需要利用SQL的一些函数来进行判断，比如说判断age不能为空就需要在where语句中加上age is not null 。

那我们能不能在sql层面，加入一些逻辑代码,根据不同的条件生成不同的sql语句。是可以的。

其中包括，if判断，是否需要当前的SQL片段， where/trim/choose/foreach/sql片段include等等。

1、if

if标签可通过test属性的表达式进行判断，若表达式的结果为true，则标签中的内容会执行;反之标签中的内容不会执行
应用的场景：主要是多条件的查询
下面通过例子来说明：对于MyBatis项目搭建不太会的，直接看博文从0到1搭建MyBatis实例思路剖析，跟着做完就能入门MyBatis。
如果我想是以下这种情况的查询，就是一种多条件查询

对于这种多条件查询，如果没有动态SQL的处理逻辑是：
直接在SQL进行判断，或者是，直接在代码中处理。

select * from table where (userid = #{userid} or @userid is null) and (birth = #{birth} or birth = null”) and .....

或者是

if(userid != null || userid != ''){
	userMapper.select(id)
}
...

对于动态SQL可以很好的解决这个问题：
首先定义DynamicSqlMapper接口

public interface DynamicSQLMapper {
    /**
     * 多条件查询
     */
    List<Emp> getEmpByCondition(Emp emp);
}

DynamicSqlMapper.xml

   <!--        List<Emp> getEmpByCondition(Emp emp);-->
    <!--    加上1=1使得：即使emp_name为空，也不会导致sql语句变成：where and xxx-->
    <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp where 1=1
        <if test="empName != null and empName != ''">
            and emo_name = #{empName}
        </if>
        <if test="age != null and age != ''">
            and age = #{age}
        </if>
        <if test="email != null and email != ''">
            and email = #{email}
        </if>
        <if test="sex != null and sex != ''">
            and sex = #{sex}
        </if>
    </select>

测试类：

 /**
     * 动态sql
     * 1： if： 根据标签中test属性所对应的内容决定标签中的内容是否拼接在sql语句中
     */
    @Test
    public void testGetEmpByCondition() throws IOException {
        SqlSession sqlSession = getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        // 各信息都不为null/空字符串
        List<Emp> emp1 = mapper.getEmpByCondition(new Emp(null, "lucy", 23, "0", "lucy@gmail.com", null));
        // 中间存在查询出来是空，可能导致"select * from t_emp where emp_name= ? and and sex = ?..."的and和and在一起的情况
        List<Emp> emp2 = mapper.getEmpByCondition(new Emp(null, "", null, "1", "", null));
        // 第一个查询条件为空字符串，可能导致"select * from t_emp where and age = ? and ..."的where和and在一起的情况
        List<Emp> emp3 = mapper.getEmpByCondition(new Emp(null, null, null, "0", null, null));
        System.out.println(emp1);
        System.out.println(emp2);
        System.out.println(emp3);
    }

在这里插入图片描述

2、where

用于替代SQL语句中的where出现的，通过观察发现，对于只使用if来对是否要拼接条件进行判断，可能出现的情况是如果所有的if都不满足。那么最后生成的SQL会变成

select * from t_emp where

这必定报错，所以，需要加上一个 1 = 1，让其不能报错。能不能把where也加上一个判断。<where>应运而生，
当where中的所有条件都不成立，在生成最终SQL的时候，就不会加上where
应用场景：多条件查询
使用方法是直接替代where

   <!--        List<Emp> getEmpByCondition(Emp emp);-->
    <!--    加上1=1使得：即使emp_name为空，也不会导致sql语句变成：where and xxx-->
    <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp
        <where>
            <if test="empName != null and empName != ''">
                and emo_name = #{empName}
            </if>
            <if test="age != null and age != ''">
                and age = #{age}
            </if>
            <if test="email != null and email != ''">
                and email = #{email}
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>
        </where>
    </select>

测试类和if的一样，这里就不在重复。

3、trim

trim标签是自己自动的加上前缀和后缀，或者是去除掉前缀和后缀。
有四个属性；

prefix，prefixOverrides，suffix，suffixOverrides

prefix:给sql语句拼接的前缀
suffix:给sql语句拼接的后缀
prefixOverrides:去除sql语句前面的关键字或者字符，该关键字或者字符由prefixOverrides属性指定，假设该属性指定为"AND"，当sql语句的开头为"AND"，trim标签将会去除该"AND"
suffixOverrides:去除sql语句后面的关键字或者字符，该关键字或者字符由suffixOverrides属性指定

 <select id="getEmpByCondition" resultType="Emp">
        select * from t_emp
        <trim prefix="where" suffixOverrides="or|and" prefixOverrides="or|and">
            <if test="empName != null and empName != ''">
                and emo_name = #{empName}
            </if>
            <if test="age != null and age != ''">
                and age = #{age}
            </if>
            <if test="email != null and email != ''">
                and email = #{email}
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>
        </trim>
    </select>

去除掉最后的and或者是or，加上where的前缀。如果为空，则直接不输出标签内的内容。

4、choose-when-otherwise

choose、when、otherwise相当于if…else if…else

<!--        List<Emp> getEmpByChoose(Emp emp);-->
    <select id="getEmpByChoose" resultType="Emp">
        select * from t_emp
        <where>
            <choose>
                <when test="empName != null and empName != ''">
                    emo_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 = 2
                </otherwise>
            </choose>
        </where>
    </select>

5、foreach

应用场景1：通过数组实现批量删除

我们知道，如果要写批量删除的SQL有两种方式，一种是
delete from table where id in (1,2,3) 或者是delete from table where id = 1 or id = 2 or id = 3
对于foreach的使用也有两种形式:
DynamicSqlMapper接口:

public interface DynamicSQLMapper {
    /**
     * 通过数组实现批量删除
     */
    int deleteMoreByArray(@Param("eids") Integer[] eids);
}

DynamicSqlMapper.xml
方法一：delete from table where id in (1,2,3) 的形式

<!--        int deleteMoreByArray(Integer[] eids);-->
<!--    没加@Param时，
        报错：Parameter 'eids' not found. Available parameters are [array, arg0]
        因此最好都加上@Param-->
<!--        int deleteMoreByArray(@Param("eids") Integer[] eids);-->
    <delete id="deleteMoreByArray">
        delete from t_emp where eid in
            <foreach collection="eids" item="eid" separator="," open="(" close=")">
                #{eid}
    		</foreach>
    </delete>

方法二：delete from table where id = 1 or id = 2 or id = 3的形式

<!--        int deleteMoreByArray(Integer[] eids);-->
    <delete id="deleteMoreByArray">
    <!--方法2：-->
        delete from t_emp where
        <foreach collection="eids"  item="eid" separator="or">
            eid = #{eid}
        </foreach>
    </delete>

测试类；

 @Test
    public void testDeleteMoreByArray() throws IOException {
        SqlSession sqlSession = getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        int result = mapper.deleteMoreByArray(new Integer[]{2,3});
        System.out.println(result);
    }

在这里插入图片描述

应用场景2：通过list集合实现批量添加

DynamicSqlMapper接口

public interface DynamicSQLMapper {
    /**
     * 通过list集合实现批量添加
     */
    int insertMoreByList(@Param("emps") List<Emp> emps);
}

DynamicSqlMapper.xml

<!--        int insertMoreByList(List<Emp> emps);-->
<!--    不加注解会报错：Parameter 'emps' not found. Available parameters are [arg0, collection, list]-->
<!--    int insertMoreByList(@Param("emps") List<Emp> emps);-->
    <insert id="insertMoreByList">
        insert into t_emp values
        <foreach collection="emps" item="emp" separator=",">
            (#{emp.eid}, #{emp.empName}, #{emp.age}, #{emp.sex}, #{emp.email}, null)
        </foreach>
    </insert>

测试类:

 /**
     * 5、foreach
     *      collection  需要循环的数组或集合
     *      item        表示数组或集合中的每一个数据
     *      separator   循环体之间的分隔符
     *      open        foreach标签所循环的所有内容的开始符
     *      close       foreach标签所循环的所有内容的结束符
     */
 @Test
    public void testInsertMoreByList() throws IOException {
        SqlSession sqlSession = getSqlSession();
        DynamicSQLMapper mapper = sqlSession.getMapper(DynamicSQLMapper.class);
        Emp emp1 = new Emp(100, "fckey", 23, "0", "lucy@gmail.com", null);
        Emp emp2 = new Emp(101, "fckey", 23, "0", "lucy@gmail.com", null);
        Emp emp3 = new Emp(102, "lucy", 23, "0", "lucy@gmail.com", null);
        List<Emp> emps = Arrays.asList(emp1, emp2, emp3);
        System.out.println(mapper.insertMoreByList(emps));
    }

在这里插入图片描述

6、include抽取公共SQL片段

在mapper.xml中有些sql片段，在多个sql语句中都有用，每个都写的话显得代码冗余，可以用到代码片段，将公共部分抽取出来
注意点：

最好基于单表定义sql片段
sql片段中不要存在where标签，因为where标签会自动优化sql中的and和or
尽量存放是简单的if标签

        重点1：使用sql标签，定义自定义id，将工共sql片段抽取出来
        <sql id="choose-when-id-name">
            <choose>
                <when test="id!=null">id=#{id}</when>
                <when test="name!=null">name=#{name}</when>
            </choose>
        </sql>
        <update id="updatePwd">
            update public."user"
            <set>
                <if test="name !=null">name=#{name},</if>
                <if test="pwd !=null">pwd=#{pwd},</if>
            </set>
            <where>
                重点2：使用include标签和refid标签来进行sql片段的引入
               <include refid="choose-when-id-name"></include>
            </where>
        </update>

notice: 这里的set标签是用来替代update 中的set的。可以理解为是具有判断功能的set字段。

Fon |

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MyBatis中动态SQL的使用和注意事项说明

MyBatis框架动态SQL技术是根据特定的条件拼接SQL语句的功能，存在的意义是为了解决拼接SQL语句字符串痛点问题。对于动态SQL不是什么新的东西，我们之前写的SQL是直接写死在xml配置文件中的，对于很多的代码判断，我们需要利用SQL的一些函数来进行判断，比如说判断age不能为空就需要在where语句中加上。那我们能不能在sql层面，加入一些逻辑代码,根据不同的条件生成不同的sql语句。是可以的。其中包括，if。
复制链接

扫一扫