- 在SQL层面执行一个逻辑代码
- 根据不同的条件生成不同的SQL语句
- 主要还是在拼接SQL,只要保证SQL的正确性,去排列组合就好了
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类,现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
create table blog ( id varchar(50) primary key 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 = utf8mb4;
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; }
属性名和字段名不一致
解决:在MyBatis核心配置文件中配置设置
<!-- 是否开启自动驼峰命名规则(camel case)映射,即从经典数据库列名 A_COLUMN 到经典 Java 属性名 aColumn 的类似映射。 --> <settings> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings>
public interface BlogMapper { //插入博客 int addBlog(Blog blog); }
<?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.mybatis.dao.BlogMapper"> <insert id="addBlog" parameterType="blog"> insert into blog (id, title, author, create_time, views) values (#{id}, #{title}, #{author}, #{createTime}, #{views}); </insert> </mapper>
public class IdUtil { public static String getId(){ return UUID.randomUUID().toString().replaceAll("-",""); } @Test public void Test(){ System.out.println(IdUtil.getId()); System.out.println(IdUtil.getId()); System.out.println(IdUtil.getId()); } }
@Test public void addBlogTest(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); 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(20200202); mapper.addBlog(blog); blog.setId(IdUtil.getId()); blog.setTitle("Spring如此简单"); blog.setAuthor("快乐的小小程序猿"); blog.setCreateTime(new Date()); blog.setViews(20202020); mapper.addBlog(blog); blog.setId(IdUtil.getId()); blog.setTitle("SpringMVC如此简单"); blog.setAuthor("乐小猿"); blog.setCreateTime(new Date()); blog.setViews(20202002); mapper.addBlog(blog); blog.setId(IdUtil.getId()); blog.setTitle("Spring Boot如此简单"); blog.setAuthor("快乐的小小程序猿"); blog.setCreateTime(new Date()); blog.setViews(20202222); mapper.addBlog(blog); sqlSession.close(); }
//查询博客 List<Blog> queryBlogIf(Map map);
<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>
@Test public void queryBlogIfTest(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String, String> map = new HashMap<String, String>(); map.put("title","Spring Boot如此简单"); //map.put("author" , "快乐的小小程序猿"); map.put("author" , "乐小猿"); List<Blog> blogList = mapper.queryBlogIf(map); for (Blog blog : blogList) { System.out.println(blog); } sqlSession.close(); }
提供了“title”就按“title”查找,提供了“author”就按“author”查找的情形,若两者都没有提供,就返回所有符合条件的 blog
//查询博客--使用choose List<Blog> queryBlogChoose(Map map);
<select id="queryBlogChoose" resultType="blog" parameterType="map"> select * from blog where 1=1 <choose> <when test="title != null"> and title like #{title} </when> <when test="author != null"> and author like #{author} </when> <otherwise> and views > 20202020 </otherwise> </choose> </select>
@Test public void queryBlogChooseTest(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String, String> map = new HashMap<String, String>(); //map.put("title","%Spring%"); map.put("author" , "%快乐%"); List<Blog> blogList = mapper.queryBlogChoose(map); for (Blog blog : blogList) { System.out.println(blog); } sqlSession.close(); }
where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且,若语句的开头为“AND”或“OR”,where 元素也会将它们去除。
可以防止出现
SELECT * FROM BLOG WHERE #或 SELECT * FROM BLOG WHERE AND title like ‘someTitle’
//查询博客--使用where List<Blog> queryBlogWhere(Map map);
<select id="queryBlogWhere" resultType="blog" parameterType="map"> select * from blog <where> <if test="title != null"> title=#{title} </if> <if test="author != null"> and author=#{author} </if> </where> </select>
@Test public void queryBlogWhereTest(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String, String> map = new HashMap<String, String>(); //map.put("title","Spring Boot如此简单"); map.put("author" , "乐小猿"); List<Blog> blogList = mapper.queryBlogWhere(map); for (Blog blog : blogList) { System.out.println(blog); } sqlSession.close(); }
用于动态更新语句的解决方案叫做 set。set 元素可以用于动态包含需要更新的列,而舍去其它的。这里,set 元素会动态前置 SET 关键字,同时也会删掉无关的逗号,因为用了条件语句之后很可能就会在生成的 SQL 语句的后面留下这些逗号。(译者注:因为用的是“if”元素,若最后一个“if”没有匹配上而前面的匹配上,SQL 语句的最后就会有一个逗号遗留)
//更新博客 int updateBlogSet(Map map);
<update id="updateBlogSet" parameterType="map"> update blog <set> <if test="views != 20200202">views = #{views},</if> <if test="author != null">author=#{author}</if> </set> where id=#{id} </update>
@Test public void updateBlogSetTest(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap<String, String> map = new HashMap<String, String>(); map.put("id" , "54f147aa553d4440b565f098495b3e3b"); map.put("views" , "99999"); map.put("author" , "乐小猿"); mapper.updateBlogSet(map); sqlSession.close(); }
通常是在构建 IN 条件语句的时候使用,foreach 元素的功能非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。它也允许你指定开头与结尾的字符串以及在迭代结果之间放置分隔符。
注意:你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时,index 是当前迭代的次数,item 的值是本次迭代获取的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
//查询博客--使用foreach List<Blog> queryBlogForeach(Map map);
<!-- select * from blog where author is not null and author in ('徐仕成', '快乐的小小程序猿'); --> <select id="queryBlogForeach" parameterType="map" resultType="blog"> select * from blog <where> author is not null and author in <foreach collection="authorList" item="item" index="index" open="(" separator="," close=")"> #{item} </foreach> </where> </select>
@Test public void queryBlogForeachTest(){ SqlSession sqlSession = MyBatisUtil.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); ArrayList<String> list = new ArrayList<String>(); HashMap<String, Object> map = new HashMap<String, Object>(); list.add("乐小猿"); list.add("快乐的小小程序猿"); map.put("authorList", list); List<Blog> blogList = mapper.queryBlogForeach(map); for (Blog blog : blogList) { System.out.println(blog); } sqlSession.close(); }
<sql><sql/>
标签抽取公共部分<sql id="if-title-author"> <if test="title != null"> and title=#{title} </if> <if test="author != null"> and author=#{author} </if> </sql>
<include refid=""/>
标签引用片段<select id="queryBlogIf" parameterType="map" resultType="blog"> select * from blog where 1=1 <include refid="if-title-author"/> </select>
注意事项:
where
标签