【Mybatis】Mybatis之动态SQL

liupjie
798 阅读3分钟

这是我参与8月更文挑战的第13天，活动详情查看：8月更文挑战

上文介绍了Mybatis之缓存、懒加载。也许缓存懒加载在平时不一定能用得上，那么动态SQL在平时的工作中使用可就是太频繁了，就像那啥天天见一样一样的。废话不多说，开始今天的介绍。

准备

public class QueryCondition {

    /**
     * ID
     */
    private Integer id;
    /**
     * 名称
     */
    private String name;
    /**
     * 价格
     */
    private Integer price;
    /**
     * 分类
     */
    private Integer category;
    /**
     * 分类集合
     */
    private List<Integer> categoryList;

    // 省略get/set
}

SQL拼接：if、choose、foreach

if标签，单条件选择

  • if标签只有一个test属性，属性值使用OGNL表达式来表示true/false，当属性值为true时，则将if标签内部的SQL片段拼接到最终的SQL中去，否则不拼接
<select id="findByCondition" parameterType="org.apache.ibatis.z_run.pojo.QueryCondition" resultType="org.apache.ibatis.z_run.pojo.Purchase">
    select * from purchase
    <where>
        <if test="id != null">
            and id = #{id,jdbcType=INTEGER}
        </if>
        <if test="category != null">
            and category = #{category,jdbcType=INTEGER}
        </if>
    </where>
</select>
  • 此SQL语句，当传入条件id有值（不等于null），而category为null时，其输出的SQL如下：
@Test
public void dynamicSQL() {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    QueryCondition condition = new QueryCondition();
    condition.setId(2);
    System.out.println(mapper.findByCondition(condition));

}

DEBUG [main] - ==>  Preparing: select * from purchase WHERE id = ? 
DEBUG [main] - ==> Parameters: 2(Integer)
DEBUG [main] - <==      Total: 1
[Purchase{id=2, name='爆米花', price=18, category=2}]

choose标签，多条件选择

  • choose标签需要搭配when、otherwise标签来使用，使用choose来包裹多个when标签和一个otherwise标签就可以实现多条件选择。示例如下：
<select id="findByCondition" parameterType="org.apache.ibatis.z_run.pojo.QueryCondition" resultType="org.apache.ibatis.z_run.pojo.Purchase">
    select * from purchase
    <where>        
        <choose>
            <when test="id == -1">
                and id = -10
            </when>
            <when test="id == 0">
                and id = -5
            </when>
            <otherwise>
                and id = #{id,jdbcType=INTEGER}
            </otherwise>
        </choose>
    </where>
</select>
  • 当id为-1时，进入第一个when标签，当匹配到一个结果后，就不再向下匹配了，直接跳出choose标签，当所有when标签都不满足时，如果存在otherwise标签，则进入otherwise标签。
@Test
public void dynamicSQL() {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    QueryCondition condition = new QueryCondition();
    // 参数为-1
    System.out.println("===========参数为-1============");
    condition.setId(-1);
    System.out.println(mapper.findByCondition(condition));
    // 参数为0
    System.out.println("===========参数为0============");
    condition.setId(0);
    System.out.println(mapper.findByCondition(condition));
    // 参数为1
    System.out.println("===========参数为1============");
    condition.setId(1);
    System.out.println(mapper.findByCondition(condition));
}

===========参数为-1============
DEBUG [main] - Cache Hit Ratio [org.apache.ibatis.z_run.mapper.PurchaseMapper]: 0.0
DEBUG [main] - ==>  Preparing: select * from purchase WHERE id = -10 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 0
[]
===========参数为0============
DEBUG [main] - Cache Hit Ratio [org.apache.ibatis.z_run.mapper.PurchaseMapper]: 0.0
DEBUG [main] - ==>  Preparing: select * from purchase WHERE id = -5 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 0
[]
===========参数为1============
DEBUG [main] - Cache Hit Ratio [org.apache.ibatis.z_run.mapper.PurchaseMapper]: 0.0
DEBUG [main] - ==>  Preparing: select * from purchase WHERE id = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
DEBUG [main] - <==      Total: 1
[Purchase{id=1, name='可乐', price=3, category=1}]

