MyBatis：NO4 —— 动态SQL_实现findby查询-优快云博客

本文链接：https://blog.youkuaiyun.com/note_hsy/article/details/107325932

本文深入探讨MyBatis框架中的动态SQL特性，包括if、choose、where、set、foreach等元素的应用，以及如何通过SQL脚本提升代码复用性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.概述

动态SQL：通过不同的查询条件，生成不同的SQL语句。防止业务需要使用复杂的SQL语句进行拼接而造成的错误

官网描述：
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你
就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意
去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语
句中的强大的动态 SQL 语言得以改进这种情形。
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有
很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。
MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

常用标签

if
choose (when, otherwise)
trim (where, set)
foreach

2.测试环境搭建

2.1新建数据库表

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.2MyBatis工程架构

在这里插入图片描述

2.3工具类的编写

import java.util.UUID;
/**
 * 随机生成ID
 */
public class IDUtils {
    public static String getID(){
        return UUID.randomUUID().toString().replace("-" +
                "","");
    }
}

import java.io.IOException;
import java.io.InputStream;
public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            InputStream in = Resources.getResourceAsStream("SqlMapperConfig.xml");
            sqlSessionFactory  = new SqlSessionFactoryBuilder().build(in);
        } catch (IOException e) {
        }
    }
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }
}

2.4实体类Blog.java

package pojo;
import java.util.Date;
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
    public Blog() {
    }
    public Blog(String id, String title, String author, Date createTime, int views) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.createTime = createTime;
        this.views = views;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getTitle() {
        return title;
    }
    public void setTitle(String title) {
        this.title = title;
    }
    public String getAuthor() {
        return author;
    }
    public void setAuthor(String author) {
        this.author = author;
    }
    public Date getCreateTime() {
        return createTime;
    }
    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }
    public int getViews() {
        return views;
    }
    public void setViews(int views) {
        this.views = views;
    }
    @Override
    public String toString() {
        return "Blog{" +
                "id='" + id + '\'' +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", createTime=" + createTime +
                ", views=" + views +
                '}';
    }
}

2.5mapper接口以及xml映射文件

public interface BlogMapper {
}

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

2.6核心配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <mappers>
        <mapper class="dao.BlogMapper"></mapper>
    </mappers>
</configuration>

2.7新增blog

BlogMapper

package dao;

import pojo.Blog;
import java.util.List;
import java.util.Map;

