Mbatis动态sql 详解 if choose when trim where Update set

if

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。比如：

choose、when、otherwise

有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

还是上面的例子，但是策略变为：传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 Blog，还不如返回一些由管理员精选的 Blog）。

trim、where、set

前面几个例子已经方便地解决了一个臭名昭著的动态 SQL 问题。现在回到之前的 “if” 示例，这次我们将 “state = ‘ACTIVE’” 设置成动态条件，看看会发生什么。

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>
prefixOverrides 属性会忽略通过管道符分隔的文本序列（注意此例中的空格是必要的）。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。

用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。比如：
<update id="updateAuthorIfNecessary">
  update Author
    <set>
      <if test="username != null">username=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="bio != null">bio=#{bio}</if>
    </set>
  where id=#{id}
</update>
这个例子中，set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

来看看与 set 元素等价的自定义 trim 元素吧：
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

创建mysql 表 blog

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 DEFAULT CHARSET=utf8

第一步导包

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <parent>
        <artifactId>MybatisTest0603</artifactId>
        <groupId>com.kgc2</groupId>
        <version>1.0-SNAPSHOT</version>
    </parent>
    <modelVersion>4.0.0</modelVersion>

    <artifactId>MybatisTest0607</artifactId>
    <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
            <scope>provided</scope>
        </dependency>
    </dependencies>

</project>

第二步导入核心文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <typeAliases>
        <package name="com.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=true &amp; useUnicode=true &amp; characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123qwe"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
<!--       <mapper class="com.dao.StudentMapper"/>-->
<!--        <mapper class="com.dao.TeacherMapper"/>-->


<!--        <mapper resource="com/dao/StudentMapper.xml"/>-->
<!--        <mapper resource="com/dao/TeacherMapper.xml"/>-->

    <package name="com.dao"/>
    </mappers>

</configuration>

第三步编写实体类使用了lombok注解

package com.pojo; import lombok.Data; import java.util.Date; @Data public class Blog { private String id; private String title; private String author; private Date createTime; private int views;
}

第四步编写 BlogMapper接口和xml

package com.dao; import com.pojo.Blog; import java.util.List; import java.util.Map; public interface BlogMapper { int addBook(Blog blog);

    List<Blog> queryBlog(Map map);


    List<Blog> queryWhere(Map map); int updateBlog(Map map);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.BlogMapper">
    <insert id="addBook" parameterType="blog">
        insert into mybatis.blog values
        (#{id},#{title},#{author},#{createTime},#{views})
    </insert>

    <select id="queryBlog" parameterType="map" resultType="blog">
       select *
       from mybatis.blog
       <where>
           <if test="title !=null">
              and title=#{title}
           </if>
           <if test="author">
               and author=#{author}
           </if>
       </where>



    </select>

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

       </where>

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

           where id=#{id}



    </update>
    
</mapper>

第五步测试

import com.dao.BlogMapper; import com.pojo.Blog; import com.utils.IDUtils; import com.utils.util; import org.apache.ibatis.session.SqlSession; import java.util.Date; import java.util.HashMap; import java.util.List; import java.util.Map; public class Test { @org.junit.Test public void addInitBlog(){ SqlSession sqlSession = util.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog=new Blog();
        blog.setId(IDUtils.getId());
        blog.setTitle("mybatis如此简单");
        blog.setAuthor("狂神说");
        blog.setCreateTime(new Date());
        blog.setViews(9999);
        mapper.addBook(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("java如此简单");
        mapper.addBook(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("Spring如此简单");
        mapper.addBook(blog);

        blog.setId(IDUtils.getId());
        blog.setTitle("微服务如此简单");
        mapper.addBook(blog);


        sqlSession.close();


    } @org.junit.Test public void queryBlogIf(){ SqlSession sqlSession = util.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); //        map.put("title","java如此简单"); //        map.put("author","狂神说"); List<Blog> blogs = mapper.queryBlog(map); for (Blog blog : blogs) {
            System.out.println(blog);
        }


        sqlSession.close();


    } @org.junit.Test public void queryWhere1(){ SqlSession sqlSession = util.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map=new HashMap();
        map.put("views",9999); //        map.put("title","java如此简单"); List<Blog> blogs = mapper.queryWhere(map); for (Blog blog : blogs) {
            System.out.println(blog);
        }


        sqlSession.close();


    } @org.junit.Test public void updateBlog1(){ SqlSession sqlSession = util.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); Map map =new HashMap();
       map.put("author","狂神说3");
       map.put("id","0b4191aa820d4b4c99fba5d3a6be031c");

        mapper.updateBlog(map);

        sqlSession.close();

    }
}