foreach标签，遍历集合

  • foreach标签用于遍历集合，有collection、item、index、open、close、separator几个属性
    • collection是必填的，表示要遍历的集合名称，其他属性都是非必填；
    • item表示集合中的元素；
    • index表示集合中元素的索引；
    • open表示在拼接foreach标签内部SQL片段之前需要拼接的SQL片段；
    • close表示在拼接完所有foreach标签中的SQL片段之后需要拼接的SQL片段；
    • separator表示每个foreach标签内部的SQL片段的分隔符。
<select id="findByCondition" parameterType="org.apache.ibatis.z_run.pojo.QueryCondition" resultType="org.apache.ibatis.z_run.pojo.Purchase">
    select * from purchase
    <where>
        <if test="categoryList != null and categoryList.size > 0">
            <foreach collection="categoryList" open="and category in (" close=")" item="category" separator=",">
                ${category}
            </foreach>
        </if>
    </where>
</select>
  • 以上SQL表示，当categoryList集合不为null且size大于0时，对其进行遍历操作
  • foreach标签中，collection="categoryList"表示要遍历的集合是categoryList；
  • open="and category in ("表示在拼接foreach标签内部的${category}SQL片段之前，需要先拼接open属性的值"and category in ("；
  • 随后开始遍历集合，每次循环，集合中的元素名称都是item="category"，每次循环都会拼接category的值，并且在其后追加separator属性值,；
  • close=")"表示循环完成之后，在SQL末端拼接一个)；
  • 最终形成的SQL如下，可以看到Mybatis在遍历最后一个元素时，不会在末尾拼接separator属性的值。
@Test
public void dynamicSQL() {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    QueryCondition condition = new QueryCondition();
    List<Integer> categoryList = new ArrayList<>();
    categoryList.add(1);
    categoryList.add(2);
    categoryList.add(3);
    condition.setCategoryList(categoryList);
    System.out.println(mapper.findByCondition(condition));
}

DEBUG [main] - ==>  Preparing: select * from purchase WHERE category in ( 1 , 2 , 3 ) 
DEBUG [main] - ==> Parameters: 
DEBUG [main] - <==      Total: 6
[Purchase{id=1, name='可乐', price=3, category=1}, Purchase{id=2, name='爆米花', price=18, category=2}, Purchase{id=8, name='火腿', price=3, category=1}, Purchase{id=9, name='火腿', price=3, category=1}, Purchase{id=10, name='火腿', price=3, category=1}, Purchase{id=11, name='火腿', price=3, category=1}]

格式化输出：where、set、trim

where标签

  • 主要用于条件查询中，对查询条件进行包装，并自动为查询条件添加where关键字，以及自动去除条件中第一个and或or.
<select id="findByCondition" parameterType="org.apache.ibatis.z_run.pojo.QueryCondition" resultType="org.apache.ibatis.z_run.pojo.Purchase">
    select * from purchase
    <where>
        <if test="id != null">
            and id = #{id,jdbcType=INTEGER}
        </if>
        <if test="category != null">
            and category = #{category,jdbcType=INTEGER}
        </if>
    </where>
</select>

@Test
public void dynamicSQL() {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    QueryCondition condition = new QueryCondition();
    condition.setId(1);
    condition.setCategory(1);
    System.out.println(mapper.findByCondition(condition));
}

DEBUG [main] - ==>  Preparing: select * from purchase WHERE id = ? and category = ? 
DEBUG [main] - ==> Parameters: 1(Integer), 1(Integer)
DEBUG [main] - <==      Total: 1
[Purchase{id=1, name='可乐', price=3, category=1}]

set标签

  • 主要用于update语句中，用于拼接需要更新的数据库属性，为SQL语句自动生成set关键字，并去除语句末尾的,.
<update id="updateXmlPojoByID" parameterType="org.apache.ibatis.z_run.pojo.Purchase">
    update purchase
    <set>
        <if test="name != null">
            `name` = #{name,jdbcType=VARCHAR},
        </if>
        <if test="price != null">
            `price` = #{price,jdbcType=INTEGER},
        </if>
        <if test="category != null">
            category = #{category,jdbcType=INTEGER},
        </if>
    </set>
    where id = #{id,jdbcType=INTEGER}
