动态SQL
官方中文文档:https://mybatis.org/mybatis-3/zh/dynamic-sql.html
什么是动态SQL:动态SQL就是指根据不同的条件生成不同的SQL语句
动态SQL就是在拼接SQL语句,我们只要保证SQL的正确性,按照SQL的格式,去排列组合就可以了
-
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器,你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。 - 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 DEFAULT CHARSET=utf8
创建一个基础工程
1、导包
2、编写配置文件
配置文件中添加setting配置
<!-- 开启驼峰命名转化-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
3、编写IDUtils工具类
import org.junit.Test;
import java.util.UUID;
@SuppressWarnings("all")//抑制警告
public class IDutils {
public static String getId(){
return UUID.randomUUID().toString().replaceAll("-","");
}
@Test
public void test(){
System.out.println(IDutils.getId());
System.out.println(IDutils.getId());
System.out.println(IDutils.getId());
}
}
4、编写实体类
@Data
public class Blog {
private int id;
private String title;
private String author;
private Date createTime;
private int views;
}
5、编写实体类对应Mapper接口和Mapper.XML文件
Mapper接口
import com.kuang.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//插入数据
int addBlog(Blog blog);
//查询博客
List<Blog> queryBlogIF(Map map);
List<Blog> queryBlogChoose(Map map);
//更新博客
int updateBlog(Map map);
//查询第1.2.3号记录的博客
List<Blog> queryBlogForeach(Map map);
}
6、测试类
import com.kuang.dao.BlogMapper;
import com.kuang.pojo.Blog;
import com.kuang.utils.IDutils;
import com.kuang.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
public class MyTest {
@Test
public void addInitBlog(){
SqlSession sqlSession = MybatisUtils.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.addBlog(blog);
blog.setId(IDutils.getId());
blog.setTitle("Java如此简单");
mapper.addBlog(blog);
blog.setId(IDutils.getId());
blog.setTitle("Spring如此简单");
mapper.addBlog(blog);
blog.setId(IDutils.getId());
blog.setTitle("微服务如此简单");
mapper.addBlog(blog);
sqlSession.close();
}
@Test
public void queryBlogIF(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","Java如此简单");
map.put("author","狂神说");
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
@Test
public void queryBlogChoose(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title","Java如此简单");
map.put("author","狂神说");
map.put("views",9999);
List<Blog> blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
@Test
public void queryBlogSet(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
//map.put("title","Java如此简单2");
map.put("author","狂神说2");
map.put("id","3879c36d027e4681a23939106d46ab18");
mapper.updateBlog(map);
sqlSession.close();
}
@Test
public void queryBlogForeach(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
}
IF
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
choose (when, otherwise)
从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
<select id="queryBlogChoose" parameterType="map" resultType="blog">
select * from 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>
trim (where, set)
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
<where>
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
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>
Foreach
select * from user where 1=1 and (id=1 or id=2 or id=3)
<foreach item="id" collection="ids"
open="(" separator="or" close=")">
#{id}
</foreach>
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<foreach collection="ids" item="id" open="and (" separator="or" close=")">
id = #{id}
</foreach>
</where>
</select>
测试类
@Test
public void queryBlogForeach(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
ArrayList<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
map.put("ids",ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
SQL片段
有的时候,我们可能会将一些功能的部分抽取出来,方便复用
1、使用SQL标签抽取公共部分
<sql id="if-title-author">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
2、在需要的地方使用include标签引用即可
<select id="queryBlogIF" parameterType="map" resultType="blog">
select * from blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
注意事项:
-
最好基于单表来定义SQL片段!
-
不要存在where标签