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

最新推荐文章于 2022-10-30 11:04:13 发布

kjshuan

最新推荐文章于 2022-10-30 11:04:13 发布

阅读量755

点赞数 1

分类专栏： java 文章标签： java 动态sql Mybatis

本文链接：https://blog.csdn.net/just_learing/article/details/123931354

版权

java 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本文详细介绍了MyBatis中动态SQL的使用，包括choose、when、otherwise元素实现条件选择，以及trim、where、set元素在构建动态查询和更新语句中的应用。通过实例展示了如何根据传入参数灵活地构造SQL语句，避免全表扫描，提高查询效率。

摘要由CSDN通过智能技术生成

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