</update>

@Test
public void dynamicSQL() {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    Purchase purchase = new Purchase();
    purchase.setId(1);
    purchase.setPrice(6);
    mapper.updateXmlPojoByID(purchase);
}


DEBUG [main] - ==>  Preparing: update purchase SET `price` = ? where id = ? 
DEBUG [main] - ==> Parameters: 6(Integer), 1(Integer)
DEBUG [main] - <==    Updates: 1

trim标签

  • 可以用来为SQL增加前缀（prefix），后缀（suffix），也可以用来去除SQL前面的AND/OR，（prefixOverrides），也可以去除SQL末尾的逗号（suffixOverrides）
  • 四个属性都是选填。
<insert id="insertXmlPojo" parameterType="org.apache.ibatis.z_run.pojo.Purchase" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
    insert into purchase
    <trim prefix="(" suffix=")" suffixOverrides="," prefixOverrides="AND | OR">
        <if test="id != null">
            id,
        </if>
        <if test="name != null">
            `name`,
        </if>
        <if test="price != null">
            `price`,
        </if>
        <if test="category != null">
            category,
        </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," prefixOverrides="AND | OR">
        <if test="id != null">
            #{id,jdbcType=INTEGER},
        </if>
        <if test="name != null">
            #{name,jdbcType=VARCHAR},
        </if>
        <if test="price != null">
            #{price,jdbcType=INTEGER},
        </if>
        <if test="category != null">
            #{category,jdbcType=INTEGER},
        </if>
    </trim>
</insert>

@Test
public void dynamicSQL() {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    Purchase purchase = new Purchase();
    purchase.setName("榴莲");
    purchase.setPrice(141);
    purchase.setCategory(3);
    mapper.insertXmlPojo(purchase);
}

DEBUG [main] - ==>  Preparing: insert into purchase ( `name`, `price`, category ) values ( ?, ?, ? ) 
DEBUG [main] - ==> Parameters: 榴莲(String), 141(Integer), 3(Integer)
DEBUG [main] - <==    Updates: 1

定义常量及引用：sql、include

sql标签

  • 用于定义提取的公共SQL片段
  • 包含一个id属性，为此SQL片段取一个唯一的ID，方便调用

include标签

  • 用于使用sql标签定义的公共SQL片段
  • 包含一个refid属性，用于引用指定的id的SQL片段。
<sql id="Base_Column_List">
    id, `name`, price, category
</sql>

<select id="findByCondition" parameterType="org.apache.ibatis.z_run.pojo.QueryCondition" resultType="org.apache.ibatis.z_run.pojo.Purchase">
    select
    <include refid="Base_Column_List" />
    from purchase
    <where>
        <if test="id != null">
            And id = #{id,jdbcType=INTEGER}
        </if>
        <if test="category != null">
            And category = #{category,jdbcType=INTEGER}
        </if>
    </where>
</select>

@Test
public void dynamicSQL() {
    PurchaseMapper mapper = sqlSession.getMapper(PurchaseMapper.class);
    QueryCondition condition = new QueryCondition();
    condition.setId(1);
    condition.setCategory(1);
    System.out.println(mapper.findByCondition(condition));
}

DEBUG [main] - ==>  Preparing: select id, `name`, price, category from purchase WHERE id = ? And category = ? 
DEBUG [main] - ==> Parameters: 1(Integer), 1(Integer)
DEBUG [main] - <==      Total: 1
[Purchase{id=1, name='可乐', price=6, category=1}]

以上便是关于Mybatis动态标签的介绍，赶快用起来吧！

avatar
文章
阅读
粉丝
相关推荐
🐳 Mybatis 中的动态查询
3.5k阅读
 · 
28点赞
Mybatis的CURD及动态SQL
157阅读
 · 
2点赞
Mybatis之动态Sql解析
435阅读
 · 
0点赞
快速测试Mybatis复杂sql，无需启动spring
3.8k阅读
 · 
32点赞
探秘MyBatis-Flex：超越Mybatis-plus的优雅魅力！
20k阅读
 · 
192点赞