Mybatis-Plus 实战完整学习笔记(九)------条件构造器核心用法大全（上）

一、Mybatisplus通用（公共方法）CRUD，一共17种（3.0.3版），2.3系列也是这么多，这个新版本一定程度进行了改造和删减。

二、构造器UML图（3.0.3）-----实体包装器，主要用于处理 sql 拼接，排序，实体参数查询等

注意: 使用的是数据库字段，不是 Java 属性!

总体就是入上图的样式的结构，主要使用QueryWrapper 和UpdateWrapper，JDK1.8使用LambdaQueryWrapper和LambdaUpdateWrapper

注意2.3版本使用的是EntityWrapper实现条件构造器，这里将阐述和说明

构造器拼接的方法

实战演练：(3.0.3版)

1、全部查询,拼接一个where过滤条件

 1 @Test
 2     public void selectWrapper() throws SQLException {
 3 
 4 
 5         // 条件构造器使用
 6 
 7         // 1、全部查询,拼接一个where过滤条件，如果是多个可以map中put多个，多个字段如果有某个字段是空的，就会默认添加空查询条件
 8         // SELECT id,last_name,email,gender,age FROM tbl_employee WHERE gender = ? AND age IS NULL
 9         Employee employee = new Employee();
10         employee.setGender(1);
11 
12         Map<String,Object> map = new HashMap<>(16);
13         map.put("gender",employee.getGender());
14         map.put("age",employee.getAge());
15 
16        List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().allEq(map));
17 
18 
19         if (!employeeList.isEmpty()) {
20             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
21         }
22 }

View Code

注意2.3版本的可以用EntityWrapper代替QureyWrapper，其他写法一样

2、map添加的字段是否加到where的条件中

 1 @Test
 2     public void selectWrapper() throws SQLException {
 3 
 4 
 5         // 条件构造器使用
 6         Map<String,Object> map = new HashMap<>(16);
 7         map.put("gender",employee.getGender());
 8         map.put("age",employee.getAge());
 9 
10         // 2、map添加的字段是否加到where的条件中，通过lambda表达式判断，如果是true就加入，反之，不加入，因为Map的key的String有gender，所以会过滤
11         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().allEq((String,Object)->String.equals("gender"),map));
12 
13 
14         if (!employeeList.isEmpty()) {
15             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
16         }
17 }

View Code

3、and使用

 1 @Test
 2     public void selectWrapper() throws SQLException {
 3 
 4         // 条件构造器使用
 5         // 3、and使用
 6 
 7         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().and(i -> i.eq("last_name", "Betty0")));
 8 
 9         if (!employeeList.isEmpty()) {
10             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
11         }

View Code

相当于SQL语句SELECT id,last_name,email,gender,age FROM tbl_employee WHERE ( last_name = ? )

and前会有一个condition，判别是否加入到sql语句中的条件，默认是true
List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().and(true,i -> i.eq("last_name", "Betty0")));

4、apply拼接条件使用

1     4、apply使用
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().apply("last_name='Betty0'"));
3 
4 
5         if (!employeeList.isEmpty()) {
6             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
7         }

View Code

外部参数调用的话List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().apply("last_name={0}","Betty0"));

5、between使用方法

1       5、between
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().between("age",20,50));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

6、eq 等于 =

1    6、eq 充当and使用
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().eq("last_name","Betty0"));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

7、ge大于等于 >=

1     7、ge大于等于
2 
3         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().ge("age","40"));
4 
5 
6         if (!employeeList.isEmpty()) {
7             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
8         }

View Code

8、gt大于

1   // 8 、gt 大于
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().gt("age","40"));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

9、分组操作

1 //        9、分组操作
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().eq("id","25").groupBy("age","gender"));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

10、having使用

1   10、having使用
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().eq("gender","1").having("age = {0}",30));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

11、in使用

1 //        11、in 操作
2         List<Integer> idList = new ArrayList<>();
3         idList.add(31);
4         idList.add(32);
5 
6         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().in(true,"id",idList));
7         if (!employeeList.isEmpty()) {
8             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
9         }

View Code

12、inSql使用

1    12、inSql使用
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().inSql("id", "12,22,23,24,25,26"));
3         if (!employeeList.isEmpty()) {
4             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
5         }

View Code

13、isNotNull字段值非空

1   13、字段值非空
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().isNotNull("age"));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

14、字段值为空的

1  14、字段值为空的
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().isNull("age"));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

15、结尾拼接sql语句，存在Sql注入风险，不建议使用

1     15、结尾拼接sql语句
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().last("and age >30"));
3 
4 
5         if (!employeeList.isEmpty()) {
6             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
7         }

View Code

16、小于等于 <=

1  16、小于等于 <=
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().le("age", "20"));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

17、小于

1     17、小于
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().lt("age", "20"));
3 
4 
5         if (!employeeList.isEmpty()) {
6             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
7         }

View Code

18、模糊查询

1   18、模糊查询
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().like("age", "20"));
3 
4 
5         if (!employeeList.isEmpty()) {
6             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
7         }

View Code

19、LikeLeft以什么结尾的查询

1      19、 LikeLeft以什么结尾的查询
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().likeLeft("age", "20"));
3 
4 
5         if (!employeeList.isEmpty()) {
6             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
7         }

View Code

20、likeRight以什么开头

1   20、likeRight以什么开头
2 //        List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().likeRight("age", "20"));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

21、ne不等于 <>用法

1    21、ne不等于 <>用法
2          List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().ne("age", "20"));
3 
4 
5         if (!employeeList.isEmpty()) {
6             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
7         }

View Code

22、嵌套的查询

1 22、嵌套的查询
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().nested(true,i -> i.eq("last_name", "Betty0")));
3 
4 
5 
6         if (!employeeList.isEmpty()) {
7             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
8         }

View Code

23、不在什么区间

1 List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().notBetween("age",20,50));
2 
3         if (!employeeList.isEmpty()) {
4             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
5         }

View Code

24、不存在notexist

1   24、不存在
2       List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().notExists("select id from tbl_employee where age = 1"));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

25、存在exist

1 25、存在
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().exists("select id from tbl_employee where age = 12"));
3 
4 
5         if (!employeeList.isEmpty()) {
6             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
7         }

View Code

26、not in 不在区间内

1 List<Integer> idList = new ArrayList<>();
2         idList.add(31);
3         idList.add(32);
4         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().notIn("id",idList));
5 
6         if (!employeeList.isEmpty()) {
7             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
8         }

View Code

27、notSQL 不在区间内

1  List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().notInSql("id","12,22"));
2 
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

28、notLike 模糊查询没有这个关键字

1 List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().notLike("age", "2"));
2 
3         if (!employeeList.isEmpty()) {
4             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
5         }

View Code

29、orderBy根据字段升序还是降序排序

1 29、orderBy根据字段升序还是降序排序
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().orderBy(true, false, "age"));
3 
4 
5         if (!employeeList.isEmpty()) {
6             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
7         }

View Code

30、or或者

1  30、or或者
2         List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().eq("id","25").or().eq("gender","1"));
3 
4         if (!employeeList.isEmpty()) {
5             logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList));
6         }

View Code

posted @ 2018-10-03 06:14 北极的大企鹅阅读(22770) 评论(3) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

北极的大企鹅

Mybatis-Plus 实战完整学习笔记(九)------条件构造器核心用法大全（上）

公告