日本免费高清视频-国产福利视频导航-黄色在线播放国产-天天操天天操天天操天天操|www.shdianci.com

學(xué)無(wú)先后，達(dá)者為師

網(wǎng)站首頁(yè) 編程語(yǔ)言 正文

Mybatis if, set, where 動(dòng)態(tài)sql和sql片段的使用

作者：weixin_44953227 更新時(shí)間： 2022-04-10 編程語(yǔ)言

目錄

  • 動(dòng)態(tài)SQL
  • if
  • trim (where, set)
  • choose (when, otherwise)
  • SQL片段
  • foreach

Mybatis 官方文檔： https://mybatis.org/mybatis-3/zh/dynamic-sql.html

動(dòng)態(tài)SQL

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

數(shù)據(jù)表

CREATE TABLE `blog` ( 
    `id` VARCHAR(50) NOT NULL COMMENT '博客id', 
    `title` VARCHAR(100) NOT NULL COMMENT '博客標(biāo)題', 
    `author` VARCHAR(30) NOT NULL COMMENT '博客作者', 
    `create_time` DATETIME NOT NULL COMMENT '創(chuàng)建時(shí)間', 
    `views` INT(30) NOT NULL COMMENT '瀏覽量'
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci;

實(shí)體類

public class Blog {
    private String id;
    private String title;
    private String auther;
    private java.util.Date createTime;
    private int views;
}

開(kāi)啟駝峰命名自動(dòng)映射

<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：在包裹的代碼塊前面添加一個(gè) xxx
  • prefixOverrides：屬性會(huì)忽略通過(guò)管道符分隔的文本序列（注意此例中的空格是必要的）
  • suffixOverrides： 忽略最后一個(gè) xxx 
## 等價(jià)于 where 標(biāo)簽
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
trim>

## 等價(jià)于 set 標(biāo)簽
<trim prefix="SET" suffixOverrides=",">
  ...
trim>

where

若子句的開(kāi)頭為 “AND” 或 “OR”，where 元素也會(huì)將它們?nèi)コ?/strong>

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

<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 元素會(huì)動(dòng)態(tài)地在行首插入 SET 關(guān)鍵字，并會(huì)刪掉額外的逗號(hào)

<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片段

我們可以把一些功能抽取出來(lái)，方便復(fù)用

  • 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>

注意事項(xiàng)

  • 最好基于單表來(lái)定義SQL片段
  • 不要存在 where 標(biāo)簽


foreach

  • collection：遍歷對(duì)象
  • item：每一項(xiàng)
  • index：索引
  • open：開(kāi)頭
  • separator：分隔符
  • close：結(jié)尾
int[] array = new int[]{10, 5000, 9999};
List<Integer> list = new ArrayList<>();
for (int i : array) {
    list.add(i);
}

<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>

原文鏈接：https://blog.csdn.net/weixin_44953227/article/details/112790534