Spring Boot学习随笔- 集成MyBatis-Plus(二)条件查询QueryWrapper、聚合函数的使用、Lambda条件查询
2023-12-30 19:13:04
查询方法详解
-
普通查询
// 根据主键id去查询单个结果的。 @Test public void selectById() { User user = userMapper.selectById(1739970502337392641L); System.out.println(user); } //根据多个主键id批量查询结果的 @Test public void selectIds() { List<Long> list = Arrays.asList(1739970502337392641L, 1739983903621038082L, 1739984905459900417L); List<User> userList = userMapper.selectBatchIds(list); userList.forEach(System.out::println); } // 根据多个条件查询结果的 @Test public void selectByMap() { // map.put("name","小明") // map.put("age",30) // 相当于 where name ="小明" and age=30 Map<String, Object> columnMap = new HashMap<>(); columnMap.put("name", "小刚"); columnMap.put("age", "18"); List<User> userList = userMapper.selectByMap(columnMap); userList.forEach(System.out::println); }
-
条件构造器查询 【重要】
AbstractWrapper
?是 MyBatis Plus 中的一个抽象类,用于构建 SQL 查询条件。定义了泛型?T
、C
?和?Children
。其中,T
?表示实体类的类型,C
?表示查询条件的类型,Children
?表示子类的类型,用于支持链式调用。它提供了一系列方法,用于构建 SQL 查询条件,包括设置查询字段、设置查询条件、排序等。常用实现类包括?
QueryWrapper
?和?UpdateWrapper
。这两个类都是 MyBatis Plus 提供的具体实现,用于构建查询条件和更新条件。 -
条件构造器查询示例
@SpringBootTest public class SelectTests { @Autowired private UserMapper userMapper; /** * 1.名字中包含刘且年龄大于20 * name like '%刘%' and age>20 */ @Test public void selectByWrapper() { // 创建queryWrapper的两种方式 QueryWrapper<User> queryWrapper = new QueryWrapper<>(); // QueryWrapper<User> queryWrapper1 = Wrappers.<User>query(); queryWrapper.like("name", "刘").gt("age", 20); List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); } /** * 2.名字中包含刘且年龄大于等于20且小于30并且email不为空 * name like ‘%刘’ and age between 20 and 30 and email is not null */ @Test public void selectByWrapper2() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.like("name", "刘").between("age", 20, 30).isNull("email"); List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); } /** * 3.名字中包含刘或年龄大于等于20,按照年龄降序排序,年龄相同按照id升序排列 * name like ‘%刘’ and age between 20 and 30 and email is not null */ @Test public void selectByWrapper3() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.like("name", "刘").or().ge("age",20) .orderByDesc("age") .orderByAsc("user_id"); List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); } /** * 4.创建日期为2023年12月17日并且直属上级为王姓 * date_format(create_time,'%Y-%m-$d') and manager_id in(select id from user where name like '王%' */ @Test public void selectByWrapper4() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2023-12-27") // 使用占位符避免sql注入的风险 .inSql("manager_id","select user_id from mp_user where name like '王%'"); List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); } /** * 5.名字为刘姓且年龄小于30或者邮箱不为空 * name like '王%' and (age<20 or email is not null) */ @Test public void selectByWrapper5() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.likeRight("name", "刘") .and(wq -> wq.lt("age", 30).or().isNotNull("email")); List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); } /** * 6.名字为刘姓或者(年龄小于30且大于20并且邮箱不为空) * name like '刘%' or (age<30 and age>20 and email is not null) */ @Test public void selectByWrapper6() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.likeRight("name", "刘") .or(wq -> wq.lt("age", 30).gt("age", 20) .isNotNull("email")); List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); } /** * 7.(年龄小于30且邮箱不为空)并且名字为刘姓 * (age<30 or email is not null) and name like '刘%' */ @Test public void selectByWrapper7() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.nested(wq -> wq.lt("age", 30) .or().isNotNull("email")) .likeRight("name", "刘"); List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); } /** * 8. 年龄为 21 25 33 * age in(21,25,33) */ @Test public void selectByWrapper8() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.in("age", 21, 25, 33); List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); } /** * 9. 只返回满足条件的其中一条语句即可 * limit 1 */ @Test public void selectByWrapper9() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.in("age", 21, 25, 33).last("limit 1"); // last() 有sql注入的风险,谨慎使用 List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); } }
-
select 不列出全部字段
/** * 10.名字中包含刘且年龄小于30(需求1加强版) */ @Test public void selectByWrapper() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); // queryWrapper.select("user_id","name","age").like("name", "刘").lt("age", 30); // 包含字段 queryWrapper.like("name", "刘").lt("age", 30) .select(User.class, info -> !info.getColumn().equals("create_time") && !info.getColumn().equals("manager_id"));// 排除字段 List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); }
-
condition作用
根据判断决定查询条件是否加到sql语句里面
@Test public void testCondition() { String name = "东"; String email = ""; condition(name, email); } private void condition(String name, String email) { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); // if (StringUtils.isNotEmpty(name)) { // queryWrapper.like("name", name); // } // if (StringUtils.isNotEmpty(email)) { // queryWrapper.like("email", email); // } queryWrapper.like(StringUtils.isNotEmpty(name), "name", name) .like(StringUtils.isNotEmpty(email), "email", email); List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); }
-
实体作为条件构造器构造方法的参数
@Test public void selectByWrapperEntity() { User user = new User(); user.setRealName("小刚"); user.setAge(18); QueryWrapper<User> queryWrapper = new QueryWrapper<>(user); List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); } 运行结果的sql语句:SELECT * FROM mp_user WHERE **name=? AND age=?** 会把set注入的属性和值映射到where条件中
-
通过
SqlCondition
更改查询条件用法 @TableField(condition = SqlCondition.LIKE) private String name; @TableField(condition = "%s<#{%s}") //根据定义的常量,自己也可以参考并更改 private Integer age; 运行结果:SELECT * FROM mp_user WHERE **name LIKE CONCAT('%',?,'%')** AND AND age<?
-
-
allEq
**
allEq
**是一个查询条件配置方法,用于构建查询条件。它可以根据传入的条件参数,生成一个包含所有等值条件的查询条件对象。@Test public void selectByWrapperAllEq() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); Map<String, Object> params = new HashMap<>(); params.put("name", "小明"); params.put("age", null); **queryWrapper.allEq(params,false)**; // false代表忽略值为null的字段,即不参与where条件 List<User> list = userMapper.selectList(queryWrapper); list.forEach(System.out::println); }
-
其他条件构造器
**selectMaps
返回**@Test public void selectByWrapperMaps() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.select("user_id","name").like("name", "刘").lt("age", 30); // List<User> list = userMapper.selectList(queryWrapper);// 返回值为实体类的话,没设置的值还是会返回,只不过值为null List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper);// 只返回限定的字段 userList.forEach(System.out::println); } /** 示例 * 11.按照直属上级分组,查询每组的平均年龄、最大年龄、最小年龄,并且至取年龄总和为500的组 * select avg(age)avg_age,min(age) min_age,max(age) max_age from user group by manager_id having sum(age)<500 */ @Test public void selectByWrapperMaps2() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age") .groupBy("manager_id").having("sum(age)<{0}", 500); List<Map<String, Object>> userList = userMapper.selectMaps(queryWrapper); userList.forEach(System.out::println); }
-
lambda条件构造器
@Test public void selectLambda() { //创建lambda条件构造器的三种方式 LambdaQueryWrapper<User> lambda = new LambdaQueryWrapper<>(); LambdaQueryWrapper<User> lambda2 = new QueryWrapper<User>().lambda(); LambdaQueryWrapper<User> lambda3= Wrappers.<User>lambdaQuery(); lambda3.like(User::getName, "刘") .lt(User::getAge, 30); // where name like '%雨%' and age<30 List<User> userList = userMapper.selectList(lambda3); userList.forEach(System.out::println); } /** * 5.名字为刘姓且(年龄小于30或邮箱不为空) * name like '刘%' and (age<30 or email is not null) */ @Test public void selectLambda2() { LambdaQueryWrapper<User> lambda= Wrappers.<User>lambdaQuery(); lambda.like(User::getName, "刘") .and(wq -> wq.lt(User::getAge, 20) .or().isNotNull(User::getEmail)); List<User> userList = userMapper.selectList(lambda); userList.forEach(System.out::println); } // 链式调用多个条件 LambdaQueryChainWrapper @Test public void selectLambda3() { List<User> userList = new LambdaQueryChainWrapper<>(userMapper) .like(User::getName, "刘").ge(User::getAge, 20).list(); userList.forEach(System.out::println); }
文章来源:https://blog.csdn.net/m0_72770746/article/details/135307705
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符,请联系我的编程经验分享网邮箱:veading@qq.com进行投诉反馈,一经查实,立即删除!