MyBatis学习笔记——动态SQL(if、trim、choose、set、foreach、sql)

最新推荐文章于 2022-11-28 21:36:55 发布

从程序猿到程序员

最新推荐文章于 2022-11-28 21:36:55 发布

阅读量1.4k

点赞数 3

分类专栏： Mybatis

本文链接：https://blog.csdn.net/u012525096/article/details/81836122

版权

Mybatis 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

动态拼接SQL

if判断

（1）新建Mapper接口

public interface EmployeeMapperDynamicSQL {

    public List<Employee> getEmpsByConditionIf(Employee employee);
}

（2）新建Mapper XML

    <!-- 
        查询员工,要求:携带了哪个字段,查询条件就带上这个字段
    -->
    <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
    <select id="getEmpsByConditionIf" resultType="com.shen.mybaties.bean.Employee">
        select * from tbl_employee 
        where 
        <!-- test:判断表达式(OGNL表达式) 
            OGNL参考PPT或者官方文档
            c:if(EL表达式) test 
            从参数中取值进行判断
            遇见特殊符号应该去写转义字符
        -->
        <if test="id!=null">
            id=#{id}
        </if>
        <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
            and last_name like #{lastName}
        </if>
        <if test="email!=null and email.trim()!=&quot;&quot;">
            and email=#{email}
        </if>
        <!-- OGNL会进行字符串与数字的转换判断 -->
        <if test="gender==0 or gender==1">
            and gender=#{gender}
        </if>
    </select>

注意：
（1）这里的test，写的的是判断表达式(OGNL表达式) ，OGNL参考PPT或者官方文档。
（2）test里面使用的值是从参数中取值进行判断，且遇见特殊符号应该写转义字符。

where标签

在if使用过程中，发现拼接的语句中带有and，如果第一个拼接语句不符合，那么拼接的语句将会是where and ...，很明显这样的SQL是错误的语法。

解决办法一

很多公司或者团队，喜欢用where 1=1，后面每个if条件都带有and。

解决办法二

可以使用<where>标签，MyBatis会自动将多出来的and或者or去掉。

    <!-- 
        查询员工,要求:携带了哪个字段,查询条件就带上这个字段
    -->
    <!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
    <select id="getEmpsByConditionIf" resultType="com.shen.mybaties.bean.Employee">
        select * from tbl_employee 
        <where> 
            <!-- test:判断表达式(OGNL表达式) 
                OGNL参考PPT或者官方文档
            c:if(EL表达式) test 
                            从参数中取值进行判断
                            遇见特殊符号应该去写转义字符
            -->
            <if test="id!=null">
                id=#{id}
            </if>
            <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
                and last_name like #{lastName}
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                and email=#{email}
            </if>
            <!-- OGNL会进行字符串与数字的转换判断 -->
            <if test="gender==0 or gender==1">
                and gender=#{gender}
            </if>
        </where>
    </select>

注意：只会去掉多出来的第一个and，如果写法是id=#{id} and这样的形式，将会造成错误。如果使用<where>标签，那么最好统一使用前缀的方式去写拼接语句。
<where>标签封装查询条件。

trim标签

where标签不能解决多余的后缀的拼接词，那么我们可以使用<trim>标签解决，顾名思义，就是处理字符串的一个标签，有如下几个属性。

prefix=”“：前缀，rim标签体中是整个字符串拼串后的结果，prefix给拼串后的整个字符换加一个前缀。
prefixOverrides=”“：前缀覆盖:去掉整个字符串前面多余的字符。
suffix=”“：后缀，suffix给拼串后的整个字符换加一个后缀。
suffixOverrides=”“，前缀覆盖:去掉整个字符串后面多余的字符。

    <!-- public List<Employee> getEmpsByConditionTrim(Employee employee);  -->
    <select id="getEmpsByConditionTrim" resultType="com.shen.mybaties.bean.Employee">
        select * from tbl_employee 
        <!-- 后面多出的and或者or where不能解决 
            prefix="":前缀,trim标签体中是整个字符串拼串后的结果
                prefix给拼串后的整个字符换加一个前缀。
            prefixOverrides="",
                                                前缀覆盖:去掉整个字符串前面多余的字符
            suffix="":后缀
                suffix给拼串后的整个字符换加一个后缀。
            suffixOverrides="",
                                                前缀覆盖:去掉整个字符串后面多余的字符
        -->
        <!-- 自定义的截取规则 -->
        <trim prefix="where" suffixOverrides="and">
            <if test="id!=null">
                id=#{id} and
            </if>
            <if test="lastName!=null &amp;&amp; lastName!=&quot;&quot;">
                last_name like #{lastName} and
            </if>
            <if test="email!=null and email.trim()!=&quot;&quot;">
                email=#{email} and
            </if>
            <!-- OGNL会进行字符串与数字的转换判断 -->
            <if test="gender==0 or gender==1">
                gender=#{gender}
            </if>
        </trim>
    </select>

