Mybatis-plus条件构造器和常用接口

旧城等待， 2021-07-24 14:22 545阅读 0赞

**目录**

一 wapper介绍

二 查询方式

三 测试用例

1 ge、gt、le、lt、isNull、isNotNull

2 eq、ne

3 between、notBetween

4 like、notLike、likeLeft、likeRight

5 in、notIn、inSql、notinSql、exists、notExists

6 or、and

7 lambda表达式

8 orderBy、orderByDesc、orderByAsc

9 set、setSql

--------------------

# 一 wapper介绍 #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70][]

Wrapper ： 条件构造抽象类，最顶端父类

AbstractWrapper ： 用于查询条件封装，生成 sql 的 where 条件

QueryWrapper ： 查询条件封装

UpdateWrapper ： Update 条件封装

AbstractLambdaWrapper ： 使用Lambda 语法

LambdaQueryWrapper ：用于Lambda语法使用的查询Wrapper

LambdaUpdateWrapper ： Lambda 更新封装Wrapper

# 二 查询方式 #

<table> 
 <tbody> 
 <tr> 
 <td> 查询方式 </td> 
 <td> 说明 </td> 
 </tr> 
 <tr> 
 <td> setSqlSelect </td> 
 <td> 设置 SELECT 查询字段 </td> 
 </tr> 
 <tr> 
 <td> where </td> 
 <td> WHERE 语句，拼接 + WHERE 条件 </td> 
 </tr> 
 <tr> 
 <td> and </td> 
 <td> AND 语句，拼接 + AND 字段=值 </td> 
 </tr> 
 <tr> 
 <td> andNew </td> 
 <td> AND 语句，拼接 + AND (字段=值) </td> 
 </tr> 
 <tr> 
 <td> or </td> 
 <td> OR 语句，拼接 + OR 字段=值 </td> 
 </tr> 
 <tr> 
 <td> orNew </td> 
 <td> OR 语句，拼接 + OR (字段=值) </td> 
 </tr> 
 <tr> 
 <td> eq </td> 
 <td> 等于= </td> 
 </tr> 
 <tr> 
 <td> allEq </td> 
 <td> 基于 map 内容等于= </td> 
 </tr> 
 <tr> 
 <td> ne </td> 
 <td> 不等于&lt;&gt; </td> 
 </tr> 
 <tr> 
 <td> gt </td> 
 <td> 大于&gt; </td> 
 </tr> 
 <tr> 
 <td> ge </td> 
 <td> 大于等于&gt;= </td> 
 </tr> 
 <tr> 
 <td> lt </td> 
 <td> 小于&lt; </td> 
 </tr> 
 <tr> 
 <td> le </td> 
 <td> 小于等于&lt;= </td> 
 </tr> 
 <tr> 
 <td> like </td> 
 <td> 模糊查询 LIKE </td> 
 </tr> 
 <tr> 
 <td> notLike </td> 
 <td> 模糊查询 NOT LIKE </td> 
 </tr> 
 <tr> 
 <td> in </td> 
 <td> IN 查询 </td> 
 </tr> 
 <tr> 
 <td> notIn </td> 
 <td> NOT IN 查询 </td> 
 </tr> 
 <tr> 
 <td> isNull </td> 
 <td> NULL 值查询 </td> 
 </tr> 
 <tr> 
 <td> isNotNull </td> 
 <td> IS NOT NULL </td> 
 </tr> 
 <tr> 
 <td> groupBy </td> 
 <td> 分组 GROUP BY </td> 
 </tr> 
 <tr> 
 <td> having </td> 
 <td> HAVING 关键词 </td> 
 </tr> 
 <tr> 
 <td> orderBy </td> 
 <td> 排序 ORDER BY </td> 
 </tr> 
 <tr> 
 <td> orderAsc </td> 
 <td> ASC 排序 ORDER BY </td> 
 </tr> 
 <tr> 
 <td> orderDesc </td> 
 <td> DESC 排序 ORDER BY </td> 
 </tr> 
 <tr> 
 <td> exists </td> 
 <td> EXISTS 条件语句 </td> 
 </tr> 
 <tr> 
 <td> notExists </td> 
 <td> NOT EXISTS 条件语句 </td> 
 </tr> 
 <tr> 
 <td> between </td> 
 <td> BETWEEN 条件语句 </td> 
 </tr> 
 <tr> 
 <td> notBetween </td> 
 <td> NOT BETWEEN 条件语句 </td> 
 </tr> 
 <tr> 
 <td> addFilter </td> 
 <td> 自由拼接 SQL </td> 
 </tr> 
 <tr> 
 <td> last </td> 
 <td> 拼接在最后，例如：last(“LIMIT 1”) </td> 
 </tr> 
 </tbody> 
</table>

