08-Mybatis基础之动态SQL

最新推荐文章于 2024-11-13 16:46:51 发布
最新推荐文章于 2024-11-13 16:46:51 发布
阅读量542 收藏
点赞数
分类专栏： Mybatis 文章标签： sql 数据库 java mybatis
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43155804/article/details/122549330
版权

动态SQL

1、动态SQL介绍

动态SQL指的是根据不同的查询条件 , 生成不同的SQL语句.

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

使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

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

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

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

2、环境搭建

  1. mybatis01数据库中新建一个数据表：blog；字段内容包括：id，title, author，create_time，views：
CREATE TABLE `blog` (
`id` varchar(50) NOT NULL COMMENT '博客id',
`title` varchar(200) NOT NULL COMMENT '博客标题',
`author` varchar(20) NOT NULL COMMENT '博客作者',
`create_time` datetime NOT NULL COMMENT '创建时间',
`views` int(30) NOT NULL COMMENT '浏览量'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1. 创建基础的maven子模块：mybatis-study-07,导入依赖，配置文件，创建工具类，实体类，Mapper,Mapper.xml等；

  • 创建IDUtils工具类，用户获取随机生成的UUID，作为数据库表的id值：

  • 编写blog对应的实体类：

package com.kevin.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;

/**
 * @author : kevin ding
 * @date : 2022/1/16 17:56
 * @description : blog数据库表对应的实体类
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

  • Mapper及对应的Mapper.xml文件（省略）

  • Mybatis核心配置文件中，开启字段名的驼峰自动转换：解决实体类属性名和数据库字段名不一致：

<settings>
   <setting name="mapUnderscoreToCamelCase" value="true"/>
   <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
  1. 初始化数据库表：插入数据（通过编写SQL进行插入数据）
// 初始化 往Blog中插入数据
int addBlogs(Blog blog);

Mapper.xml编写sql：

<insert id="addBlogs" parameterType="blog">
    insert into blog (id, title, author, create_time, views) values (#{id},#{title},#{author},#{createTime},#{views})
</insert>

测试 插入：

@Test
public void addBlogs(){
    SqlSession session = MybatisUtils.getSqlSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);

    Blog blog = new Blog();
    blog.setId(IDUtils.getRandomUuId());
    blog.setTitle("Mybatis基础不算太难");
    blog.setAuthor("kevin");
    blog.setCreateTime(new Date());
    blog.setViews(9999);

    mapper.addBlogs(blog);

    blog.setId(IDUtils.getRandomUuId());
    blog.setTitle("Java还算可以");
    blog.setAuthor("隔壁老王");
    blog.setCreateTime(new Date());
    mapper.addBlogs(blog);

    blog.setId(IDUtils.getRandomUuId());
    blog.setTitle("Spring打好基础");
    mapper.addBlogs(blog);

    blog.setId(IDUtils.getRandomUuId());
    blog.setTitle("springBoot快速开发");
    mapper.addBlogs(blog);
    session.commit();
    session.close();
}

3、动态SQL

3.1 if

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

根据动态SQL的概念，当需求中没有明确给出按照固定的方式进行查询时，需要在sql语句中完成动态的适配，即传进去什么参数能够适配，就根据哪个参数进行查询。

  1. BlogMapper接口：
// 动态SQL的if查询，如果字段为不为空，根据字段查询
List<Blog> queryBlogsByIf(Map map);
  1. BlogMapper.xml编写sql：

一般情况下的写法为：

方式1：如果需要在每个test标签中的首个单次为and时，需要在sql中where关键字最后加上一个恒成立的条件“1=1”：

<select id="queryBlogsByIf" resultType="blog" parameterType="map">
    select * from blog where 1 = 1
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>

方式2，直接去掉where后面的恒等条件，并去掉第一个if标签中的and连接。

<select id="queryBlogsByIf" resultType="blog" parameterType="map">
    select * from blog where
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>
  1. 测试：
@Test
public void queryBlogsByIf1(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map map = new HashMap<String, Object>();
    map.put("title", "Mybatis基础不算太难");
    map.put("author", "kevin");
    List<Blog> blogs = mapper.queryBlogsByIf(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

测试结果显示正确，但是上述的两种方法有一定的局限性：

方法1：在sql语句中出现1=1不符合实际场景和规范

方法2：当第一个标签中的内容不成立时，此时的sql语句变为：select * from blog where and author = #{author}，此时sql语句总出现了语法错误.

优化方法，使用where标签将其括起来；

3.2 where

对上述9.3.1需求中的代码进行优化,使其更加适用于多个场景：

  1. BlogMapper接口编写新的抽象方法：
// 动态SQL的if查询优化 where标签
List<Blog> queryBlogsByIf2(Map map);
  1. BlogMapper.xml进行sql编写，完成对9.3.1的优化：

将where关键字替换成一个标签，里面的每一个if都可以在开头添加一个and连接词，

<select id="queryBlogsByIf2" resultType="blog" parameterType="map">
    select * from blog
    <where>
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>

</select>
  1. 测试：
@Test
public void queryBlogsByIf2(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map map = new HashMap<String, Object>();
    map.put("title", "Spring打好基础");
    map.put("author", "隔壁老王");
    List<Blog> blogs = mapper.queryBlogsByIf2(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}
  1. 运行结果：
    请添加图片描述

测试中发现，即使是当第一个条件匹配时，where标签会根据语境，自动进行去除第一个and连接词；这个“where”标签会知道如果它包含的标签中有返回值的话，它就插入一个‘where’。此外，如果标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

3.3 set

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

  1. BlogMapper中编写抽象方法：
// set标签更新数据库
int updateBlog(Map map);
  1. BlogMapper.xml编写sql语句：
<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>
  1. 测试：
@Test
public void updateBlog(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map map = new HashMap<String, Object>();
    map.put("title", "Mybatis从基础发到入门");
    map.put("author", "kevin ding");
    map.put("id", "e0e2cea2537e4e9882c500826174de10");
    int res = mapper.updateBlog(map);
	// 修改数据库表 需要先提交事务
    sqlSession.commit();
    sqlSession.close();
}

4.执行结果：
Set标签的执行结果

3.4 choose

然而，在实际的开发中，我们不想用到所有的查询条件，可能只是想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch…case 语句，当遇到第一个满足条件的之后，后面的不再进行匹配

  1. BlogMapper中编写抽象方法：
// choose标签 单个选择（类似于switch..case）
List<Blog> queryBlogByChoose(Map map);
  1. BlogMapper.xml编写sql语句：
<select id="queryBlogByChoose" resultType="blog" parameterType="map">
        select * from blog
        <where>
            <choose>
                <when test="title != null">
                    title = #{title}
                </when>
                <when test="author != null">
                    author = #{author}
                </when>
                <otherwise>
                    views = #{views}
                </otherwise>
            </choose>
        </where>
    </select>
  1. 测试：
@Test
public void queryBlogByChoose(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map map = new HashMap<String, Object>();
    // map.put("title", "Mybatis从基础发到入门");
    map.put("author", "隔壁老王");
    map.put("views", "9999");
    map.put("id", "e0e2cea2537e4e9882c500826174de10");
    List<Blog> blogs = mapper.queryBlogByChoose(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}
  1. 测试结果：
  • 当map中包含所有的参数字段时，从如下结果的sql语句可以看出是执行的第一个参数title匹配上了：

在这里插入图片描述

  • 当将title的参数一行注释之后，map参数中的元素，会首先匹配到author，sql中的执行结果为根据author进行查询
    在这里插入图片描述

  • 当将author参数一行也注释掉之后，前两个没有匹配，则会自动匹配otherwise标签中的views，执行结果为根据views进行查询：
    在这里插入图片描述

3.5 SQL片段

​ **背景：**有些时候，在一个Mapper.xml中需要重复使用某一部分固定的sql语句，即部分sql语句的重复使用率较高，为了增加代码的重用性，简化代码，我们可以将这些sql语句进行抽取，使用的时候直接调用，有点类似于java中的方法抽取。

	SQL片段的抽取，使用sql标签，id属性为对其起的名字，其他部分调用时，直接引用该id值即可（java方法抽取，给方法起个名字，直接方法名调用）：

在上述动态sql中包含重复的代码：对title和author的判断，可以将其抽取出来作为片段：

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

测试：

  1. BlogMapper接口中编写一个抽象方法：
// common-sql 查询
List<Blog> queryBlogByCommonSql(Map map);
  1. BlogMapper.xml中编写sql语句，使用sql片段：
<!--通过使用include标签来引用sql片段，其中的refid属性值为所引用的sql片段的id值-->
<select id="queryBlogByCommonSql" resultType="blog" parameterType="map">
    select * from blog
    <where>
        <include refid="common-sql"></include>
    </where>
</select>
  1. 测试类：
@Test
public void queryBlogByCommonSql(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    map.put("title", "springBoot快速开发");
    map.put("author", "隔壁老王");
    List<Blog> blogs = mapper.queryBlogByCommonSql(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}
  1. 测试结果：成功执行！

注意：

  • 在使用sql片段时，最好基于单表来定义 sql 片段，可以提高片段的可重用性

  • 在 sql 片段中不要包括 where条件，只需在sql片段中写if标签的内容

3.6 foreach

背景：当需要根据某一字段属性值进行查询时，该字段的值满足多个条件时，即将满足的值存放于一个列表或者另一个sql的查询结果中，以list形式出现时，可以使用foreach进行遍历，完成动态查询。

譬如：将本案例中数据表的id字段的值按1,2,3进行排列存储，查询表中id值为1,2,3中任一个的数据信息:

普通的SQL语句：select * from blog where id = 1 or id=2 or id=3; 或者：select * from blog where id in (1,2,3);

动态sql语句的写法：

  1. 编写抽象方法：
// foreach查询
List<Blog> queryBlogByForeach(Map map);
  1. BlogMapper.xml中编写sql：
<!--foreach 标签中
   collection:指定输入对象中的集合属性
   item:每次遍历生成的对象
   open:开始遍历时的拼接字符串
   close:结束时拼接的字符串
   separator:遍历对象之间需要拼接的字符串
   select * from blog where 1=1 and (id=1 or id=2 or id=3)
 -->
<select id="queryBlogByForeach" resultType="blog" parameterType="map">
    select * from blog
    <where>
        <foreach collection="idList" item="id" open="and (" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>
  1. 测试：
@Test
public void queryBlogByForeach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    ArrayList<Integer> idList = new ArrayList<>();
    // 查询id为1,3,6的信息
    idList.add(1);
    idList.add(3);
    idList.add(6);
    map.put("idList", idList);
    List<Blog> blogs = mapper.queryBlogByForeach(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}
  1. 测试结果：拼接出了sql语句，并查询到了正确的结果：
    在这里插入图片描述

4、总结

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

Kevin-Ding
关注
专栏目录
Fluent-MyBatis
简简单单Onlinezuozuo
08-18 519
何为 Fluent Mybatis？Fluent Mybatis, 是一款 Mybatis 语法增强框架, 综合了 Mybatis Plus, Dynamic SQL, JPA 等框架特性和优点。
Mybatis 动态SQL(二)if语句与where标签sql标签
qq_41359998的博客
01-27 775
Mybatis之if 以一个Demo 来方便大家理解 需求：根据作者名字和博客名字来查询博客！如果作者名字为空，那么只根据博客名字查询，反之，则根据作者名来查询 1.编写接口类 //需求1 List<Blog> queryBlogIf(Map map); 2.编写SQL语句 <sql id="if-title-author"> <if test="title!=null"> and title=#{title}
MyBatis：动态SQL
adsdaas的博客
03-17 263
动态SQL 官方文档 介绍     什么是动态SQL：动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句. 官网描述： MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号...
MyBatis笔记(6)：动态SQL/狂神说
mingyuli的博客
02-20 864
·
Mybatis学习之动态Sql
weixin_44362089的博客
11-10 3282
动态Sql的详细介绍使用，Mybatis框架，PageHelper分页插件
Java - MyBatis中的动态SQL是什么意思？
了解➔熟悉➔掌握➔精通
03-20 4万+
分享一个大牛的人工智能教程。零基础！通俗易懂！风趣幽默！希望你也加入到人工智能的队伍中来！请点击http://www.captainbed.net 对于一些复杂的查询，我们可能会指定多个查询条件，但是这些条件可能存在也可能不存在，例如在58同城上面找房子，我们可能会指定面积、楼层和所在位置来查找房源，也可能会指定面积、价格、户型和所在位置来查找房源，此时就需要根据用户指定的条件动态生成SQL语...
Mybatis实用教程之XML实现动态sql
小张的博客
12-02 1824
Mybatis实用教程之XML实现动态sql
Mybatis-Plus：实现自定义SQL
热门推荐
拾一二的博客
06-26 4万+
Mybatis-Plus（以下简称MBP）的初衷是为了简化开发，而不建议开发者自己写SQL语句的；但是有时客户需求比较复杂，仅使用MBP提供的Service，Mapper与Wrapper进行组合，难以实现可以需求；这时我们就要用到自定义的SQL了。Java 开发使用 mybatis-plus 来执行 sql 操作，往往比 mybatis 能够省时省力，因为 mybatis-plus 封装了很多常用的接口。但对于一些更为复杂的查询来说，mybatis-plus 也相形见绌，还得需要我们自定义 sql 语句。
MyBatis基础 --条件查询与动态SQL
十八岁讨厌编程的博客
04-28 1488
文章目录配置文件完成增删改查条件查询sql语句设置多个参数变量名对应关系梳理多条件 - 动态条件查询单条件 - 动态条件查询 配置文件完成增删改查 条件查询 场景： 分析： 当前状态使用精准查询 企业名称使用模糊查询 品牌名称使用模糊查询 且他们同时成立，故使用and连接 sql语句设置多个参数 步骤详解： ①编写接口方法 在 BrandMapper 接口中定义多条件查询的方法。 而该功能有三个参数，我们就需要考虑定义接口时，参数应该如何定义。Mybatis针对多参数有多种实现 使用 @Para
MyBatis基础：动态SQL与条件判断
AI天才研究院
01-25 952
1.背景介绍 MyBatis是一款优秀的Java持久层框架，它可以简化数据库操作，提高开发效率。在MyBatis中，动态SQL和条件判断是非常重要的功能，它可以让开发者更加灵活地编写SQL查询语句。在本文中，我们将深入探讨MyBatis的动态SQL与条件判断功能，并提供实际应用场景和最佳实践。 1. 背景介绍 MyBatis是一款基于Java的持久层框架，它可以简化数据库操作，提高开发效率。...
# MybatisLog sql日志 Free-Mybatis 插件
06-01
MybatisLog是一款针对Mybatis框架的日志插件，Free-Mybatis则是它的扩展，两者结合为开发者提供了强大的SQL日志追踪功能。在开发过程中，理解并优化SQL查询是提升应用程序性能的关键步骤，而MybatisLog与Free-...
高级软件人才培训专家-day08-Mybatis入门
03-10
- **MyBatis基础增删改查**：详细介绍如何通过 MyBatis 实现数据库的基本操作。 - **MyBatis动态SQL**：讲解 MyBatis 中如何根据条件动态生成 SQL 语句。 #### 四、MyBatis快速入门案例 ##### 4.1 需求分析 需求...
better-mybatis-generator
02-06
"better-mybatis-generator"是一个专为提升开发效率而设计的工具，它主要针对MyBatis框架，能够在IntelliJ IDEA的数据库工具中直接对数据库表进行操作，自动生成与之对应的MyBatis代码文件。这一功能极大地简化了...
高级软件人才培训专家-day08-Mybatis
03-10
### 五、MyBatis 动态 SQL - **概念**：动态 SQL 允许在运行时动态构建 SQL 语句，提高了代码的灵活性。 - **示例**： - `<if>` 用于判断某个条件是否成立。 - `<choose>`、`<when>` 和 `<otherwise>` 用于实现 ...
auto-mybatis-new.zip
12-21
7. **动态SQL**：类似于Mybatis的、、、等标签，Mybatis-Plus同样支持，可以进行动态SQL拼接。 8. **乐观锁**：通过在实体类上添加Version字段，Mybatis-Plus能实现乐观锁机制，防止并发更新时的数据冲突。 9. **...
如何在Linux中使用Cron定时执行SQL任务
Mr_Wanter
11-08 659
linux crontab 定时执行sql脚本。
sql注入之二次注入（sqlilabs-less24）
CSDNRomance的博客
11-13 305
二阶注入相对隐蔽，因为其恶意代码不会在第一步直接执行，所以在开发和测试时，务必要考虑输入数据在不同阶段的调用方式，以避免此类攻击。
SQL，力扣题目1107，每日新用户统计
m0_59659684的博客
11-13 137
activity 列是 ENUM 类型，可能取 ('login', 'logout', 'jobs', 'groups', 'homepage') 几个值之一。编写解决方案，找出从今天起最多 90 天内，每个日期该日期首次登录的用户数。2、思路：找出每个用户第一天登录的日期之后再进行过滤90天以内的日期。2、找出第一天登录日期与指定日期间隔90天以内的日期，并计算人数。1、CTE表达式 + 窗口函数 + datediff()1、找出用户第一天登录的日期。该表可能有重复的行。
labview中连接sql server数据库查询语句
最新发布
weixin_70653117的博客
11-13 216
日常记录
mybatis-plus动态sql
08-18
Mybatis-plus是在Mybatis基础上进行封装的一个框架，它简化了平时开发过程中对常用接口的调用，可以省去一些繁琐的操作。然而，对于一些更为复杂的查询，Mybatis-plus可能无法满足需求，此时就需要我们自定义SQL语句...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值