【MyBatis】动态SQL，使用一些简单常用的动态SQL语句

什么是动态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语句，彻底摆脱这种痛苦了。