# 三 测试用例 #

## 1 ge、gt、le、lt、isNull、isNotNull ##

/**
 * 功能描述：ge、gt、le、lt、isNull、isNotNull 测试
 *
 * @author cakin
 * @date 2020/11/15
 */
 @Test
 public void testDelete() {
 QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
 queryWrapper
 .isNull("name") // 为Null
 .ge("age", 12) // 大于等于
 .isNotNull("email"); // 不为Null
 int result = userMapper.delete(queryWrapper);
 System.out.println("删除了" + result + "行");
 }

测试结果

Creating a new SqlSession
 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@36dfbdaf] was not registered for synchronization because synchronization is not active
 2020-11-15 16:09:40.044 INFO 2464 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
 2020-11-15 16:09:40.522 INFO 2464 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
 JDBC Connection [HikariProxyConnection@1880869198 wrapping com.mysql.cj.jdbc.ConnectionImpl@3d8b319e] will not be managed by Spring
 ==> Preparing: UPDATE user SET deleted=1 WHERE deleted=0 AND (name IS NULL AND age >= ? AND email IS NOT NULL)
 ==> Parameters: 12(Integer)
 <== Updates: 1
 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@36dfbdaf]
 删除了1行

## 2 eq、ne ##

注意：seletOne()返回的是一条实体记录，当出现多条时会报错

/**
 * 功能描述：eq、ne 测试
 *
 * @author cakin
 * @date 2020/11/15
 */
 @Test
 public void testSelectOne() {
 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
 queryWrapper.eq("name", "Tom");
 User user = userMapper.selectOne(queryWrapper); // 只能返回一条记录，多余一条则抛出异常
 System.out.println(user);
 }

测试结果

Creating a new SqlSession
 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@971e903] was not registered for synchronization because synchronization is not active
 2020-11-15 16:11:48.113 INFO 15376 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
 2020-11-15 16:11:48.503 INFO 15376 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
 JDBC Connection [HikariProxyConnection@609116570 wrapping com.mysql.cj.jdbc.ConnectionImpl@10acd6] will not be managed by Spring
 ==> Preparing: SELECT id,name,age,email,create_time,update_time,deleted FROM user WHERE deleted=0 AND (name = ?)
 ==> Parameters: Tom(String)
 <== Columns: id, name, age, email, create_time, update_time, deleted
 <== Row: 3, Tom, 28, test3@baomidou.com, null, null, 0
 <== Total: 1
 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@971e903]
 User(id=3, name=Tom, age=28, email=test3@baomidou.com, createTime=null, updateTime=null, deleted=0)

## 3 between、notBetween ##

包含大小边界

/**
 * 功能描述：between、notBetween 测试
 *
 * @author cakin
 * @date 2020/11/15
 */
 @Test
 public void testSelectCount() {
 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
 queryWrapper.between("age", 20, 30);
 Integer count = userMapper.selectCount(queryWrapper); // 返回数据数量
 System.out.println(count);
 }

测试结果

Creating a new SqlSession
 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c6ee758] was not registered for synchronization because synchronization is not active
 2020-11-15 16:13:40.121 INFO 11160 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
 2020-11-15 16:13:40.596 INFO 11160 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
 JDBC Connection [HikariProxyConnection@891232836 wrapping com.mysql.cj.jdbc.ConnectionImpl@5496c165] will not be managed by Spring
 ==> Preparing: SELECT COUNT( 1 ) FROM user WHERE deleted=0 AND (age BETWEEN ? AND ?)
 ==> Parameters: 20(Integer), 30(Integer)
 <== Columns: COUNT( 1 )
 <== Row: 5
 <== Total: 1
 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2c6ee758]
 5

## 4 like、notLike、likeLeft、likeRight ##

selectMaps()返回Map集合列表，通常配合select()使用

/**
 * 功能描述：like、notLike、likeLeft、likeRight 测试
 *
 * @author cakin
 * @date 2020/11/15
 */
 @Test
 public void testSelectMaps() {
 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
 queryWrapper
 .select("name", "age")
 .like("name", "e")
 .likeRight("email", "5");
 List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper); // 返回值是Map列表
 maps.forEach(System.out::println);
 }

测试结果

Creating a new SqlSession
 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@c6a6c1d] was not registered for synchronization because synchronization is not active
 2020-11-15 16:17:13.414 INFO 8224 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
 2020-11-15 16:17:13.775 INFO 8224 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
 JDBC Connection [HikariProxyConnection@1906128360 wrapping com.mysql.cj.jdbc.ConnectionImpl@2f651f93] will not be managed by Spring
 ==> Preparing: SELECT name,age FROM user WHERE deleted=0 AND (name LIKE ? AND email LIKE ?)
 ==> Parameters: %e%(String), 5%(String)
 <== Columns: name, age
 <== Row: Annie, 28
 <== Row: Helen, 18
 <== Total: 2
 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@c6a6c1d]
 {name=Annie, age=28}
 {name=Helen, age=18}

