记录mybatis动态sql

m0_59259076

已于 2022-03-02 16:34:55 修改

阅读量878

点赞数

分类专栏：笔记文章标签： java mybatis

于 2022-01-30 13:24:14 首次发布

本文链接：https://blog.csdn.net/m0_59259076/article/details/122751860

版权

笔记专栏收录该内容

77 篇文章 0 订阅

订阅专栏

（注：本文参考自刘增辉老师的《MyBatis从入门到精通》）

1.1 if 用法

1.1.1 在 where 条件中使用 if

1.1.2 在 update 更新列中使用 if

1.1.3 在 insert 动态插入列中使用 if

2.1 choose 用法

3.1 where set trim 用法

4.1.1 foreach 实现 in 集合

4.1.2 foreach 实现批量插入

4.1.3 foreach 实现动态 update (以参数类型Map为例)

5.1 bind 用法

1.1 if 用法

if 标签通常用于where语句中，通过判断参数值来决定是否使用某个查询条件，它也常用于update语句中判断是否更新某个字段，还可以在INSERT语句中用来判断是否插入某个字段的值。
字符串的判断几乎都包含null和空的判断，这两个条件不是必须写在一起的，可以根据实际业务决定是否需要进行空值判断。

1.1.1 在 where 条件中使用 if

假如有这么一个需求：客户输入名字，则根据名字进行查询，否则，做全表查询。这时候我们就可以在 where 条件中使用 if。

例如，在 AnimalMapper.xml中添加如下sql:

    <select id="getAnimalByName" resultType="com.zhang.entity.Animal">
        select * from animal
        <where>
            <if test="name != null and name != ''">
                and `name` = #{name}
            </if>
        </where>
    </select>

1.1.2 在 update 更新列中使用 if

现在要实现这样一个需求：只更新有变化的字段。需要注意，更新的时候不能将原来有值（没有发生变化的字段）更新为空或null。通过 if 标签可以实现这种动态列更新。

例如，在 AnimalMapper.xml中添加如下sql:

    <update id="update">
        update animal
        <set>
            <if test="age != null and age != ''">
                `age` = #{age},
            </if>
            <if test="name != null and name != ''">
                `name` = #{name},
            </if>
            <if test="hobby != null and hobby != ''">
                `hobby` = #{hobby},
            </if>
        </set>
        where `id` = #{id}
    </update>

1.1.3 在 insert 动态插入列中使用 if

在数据库表中插入数据的时候，如果某列的参数值不为空，就使用传入的值，如果传入参数为空，就使用数据库中的默认值（通常是空），而不使用传入的空值使用 if 就可以这种动态插入列的功能。

例如，在 AnimalMapper.xml中添加如下sql:

 <insert id="addOne" parameterType="com.zhang.entity.Animal">
        insert into `animal`
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">
                `id`,
            </if>
            <if test="name != null and name != ''">
                `name`,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">
                #{id,jdbcType=VARCHAR},
            </if>
            <if test="name != null and name != ''">
                #{name,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>

2.1 choose 用法

if 标签提供了基本的条件判断，但是它无法实现if...else-if...else...的逻辑，要想实现这样的逻辑，就需要用到choose when otherwise标签。choose元素中包含when和otherwise两个标签，一个choose中至少有一个when，有0个或者1个otherwise。

在已有的`animal`表中，除了主键id外，我们认为name（动物名）也是唯一的，所有的动物名都不可以重复。现在进行如下查询：当参数id有值的时候优先使用 id 查询，当 id 没有值时就去判断动物名是否有值，如果有值就使用动物名查询，如果动物名也没有值，就使SQL查询无结果。

例如，在 AnimalMapper.xml中添加如下sql:

    <select id="selectByIdOrName" resultType="com.zhang.entity.Animal">
        select * from `animal`
        where 1 = 1
        <choose>
            <when test="animal.id != null and animal.id != ''">
                and `id` = #{animal.id}
            </when>
            <when test="animal.name != null and animal.name != ''">
                and `name` = #{animal.name}
            </when>
            <otherwise>
                and 1 = 2
            </otherwise>
        </choose>
    </select>

在AnimalMapper.java中添加:

    /**
     * 当参数id有值的时候优先使用id查询，
     * 当id没有值时就去判断动物名是否有值，
     * 如果有值就使用动物名查询，如果动物名也没有值，
     * 就使SQL查询无结果 。
     * @param animal
     * @return
     */
    Animal selectByIdOrName(@Param("animal") Animal animal);

(注:使用choose when otherwise的时候逻辑要严密，避免由于某些值出现问题导致SQL 出错。)

