一、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过滤条件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
注意2.3版本的可以用EntityWrapper代替QureyWrapper,其他写法一样
2、map添加的字段是否加到where的条件中
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
3、and使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
相当于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拼接条件使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
外部参数调用的话List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().apply("last_name={0}","Betty0"));
5、between使用方法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
6、eq 等于 =
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
7、ge大于等于 >=
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
8、gt大于
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
9、分组操作
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
10、having使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
11、in使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
12、inSql使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
13、isNotNull字段值非空
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
14、字段值为空的
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
15、结尾拼接sql语句,存在Sql注入风险,不建议使用
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
16、小于等于 <=
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
17、小于
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
18、模糊查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
19、LikeLeft以什么结尾的查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
20、likeRight以什么开头
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
21、ne不等于 <>用法
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
22、嵌套的查询
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
23、不在什么区间
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
24、不存在notexist
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
25、存在exist
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
26、not in 不在区间内
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
27、notSQL 不在区间内
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
28、notLike 模糊查询没有这个关键字
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 List<Employee> employeeList = employeeMapper.selectList(new QueryWrapper<Employee>().notLike("age", "2")); 2 3 if (!employeeList.isEmpty()) { 4 logger.info("++条件构造器查询员工信息+++++" + gson.toJson(employeeList)); 5 }
29、orderBy根据字段升序还是降序排序
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }
30、or或者
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 }