动态sql语句基本用法

if 标签

if 标签通常用于 WHERE 语句、UPDATE 语句、INSERT 语句中，通过判断参数值来决定是否使用某个查询条件、判断是否更新某一个字段、判断是否插入某个字段的值。

<if test="name != null and name != ''">
and NAME = #{name}
</if>

foreach 标签

foreach 标签主要用于构建 in 条件，可在 sql 中对集合进行迭代。也常用到批量删除、添加等操作中。

<!-- in查询所有，不分页 -->
<select id="selectIn" resultMap="BaseResultMap">
select name,hobby from student where id in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
#{item}
</foreach>
</select>

属性介绍：

collection：collection 属性的值有三个分别是 list、array、map 三种，分别对应的参数类型为：List、数组、map 集合。
item ：表示在迭代过程中每一个元素的别名
index ：表示在迭代过程中每次迭代到的位置（下标）
open ：前缀
close ：后缀
separator ：分隔符，表示迭代时每个元素之间以什么分隔

choose 标签

有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。MyBatis 提供了 choose 元素，按顺序判断 when 中的条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when
的条件都不满则时，则执行 otherwise 中的 sql。类似于 Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

if 是与(and)的关系，而 choose 是或（or）的关系。

<select id="getStudentListChoose" parameterType="Student" resultMap="BaseResultMap">
SELECT * from STUDENT WHERE 1=1
<where>
<choose>
<when test="Name!=null and student!='' ">
AND name LIKE CONCAT(CONCAT('%', #{student}),'%')
</when>
<when test="hobby!= null and hobby!= '' ">
AND hobby = #{hobby}
</when>
<otherwise>
AND AGE = 15
</otherwise>
</choose>
</where>
</select>

四. 格式化输出

where 标签

当 if 标签较多时，这样的组合可能会导致错误。 如下：

<select id="getStudentListWhere" parameterType="Object" resultMap="BaseResultMap">
SELECT * from STUDENT WHERE
<if test="name!=null and name!='' ">
NAME LIKE CONCAT(CONCAT('%', #{name}),'%')
</if>
<if test="hobby!= null and hobby!= '' ">
AND hobby = #{hobby}
</if>
</select>

当 name 值为 null 时，查询语句会出现 “WHERE AND” 的情况，解决该情况除了将"WHERE"改为“WHERE 1=1”之外，还可以利用 where
标签。这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以 AND 或 OR 开头的，则它会剔除掉。

<select id="getStudentListWhere" parameterType="Object" resultMap="BaseResultMap">
SELECT * from STUDENT
<where>
<if test="name!=null and name!='' ">
NAME LIKE CONCAT(CONCAT('%', #{name}),'%')
</if>
<if test="hobby!= null and hobby!= '' ">
AND hobby = #{hobby}
</if>
</where>
</select>

set 标签

没有使用 if 标签时，如果有一个参数为 null，都会导致错误。当在 update 语句中使用 if 标签时，如果最后的 if 没有执行，则或导致逗号多余错误。使用 set 标签可以将动态的配置 set
关键字，和剔除追加到条件末尾的任何不相关的逗号。

<update id="updateStudent" parameterType="Object">
UPDATE STUDENT
SET NAME = #{name},
MAJOR = #{major},
HOBBY = #{hobby}
WHERE ID = #{id};
</update>

<update id="updateStudent" parameterType="Object">
UPDATE STUDENT SET
<if test="name!=null and name!='' ">
NAME = #{name},
</if>
<if test="hobby!=null and hobby!='' ">
MAJOR = #{major},
</if>
<if test="hobby!=null and hobby!='' ">
HOBBY = #{hobby}
</if>
WHERE ID = #{id};
</update>

使用 set+if 标签修改后，如果某项为 null 则不进行更新，而是保持数据库原值。

<update id="updateStudent" parameterType="Object">
UPDATE STUDENT
<set>
<if test="name!=null and name!='' ">
NAME = #{name},
</if>
<if test="hobby!=null and hobby!='' ">
MAJOR = #{major},
</if>
<if test="hobby!=null and hobby!='' ">
HOBBY = #{hobby}
</if>
</set>
WHERE ID = #{id};
</update>

trim 标签

格式化输出，也可以通过 trim 标签设定或忽略前后缀来实现，_详见我的另一博客_

五. 配置关联关系

5.1 collection 标签

5.2 association 标签

 

1.if语句

如果empno不为空，则在WHERE参数后加上AND empno = #{empno},这里有1=1所以即使empno为null，WHERE后面也不会报错。

映射文件

<select id="getEmpById2" resultType="emp"> 
        SELECT * FROM emp WHERE 1=1
        <if test="empno != null">
            AND empno = #{empno}
        </if>   
    </select>

EmpMapper接口

public Emp getEmpById2(@Param("empno")Integer empno) throws IOException;

有时候我们并不想应用所有的条件，而只是想从多个选项中选择一个。而使用if标签时，只要test中的表达式为 true，就会执行 if 标签中的条件。MyBatis 提供了 choose 元素。if标签是与(and)的关系，而 choose 是或(or)的关系。

2.where语句和Choose(when,otherwise)

1.Where后面empno和ename为null，那where就不会出现在sql语句中。
2. choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql。类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

映射文件

<select id="getEmpById3" resultType="emp" parameterType="emp">
        SELECT * FROM EMP 
        <where>
            <choose>
                <when test="empno != null">
                    AND empno like #{empno}
                </when>
                <when test="ename != null">
                    AND ename like #{ename}
                </when>
                <otherwise>
                    AND  job = "zz"
                </otherwise>
            </choose>
        </where>
    </select>

3.set语句

set主要也是用来解决更新问题的。
映射文件

<update id="updateEmprById2" parameterType="emp">
        UPDATE emp
        <set>
            <if test="ename!=null"> ename=#{ename},</if>
            <if test="job!=null"> job=#{job},</if>
        </set>
        <where>
            <if test="empno!=null">
                empno=#{empno};
            </if>
        </where>
    </update>

4.trim

trim标记是一个格式化的标记，可以完成set或者是where标记的功能。
相关属性：
Prefix：前缀。
prefixOverrides：去掉第一个指定内容。
suffix：后缀。
suffixoverride：去掉最后一个指定内容。
映射文件

<!-- 代替where -->
    <select id="getEmpById4" resultType="emp" parameterType="emp">
        SELECT * FROM emp
        <!-- <where> <if test="username!=null"> and name = #{username} </if> </where> -->
        <trim prefix="where" prefixOverrides="AND |OR ">
            <if test="empno != null">
                and empno = #{empno}
            </if>
            <if test="ename!=null">
                AND ename = #{ename}
            </if>
        </trim>
    </select>

映射文件

<!-- 代替set -->
    <update id="updateEmprById3" parameterType="emp">
        update emp
        <trim prefix="set" suffixOverrides=",">
            <if test="ename!=null">
                ename = #{ename},
            </if>
            <if test="job != null">
                job = #{job}
            </if>
        </trim>
        <trim prefix="where" prefixOverrides="AND |OR ">
            <if test="empno != null">
                and empno = #{empno}
            </if>
        </trim>
    </update>

5.foreach语句

foreach用来遍历，遍历的对象可以是数组，也可以是集合。
相关属性：
Collection：collection属性的值有三个分别是list、array、map三种。
Open：前缀。
Close：后缀。
Separator：分隔符，表示迭代时每个元素之间以什么分隔。
Item：表示在迭代过程中每一个元素的别名。
Index：用一个变量名表示当前循环的索引位置。
映射文件

    <insert id="addEmp6">
        insert into emp(ename,job)values
        <foreach collection="emps" item="emp" separator=",">
            (#{emp.ename},#{emp.job})
        </foreach>
    </insert>

6.SQL块

映射文件

<!-- 定义重复使用的SQL内容 -->
    <sql id="baseSql">
        empno,ename,job
    </sql>
    
    <!-- 使用include引入sql块 -->
    <select id="selEmp1" resultType="emp">
        select 
        <include refid="baseSql"/>
         from emp 
    </select>