ORM框架
Mybatis

Mybatis-动态sql

为什么需要动态sql？

我们通常会把多种条件查询放在一个接口中，后端接口不知道前端回会传什么参数，所以要将所有的参数类型考虑在内，执行之前根据传入参数类型动态的生成查询语句。

Mybatis的动态sql是基于ognl表达式的。

动态标签

按照官网的分类，Mybatis的动态标签主要有四类：if，choose（when,otherwise）,trim(where,set),foreach

if–用在需要判断的时候，条件写在test中：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="selectBlogListIf" parameterType="blog" resultMap="BaseResultMap" >
    select bid, name, author_id authorId from blog
    <where>
        <if test="bid != null">
            AND bid = #{bid}
        </if>
        <if test="name != null and name != ''">
            AND name LIKE '%${name}%'
        </if>
        <if test="authorId != null">
            AND author_id = #{authorId}
        </if>
    </where>
</select>

choose(when,otherwise) —需要选择一个条件的时候

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<select id="selectBlogListChoose" parameterType="blog" resultMap="BaseResultMap" >
    select bid, name, author_id authorId from blog
    <where>
        <choose>
            <when test="bid !=null">
                bid = #{bid, jdbcType=INTEGER}
            </when>
            <when test="name != null and name != ''">
                AND name LIKE CONCAT(CONCAT('%', #{name, jdbcType=VARCHAR}),'%')
            </when>
            <when test="authorId != null ">
                AND author_id = #{authorId, jdbcType=INTEGER}
            </when>
            <otherwise>
            </otherwise>
        </choose>
    </where>
</select>

set—专注于更新

1
2
3
4
5
6
7
8
9
10
11
12
<update id="updateByPrimaryKey" parameterType="blog">
    update blog
    <set>
        <if test="name != null">
            name = #{name,jdbcType=VARCHAR},
        </if>
        <if test="authorId != null">
            author_id = #{authorId,jdbcType=CHAR},
        </if>
    </set>
    where bid = #{bid,jdbcType=INTEGER}
</update>

trim标签用于将动态参数前后的sql语句拎出来，prefix属性是动态参数前缀内容，suffix属性是动态参数后缀内容，suffixOverrides属性指后缀内容前需要去掉的部分，如下代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<insert id="insertBlog" parameterType="blog">
insert into blog
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="bid != null">
            bid,
        </if>
        <if test="name != null">
            name,
        </if>
        <if test="authorId != null">
            author_id, <!--suffixOverrides 去掉的就是这个逗号 -->
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides=",">
        <if test="bid != null">
            #{bid,jdbcType=INTEGER},
        </if>
        <if test="name != null">
            #{name,jdbcType=VARCHAR},
            <!-- #{name,jdbcType=VARCHAR,typeHandler=com.gupaoedu.type.MyTypeHandler}, -->
        </if>
        <if test="authorId != null">
            #{authorId,jdbcType=INTEGER},
        </if>
    </trim>
</insert>

批量插入

1
2
3
4
5
6
7
<insert id="insertBlogList" parameterType="java.util.List">
    insert into blog (bid, name, author_id)
    values
    <foreach collection="list" item="blog" index="index"  separator=",">
        ( #{blog.bid},#{blog.name},#{blog.authorId} )
    </foreach>
</insert>

批量删除

1
2
3
4
5
6
<delete id="deleteByList" parameterType="java.util.List">
    delete from blog where bid in
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.bid,jdbcType=INTEGER}
    </foreach>
</delete>

批量更新

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<update id="updateBlogList">
    update blog set
    name =
    <foreach collection="list" item="blogs" index="index" separator=" " open="case bid" close="end">
        when #{blogs.bid} then #{blogs.name}
    </foreach>
    ,author_id =
    <foreach collection="list" item="blogs" index="index" separator=" " open="case bid" close="end">
        when #{blogs.bid} then #{blogs.authorId}
    </foreach>
    where bid in
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.bid,jdbcType=INTEGER}
    </foreach>
</update>