动态SQL
什么是动态SQL:动态SQL就是指根据不同的条件生成不同的SQL语句
环境搭建
创建数据库
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
编写实体类
package com.qin.pojo;
import lombok.Data;
import java.util.Date;
@Data
public class Blog {
private int id;
private String title;
private String author;
private Date createTime;
private int views;
}
编写mapper接口
package com.qin.dao;
import com.qin.pojo.Blog;
public interface BlogMapper {
public int addBlog(Blog blog);
}
编写mapper.xml文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qin.dao.BlogMapper">
<insert id="addBlog" parameterType="blog">
insert into blog values (#{id},#{title},#{author},#{createTime},#{views})
</insert>
</mapper>
实体类属性名与数据库字段名不一样可以在mybatis-config.xml中设置
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
测试
package com.qin.dao;
import com.qin.pojo.Blog;
import com.qin.utils.IdUtil;
import com.qin.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
public class BlogTest {
@Test
public void test1(){
SqlSession sqlSession = MybatisUtils.getSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IdUtil.getId());
blog.setTitle("Mybatis");
blog.setAuthor("狂神说");
blog.setCreateTime(new Date());
blog.setViews(9999);
mapper.addBlog(blog);
blog.setId(IdUtil.getId());
blog.setTitle("Java");
mapper.addBlog(blog);
blog.setId(IdUtil.getId());
blog.setTitle("Spring");
mapper.addBlog(blog);
blog.setId(IdUtil.getId());
blog.setTitle("微服务");
mapper.addBlog(blog);
sqlSession.commit();
sqlSession.close();
}
}
If语句
编写接口
public List<Blog> getBlogIf(Map map);
编写mapper
<select id="getBlogIf" parameterType="map" resultType="blog">
select * from mybatis.blog where 1=1
<if test="title != null">
and title like #{title}
</if>
<if test="author != null">
and author like #{author}
</if>
</select>
测试
@Test
public void test2(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<>();
map.put("title","Mybatis");
map.put("author","%"+"狂"+"%");
List<Blog> blogs = mapper.getBlogIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
choose (when, otherwise)
编写接口
public List<Blog> getBlogChoose(Map map);
编写mapper
<select id="getBlogChoose" parameterType="map" resultType="blog">
select * from mybatis.blog where
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="views != null">
views > #{views}
</when>
<otherwise>
author like "狂神%"
</otherwise>
</choose>
</select>
测试
@Test
public void test3(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<>();
// map.put("title","Mybatis");
// map.put("views",5000);
List<Blog> blogs = mapper.getBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
- choose会执行最先成立的when
- 如果都不成立则执行otherwise
- 只会执行其中的一个
trim (where,set)
编写接口
public List<Blog> getBlogWhere(Map map);
public int updateBlog(Map map);
编写mapper
<select id="getBlogWhere" parameterType="map" resultType="blog">
select * from mybatis.blog
<where>
<if test="title != null">
title like #{title}
</if>
<if test="views != null">
and views >#{views}
</if>
</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>
测试
@Test
public void test4(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<>();
// map.put("title","Mybatis");
// map.put("views",5000);
List<Blog> blogs = mapper.getBlogWhere(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
session.close();
}
@Test
public void test5(){
SqlSession session = MybatisUtils.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<>();
map.put("title","Mybatis");
map.put("author","尚硅谷");
map.put("id","62534776271444bfa6150c577f6d7c47");
mapper.updateBlog(map);
session.commit();
session.close();
}
set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
set 元素等价的自定义 trim 元素:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
where 元素等价的自定义 trim 元素:
<trim prefix="where"prefixOverrides="and | or">
...
</trim>
SQL片段
有的时候,我们可能会将一些功能的部分抽取出来,方便复用!
-
使用SQL标签抽取公共的部分
<sql id="if-title-author"> <if test="title != null"> title = #{title} </if> <if test="author != null"> and author = #{author} </if> </sql>
-
在需要使用的地方使用Include标签引用即可
<select id="queryBlogIF" parameterType="map" resultType="blog"> select * from mybatis.blog <where> <include refid="if-title-author"></include> </where> </select>
注意事项:
- 最好基于单表来定义SQL片段!
- 不要存在where标签
Foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)
foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符,
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符,
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>