结果如下

Listing file:/D:/Study/ssmtools/mavan/MybatisTest0603/MybatisTest0607/target/classes/com/dao
Find JAR URL: file:/D:/Study/ssmtools/mavan/MybatisTest0603/MybatisTest0607/target/classes/com/dao/BlogMapper.class Not a JAR: file:/D:/Study/ssmtools/mavan/MybatisTest0603/MybatisTest0607/target/classes/com/dao/BlogMapper.class Reader entry: ���� 1 addBook (Lcom/pojo/Blog;)I     queryBlog !(Ljava/util/Map;)Ljava/util/List;     Signature 2(Ljava/util/Map;)Ljava/util/List<Lcom/pojo/Blog;>; 
Find JAR URL: file:/D:/Study/ssmtools/mavan/MybatisTest0603/MybatisTest0607/target/classes/com/dao/BlogMapper.xml
Not a JAR: file:/D:/Study/ssmtools/mavan/MybatisTest0603/MybatisTest0607/target/classes/com/dao/BlogMapper.xml
Reader entry: <?xml version="1.0" encoding="UTF-8" ?>
Checking to see if class com.dao.BlogMapper matches criteria [is assignable to Object]
Opening JDBC Connection
Created connection 1212772528. ==>  Preparing: select * from mybatis.blog WHERE views =?
==> Parameters: 9999(Integer)
<==    Columns: id, title, author, create_time, views
<==        Row: 0b4191aa820d4b4c99fba5d3a6be031c, mybatis如此简单, 狂神说3, 2022-04-02 21:48:23, 9999 <==        Row: 0c149fad3d27443cac687095d84101c3, Spring如此简单, 狂神说, 2022-04-02 21:48:23, 9999 <==        Row: 1e8c63dcfbe44abc8adca942ff6e63ff, 微服务如此简单, 狂神说, 2022-04-02 21:48:23, 9999 <==        Row: 2ed2bff67d254057ac9f61a12760dee2, mybatis如此简单, 狂神说, 2022-04-02 22:29:37, 9999 <==        Row: 0c89eb0f71654e668dabac747c5685d6, java如此简单, 狂神说, 2022-04-02 22:29:37, 9999 <==        Row: 1aec1fd3fa84491dafd8eb7782af8abf, Spring如此简单, 狂神说, 2022-04-02 22:29:37, 9999 <==        Row: bf0dba33972b47c08165f761c534036a, 微服务如此简单, 狂神说, 2022-04-02 22:29:37, 9999 <==      Total: 7 Blog(id=0b4191aa820d4b4c99fba5d3a6be031c, title=mybatis如此简单, author=狂神说3, createTime=Sat Apr 02 21:48:23 CST 2022, views=9999)
Blog(id=0c149fad3d27443cac687095d84101c3, title=Spring如此简单, author=狂神说, createTime=Sat Apr 02 21:48:23 CST 2022, views=9999)
Blog(id=1e8c63dcfbe44abc8adca942ff6e63ff, title=微服务如此简单, author=狂神说, createTime=Sat Apr 02 21:48:23 CST 2022, views=9999)
Blog(id=2ed2bff67d254057ac9f61a12760dee2, title=mybatis如此简单, author=狂神说, createTime=Sat Apr 02 22:29:37 CST 2022, views=9999)
Blog(id=0c89eb0f71654e668dabac747c5685d6, title=java如此简单, author=狂神说, createTime=Sat Apr 02 22:29:37 CST 2022, views=9999)
Blog(id=1aec1fd3fa84491dafd8eb7782af8abf, title=Spring如此简单, author=狂神说, createTime=Sat Apr 02 22:29:37 CST 2022, views=9999)
Blog(id=bf0dba33972b47c08165f761c534036a, title=微服务如此简单, author=狂神说, createTime=Sat Apr 02 22:29:37 CST 2022, views=9999)
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@484970b0]
Returned connection 1212772528 to pool.

Process finished with exit code 0