Mybatis 动态SQL的几种实现方法-FinClip官网

Mybatis 动态SQL的几种实现方法

网友投稿 1030 2023-03-16

Mybatis 动态SQL的几种实现方法

案例sql脚本

DROP DATABASE IF EXISTS `javacode2018`;

CREATE DATABASE `javacode2018`;

USE `javacode2018`;

DROP TABLE IF EXISTS t_user;

CREATE TABLE t_user(

id int AUTO_INCREMENT PRIMARY KEY COMMENT '用户id',

name VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名',

age SMALLINT NOT NULL DEFAULT 1 COMMENT '年龄'

) COMMENT '用户表';

INSERT INTO t_user VALUES (1,'路人甲Java',30),(2,'张学友',50),(3,'刘德华',50);

if元素

相当于java中的if判断，语法：

需要追加的sql

test的值为一个判断表达式，写法上采用OGNL表达式的方式，OGNL在struts2中用的比较多，本文暂时对ognl不做详细介绍，有兴趣的可以去查一下相关资料。

当test成立的时候，if体内部的sql会被拼接上。

如：

SELECT id,name,age FROM t_user

WHERE 1 = 1

AND id = #{id}

AND name = #{name}

AND age = #{age}

上面查询用户列表，参数为一个map，当map中id不为空的时候，将其作为条件查询，如果name不为空，将name也作为条件，如果age不为空，将age也作为条件进行查询

当只传入id的时候，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ?

当3个参数都传了，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ? AND name = ? AND age = ?

上面这种写法相对于java代码看起来是不是清爽了很多，也更方便维护，大家注意一下sql中有个WHERE 1=1，如果没有这个，上面单通过if元素就不好实现了，mybatis也有解决方案，稍后会说明。

choose/when/otherwise元素

这个相当于java中的if…else if…else，语法：

满足条件1追加的sql

满足条件2追加的sql

满足条件n追加的sql

都不满足追加的sql

choose内部的条件满足一个，choose内部的sql拼接就会结束。

otherwise属于可选的，当所有条件都不满足的时候，otherwise将起效。

如：

传入id、name、age作为条件，按顺序进行判断，如果id不为空，将id作为条件，忽略其他条件，如果id为空，会判断name是否为空，name不为空将name作为条件，如果name为空，再看看age是否为空，如果age不为空，将age作为条件。

SELECT id,name,age FROM t_user

WHERE 1 = 1

AND id = #{id}

AND name = #{name}

AND age = #{age}

如果id、name、age都传了，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ?

如果值传递了name、age，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND name = ?

name判断在age前面，所以name条件先匹配上了。

where元素

上面2个案例的sql中都有where 1=1这部分代码，虽然可以解决问题，但是看起来不美观，如果将where 1=1中1=1这部分干掉，上面的两个案例都会出问题，where后面会多一个AND符号，mybatis中已经考虑到这种问题了，属于通用性的问题，mybatis中通过where 元素来解决，当使用where元素的时候，mybatis会将where内部拼接的sql进行处理，会将这部分sql前面的AND 或者 OR给去掉，并在前面追加一个where，我们使用where元素来对上面的案例1进行改造，如下：

SELECT id,name,age FROM t_user

AND id = #{id}

AND name = #{name}

AND age = #{age}

where 1=1被替换成了where 元素。

当传入id、name的时候，where内部的sql会变成这样：

AND id = ? AND name = ?

mybatis会对上面这个sql进行处理，将前面的AND给去掉，并在前面追加一个where，变成了下面这样

where id = ? AND name = ?

案例2也用where改造一下，变成了下面这样：

SELECT id,name,age FROM t_user

AND id = #{id}

AND name = #{name}

AND age = #{age}

这下看起来是不是舒服很多了。

set元素

现在我们想通过用户id更新用户信息，参数为UserModel对象，对象中的属性如果不为空，就进行更新，我们可以这么写：

UPDATE t_user SET

name = #{name},

age = #{age},

AND id = #{id}

我们来看一下，当所有属性都传值了，sql变成了下面这样：

UPDATE t_user SET name = ?, age = ?, where id = ?

上面这个sql是有问题的，where前面多了一个逗号，得想办法将这个逗号去掉，这个逗号属于最后一个需要更新的字段后面的逗号，属于多余的，mybatis中提供了set元素来解决这个问题，将上面的代码改成下面这样：

UPDATE t_user

name = #{name},

age = #{age},

AND id = #{id}

我们将sql中的set去掉了，加了个set元素，set元素会对其内部拼接的sql进行处理，会将这部分sql前后的逗号给去掉并在前面加上set。

当传入id和age的时候，生成的sql：

