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 & useUnicode=true & 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