条件构造器和常用接口
# wrapper 介绍
常用的是QueryWrapper
可以实现一些复杂的查询操作
# 测试用例
# ge、gt、le、lt、isNull、isNotNull
ge: 大于等于
gt:大于
le:小于等于
lt:小于
@Test
public void testComplexSelect() {
// 构建查询构造器
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// age > 21
queryWrapper.gt("age", 21);
List<User> users = userMapper.selectList(queryWrapper);
System.out.println(users);
}
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@bc4d5e1] was not registered for synchronization because synchronization is not active
2022-02-28 22:09:21.494 INFO 56052 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-02-28 22:09:21.799 INFO 56052 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@2011912080 wrapping com.mysql.cj.jdbc.ConnectionImpl@68e7c8c3] 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)
<== Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@bc4d5e1]
[]
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
@Test
public void testComplexSelect() {
// 构建查询构造器
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name", "王五");
List<User> users = userMapper.selectList(queryWrapper);
System.out.println(users);
}
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@bc4d5e1] was not registered for synchronization because synchronization is not active
2022-02-28 22:09:21.494 INFO 56052 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-02-28 22:09:21.799 INFO 56052 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@2011912080 wrapping com.mysql.cj.jdbc.ConnectionImpl@68e7c8c3] 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)
<== Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@bc4d5e1]
[]
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# between、notBetween
@Test
public void testComplexSelect() {
// 构建查询构造器
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
// age 在24和28之间的
queryWrapper.between("age", 24, 28);
List<User> users = userMapper.selectList(queryWrapper);
System.out.println(users);
}
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@16073fa8] was not registered for synchronization because synchronization is not active
2022-02-28 22:12:15.229 INFO 56131 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-02-28 22:12:15.502 INFO 56131 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@956429999 wrapping com.mysql.cj.jdbc.ConnectionImpl@602ae7b6] 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 BETWEEN ? AND ?)
==> Parameters: 24(Integer), 28(Integer)
<== Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@16073fa8]
[]
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# like、notLike、likeLeft、likeRight
select * from user where name like '%张%';
1
有百分号在左边和右边的区别。
@Test
public void testComplexSelect() {
// 构建查询构造器
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("'name'", "张");
List<User> users = userMapper.selectList(queryWrapper);
System.out.println(users);
}
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@17740dae] was not registered for synchronization because synchronization is not active
2022-02-28 22:13:52.479 INFO 56195 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting...
2022-02-28 22:13:52.756 INFO 56195 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
JDBC Connection [HikariProxyConnection@925152318 wrapping com.mysql.cj.jdbc.ConnectionImpl@77eb5790] 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' LIKE ?)
==> Parameters: %张%(String)
<== Total: 0
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@17740dae]
[]
1
2
3
4
5
6
7
8
9
10
11
2
3
4
5
6
7
8
9
10
11
# orderBy、orderByDesc、orderByAsc
排序方法
@Test
public void testComplexSelect() {
// 构建查询构造器
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("id");
List<User> users = userMapper.selectList(queryWrapper);
System.out.println(users);
}
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
==> Preparing: SELECT id,name,age,email,create_time,update_time,version,deleted FROM user WHERE deleted=0 ORDER BY id DESC
==> Parameters:
<== Columns: id, name, age, email, create_time, update_time, version, deleted
<== Row: 1495378144188751873, 张三, 20, 12@qq.com, 2022-02-20 20:41:47, 2022-02-20 20:52:14, 2, 0
<== Row: 1495319145393180674, 无解的修改测试时间, 20, 12@qq.com, 2022-02-20 16:47:21, 2022-02-20 16:48:54, null, 0
<== Row: 1494318945161748482, 无解的修改, 20, 12@qq.com, null, null, null, 0
<== Row: 3, tom, 20, tom@qq.com, null, null, null, 0
<== Row: 2, jack, 19, dwq@qq.com, null, null, null, 0
<== Total: 5
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7918c7f8]
[User(id=1495378144188751873, name=张三, age=20, email=12@qq.com, createTime=Sun Feb 20 20:41:47 CST 2022, updateTime=Sun Feb 20 20:52:14 CST 2022, version=2, deleted=0), User(id=1495319145393180674, name=无解的修改测试时间, age=20, email=12@qq.com, createTime=Sun Feb 20 16:47:21 CST 2022, updateTime=Sun Feb 20 16:48:54 CST 2022, version=null, deleted=0), User(id=1494318945161748482, name=无解的修改, age=20, email=12@qq.com, createTime=null, updateTime=null, version=null, deleted=0), User(id=3, name=tom, age=20, email=tom@qq.com, createTime=null, updateTime=null, version=null, deleted=0), User(id=2, name=jack, age=19, email=dwq@qq.com, createTime=null, updateTime=null, version=null, deleted=0)]
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
编辑 (opens new window)
上次更新: 2022/02/28, 22:19:14