补充：
随机生成一个id,utils包下建一个IDutils.java
import java.util.UUID; @SuppressWarnings("all")//抑制警告 public class IDutil { public static String getId(){ return UUID.randomUUID().toString().replaceAll("-",""); } @Test public void test(){ System.out.println(IDutil.getId()); } }
测试输出的结果：3f39b061e455426585461ed21d6822d5
if
choose (when, otherwise)
trim (where, set)
1.if
使用动态 SQL 最常见情景，条件 where 后的子句
pojo层Blog.java
import java.util.Date; @AllArgsConstructor @NoArgsConstructor @Data public class Blog { private String id; private String title; private String author; private Date creat_time; private int views;
BlogMapper.java接口
public interface BlogMapper { //插入数据 int addBlog(Blog blog); //查询 List<Blog> getBlogIf(Map map); }
BlogMapper.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="mapper.BlogMapper"> <!--插入数据--> <insert id="addBlog" parameterType="Blog"> insert into blog (id,title,author,creat_time,views) values (#{id},#{title},#{author},#{creat_time},#{views}) </insert> <!--查询数据--> <select id="getBlogIf" 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> </mapper>
2.where
- 如果没有匹配的条件会怎么样？查询失败
SELECT * FROM BLOG
WHERE
- 如果匹配的只是第二个条件又会怎样？查询失败
- SELECT * FROM BLOG
WHERE
AND author like ‘嘻哈’
where后紧跟着and会报错
where and这样的sql语句是不存在的，但是用where标签就能避免这种错误
第一条拼接语句不要加and，但是有了where标签，会自动删除多余的and
where语句会根据你后面是否为空，若为空自动筛掉where后面拼接的语句，
若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除
写为where标签形式
<!--查询所有--> select * from blog <where> <!--第一条数据不加and--> <if test="title!=null"> and title = #{title} </if> <if test="author!=null"> and author = #{author} </if> </where>
测试类
@Test public void addBlog(){ SqlSession session = MybatisUtil.getSqlSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); mapper.addBlog(new Blog("22","mybatis","嘻哈",new Date(),100000)); mapper.addBlog(new Blog("25","SpringSecurity","嘻哈",new Date(),90000)); mapper.addBlog(new Blog("25","动态Sql","小哈",new Date(),8888)); mapper.addBlog(new Blog(IDutil.getId(),"动态Sql","小哈",new Date(),8888)); session.commit(); session.close(); } //查询数据 @Test public void getBlogIf(){ SqlSession session = MybatisUtil.getSqlSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap<String, Object> map = new HashMap<>(); map.put("title","mybatis"); map.put("author","嘻哈"); List<Blog> list = mapper.getBlogIf(map); for (Blog blog : list) { System.out.println(blog); } session.close(); }
3.choose
MyBatis 提供了 choose-when-otherwise 元素，它有点像 Java 中的 switch-case-default 语句
<select id="getBlogChoose" parameterType="Map" resultType="blog"> select * from blog <where> <choose> <when test="title!=null"> title = #{title} </when> <when test="author!=null"> author = #{author} </when> <otherwise> views = #{views} </otherwise> </choose> </where> </select>
4.set
sql语句：update blog set title=?,author=? where id=?
set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号
映射接口BlogMapper.java
//更新博客 int updateBlog(Map map);
映射配置文件BlogMapper.xml
<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>
测试
//更新博客 @Test public void updateBlog(){ SqlSession session = MybatisUtil.getSqlSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap map = new HashMap(); map.put("title","springboot"); map.put("id","22"); mapper.updateBlog(map); session.commit(); session.close(); }
5.trim(where/set)
- prefixOverrides-前缀：属性值的空格是必要的
AND |OR - suffixOverrides-后缀
可以替换where
<trim prefix="WHERE" prefixOverrides="AND |OR "> ... </trim>
可以替换set
<trim prefix="SET" suffixOverrides=","> ... </trim>
6.foreach
查询一个结果集–>集合
sql语句
select * from blog where 1=1 and (id=1 or id =2 or id = 3)
BlogMapper.java接口
//查找id为1、2、3的博客 List<Blog> blogForeach(Map map);
BlogMapper.xml配置文件
<select id="blogForeach" parameterType="Map" resultType="blog"> select * from blog <where> <foreach collection="ids" item="id" open="(" close=")" separator="or" > id=#{id} </foreach> </where> </select>
测试类
//foreach遍历 @Test public void blogForeach(){ SqlSession session = MybatisUtil.getSqlSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); HashMap map = new HashMap(); ArrayList<Integer> ids = new ArrayList(); ids.add(1); ids.add(2); ids.add(3); map.put("ids",ids); List<Blog> blogs = mapper.blogForeach(map); for (Blog blog : blogs) { System.out.println(blog); } session.close(); }
Blog(id=1, title=springboot, author=嘻哈, creat_time=Sat Nov 27 15:55:12 CST 2021, views=100000)
Blog(id=2, title=SpringSecurity, author=嘻哈, creat_time=Sat Nov 27 15:55:13 CST 2021, views=90000)
Blog(id=3, title=动态Sql, author=小哈, creat_time=Sat Nov 27 15:55:13 CST 2021, views=8888)
7.SQL片段
将代码公共部分抽取出来，以便复用。
使用SQL标签抽取公共部分
<sql id="if-author-title"> <if test="title!=null"> and title = #{title} </if> <if test="author!=null"> and author = #{author} </if> </sql>
在需要使用到的地方用include标签引入
<include refid="if-author-title"></include>
总结：
1.trim一般不用，where和set就可以实现，若使用注意prefixOverrides属性值后加空格
