一文掌握MyBatis Plus的条件构造器方法
世界尽头与你 人气:01.组装查询条件
组装查询其实很简单,可以支持条件的链式编程:
查询用户名包含a,年龄在 10 - 20 之间并且邮箱不为空的用户:
@Test void contextLoads() { // 查询用户名包含a,年龄在 10 - 20 之间并且邮箱不为空的用户 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.like("name","a") .between("age",10,20) .isNotNull("email"); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL) ==> Parameters: %a%(String), 10(Integer), 20(Integer) <== Columns: id, name, age, email, is_delete <== Row: 2, dada, 11, 111@qq.com, 0 <== Row: 4, dahe, 12, 34567@qq.com, 0 <== Total: 2
2.组装排序条件
查询用户信息,按照年龄的降序排序,若年龄相同,则按照id升序排序:
@Test void contextLoads() { // 查询用户信息,按照年龄的降序排序,若年龄相同,则按照id升序排序 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.orderByDesc("age").orderByAsc("id"); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 ORDER BY age DESC,id ASC ==> Parameters: <== Columns: id, name, age, email, is_delete <== Row: 3, dahe, 33, 34567@qq.com, 0 <== Row: 4, dahe, 12, 34567@qq.com, 0 <== Row: 2, dada, 11, 111@qq.com, 0 <== Total: 3
3.使用Lambda表达式解决条件优先级
将用户名中包含有a并且(年龄大于20或邮箱为null)的用户信息查询出来:
Lambda中的条件优先执行!
@Test void contextLoads() { // 将用户名中包含有a并且(年龄大于20或邮箱为null)的用户信息查询出来 QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.like("name", "a") .and(i -> i.gt("age", 20).or().isNull("email")); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (name LIKE ? AND (age > ? OR email IS NULL)) ==> Parameters: %a%(String), 20(Integer) <== Columns: id, name, age, email, is_delete <== Row: 3, dahe, 33, 34567@qq.com, 0 <== Total: 1
4.组装select语句
有时我们不想查询数据库某张表的所有字段,可以使用maps集合选择只查询某些字段的值:
@Test void contextLoads() { QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.select("name","email"); List<Map<String, Object>> maps = userMapper.selectMaps(wrapper); maps.forEach(System.out::println); }
==> Preparing: SELECT name,email FROM user WHERE is_delete=0 ==> Parameters: <== Columns: name, email <== Row: dada, 111@qq.com <== Row: dahe, 34567@qq.com <== Row: dahe, 34567@qq.com <== Total: 3 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@237add] {name=dada, email=111@qq.com} {name=dahe, email=34567@qq.com} {name=dahe, email=34567@qq.com}
5.组装子查询
@Test void contextLoads() { QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.inSql("id","select id from user where id > 1"); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (id IN (select id from user where id > 1)) ==> Parameters: <== Columns: id, name, age, email, is_delete <== Row: 2, dada, 11, 111@qq.com, 0 <== Row: 3, dahe, 33, 34567@qq.com, 0 <== Row: 4, dahe, 12, 34567@qq.com, 0 <== Total: 3
6.使用UpdateWrapper实现修改功能
将名字中包含a的记录修改:
@Test void contextLoads() { // 将名字中包含a的记录修改 UpdateWrapper<User> wrapper = new UpdateWrapper<>(); wrapper.like("name","a"); wrapper.set("name","hello").set("age",18); userMapper.update(null, wrapper); }
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@36c07c75] will not be managed by Spring ==> Preparing: UPDATE user SET name=?,age=? WHERE is_delete=0 AND (name LIKE ?) ==> Parameters: hello(String), 18(Integer), %a%(String) <== Updates: 3
7.模拟开发中组装条件的情况
真实的开发中往往有这样的情况:我们不知道前端传来的条件是什么,此时无法正常的拼接sql,那么就需要在后端进行相应的判断:
@Test void contextLoads() { String name = null; Integer ageBegin = 18; Integer ageEnd = 28; QueryWrapper<User> wrapper = new QueryWrapper<>(); if (StringUtils.isNotBlank(name)) { wrapper.like("name", name); } if (ageBegin != null) { wrapper.ge("age", ageBegin); } if (ageEnd != null) { wrapper.le("age", ageEnd); } List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (age >= ? AND age <= ?) ==> Parameters: 18(Integer), 28(Integer) <== Columns: id, name, age, email, is_delete <== Row: 3, hello, 18, 34567@qq.com, 0 <== Row: 4, hello, 18, 34567@qq.com, 0 <== Total: 2
但是,这样的判断我们认为过于的简陋和复杂,事实上,MyBatis Plus的条件构造器中为我们封装了解决此类问题的一些重载方法:
@Test void contextLoads() { String name = null; Integer ageBegin = 18; Integer ageEnd = 28; QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.like(StringUtils.isNotBlank(name), "name", name); wrapper.ge(ageBegin != null, "age", ageBegin); wrapper.le(ageEnd != null, "age", ageEnd); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (age >= ? AND age <= ?) ==> Parameters: 18(Integer), 28(Integer) <== Columns: id, name, age, email, is_delete <== Row: 3, hello, 18, 34567@qq.com, 0 <== Row: 4, hello, 18, 34567@qq.com, 0 <== Total: 2
8.LambdaQueryWrapper和LambdaUpdateWrapper
使用LambdaQueryWrapper
和LambdaUpdateWrapper
可以防止我们的字段名修改导致的程序错误或字段名写错
例如:
@Test void contextLoads() { String name = null; Integer ageBegin = 18; Integer ageEnd = 28; LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>(); wrapper.like(StringUtils.isNotBlank(name), User::getName, name); wrapper.ge(ageBegin != null, User::getAge, ageBegin); wrapper.le(ageEnd != null, User::getAge, ageEnd); List<User> users = userMapper.selectList(wrapper); users.forEach(System.out::println); }
==> Preparing: SELECT id,name,age,email,is_delete FROM user WHERE is_delete=0 AND (age >= ? AND age <= ?) ==> Parameters: 18(Integer), 28(Integer) <== Columns: id, name, age, email, is_delete <== Row: 3, hello, 18, 34567@qq.com, 0 <== Row: 4, hello, 18, 34567@qq.com, 0 <== Total: 2
LambdaUpdateWrapper
的使用方法类似
加载全部内容