public interface BlogMapper {
    //添加博客
    int addBlog(Blog blog);
}

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="dao.BlogMapper">
<!--    插入数据-->
    <insert id="addBlog" parameterType="Blog">
        insert into db.blog (id, title, author, create_time, views)
        values(#{id}, #{title}, #{author}, #{createTime}, #{views})
    </insert>
</mapper>

MyTest.java

@Test
    public void addBlogTest(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog1 = new Blog();
        blog1.setId(IDUtils.getID());
        blog1.setTitle("JavaEE");
        blog1.setAuthor("haosiyu");
        blog1.setCreateTime(new Date());
        blog1.setViews(1000);
        mapper.addBlog(blog1);


        Blog blog2 = new Blog();
        blog2.setId(IDUtils.getID());
        blog2.setTitle("JavaWEB");
        blog2.setAuthor("haosiyu");
        blog2.setCreateTime(new Date());
        blog2.setViews(9999);
        mapper.addBlog(blog2);


        Blog blog3 = new Blog();
        blog3.setId(IDUtils.getID());
        blog3.setTitle("JavaSE");
        blog3.setAuthor("haosiyu");
        blog3.setCreateTime(new Date());
        blog3.setViews(8989);
        mapper.addBlog(blog3);


        Blog blog4 = new Blog();
        blog4.setId(IDUtils.getID());
        blog4.setTitle("Java");
        blog4.setAuthor("haosiyu");
        blog4.setCreateTime(new Date());
        blog4.setViews(2345);
        mapper.addBlog(blog4);


        sqlSession.close();
    }

3.if语句

根据作者名字和博客名字来查询博客！
如果作者名字为空，那么根据博客名字查询，反之，则根据作者名来查询

接口：BlogMapper

package dao;

import pojo.Blog;
import java.util.List;
import java.util.Map;

public interface BlogMapper {
    //添加博客
    int addBlog(Blog blog);
    //查询
    List<Blog> findByIf(Map map);

    List<Blog> findByWhere(Map map);

    List<Blog> findByChoose(Map map);

    int updateBySet(Map map);

    List<Blog> findBySql(Map map);

    List<Blog> findByForeach(Map map);

}

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="dao.BlogMapper">
<!--    使用if标签查询-->
    <select id="findByIf" parameterType="map" resultType="Blog">
        select * from db.blog where
        <if test="title != null">
            and title=#{title}
        </if>
        <if test="author !=null">
            and author=#{author}
        </if>
    </select>
</mapper>

MyTest.java

    @Test
    public void findByIfTest(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map map = new HashMap();
        //map.put("title","JavaEE");
        map.put("author","haosiyu");
        //List<Blog> blogs = mapper.findByIf(map);
        List<Blog> blogs = mapper.findBySql(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

这样写我们可以看到，如果 author 等于 null，那么查询语句为 select * from user where title=#{title}, 但是如果title为空呢？那么查询语句为 select * from user where and author=#{author}，这是错误的 SQL 语句，如何解决呢？请看下面的 where 语句！

4.where语句

where标签里面包含的标签如果有返回值就会自动补全一个where，如果该返回值紧跟where之后并且包含and或者or，where会自动删除and或者or

BlogMapper.xml

<!--    使用where标签-->
    <select id="findByWhere" parameterType="map" resultType="Blog">
        select * from db.blog
        <where>
            <if test="title != null">
                title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
            <if test="views != null">
                and views = #{views}
            </if>
        </where>
    </select>

MyTest.java

 @Test
    public void findByWhereTest(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        Map map = new HashMap();
        //map.put("title","Java");
        map.put("author","haosiyu");
        //map.put("views","2345");

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        List<Blog> blogs = mapper.findByWhere(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

5.choose

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

BlogMapper.xml

<!--    使用choose、when、otherwise标签-->
    <select id="findByChoose" parameterType="map" resultType="Blog">
        select * from db.blog where
<!--        <where>-->
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author != null">
                    author = #{author}
                </when>
                <otherwise>
                    views = #{views}
                </otherwise>
            </choose>
<!--        </where>-->
    </select>

MyTest.java

    @Test
    public void findByChooseTest(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        Map map = new HashMap();
        //map.put("title","Java");
        map.put("author","haosiyu");
        map.put("views","1000");

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        List<Blog> blogs = mapper.findByChoose(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

6.set

更新操作中SQL包含set

BolgMapper.xml

<!--    使用set标签-->
    <update id="updateBySet" parameterType="map">
        update db.blog
        <set>
            <if test="title != null">title = #{title},</if>
            <if test="author != null">author = #{author},</if>
            <if test="views != null">views = #{views},</if>
        </set>
        where id=#{id}
    </update>

MyTest.java

    public void findBySetTest(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        Map map = new HashMap();
        map.put("title","JavaWEB666");
        map.put("author","haosiyu666");
        //map.put("views","1000");
        map.put("id","05ad860067cd4ce289f289ef36429b56");

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        mapper.updateBySet(map);

        sqlSession.close();
    }

7.SQL脚本

当某段SQL语句我们使用的比较多时，为了提高代码的复用性，我们可以采用SQL脚本
最好基于单表来定义SQL脚本，脚本中不要有where

BlogMapper.xml

<!--    使用SQL脚本-->
    <select id="findBySql" parameterType="map" resultType="Blog">
        select * from db.blog
        <where>
            <include refid="find_Sql"></include>
        </where>
    </select>

    <sql id="find_Sql">
        <if test="title != null">
            and title=#{title}
        </if>
        <if test="author !=null">
            and author=#{author}
        </if>
    </sql>

8.foreach

同时查找多条记录，相当于java的循环

BlogMapper.xml

<!--    使用foreach标签-->
    <select id="findByForeach" resultType="Blog" parameterType="map">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="(" close=")" separator="or">
                id =#{id}
            </foreach>
        </where>
    </select>

MyTest.java

    @Test
    public void findByForeach(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();

        HashMap map = new HashMap();
        ArrayList<Integer> list = new ArrayList<>();
        list.add(1);
        list.add(2);
        list.add(3);
        map.put("ids",list);
       // System.out.println(map);
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        List<Blog> blogs = mapper.findByForeach(map);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }
        sqlSession.close();
    }

9.小结

其实动态 sql 语句的编写往往就是一个拼接的问题，为了保证拼接准确，我们最好首先要写原生的 sql 语句出来，然后在通过 mybatis 动态sql 对照着改，防止出错。