UPDATE t_user SET age = ? where id = ?

trim元素

这个元素的功能比较强大，先看一下他的语法：

trim元素内部可以包含各种动态sql，如where、chose、sql等各种元素，使用trim包含的元素，mybatis处理过程：

1.先对trim内部的sql进行拼接，比如这部分sql叫做sql1

2.将sql1字符串前面的部分中包含trim的prefixOverrides指定的部分给去掉，得到sql2

3.将sql2字符串后面的部分中包含trim的suffixOverrides指定的部分给去掉，得到sql3

4.在sql3前面追加trim中prefix指定的值，得到sql4

5.在sql4后面追加trim中suffix指定的值，得到最终需要拼接的sql5

了解了这个过程之后，说明可以通过trim来代替where和set，我们使用trim来改造一下案例1，如下：

SELECT id,name,age FROM t_user

AND id = #{id}

AND name = #{name}

AND age = #{age}

注意上面的prefixOverrides的值的写法，如果有多个需要覆盖的之间用|进行分割，suffixOverrides写法和prefixOverrides的写法类似。

我们在用trim来改造一下上面的update中的，如下：

UPDATE t_user

name = #{name},

age = #{age},

AND id = #{id}

上面的prefixOverrides和suffixOverrides都设置的是逗号，表示trim内部的sql前后的逗号会被去掉，最后会在前面拼接一个prefix指定的set。

大家有兴趣的可以去看一下trim的java实现，代码下面这个类中：

org.apache.ibatis.scripting.xmltags.TrimSqlNode

实际上where和set的实现是继承了TrimSqlNode，where对应的java代码：

