什么是动态SQL？

根据不同的条件生成不同的SQL语句

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

以下测试实现一些常用的动态SQL

在接口中定义一些方法

package com.robot.mapper; import com.robot.pojo.Blog; import java.util.List; import java.util.Map; public interface BlogMapper { int addBlog(Blog blog); List<Blog> queryBlog(Map map); List<Blog> queryBlog2(Map map); List<Blog> queryBlog3(Map map); int updateBlog(Map map); List<Blog> queryBlogForeach(Map map); }

在xml中实现

动态SQL这些标签和Java、SQL中的关键字意思是差不多的

比如

if 标签就是条件判断语句；

where 标签就是SQL中的where，只不过这个where标签会自动去除或添加标签内多余的and

choose 标签就是Java中的switch

下面代码中的SQL片段，就相当于写了一个方法，增加代码的复用性，而使用这个方法的时候，就可以用include标签来引用

foreach 标签是遍历查询，如果根据id查询，则需要有一个id集合：collection=“ids”

<?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.robot.mapper.BlogMapper"> <!-- 插入数据--> <insert id="addBlog" parameterType="blog"> insert into blog (id, title, author, create_time, views) values (#{id},#{title},#{author},#{createTime},#{views}) </insert> <!-- 如果不指定，则查询所有，如果指定参数，则按条件查询--> <select id="queryBlog" 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> <!-- where语句+if, 防止where后直接拼接and--> <select id="queryBlog2" parameterType="map" resultType="blog"> select * from blog <where> <if test="title != null"> title=#{title} </if> <if test="author != null"> and author=#{author} </if> </where> </select> <!-- choose-when-otherwise 相当于 Switch-case-default 语句,只会按顺序选择其中一个执行--> <select id="queryBlog3" 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> <!-- SQL片段，代码复用，相当于封装成一个方法--> <sql id="if-blog"> <if test="title != null"> title = #{title}, </if> <if test="author != null"> author = #{author} </if> </sql> <!-- set标签：动态前置set，删掉无关逗号--> <update id="updateBlog" parameterType="map"> update blog <set> <include refid="if-blog"></include> </set> where id = #{id} </update> <!-- 遍历查询 select * from blog where (id=? or id=? or id=?)--> <select id="queryBlogForeach" parameterType="map" resultType="blog"> select * from blog <where> <foreach collection="ids" item="id" open="(" separator="or" close=")"> id = #{id} </foreach> </where> </select> </mapper>

测试

以下测试，分别测试了以上所有实现的方法

package com.robot.mapper; import com.robot.pojo.Blog; import com.robot.utils.IDutils; import com.robot.utils.MybatisUtils; import org.apache.ibatis.session.SqlSession; import org.junit.Test; import java.util.*; 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("robot"); 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 queryBlog() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class); Map map = new HashMap(); // map.put("title", "Java如此简单"); map.put("author", "robot"); List<Blog> blogs = blogMapper.queryBlog(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); } @Test public void queryBlog2() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class); Map map = new HashMap(); map.put("title", "Java如此简单"); map.put("author", "robot"); List<Blog> blogs = blogMapper.queryBlog2(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); } @Test public void queryBlog3() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class); Map map = new HashMap(); map.put("title", "Java如此简单"); map.put("views", "9999"); List<Blog> blogs = blogMapper.queryBlog3(map); for (Blog blog : blogs) { System.out.println(blog); } sqlSession.close(); } @Test public void updateBlog() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class); Map map = new HashMap(); map.put("id", "6d6cb532858b4fb1b0204ce5b8601c9f"); map.put("title", "Java攻城狮-robot"); //map.put("author", "baby"); int blog = blogMapper.updateBlog(map); System.out.println(blog); sqlSession.close(); } @Test public void queryBlogForeach() { SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper = sqlSession.getMapper(BlogMapper.class); HashMap map = new HashMap(); ArrayList<String> ids = new ArrayList(); ids.add("6d6cb532858b4fb1b0204ce5b8601c9f"); map.put("ids", ids); mapper.queryBlogForeach(map); sqlSession.close(); } }

根据不同条件去拼接SQL语句，确实是一件很痛苦的事情，而使用动态SQL，就可以很灵活的去拼接这些SQL语句，彻底摆脱这种痛苦了。