这样，通过trim，为后面的整个字符串，使用prefix添加了前缀where，并使用suffixOverrides去掉了多余的后缀，可以达到截取的效果。

choose标签

    <!-- choose:带了id就用id查,带了lastName就用lastName查,只会进入一个分支,而不是拼接 -->
    <!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->
    <select id="getEmpsByConditionChoose" resultType="com.shen.mybaties.bean.Employee">
        select * from tbl_employee 
        <where>
            <choose>
                <when test="id!=null">
                    id=#{id}
                </when>
                <when test="lastName!=null">
                    last_name like #{lastName}
                </when>
                <when test="email!=null">
                    email = #{email}
                </when>
                <otherwise>
                    gender = 0;
                </otherwise>
            </choose>
        </where>
    </select>

使用choose标签可以进行分支的选择，且只会进入一个分支，类似于java的switch case。

set标签

更新时，和查询一样，如果使用了if标签进行SQL语句的拼接，将会造成连接词或连接符号的剩余。
错误版本：

    <!-- public void updateEmp(Employee employee); -->
    <update id="updateEmp">
        update tbl_employee 
        set 
        <if test="lastName!=null">
            last_name=#{lastName},
        </if>
        <if test="email!=null">
            email=#{email},
        </if>
        <if test="gender!=null">
            gender=#{gender}
        </if>
        where id = #{id}
    </update>

这样，如果判断条件只有一个，那么会进入前面的拼接，造成后面多余一个,，造成SQL语法错误。

方法一，使用set标签

    <update id="updateEmp">
        update tbl_employee 
        <set>
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </set>
        where id = #{id}
    </update>

这样，MyBatis会自动帮你去除后面多余的,。
注意，不要将where放在set标签内，这样会造成set标签失效，猜测源码内部也只是通过字符串截取，去掉最后一个逗号。

方法二，使用trim标签

    <update id="updateEmp">
        update tbl_employee 
        <trim prefix="set" suffixOverrides=",">
            <if test="lastName!=null">
                last_name=#{lastName},
            </if>
            <if test="email!=null">
                email=#{email},
            </if>
            <if test="gender!=null">
                gender=#{gender}
            </if>
        </trim>
        where id = #{id}
    </update>

使用了前缀和后缀覆盖，很容易理解。

foreach标签

用于select

Mapper接口方法：

public List<Employee> getEmpsByConditionForeach(@Param("ids") List<Integer> ids);

此处使用了@param注解进行了修饰，可以指定封装参数时（都会被封装成map）的Key名，值就是入参的值；
若没有进行修饰，且只有一个参数，则List类型默认为list，Array类型默认为array；多个参数时，默认key为参数名。
这一点是参数处理学习过的（今天学习，视频中没提到，程序报参数未找到的错误，思考了一会才想到，不应该。）
Mapper XML：

    <!-- public List<Employee> getEmpsByConditionForeach(List<Integer> ids); -->
    <select id="getEmpsByConditionForeach" resultType="com.shen.mybaties.bean.Employee">
        select * from tbl_employee 
        <!--  
            collection:指定要遍历的集合
                list类型的参数会特殊处理,封装在map中,map的key就叫list,参数使用@Param修饰,可以制定修改
            item:将遍历出的元素赋值给指定的变量
            separator:每个元素之间的分隔符
            open:遍历出所有结果拼接一个开始的字符
            close:遍历出所有结果拼接一个结束的字符
            index:索引,遍历list的时候index是索引,item是当前值,
                                                                遍历map的时候index表示的就是map的key,item就是map的值.

            #{变量名}就能取出变量的值也就是当前遍历出的元素
        -->
        <foreach collection="ids" item="item_id" separator="," open="where id in(" close=")">
            #{item_id}
        </foreach>
    </select>

这里foreach的几个重要属性需要知道：

collection：指定要遍历的集合，list类型的参数会特殊处理,封装在map中,map的key就叫list,参数使用@Param修饰,可以指定键Key
item：将遍历出的元素赋值给指定的变量
separator：每个元素之间的分隔符
open：遍历出所有结果拼接一个开始的字符
close：遍历出所有结果拼接一个结束的字符
index：索引，遍历list的时候index是索引,item是当前值，遍历map的时候index表示的就是map的key，item就是map的值

用于insert(批量保存)

Mapper接口方法：

public void addEmps(@Param("emps")List<Employee> emps);

第一种写法

Mapper XML：

    <!-- public void addEmps(@Param("emps")List<Employee> emps); -->
    <insert id="addEmps">
        insert into tbl_employee(last_name,email,gender,d_id)
        values
        <foreach collection="emps" item="emp" separator=",">
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
        </foreach>
    </insert>

这里同样使用了@Param进行修饰，方便取值。
注意员工的部门id，属于级联属性，要使用emp.dept.id进行取值，这里的属性名都是bean的属性名。
好处：简便，易懂，mysql原生语法。