public class WhereSqlNode extends TrimSqlNode {

private static List prefixList = Arrays.asList("AND ","OR ","AND\n", "OR\n", "AND\r", "OR\r", "AND\t", "OR\t");

public WhereSqlNode(Configuration configuration, SqlNode contents) {

super(configuration, contents, "WHERE", prefixList, null, null);

}

set对应的 java代码：

public class SetSqlNode extends TrimSqlNode {

private static final List COMMA = Collections.singletonList(",");

public SetSqlNode(Configuration configuration,SqlNode contents) {

super(configuration, contents, "SET", COMMA, null, COMMA);

}

最后都是依靠TrimSqlNode来实现的。

foreach元素

相当于java中的循环，可以用来遍历数组、集合、map等。

语法

动态sql部分

collection：可以是一个List、Set、Map或者数组

item：集合中的当前元素的引用

index：用来访问当前元素在集合中的位置

separator：各个元素之间的分隔符

open和close用来配置最后用什么前缀和后缀将foreach内部所有拼接的sql给包装起来。

案例：in多值查询

我们对案例1做个改造，map中支持放入用户的id列表（ArrayList），对应的key为idList，然后支持多个用户id查询，此时我们需要用in来查询，实现如下：

SELECT id,name,age FROM t_user

AND id = #{id}

AND name = #{name}

AND age = #{age}

#{id}

大家看一下上面idList那部分判断，判断这个参数不为空，并且size()大于1，表示这个集合不为空，然后会走if元素内部的foreach元素。

比如我们传递的idList对应的是[1,2]，最后产生的sql如下：

SELECT id,name,age FROM t_user WHERE id in ( ? , ? )

案例：批量插入

传入UserModel List集合，使用foreach实现批量插入，如下：

INSERT INTO t_user (id,name,age) VALUES

(#{item.id}, #{item.name}, #{item.age})

测试用例

com.javacode2018.chat05.demo8.Demo8Test#insertBatch

@Test

public void insertBatch() throws IOException {

try (SqlSession sqlSession = this.sqlSessionFactory.openSession(true);) {

UserMapper mapper = sqlSession.getMapper(UserMapper.class)tCveusx;

List userModelList = new ArrayList<>();

for (int i = 0; i < 3; i++) {

userModelList.add(UserModel.builder().id(10 + i).name("mybatis-" + i).age(20 + i).build());

}

int count = mapper.insertBatch(userModelList);

log.info("{}", count);

}

传入了3个用户信息，运行输出

39:52.241 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - ==> Preparing: INSERT INTO t_user (id,name,age) VALUES (?, ?, ?) , (?, ?, ?) , (?, ?, ?)

39:52.317 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - ==> Parameters: 10(Integer), mybatis-0(String), 20(Integer), 11(Integer), mybatis-1(String), 21(Integer), 12(Integer), mybatis-2(String), 22(Integer)

39:52.327 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - <== Updates: 3

39:52.327 [main] INFO c.j.chat05.demo8.Demo8Test - 3

sql/include元素

这两2个元素一般进行配合使用，可以实现代码重用的效果。

sql元素可以用来定义一段动态sql，语法如下：

各种动态sql

其他地方需要使用的时候需要通过include关键字进行引入：

注意：refid值的写法，refid的值为mapper xml的namespace的值.sql的id，如果在同一个mapper中，namespace可以省略，直接写对应的sql的id就可以了，如：

来个案例

下面定义2个查询，他们的查询条件一样，最后将条件抽出来用sql元素定义了一个片段，然后进行共用。

AND id = #{id}

AND name = #{name}

AND age = #{age}

#{id}

SELECT id,name,age FROM t_user

SELECT count(*) FROM t_user

bind元素

bind元素允许我们通过ognl表达式在上下文中自定义一个变量，最后在动态sql中可以使用这个变量。

语法

案例

对sql、include中的案例进行扩展，添加一个按照用户名模糊查询，用户名在map中对应的key为likeName，主要修改上面sql片段部分，在sql中加入下面部分：

AND name like #{nameLike}

先判断传入的参数likeName是否不为空字符串，然后使用bind元素创建了一个变量nameLike，值为'%'+likeName.trim()+'%'。

对应的测试用例：

com.javacode2018.chat05.demo8.Demo8Test#getModelList

@Test

public void getModelList() throws IOException {

try (SqlSession sqlSession = this.sqlSessionFactory.openSession(true);) {

UserMapper mapper = sqlSession.getMapper(UserMapper.class);

Map paramMap = new HashMap<>();

paramMap.put("likeName","java");

List userModelList = mapper.getList1(paramMap);

log.info("{}", userModelList);

}

运行输出：

06:25.633 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Preparing: SELECT id,name,age FROM t_user WHERE name like ?

06:25.671 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Parameters: %java%(String)

06:25.690 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - <== Total: 1

06:25.691 [main] INFO c.j.chat05.demo8.Demo8Test - [UserModel(id=1, name=路人甲Java, age=31)]

注意输出中的第二部分，参数的值为%java%。

#和$

#和KaTeX parse error: Expected 'EOF', got '#' at position 15: 一般都是结合变量来使用，如：#̲{}、{}这种来进行使用。

#{}:为参数占位符？，即sql预编译，相当于使用jdbc中的PreparedStatement中的sql占位符，可以防止sql注入

${}:为字符串替换，即字符串拼接，不能访问sql注入。

#{}的用法上面已经有很多案例了，此处我们来一个${}的案例。

下面通过orderSql变量传入任意的排序sql，如下：

SELECT id,name,age FROM t_user

${orderSql}

传入值：

orderSql = "order by id asc,age desc"

最后运行产生的sql如下：

20:32.138 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Preparing: SELECT id,name,age FROM t_user order by id asc,age desc

20:32.173 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Parameters:

20:32.196 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - <== Total: 6

20:32.197 [main] INFO c.j.chat05.demo8.Demo8Test - [UserModel(id=1, name=路人甲Java, age=31), UserModel(id=2, name=张学友, age=50), UserModel(id=3, name=刘德华, age=50), UserModel(id=10, name=mybatis-0, age=20), UserModel(id=11, name=mybatis-1, age=21), UserModel(id=12, name=mybatis-2, age=22)]

mybatis会对$包含部分进行sql替换。

智慧屏第三方App安装如何提升用户体验与功能拓展

1030 2023-03-16

Mybatis 动态SQL的几种实现方法

Flutter开发App的未来及其在各行业的应用潜力分析

探索flutter框架开发的app在移动应用市场的潜力与挑战

智慧屏第三方App安装如何提升用户体验与功能拓展

最近发表

更多内容

小程序SDK

Finclip技术文档

小程序开发

小程序容器

小程序框架

Finclip小程序平台

Finclip用户投稿

车联网

推荐文章

小程序SDK是什么意思？小程序sdk和插件有什么区别？

小程序支付功能怎么实现？

企业app开发流程是什么？

app运营模式有哪些？

小程序多端引流怎么做？

小程序生态分析的机会和威胁

Flutter入门这一篇效率文章就够了

原生与跨平台解决方案分析,跨平台软件开发技术方案

热更新技术：让软件更新变得更加轻松快速

解决方案

银行解决方案

证券解决方案

互联网解决方案

政企OA解决方案

科技解决方案

loT解决方案

信任解决方案

热评文章

AppCan:基于混合模式的移动应用开发,移动混合模

Hybrid App混合模式开发的了解

小程序容器技术助力券商数字营销突围，小程序容器化的意

用mpvue开发微信小程序基础知识（vue.js开发

小程序多端框架全面测评对比，强烈推荐！

券商app架构 - 解析券商应用程序的构建与设计