一 wrapper介绍
1 类图
2 说明
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”)
|
三 实战
0 数据库
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<User> queryWrapper = new QueryWrapper<>();
// ge、gt、le、lt
queryWrapper.ge("age", 21);
List<User> 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() {
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@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() {
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@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]
6
4 like、notLike、likeLeft、likeRight
selectMaps()返回Map集合列表,通常配合select()使用
代码
@Test
public void testSelectMaps() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
.select("name", "age")
.like("name", "o")
.likeRight("email", "t");
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@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<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@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)