Wrapper ： 条件构造抽象类，最顶端父类
AbstractWrapper ： 用于查询条件封装，生成 sql 的 where 条件
QueryWrapper ： 查询条件封装
UpdateWrapper ： Update 条件封装
AbstractLambdaWrapper ： 使用Lambda 语法
LambdaQueryWrapper ：用于Lambda语法使用的查询Wrapper
LambdaUpdateWrapper ： Lambda 更新封装Wrapper
二 查询方式说明| 查询方式 | 说明 |
| setSqlSelect | 设置 SELECT 查询字段 |
| where | WHERe 语句，拼接 + WHERe 条件 |
| and | AND 语句，拼接 + AND 字段 = 值 |
| andNew | AND 语句，拼接 + AND ( 字段 = 值 ) |
| or | OR 语句，拼接 + OR 字段 = 值 |
| orNew | OR 语句，拼接 + OR ( 字段 = 值 ) |
| eq | 等于 = |
| allEq | 基于 map 内容等于 = |
| ne | 不等于 <> |
| gt | 大于 > |
| ge | 大于等于 >= |
| lt | 小于 < |
| le | 小于等于 <= |
| like | 模糊查询 LIKE |
| notLike | 模糊查询 NOT LIKE |
| in | IN 查询 |
| notIn | NOT IN 查询 |
| isNull | NULL 值查询 |
| isNotNull | IS NOT NULL |
| groupBy | 分组 GROUP BY |
| having | HAVINg 关键词 |
| orderBy | 排序 ORDER BY |
| orderAsc | ASC 排序 ORDER BY |
| orderDesc | DESC 排序 ORDER BY |
| exists | EXISTS 条件语句 |
| notExists | NOT EXISTS 条件语句 |
| between | BETWEEN 条件语句 |
| notBetween | NOT BETWEEN 条件语句 |
| addFilter | 自由拼接 SQL |
| last | 拼接在最后，例如： last(“LIMIT 1”) |
mysql> select * from user; +---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+ | id | NAME | age | email | create_time | update_time | version | deleted | +---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+ | 0 | auto | 21 | 1243@qq.com | NULL | NULL | NULL | 0 | | 2 | Jack | 20 | test2@baomidou.com | NULL | NULL | NULL | 0 | | 3 | Tom | 28 | test3@baomidou.com | NULL | NULL | NULL | 0 | | 1443158688033337346 | lucymaryupup | 20 | 1243@qq.com | NULL | 2021-09-30 08:51:12 | NULL | 0 | | 1443378040145903617 | ASSIGN_ID | 25 | 1243@qq.com | 2021-09-30 08:51:56 | 2021-09-30 08:51:56 | NULL | 0 | | 1444224656633389058 | luojishanch | 20 | 1243@qq.com | 2021-10-02 16:56:05 | 2021-10-02 16:56:05 | 1 | 0 | +---------------------+--------------+------+--------------------+---------------------+---------------------+---------+---------+ 6 rows in set (0.00 sec)1 ge、gt、le、lt、isNull、isNotNull
代码
@Test
public void testSelect() {
QueryWrapper queryWrapper = new QueryWrapper<>();
// ge、gt、le、lt
queryWrapper.ge("age", 21);
List users = userMapper.selectList(queryWrapper);
System.out.println(users);
}
测试结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1e54cb33] was not registered for synchronization because synchronization is not active 2021-10-03 10:20:51.303 INFO 10576 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2021-10-03 10:20:52.530 INFO 10576 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@784556863 wrapping com.mysql.cj.jdbc.ConnectionImpl@1d5d5621] will not be managed by Spring ==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERe deleted=0 AND (age >= ?) ==> Parameters: 21(Integer) <== Columns: id, name, age, email, create_time, update_time, version, deleted <== Row: 0, auto, 21, 1243@qq.com, null, null, null, 0 <== Row: 3, Tom, 28, test3@baomidou.com, null, null, null, 0 <== Row: 1443378040145903617, ASSIGN_ID, 25, 1243@qq.com, 2021-09-30 08:51:56, 2021-09-30 08:51:56, null, 0 <== Total: 3 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1e54cb33] [User(id=0, name=auto, age=21, email=1243@qq.com, createTime=null, updateTime=null, version=null, deleted=0), User(id=3, name=Tom, age=28, email=test3@baomidou.com, createTime=null, updateTime=null, version=null, deleted=0), User(id=1443378040145903617, name=ASSIGN_ID, age=25, email=1243@qq.com, createTime=Thu Sep 30 08:51:56 CST 2021, updateTime=Thu Sep 30 08:51:56 CST 2021, version=null, deleted=0)]2 eq、ne
注意：seletOne()返回的是一条实体记录，当出现多条时会报错
代码
@Test
public void testSelectOne() {
QueryWrapperqueryWrapper = 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@7965a51c] was not registered for synchronization because synchronization is not active 2021-10-03 10:23:14.853 INFO 4648 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2021-10-03 10:23:16.002 INFO 4648 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@1291485735 wrapping com.mysql.cj.jdbc.ConnectionImpl@78226c36] will not be managed by Spring ==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERe deleted=0 AND (name = ?) ==> Parameters: Tom(String) <== Columns: id, name, age, email, create_time, update_time, version, deleted <== Row: 3, Tom, 28, test3@baomidou.com, null, null, null, 0 <== Total: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7965a51c] User(id=3, name=Tom, age=28, email=test3@baomidou.com, createTime=null, updateTime=null, version=null, deleted=0)3 between、notBetween
包含大小边界
代码
@Test
public void testSelectCount() {
QueryWrapperqueryWrapper = 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@33cbfa57] was not registered for synchronization because synchronization is not active 2021-10-03 10:24:52.301 INFO 1752 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2021-10-03 10:24:53.598 INFO 1752 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@364389956 wrapping com.mysql.cj.jdbc.ConnectionImpl@20576557] 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: 6 <== Total: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@33cbfa57] 64 like、notLike、likeLeft、likeRight
selectMaps()返回Map集合列表，通常配合select()使用
代码
@Test
public void testSelectMaps() {
QueryWrapper queryWrapper = new QueryWrapper<>();
queryWrapper
.select("name", "age")
.like("name", "o")
.likeRight("email", "t");
List> maps = userMapper.selectMaps(queryWrapper); // 返回值是Map列表
maps.forEach(System.out::println);
}
测试结果
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7965a51c] was not registered for synchronization because synchronization is not active
2021-10-03 10:30:28.838 INFO 8232 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2021-10-03 10:30:30.051 INFO 8232 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@1291485735 wrapping com.mysql.cj.jdbc.ConnectionImpl@78226c36] will not be managed by Spring
==> Preparing: SELECT name,age FROM user WHERe deleted=0 AND (name LIKE ? AND email LIKE ?)
==> Parameters: %o%(String), t%(String)
<== Columns: name, age
<== Row: Tom, 28
<== Total: 1
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7965a51c]
{name=Tom, age=28}
5 orderBy、orderByDesc、orderByAsc
代码
@Test
public void testSelectListOrderBy() {
QueryWrapper queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("age", "id");
List users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
测试结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@834831b] was not registered for synchronization because synchronization is not active 2021-10-03 10:52:52.112 INFO 18224 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2021-10-03 10:52:53.322 INFO 18224 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@1594981181 wrapping com.mysql.cj.jdbc.ConnectionImpl@1174a305] will not be managed by Spring ==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERe deleted=0 ORDER BY age DESC,id DESC ==> Parameters: <== Columns: id, name, age, email, create_time, update_time, version, deleted <== Row: 3, Tom, 28, test3@baomidou.com, null, null, null, 0 <== Row: 1443378040145903617, ASSIGN_ID, 25, 1243@qq.com, 2021-09-30 08:51:56, 2021-09-30 08:51:56, null, 0 <== Row: 0, auto, 21, 1243@qq.com, null, null, null, 0 <== Row: 1444224656633389058, luojishanch, 20, 1243@qq.com, 2021-10-02 16:56:05, 2021-10-02 16:56:05, 1, 0 <== Row: 1443158688033337346, lucymaryupup, 20, 1243@qq.com, null, 2021-09-30 08:51:12, null, 0 <== Row: 2, Jack, 20, test2@baomidou.com, null, null, null, 0 <== Total: 6 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@834831b] User(id=3, name=Tom, age=28, email=test3@baomidou.com, createTime=null, updateTime=null, version=null, deleted=0) User(id=1443378040145903617, name=ASSIGN_ID, age=25, email=1243@qq.com, createTime=Thu Sep 30 08:51:56 CST 2021, updateTime=Thu Sep 30 08:51:56 CST 2021, version=null, deleted=0) User(id=0, name=auto, age=21, email=1243@qq.com, createTime=null, updateTime=null, version=null, deleted=0) User(id=1444224656633389058, name=luojishanch, age=20, email=1243@qq.com, createTime=Sat Oct 02 16:56:05 CST 2021, updateTime=Sat Oct 02 16:56:05 CST 2021, version=1, deleted=0) User(id=1443158688033337346, name=lucymaryupup, age=20, email=1243@qq.com, createTime=null, updateTime=Thu Sep 30 08:51:12 CST 2021, version=null, deleted=0) User(id=2, name=Jack, age=20, email=test2@baomidou.com, createTime=null, updateTime=null, version=null, deleted=0)