第二种写法

使用多个sql语句进行插入。
Mapper XML：

    <!-- 第二种写法,完整的sql语句,需要数据库连接属性allowMultiQueries=true -->
    <insert id="addEmps">
        <foreach collection="emps" item="emp" separator=";">
        insert into tbl_employee(last_name,email,gender,d_id)
        values (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
        </foreach>
    </insert>

这样的方式需要数据库连接属性allowMultiQueries的开启，即支持多条语句的执行，默认是关闭状态。
在dbconfig.properties中增加该条配置

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&allowMultiQueries=true
jdbc.username=root
jdbc.password=root

好处：这样的方式可以用于其他的批量操作，批量修改、批量删除等。

Oracle批量插入

Oracle不支持valuse(),(),()语法，有如下几种方式：
1. 使用begin insert into ...;insert into ...;insert into ...; end;这样的方式进行批量操作。

<insert id="addEmps" databaseId="oracle">
    <foreach collection="emps" item="emp" open="begin" close="end;">
        insert into employees(employee_id,last_name,email)
            values(employee_seq.nextval,#{emp.lastName},#{emp.email})
    </foreach>
</insert>

注意：end带有;。
2. 中间表方法
Orcale的sql语句：

insert into employees(employee_id, last_name, email)
    select employee_seq.nextval,lastName,email from(
        select 'test_a_01' lastName, 'test_a_email01' email from dual
        union
        select 'test_a_02' lastName, 'test_a_email02' email from dual
        union
        select 'test_a_03' lastName, 'test_a_email03' email from dual
    )

这样，就可以成功插入虚表中的数据。
Mapper XML写法如下：

<insert id="addEmps" databaseId="oracle">
    insert into employees(employee_id,last_name,email)
        select employee_seq.nextval,lastName,email from(
            <foreach collection="emps" item="emp" separator="union">
                select #{emp.lastName} lastName,#{emp.email} email from dual
            </foreach>
        )
</insert>

两个内置参数

_databaseId和_parameter

    <!-- public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
    <select id="getEmpsTestInnerParameter" resultType="com.shen.mybaties.bean.Employee">
        <if test="_databaseId==null">
            select * from tbl_employee
            <if test="_parameter!=null">
                where last_name = #{_parameter.lastName}
            </if>
        </if>
        <if test="_databaseId=='mysql'">
            select * from tbl_employee
        </if>
        <if test="_databaseId=='orcale'">
            select * from employees
        </if>
    </select>

这里的_databaseId如果为空，且配置了databaseIdProvider，一定要检查一下全局配置文件的name的大小写是否正确，别名和此处是否正确。

bind绑定

bind：可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值。
如一个场景：使用模糊查询，Java层只传入了值，但没有传入%之类的通配符，而使用#{XXX}是不能在两边直接加%的，使用%${XXX}%又不安全，那么，我们可以使用<bind>标签来解决。

    <!-- public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
    <select id="getEmpsTestInnerParameter" resultType="com.shen.mybaties.bean.Employee">
        <!-- bind,可以将OGNL表达式的值绑定到一个变量中,方便后来引用这个变量的值. -->
        <bind name="_lastName" value="'%'+lastName+'%'"/>
        <if test="_databaseId=='mysql'">
            select * from tbl_employee
        </if>
        <if test="_databaseId=='orcale'">
            select * from employees
        </if>
        <if test="_parameter!=null">
            where last_name like #{_lastName}
        </if>
    </select>

MyBatis会将传入的参数和你想要的字符串拼接一下，引用bind的name为即可。

sql标签

抽取可重用的sql片断，方便后面引用。

    <!-- 抽取可重用的sql片断，方便后面引用。 
        1.将要查询的列名,或者插入的列名抽取出来方便引用
        2.include来引用已经抽取的sql片断
        3.inculde还可以自定义一些property,sql标签内部可以使用${prop},进行取值
    -->
    <sql id="insertColumn">
        <if test="_databaseId=='mysql'">
            last_name,email,gender,d_id
        </if>
    </sql>

第三条的规则，如下：

    <include refid="insertColumn">
        <property name="testProperty" value="abc"/>
    </include>

    <sql id="insertColumn">
        <if test="_databaseId=='mysql'">
            last_name,email,gender,d_id,${testProperty}
        </if>
    </sql>

那么该sql片断为last_name,email,gender,d_id,abc。

从程序猿到程序员

关注

3
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
MyBatis学习笔记——动态SQL(if、trim、choose、set、foreach、sql)

动态拼接SQLif判断（1）新建Mapper接口public interface EmployeeMapperDynamicSQL { public List&amp;amp;amp;amp;amp;amp;lt;Employee&amp;amp;amp;amp;amp;amp;gt; getEmpsByConditionIf(Employee employee);}（2）新建Mapper XML```xml &amp;amp;amp;amp;
复制链接

扫一扫

专栏目录