## 5 in、notIn、inSql、notinSql、exists、notExists ##

in、notIn：

*  notIn("age",\{1,2,3\})--->age not in (1,2,3)
 *  notIn("age", 1, 2, 3)--->age not in (1,2,3)

inSql、notinSql：可以实现子查询

例: inSql("age", "1,2,3,4,5,6")--->age in (1,2,3,4,5,6)

例: inSql("id", "select id from table where id < 3")--->id in (select id from table where id < 3)

/**
 * 功能描述：in、notIn、inSql、notinSql、exists、notExists 测试
 *
 * @author cakin
 * @date 2020/11/15
 */
 @Test
 public void testSelectObjs() {
 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
 // queryWrapper.in("id", 1, 2, 3);
 queryWrapper.inSql("id", "select id from user where id <= 3");
 List<Object> objects = userMapper.selectObjs(queryWrapper); // 返回值是Object列表
 objects.forEach(System.out::println);
 }

测试结果

Creating a new SqlSession
 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@c6a6c1d] was not registered for synchronization because synchronization is not active
 2020-11-15 16:20:20.096 INFO 7676 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
 2020-11-15 16:20:20.454 INFO 7676 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
 JDBC Connection [HikariProxyConnection@1031479068 wrapping com.mysql.cj.jdbc.ConnectionImpl@51ce6f85] will not be managed by Spring
 ==> Preparing: SELECT id,name,age,email,create_time,update_time,deleted FROM user WHERE deleted=0 AND (id IN (select id from user where id <= 3))
 ==> Parameters:
 <== Columns: id, name, age, email, create_time, update_time, deleted
 <== Row: 2, Jack, 20, test2@baomidou.com, null, null, 0
 <== Row: 3, Tom, 28, test3@baomidou.com, null, null, 0
 <== Total: 2
 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@c6a6c1d]
 2
 3

## 6 or、and ##

注意：这里使用的是 UpdateWrapper

不调用or则默认为使用 and 连

/**
 * 功能描述：or、and 测试
 *
 * @author cakin
 * @date 2020/11/15
 */
 @Test
 public void testUpdate1() {
 // 修改值
 User user = new User();
 user.setAge(99);
 user.setName("Andy");
 // 修改条件
 UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
 userUpdateWrapper
 .like("name", "h")
 .or()
 .between("age", 20, 30);
 int result = userMapper.update(user, userUpdateWrapper);
 System.out.println(result);
 }

测试结果

Creating a new SqlSession
 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2116b68b] was not registered for synchronization because synchronization is not active
 updateFill 。。。。。。
 2020-11-15 16:30:28.501 INFO 13004 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
 2020-11-15 16:30:28.806 INFO 13004 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
 JDBC Connection [HikariProxyConnection@1868964323 wrapping com.mysql.cj.jdbc.ConnectionImpl@3fc05ea2] will not be managed by Spring
 ==> Preparing: UPDATE user SET name=?, age=?, update_time=? WHERE deleted=0 AND (name LIKE ? OR age BETWEEN ? AND ?)
 ==> Parameters: Andy(String), 99(Integer), 2020-11-15 16:30:28.494(Timestamp), %h%(String), 20(Integer), 30(Integer)
 <== Updates: 2
 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2116b68b]
 2

## 7 lambda表达式 ##

lambda表达式内的逻辑优先运算

/**
 * 功能描述：lambda表达式 测试
 *
 * @author cakin
 * @date 2020/11/15
 */
 @Test
 public void testUpdate2() {
 //修改值
 User user = new User();
 user.setAge(99);
 user.setName("Andy");
 //修改条件
 UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
 userUpdateWrapper
 .like("name", "n")
 .or(i -> i.like("name", "a").eq("age", 20));
 int result = userMapper.update(user, userUpdateWrapper);
 }

测试结果

Creating a new SqlSession
 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@10667848] was not registered for synchronization because synchronization is not active
 updateFill 。。。。。。
 2020-11-15 16:26:03.391 INFO 11788 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
 2020-11-15 16:26:03.695 INFO 11788 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
 JDBC Connection [HikariProxyConnection@1092822 wrapping com.mysql.cj.jdbc.ConnectionImpl@61dde151] will not be managed by Spring
 ==> Preparing: UPDATE user SET name=?, age=?, update_time=? WHERE deleted=0 AND (name LIKE ? OR (name LIKE ? AND age = ?))
 ==> Parameters: Andy(String), 99(Integer), 2020-11-15 16:26:03.383(Timestamp), %n%(String), %a%(String), 20(Integer)
 <== Updates: 4
 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@10667848]

