12、动态SQL
什么是动态SQL?
动态SQL就是根据不同的条件去生成不同的SQL语句
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 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.导包
pom.xml中引入lombok
2.编写配置文件
db.properties
mybatis-config.xml
基本和以往工程一样
只是mybatis-config.xml中加入了设置:开启驼峰命名
<!--是否开启驼峰命名自动映射-->
<setting name="mapUnderscoreToCamelCase" value="true"/>
3.编写实体类
package com.gongyi.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;
}
工具类:
package com.gongyi.utils;
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.编写实体类对应的Mapper接口和Mapper.xml文件
接口类:
package com.gongyi.dao;
import com.gongyi.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//插入数据
int addBlog(Blog blog);
//查询博客
List<Blog> queryBlogIF(Map map);
}
xml文件:
<?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.gongyi.dao.BlogMapper">
<insert id="addBlog" parameterType="blog">
insert into mybatis.blog(id, title, author, create_time, views) values
(#{id},#{title},#{author},#{createTime},#{views});
</insert>
<select id="queryBlogIF" resultType="blog" parameterType="map">
select * from mybatis.blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
</mapper>
测试类:
import com.gongyi.dao.BlogMapper;
import com.gongyi.pojo.Blog;
import com.gongyi.utils.IDutils;
import com.gongyi.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
public class MyTest {
@Test
public void addInitBlog() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.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);
session.close();
}
@Test
public void queryBlogIf() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
// map.put("title","MyBatis如此简单");
map.put("author", "工一说");
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
}
代码结构图:
IF
<select id="queryBlogIF" resultType="blog" parameterType="map">
select * from mybatis.blog where 1=1
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
choose(when,otherwise)
<select id="queryBlogChoose" resultType="blog" parameterType="map">
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>
trim(where,set)
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
所谓的动态SQL,本质还是SQL语句,只是我们可以在SQL层面,去执行一个逻辑代码
if
where,set,choose,when
SQL片段
有的时候,我们可能会将一些公共的部分抽取出来,方便复用
1.使用SQL标签抽取公共的部分
<sql id="if-title-author">
<if test="title != null">
and title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
2.在需要使用的地方使用include标签引用即可
<select id="queryBlogIFUsingSqlFragment" resultType="blog" parameterType="map">
select * from mybatis.blog where 1=1
<include refid="if-title-author"></include>
</select>
3.测试
@Test
public void queryBlogIFUsingSqlFragment() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
//map.put("title","MyBatis如此简单");
map.put("author", "工一说");
List<Blog> blogs = mapper.queryBlogIFUsingSqlFragment(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
注意事项:
- 最好基于单表来定义SQL片段
- 不要存在where标签
Foreach
select * from user where 1=1 and (id=1 or id=2 or id=3)
select * from user where 1=1 and
<foreach item="id" collection="ids" open="(" separator="or" close=")">
#{id}
</foreach>
修改blog的id为数字:
核心xml:
<!--
select * from user where 1=1 and (id=1 or id=2 or id=3)
我们现在传递一个万能的map,这map中可以存在一个集合
-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id = #{id}
</foreach>
</where>
</select>
测试:
@Test
public void queryBlogForeach() {
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.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);
}
session.close();
}
总结:
动态sql就是在拼接sql语句,我们只要保证sql的正确性,按照sql的格式,去排列组合就可以了
建议:
先在mysql中写出完整的sql,再对应去修改成为我们的动态sql,实现通用即可
彩蛋
2.idea中波浪线提示解决
1)代码改规范
2)@SuppressWarnings(“all”)
问题总结
1.Caused by: org.apache.ibatis.builder.BuilderException: The setting mapUnderscoreToCamelCase is not known. Make sure you spelled it correctly (case sensitive).
解决:配置中mapUnderscoreToCamelCase 加了空格,去掉即可