程序员社区

Mybatis if, set, where 动态 sql 和 sql 片段的使用

动态SQL

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach

数据表

CREATE TABLE `blog` ( 
    `id` VARCHAR(50) NOT NULL COMMENT '博客id', 
    `title` VARCHAR(100) NOT NULL COMMENT '博客标题', 
    `author` VARCHAR(30) NOT NULL COMMENT '博客作者', 
    `create_time` DATETIME NOT NULL COMMENT '创建时间', 
    `views` INT(30) NOT NULL COMMENT '浏览量'
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

实体类

//java项目www.fhadmin.org
public class Blog {
    private String id;
    private String title;
    private String auther;
    private java.util.Date createTime;
    private int views;
}

开启驼峰命名自动映射

<settings>
    <!--开启驼峰命名自动映射-->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

if

<select id="queryBlogIf" parameterType="map" resultType="Blog">
    select * from blog where 1=1
    <if test="title != null">
        and title = #{title}
    </if>
</select>

trim (where, set)

trim

  • prefix:在包裹的代码块前面添加一个 xxx
  • prefixOverrides:属性会忽略通过管道符分隔的文本序列(注意此例中的空格是必要的
  • suffixOverrides: 忽略最后一个 xxx
## 等价于 where 标签
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

## 等价于 set 标签
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

where

若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。

HashMap hashMap = new HashMap();
hashMap.put("title","java");
hashMap.put("author","自己");
//java项目www.fhadmin.org
<select id="queryBlogIf" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>
</select>

set

set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号

<update id="updateBlog" parameterType="map">
    update blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author},
        </if>
    </set>
    where id = #{id}
</update>

choose (when, otherwise)

choose

类似 Java 中的 switch

HashMap hashMap = new HashMap();
hashMap.put("title","java");
//        hashMap.put("author","自己");
hashMap.put("views", 1000);

<select id="queryBlogChoose" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                author = #{author}
            </when>
            <otherwise>
                views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

SQL片段

我们可以把一些功能抽取出来,方便复用

  • sql:抽取代码片段
  • include: 引用sql抽取的代码片段
<sql id="if-title-author">
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

<select id="queryBlogIf" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <include refid="if-title-author"/>
    </where>
</select>

注意事项

  • 最好基于单表来定义SQL片段
  • 不要存在 where 标签

foreach

  • collection:遍历对象
  • item:每一项
  • index:索引
  • open:开头
  • separator:分隔符
  • close:结尾
int[] array = new int[]{10, 5000, 9999};
List<Integer> list = new ArrayList<>();
for (int i : array) {
    list.add(i);
}
//java项目www.fhadmin.org
<select id="getBlogIn" parameterType="list" resultType="Blog">
    select * from blog
    <where>
        <if test="list != null and list.size() > 0">
            views in
            <foreach collection="list" item="id" index="index" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
</select>

赞(0) 打赏
未经允许不得转载:IDEA激活码 » Mybatis if, set, where 动态 sql 和 sql 片段的使用

相关推荐

  • 暂无文章

一个分享Java & Python知识的社区