## 8 orderBy、orderByDesc、orderByAsc ##

/**
 * 功能描述：orderBy、orderByDesc、orderByAsc 测试
 *
 * @author cakin
 * @date 2020/11/15
 */
 @Test
 public void testSelectListOrderBy() {
 QueryWrapper<User> queryWrapper = new QueryWrapper<>();
 queryWrapper.orderByDesc("age", "id");
 List<User> users = userMapper.selectList(queryWrapper);
 users.forEach(System.out::println);
 }

测试结果

Creating a new SqlSession
 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11de56e6] was not registered for synchronization because synchronization is not active
 2020-11-15 16:32:20.695 INFO 7968 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
 2020-11-15 16:32:21.151 INFO 7968 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
 JDBC Connection [HikariProxyConnection@411787317 wrapping com.mysql.cj.jdbc.ConnectionImpl@4a34e9f] will not be managed by Spring
 ==> Preparing: SELECT id,name,age,email,create_time,update_time,deleted FROM user WHERE deleted=0 ORDER BY age DESC,id DESC
 ==> Parameters:
 <== Columns: id, name, age, email, create_time, update_time, deleted
 <== Row: 1327500339715899394, Andy, 99, 55317332@qq.com, 2020-11-14 14:35:19, 2020-11-15 16:26:03, 0
 <== Row: 1244892079889334273, Andy, 99, 55317332@qq.com, 2020-03-31 15:39:35, 2020-11-15 16:26:03, 0
 <== Row: 5, Andy, 99, test5@baomidou.com, null, 2020-11-15 16:30:28, 0
 <== Row: 4, Andy, 99, test4@baomidou.com, null, 2020-11-15 16:26:03, 0
 <== Row: 3, Andy, 99, test3@baomidou.com, null, 2020-11-15 16:30:28, 0
 <== Row: 2, Andy, 99, test2@baomidou.com, null, 2020-11-15 16:26:03, 0
 <== Total: 6
 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@11de56e6]
 User(id=1327500339715899394, name=Andy, age=99, email=55317332@qq.com, createTime=Sat Nov 14 14:35:19 CST 2020, updateTime=Sun Nov 15 16:26:03 CST 2020, deleted=0)
 User(id=1244892079889334273, name=Andy, age=99, email=55317332@qq.com, createTime=Tue Mar 31 15:39:35 CST 2020, updateTime=Sun Nov 15 16:26:03 CST 2020, deleted=0)
 User(id=5, name=Andy, age=99, email=test5@baomidou.com, createTime=null, updateTime=Sun Nov 15 16:30:28 CST 2020, deleted=0)
 User(id=4, name=Andy, age=99, email=test4@baomidou.com, createTime=null, updateTime=Sun Nov 15 16:26:03 CST 2020, deleted=0)
 User(id=3, name=Andy, age=99, email=test3@baomidou.com, createTime=null, updateTime=Sun Nov 15 16:30:28 CST 2020, deleted=0)
 User(id=2, name=Andy, age=99, email=test2@baomidou.com, createTime=null, updateTime=Sun Nov 15 16:26:03 CST 2020, deleted=0)

## 9 set、setSql ##

最终的sql会合并 user.setAge()，以及 userUpdateWrapper.set()  和 setSql() 中 的字段

/**
 * 功能描述：set、setSql 测试
 *
 * @author cakin
 * @date 2020/11/15
 */
 @Test
 public void testUpdateSet() {
 // 修改值
 User user = new User();
 user.setAge(60);
 // 修改条件
 UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
 userUpdateWrapper
 .like("name", "h")
 .set("name", "Peter") // 除了可以查询还可以使用set设置修改的字段
 .setSql(" email = '123@qq.com'"); // 可以使用sql语句
 int result = userMapper.update(user, userUpdateWrapper);
 }

测试结果

Creating a new SqlSession
 SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@203c20cf] was not registered for synchronization because synchronization is not active
 updateFill 。。。。。。
 2020-11-15 16:35:49.410 INFO 3584 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
 2020-11-15 16:35:49.759 INFO 3584 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
 JDBC Connection [HikariProxyConnection@1954355506 wrapping com.mysql.cj.jdbc.ConnectionImpl@736309a9] will not be managed by Spring
 ==> Preparing: UPDATE user SET age=?, update_time=?, name=?, email = '123@qq.com' WHERE deleted=0 AND (name LIKE ?)
 ==> Parameters: 60(Integer), 2020-11-15 16:35:49.402(Timestamp), Peter(String), %h%(String)
 <== Updates: 0
 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@203c20cf]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5ncWl1bWluZw_size_16_color_FFFFFF_t_70]: /images/20210724/34a5529c4f254b1888108ea13e332275.png