条件构造器
在MP中,Wrapper接口的实现类关系如下:
在MP查询中,还可以使用lambda方式查询,降低数据库列表写错的风险。
Wrapper中基本比较操作
操作方法 | 方法说明 |
---|---|
eq |
等于 = |
ne |
不等于 <> |
gt |
大于 > |
ge |
大于等于 >= |
lt |
小于 < |
le |
小于等于 <= |
between |
BETWEEN 值1 AND 值2 |
notBetween |
NOT BETWEEN 值1 AND 值2 |
in |
字段 IN (value.get(0), value.get(1), …) |
notIn |
字段 NOT IN (v0, v1, …) |
测试案例:
1
2
3
4
5
6
7
8
9
10
11
12
|
@Test public void testEqCp() { QueryWrapper<User> wrapper = new QueryWrapper<>(); //SELECT id,user_name,password,name,age,email FROM tb_user WHERE password = ? AND age >= ? AND name IN (?,?,?) wrapper.eq( "password" , "123456" ) .ge( "age" , 20 ) .in( "name" , "李四" , "王五" , "赵六" ); List<User> users = this .userMapper.selectList(wrapper); for (User user : users) { System.out.println(user); } } |
也可以用Lambda方式构造条件:
1
2
3
4
5
6
7
8
9
10
11
12
|
@Test public void testEqCp() { LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>(); //SELECT id,user_name,password,name,age,email FROM tb_user WHERE password = ? AND age >= ? AND name IN (?,?,?) wrapper.eq(User::getPassword, "123456" ) .ge(User::getAge, 20 ) .in(User::getName, "李四" , "王五" , "赵六" ); List<User> users = this .userMapper.selectList(wrapper); for (User user : users) { System.out.println(user); } } |
上面是最简单的查询方法,通常在开发中要根据表达式进行判断,表达式为true则拼接条件,如下:
1
2
|
eq( boolean condition, R column, Object val) in( boolean condition, R column, Object... values) ... |
上面代码中的condition
就是要进行计算的表达式,最终表达式结果为boolean类型。
举个例子:
比如根据name来判断,如果name不为空则拼接条件 String name = null;
1
2
3
|
wrapper.eq(User::getPassword, "123456" ) .ge(User::getAge, 20 ) .in(name!= null ,User::getName, "李四" , "王五" , "赵六" ); |
下面那个具体的案例,条件查询测试案例:
1
2
3
4
5
6
7
8
9
10
11
12
|
@Test public void testEq3() { //条件 LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>(); String name = null ; Integer age = 20 ; queryWrapper.eq(name != null && !name.equals( "" ), User::getName, name); //等于 queryWrapper.gt(age != null , User::getAge, age); //大于? List<User> users = userMapper.selectList(queryWrapper); System.out.println(users); } |
模糊查询
like :
1
2
|
LIKE '%值%' 例: like ( "name" , "王" ) ---> name like '%王%' |
notLike:
1
2
|
NOT LIKE '%值%' 例: notLike( "name" , "王" ) ---> name not like '%王%' |
likeLeft :
1
2
|
LIKE '%值' 例: likeLeft( "name" , "王" ) ---> name like '%王' |
likeRight :
1
2
|
LIKE '值%' 例: likeRight( "name" , "王" ) ---> name like '王%' |
测试案例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
@Test public void testWrapper() { QueryWrapper<User> wrapper = new QueryWrapper<>(); //SELECT id,user_name,password,name,age,email FROM tb_user WHERE name LIKE ? //Parameters: %曹%(String) wrapper.likeRight( "name" , "曹" ); wrapper.select( "id" , "name" , "birthday" ); List<User> users = this .userMapper.selectList(wrapper); for (User user : users) { System.out.println(user); } } |
运行结果为:
逻辑查询
or
1
2
|
拼接 OR 主动调用 or 表示紧接着下一个**方法**不是用 and 连接!(不调用 or 则默认为使用 and 连接) |
and
1
2
|
AND 嵌套 例: and (i -> i.eq( "name" , "李白" ).ne( "status" , "活着" )) ---> and (name = '李白' and status <> '活着') |
测试案例:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
@Test public void testOr() { QueryWrapper<User> wrapper = new QueryWrapper<>(); //SELECT id,user_name,password,name,age,email FROM tb_user WHERE name = ? OR age = ? wrapper.eq( "name" , "李四" ).or().eq( "age" , 24 ); //变为and方式 wrapper.eq( "name" , "李四" ).eq( "age" , 24 ); List<User> users = this .userMapper.selectList(wrapper); for (User user : users) { System.out.println(user); } } |
将上面代码改为lambda方式构造条件:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
@Test public void testOr() { LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>(); //SELECT id,user_name,password,name,age,email FROM tb_user WHERE name = ? OR age = ? wrapper.eq(User::getName, "李四" ).or().eq(User::getAge, 24 ); //变为and方式 wrapper.eq(User::getName, "李四" ).eq(User::getAge", 24 ); List<User> users = this .userMapper.selectList(wrapper); for (User user : users) { System.out.println(user); } } |
select
在MP查询中,默认查询所有的字段,如果有需要也可以通过select方法进行指定字段。
测试案例:
1
2
3
4
5
6
7
8
9
10
11
12
|
@Test public void testWrapper() { QueryWrapper<User> wrapper = new QueryWrapper<>(); //SELECT id,name,age FROM tb_user WHERE name = ? OR age = ? wrapper.eq( "name" , "李四" ) .or() .eq( "age" , 24 ) .select( "id" , "name" , "age" ); List<User> users = this .userMapper.selectList(wrapper); for (User user : users) { System.out.println(user); } } |
Lambda方式构造条件:
1
2
3
4
5
|
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>(); wrapper.eq(User::getName, "李四" ) .or() .eq(User::getAge, 24 ) .select(User::getId, User::getName, User::getAge); |
排序
orderByAsc :升序排序
1
2
|
参数:变长数组,设置多个字段名 例: orderByAsc( "id" , "name" ) ---> order by id ASC,name ASC |
orderByDesc :降序排序
1
2
|
参数:变长数组,设置多个字段名 例: orderByDesc( "id" , "name" ) ---> order by id DESC,name DESC |
orderBy : 自定义排序规则
1
2
3
|
方法定义:orderBy(boolean condition, boolean isAsc, R... columns) 参数1: true 有效, false 无效 ,参数2:是否升序,参数3..设置多个字段 例: `orderBy( true , true , "id" , "name" )`‐‐‐>` order by id ASC , name ASC ` |
也可以多个orderBy拼装,如下:
1
|
orderBy( true , true , "id" ).orderBy( true , true , "name" ) |
效果同上面语句。
测试案例:
1
2
3
4
5
6
7
8
9
10
|
@Test public void testOrder() { QueryWrapper<User> wrapper = new QueryWrapper<>(); //SELECT id,user_name,password,name,age,email FROM tb_user ORDER BY age DESC wrapper.orderByDesc( "age" ); List<User> users = this .userMapper.selectList(wrapper); for (User user : users) { System.out.println(user); } } |
Lambda方式构造条件:
1
2
|
LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>(); wrapper.orderByDesc(User::getAge); |
到此这篇关于mybatis-plus条件构造器的文章就介绍到这了,更多相关mybatis-plus条件构造器内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家!
原文链接:https://blog.csdn.net/weixin_45525272/article/details/123698187