3.1 where set trim 用法

这3个标签解决了类似的问题，并且 where set 都属于 trim 的一种具体用法。下面分别来看这3个标签。

3.1.1 where 用法

where标签的作用：如果该标签包含的元素中有返回值，就插入一个where，否则，什么都不做；如果where后面字符串是以AND和OR开头的，就将它们剔除。

例如，在 AnimalMapper.xml中添加如下sql:

    <select id="getAnimalByName" resultType="com.zhang.entity.Animal">
        select * from animal
        <where>
            <if test="name != null and name != ''">
                and `name` = #{name}
            </if>
        </where>
    </select>

当 if 条件都不满足的时候，where元素中没有内容，所以在SQL中不会出现 where，如果 if 条件满足，where 元素的内容就是以 and 开头的条件，where 会自动去掉开头的and，这也能保证where条件正确。这种情况下生成的SQL更干净、更贴切，where 还可以代替 where 1 = 1 这样的条件。

3.1.2 set 用法

set 标签的作用：如果该标签包含的元素中有返回值，就插入一个 set ；如果set后面的字符串是以逗号结尾的，就将这个逗号剔除。set 比较适合 update 的情形。

例如，在 AnimalMapper.xml中添加如下sql:

    <update id="update">
        update `animal`
        <set>
            <if test="age != null and age != ''">
                `age` = #{age},
            </if>
            <if test="name != null and name != ''">
                `name` = #{name},
            </if>
            <if test="hobby != null and hobby != ''">
                `hobby` = #{hobby},
            </if>
        </set>
        where `id` = #{id}
    </update>

set 标签的用法中SQL后面的逗号没有问题了，但是如果set元素中没有内容，照样会出现SQL错误，所以为了避免错误产生，类似 id = #{id｝这样必然存在的赋值仍然有保留的必要。从这点来看，set标签并没有解决全部的问题，使用时仍然需要注意。

3.1.3 trim 用法

where、set标签的功能都可以用trim标签来实现，并且在底层就是通过TrimSqlNode实现的。 where标签对应 trim 的实现如下：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
    <!-- do something  -->
</trim>

需要注意的是，这里的AND,OR后面的空格不能省略，为了避免匹配到andes、orders等单词。实际的prefixeOverrides包含“AND”、“OR”、“AND\n”、“OR\n”、“AND\r”、“OR\r”、“AND\t”、“OR\t”，不仅仅是上面提到的两个带空格的前缀。set 标签对应 trim 的实现如下：

 <trim prefix="SET" prefixOverrides=",">
     <!-- do something  -->
 </trim>

trim标签有如下属性:

prefix：当 trim 元素内包含内容时，会给内容增加 prefix 指定的前缀。
prefixOverrides：当 trim元素内包含内容时，会把内容中匹配的前缀字符串去掉。
suffix：当 trim 元素内包含内容时，会给内容增加 suffix 指定的后缀。
suffixOverrides：当 trim 元素内包含内容时，会把内容中匹配的后缀字符串去掉。

例如，我们可以在insert中使用trim，在 AnimalMapper.xml中添加如下sql:

    <insert id="addOne" parameterType="com.zhang.entity.Animal">
        insert into `animal`
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">
                `id`,
            </if>
            <if test="name != null and name != ''">
                `name`,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null and id != ''">
                #{id,jdbcType=VARCHAR},
            </if>
            <if test="name != null and name != ''">
                #{name,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>

4.1 foreach 用法

SQL语句中有时会使用 IN 关键字，例如 id in (1,2,3,...)。可以使用${ids}方式直接获取值，但这种写法不能防止SQL注入，避免SQL注入就需要用 #{ }的方式，这时就要使用foreach 标签来满足需求了。

foreach可以对数组、Map或实现了Iterable接口（如List、Set）的对象进行遍历。数组在处理时会转换为List对象，因此foreach遍历的对象可以分为两大类Iterable类型和Map类型。

4.1.1 foreach 实现 in 集合

foreach 实现 in 集合（或数组）是最简单和常用的种情况，下面介绍如何根据传入的动物 id 集合查询出所有符合条件的动物。

在 AnimalMapper.xml中添加如下sql:

    <select id="listAnimals" resultType="com.zhang.entity.Animal">
        SELECT * FROM animal
        WHERE `id` in
        <foreach collection="ids" item="id" index="index"
                 open="(" close=")" separator=",">
            #{id}
        </foreach>
    </select>

