mybatis学习笔记（九）。动态SQL，if，where，set，choose（when、otherwise），Foreach，SQL片段讲解。

2023-09-29 21:38:46

文章目录

动态SQL
1. 数据库设计
2. 创建Mybatis基础工程
3. 动态SQL之if
4. 动态SQL常用标签
5. SQL片段
6. 小结

动态SQL

官方文档：

https://mybatis.net.cn/dynamic-sql.html

什么是动态SQL：动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句。

我们之前写的 SQL 语句都比较简单，如果有比较复杂的业务，我们需要写复杂的 SQL 语句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引号，空格等缺失可能都会导致错误。

那么怎么去解决这个问题呢？这就要使用 mybatis 动态SQL，通过 if, choose, when, otherwise,trim, where, set, foreach等标签，可组合成非常灵活的SQL语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率。

1. 数据库设计

新建一个数据库表：blog
字段：id，title，author，create_time，views

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

2. 创建Mybatis基础工程

创建实体类

Blog.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;  // 和数据库字段不一致，可以开启驼峰命名转换
    private int views;
}

在核心配置文件中开启驼峰命名转换，使用导包的方式扫描

<settings>
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<mappers>
    <!--<mapper class="com.xxc.dao.TeacherMapper"/>
        <mapper class="com.xxc.dao.StudentMapper"/>-->
    <package name="com.xxc.dao"/>
</mappers>

创建工具类

IDUtils.java

public class IDUtils {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-","");
    }
}

uuid生成不唯一的字符串，作为主键使用。

编写插入数据接口

BlogMapper.java

public interface BlogMapper {
    int addBlog(Blog blog);
    List<Blog> queryBlogIf(Map map);
}

编写xml文件

<?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.xxc.dao.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into blog (id,title,author,create_time,views)
        values (#{id} ,#{title} ,#{author} ,#{createTime},#{views});
    </insert>
</mapper>

编写测试方法

MyTest.java

public class MyTest {
    static Logger logger = Logger.getLogger(MyTest.class);
    @Test
    public void addBlog(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IDUtils.getId());
        blog.setTitle("Mybatis如此简单");
        blog.setAuthor("江南雨");
        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如此简单");
        blog.setViews(1000);
        mapper.addBlog(blog);
        blog.setId(IDUtils.getId());
        blog.setTitle("微服务如此简单");
        blog.setViews(2000);
        mapper.addBlog(blog);
        sqlSession.close();
    }
}

3. 动态SQL之if

需求：从博客表中查询数据，如果不传任何参数，查询所有，传入参数，按照参数查询。

改造我们的BlogMapper.xml

<?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.xxc.dao.BlogMapper">
    <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>
</mapper>

编写测试方法

MyTest.java

public class MyTest {
    static Logger logger = Logger.getLogger(MyTest.class);
    @Test
    public void queryBlogIf(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        List<Blog> blogs = mapper.queryBlogIf(map);
        for (Blog blog : blogs) {
            logger.debug(blog);
        }
        sqlSession.close();
    }
}

发现：当map为空时，查询了所有的数据。

发现：传入参数之后，查询出了满足要求的数据。

4. 动态SQL常用标签

4.1 where

在实际的开发中，这种写法是不规范的，不能写1=1的情况。

<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>

我们做如下改造即可

<select id="queryBlogIf" 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>

说明：这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返
回的内容是以AND 或OR 开头的，则它会剔除掉。【这是我们使用的最多的案例】

4.2 set

同理，上面的对于查询 SQL 语句包含 where 关键字，如果在进行更新操作的时候，含有 set 关键词，

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>

注意：注意set是用的逗号隔开

测试

public class MyTest {
    static Logger logger = Logger.getLogger(MyTest.class);
    @Test
    public void updateBlog(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String, String> map = new HashMap<String, String>();
        map.put("title","动态SQL");
        map.put("author","江南");
        map.put("id","7d83bc1ae6a34171b5a1db74c708ea0f");
        mapper.updateBlog(map);
        sqlSession.close();
    }
}

说明：set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号

4.3 choose（when、otherwise）

有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose
标签可以解决此类问题，类似于 Java 的 switch 语句

BlogMapper.xml

<select id="queryBlogChoose" 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>

测试

public class MyTest {
    static Logger logger = Logger.getLogger(MyTest.class);
    @Test
    public void queryBlogChoose(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String, String> map = new HashMap<String, String>();
        map.put("title","动态SQL");
        map.put("author","江南");
        mapper.queryBlogChoose(map);
        sqlSession.close();
    }
}

说明：虽然写了好几个条件，但是由于使用了choose，他只会选择其中一个最先符合sql语句的条件进行查询。

4.4 Foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）。

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符，看它多智能！

为了演示效果，先将数据库中前三个数据的id修改为1,2,3,4；

需求：我们需要查询 blog 表中 id 分别为1,2,3的博客信息。

方式一：

思考：需求相当于写了这个sql，select * from blog where id in(1,2,3);那我们将这个sql在xml中拼接出来即可。

BlogMapper.xml

<?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.xxc.dao.BlogMapper">
    <select id="queryBlogForeach" parameterType="map" resultType="blog">
        select * from blog
        <where>
            id in
            <foreach collection="ids" item="id" open="(" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>
</mapper>

说明：collection：接收集合。item：每次遍历得到的参数。open：以什么开始。close：以什么结束。separator：分隔符。

测试

public class MyTest {
    static Logger logger = Logger.getLogger(MyTest.class);
    @Test
    public void queryBlogForeach(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String, Object> map = new HashMap<String, Object>();
        ArrayList list = new ArrayList();
        list.add(1);
        list.add(2);
        list.add(3);
        map.put("ids",list);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            logger.debug(blog);
        }
        sqlSession.close();
    }
}

注意：这里的map中放入的是一个集合，使用list。

方式二：

思考：需求相当于写了这个sql，select * from blog where (id = 1 or id = 2 or id = 3);那我们将这个sql在xml中拼接出来即可。

BlogMapper.xml

<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <foreach collection="ids" item="id" open="(" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>

测试

public class MyTest {
    static Logger logger = Logger.getLogger(MyTest.class);
    @Test
    public void queryBlogForeach(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String, Object> map = new HashMap<String, Object>();
        ArrayList list = new ArrayList();
        list.add(1);
        list.add(2);
        list.add(3);
        map.put("ids",list);
        List<Blog> blogs = mapper.queryBlogForeach(map);
        for (Blog blog : blogs) {
            logger.debug(blog);
        }
        sqlSession.close();
    }
}

5. SQL片段

将多个sql中重复的部分提取出来。增强sql语句的复用性。

<mapper namespace="com.xxc.dao.BlogMapper">
    <sql id="if-title-author">
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </sql>
    <select id="queryBlogIf" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <include refid="if-title-author"></include>
        </where>
    </select>
</mapper>

sql标签和include标签。

注意：①、最好基于单表来定义 sql 片段，提高片段的可重用性
②、在 sql 片段中不要包括 where

6. 小结

其实动态 sql 语句的编写往往就是一个拼接的问题，为了保证拼接准确，我们最好首先要写原生的 sql 语句出来，然后在通过 mybatis 动态sql 对照着改，防止出错。多在实践中使用才是熟练掌握它的技巧。

码农公寓

文章目录

动态SQL

1. 数据库设计

2. 创建Mybatis基础工程

3. 动态SQL之if

4. 动态SQL常用标签

4.1 where

4.2 set

4.3 choose（when、otherwise）

4.4 Foreach

5. SQL片段

6. 小结

相关文章