在AnimalMapper.java中添加:

    /**
     * 根据id集合查询
     * @param ids
     * @return
     */
    List<Animal> listAnimals(@Param("ids") List<String> ids);

foreach包含以下属性：

collection：必填，值为要选代循环的属性名。这个属性值的情况有很多。
item：变量名，值为从迭代对象中取出的每一个值。
index：索引的属性名，在集合数组情况下值为当前索引值当选代循环的对象是Map类型时，这个值为Map的key（键值）。
open：整个循环内容开头的字符串。
close：整个循环内容结尾的字符串。
separator：每次循环的分隔符。

当参数类型为集合的时候，默认会转换为 Map 类型，井添加 key 为 collection的值（MyBatis3.3版本中增加），如果参数类型是 List 集合，那么就继续添加 key 为 list的值（MyBatis3.2.8及低版本中只有这key），这样，当collection=" list ”时，就能得到这个集合，并对它进行循环操作。

当参数类型为集合的时候，也会转换成 Map 类型，默认的 key 为 array 。当采用如下方法使用数组参数的时候，就需要把 foreach 标签中的 collection 属性值设置为 array。例如：

    /**
     * 根据id集合查询
     * @param ids
     * @return
     */
    List<Animal> listAnimals(@Param("ids") String[] ids);

4.1.2 foreach 实现批量插入

如果数据库支持批量插入，我们可以通过foreach来实现。

如，在 AnimalMapper.xml中添加如下sql:

    <insert id="addBatch" keyColumn="id" keyProperty="id" useGeneratedKeys="true">
        insert into `animal` (
        `name`,`age`,`hobby`,`create_time`,`update_time`
        )
        values
        <foreach collection="listAnimals" item="animal" separator="," index="index">
            (
            #{animal.name},#{animal.age},#{animal.hobby},#{animal.createTime},#{animal.updatetime}
            )
        </foreach>
    </insert>

（注：进行更新或者添加操作的时候，最好加上jdbcType类型，以免产生不必要的错误。）

在AnimalMapper.java中添加：

    /**
     * 批量插入数据
     * @param animals
     */
    void addBatch(@Param("listAnimals") List<Animal> animals);

4.1.3 foreach 实现动态 update (以参数类型Map为例)

当参数类型为 Map 类型时，foreach 标签的属性值对应的不是索引值，而是 Map 的 key，我们可以利用这个 key 实现动态update。其中，map的key对应数据库的字段。

例如，在 AnimalMapper.xml中添加如下sql:

   <update id="updateByMap">
        update `animal`
        set
        <foreach collection="_parameter" item="val" index="key" separator=",">
            ${key} = #{val}
        </foreach>
        <!-- 这里的id对应map中的key,map的key对应数据库的字段 -->
        where `id` = #{id}
   </update>

在AnimalMapper.java中添加：

    /**
     * 通过Map更新列
     * 这里没有通过@Param来指定参数名
     * MyBatis内部默认使用 _parameter作为该参数的key
     * 我们可以直接使用~
     * @param map
     * @return
     */
    int updateByMap(Map<String,Object> map);

5.1 bind 用法

bind 标签可以使用 OGNL 表达式创建一个变量并将其绑定到上下文中。例如，给变量参数绑‘%’，做模糊查询。

比如，在 AnimalMapper.xml中添加如下sql:

    <select id="getByName" resultType="com.zhang.entity.Animal">
        <!--animalName为传过来的参数-->
        <!--根据动物名字进行模糊查询-->
        <bind name="animalNameLike" value="'%'+ animalName +'%'"/>
        select * from animal
        <where>
            <if test="animalName != null and animalName != ''">
                and `name` like #{animalNameLike}
            </if>
        </where>
    </select>

在AnimalMapper.java中添加：

    /**
     * 根据动物名字做模糊like查询
     * @param name
     * @return
     */
    List<Animal> getByName(@Param("animalName") String name);

bind标签的两个选项都是必选项，name为绑定上下文的变量名，value为OGNL表达式。我们在创建一个bind标签后，就可以在下面直接使用了。通过使用bind，我们可以避免一些问题，比如，避免因更换数据库而修改SQL，防止SQL注入等等。

m0_59259076

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
记录mybatis动态sql

MyBatis的强大特性之一便是它的动态 SQL 。使用过 JDBC 或其他类似框架的人都会知道，根据不同条件拼接 SQL 语句时不仅不能忘了必要的空格，还要注意省略掉列名列表最后的逗号，处理方式麻烦且凌乱。 MyBatis 的动态 SQL 则能让我们摆脱这种痛苦。 ---刘增辉
复制链接

扫一扫