无锡网站推广经理,淘宝客建站模板,php 网站伪静态,上传网站到虚拟主机学习视频#xff1a;【编程不良人】Mybatis-Plus整合SpringBoot实战教程,提高的你开发效率,后端人员必备! 查询方法详解 普通查询 // 根据主键id去查询单个结果的。
Test
public void selectById() {User user userMapper.selectById(1739970502337392641L);System.out.print… 学习视频【编程不良人】Mybatis-Plus整合SpringBoot实战教程,提高的你开发效率,后端人员必备! 查询方法详解 普通查询 // 根据主键id去查询单个结果的。
Test
public void selectById() {User user userMapper.selectById(1739970502337392641L);System.out.println(user);
}//根据多个主键id批量查询结果的
Test
public void selectIds() {ListLong list Arrays.asList(1739970502337392641L, 1739983903621038082L, 1739984905459900417L);ListUser userList userMapper.selectBatchIds(list);userList.forEach(System.out::println);
}
// 根据多个条件查询结果的
Test
public void selectByMap() {// map.put(name,小明)// map.put(age,30)// 相当于 where name 小明 and age30MapString, Object columnMap new HashMap();columnMap.put(name, 小刚);columnMap.put(age, 18);ListUser 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 {Autowiredprivate UserMapper userMapper;/*** 1.名字中包含刘且年龄大于20* name like %刘% and age20*/Testpublic void selectByWrapper() {// 创建queryWrapper的两种方式QueryWrapperUser queryWrapper new QueryWrapper();
// QueryWrapperUser queryWrapper1 Wrappers.Userquery();queryWrapper.like(name, 刘).gt(age, 20);ListUser 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*/Testpublic void selectByWrapper2() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.like(name, 刘).between(age, 20, 30).isNull(email);ListUser 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*/Testpublic void selectByWrapper3() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.like(name, 刘).or().ge(age,20).orderByDesc(age).orderByAsc(user_id);ListUser 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 王%*/Testpublic void selectByWrapper4() {QueryWrapperUser 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 王%);ListUser list userMapper.selectList(queryWrapper);list.forEach(System.out::println);}/*** 5.名字为刘姓且年龄小于30或者邮箱不为空* name like 王% and (age20 or email is not null)*/Testpublic void selectByWrapper5() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.likeRight(name, 刘).and(wq - wq.lt(age, 30).or().isNotNull(email));ListUser list userMapper.selectList(queryWrapper);list.forEach(System.out::println);}/*** 6.名字为刘姓或者年龄小于30且大于20并且邮箱不为空* name like 刘% or (age30 and age20 and email is not null)*/Testpublic void selectByWrapper6() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.likeRight(name, 刘).or(wq - wq.lt(age, 30).gt(age, 20).isNotNull(email));ListUser list userMapper.selectList(queryWrapper);list.forEach(System.out::println);}/*** 7.年龄小于30且邮箱不为空并且名字为刘姓* (age30 or email is not null) and name like 刘%*/Testpublic void selectByWrapper7() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.nested(wq - wq.lt(age, 30).or().isNotNull(email)).likeRight(name, 刘);ListUser list userMapper.selectList(queryWrapper);list.forEach(System.out::println);}/*** 8. 年龄为 21 25 33* age in(21,25,33)*/Testpublic void selectByWrapper8() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.in(age, 21, 25, 33);ListUser list userMapper.selectList(queryWrapper);list.forEach(System.out::println);}/*** 9. 只返回满足条件的其中一条语句即可* limit 1*/Testpublic void selectByWrapper9() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.in(age, 21, 25, 33).last(limit 1); // last() 有sql注入的风险谨慎使用ListUser list userMapper.selectList(queryWrapper);list.forEach(System.out::println);}
}select 不列出全部字段 /*** 10.名字中包含刘且年龄小于30需求1加强版*/Testpublic void selectByWrapper() {QueryWrapperUser 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));// 排除字段ListUser list userMapper.selectList(queryWrapper);list.forEach(System.out::println);}condition作用 根据判断决定查询条件是否加到sql语句里面 Testpublic void testCondition() {String name 东;String email ;condition(name, email);}private void condition(String name, String email) {QueryWrapperUser 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);ListUser list userMapper.selectList(queryWrapper);list.forEach(System.out::println);}实体作为条件构造器构造方法的参数 Test
public void selectByWrapperEntity() {User user new User();user.setRealName(小刚);user.setAge(18);QueryWrapperUser queryWrapper new QueryWrapper(user);ListUser 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 %slt;#{%s}) //根据定义的常量自己也可以参考并更改
private Integer age;
运行结果SELECT * FROM mp_user WHERE **name LIKE CONCAT(%,?,%)** AND AND age?allEq **allEq**是一个查询条件配置方法用于构建查询条件。它可以根据传入的条件参数生成一个包含所有等值条件的查询条件对象。 Test
public void selectByWrapperAllEq() {QueryWrapperUser queryWrapper new QueryWrapper();MapString, Object params new HashMap();params.put(name, 小明);params.put(age, null);**queryWrapper.allEq(params,false)**; // false代表忽略值为null的字段即不参与where条件ListUser list userMapper.selectList(queryWrapper);list.forEach(System.out::println);
}其他条件构造器 **selectMaps 返回** Testpublic void selectByWrapperMaps() {QueryWrapperUser queryWrapper new QueryWrapper();queryWrapper.select(user_id,name).like(name, 刘).lt(age, 30);
// ListUser list userMapper.selectList(queryWrapper);// 返回值为实体类的话没设置的值还是会返回只不过值为nullListMapString, 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*/Testpublic void selectByWrapperMaps2() {QueryWrapperUser 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);ListMapString, Object userList userMapper.selectMaps(queryWrapper);userList.forEach(System.out::println);}lambda条件构造器 Test
public void selectLambda() {//创建lambda条件构造器的三种方式LambdaQueryWrapperUser lambda new LambdaQueryWrapper();LambdaQueryWrapperUser lambda2 new QueryWrapperUser().lambda();LambdaQueryWrapperUser lambda3 Wrappers.UserlambdaQuery();lambda3.like(User::getName, 刘).lt(User::getAge, 30); // where name like %雨% and age30ListUser userList userMapper.selectList(lambda3);userList.forEach(System.out::println);
}
/*** 5.名字为刘姓且年龄小于30或邮箱不为空* name like 刘% and (age30 or email is not null)*/
Test
public void selectLambda2() {LambdaQueryWrapperUser lambda Wrappers.UserlambdaQuery();lambda.like(User::getName, 刘).and(wq - wq.lt(User::getAge, 20).or().isNotNull(User::getEmail));ListUser userList userMapper.selectList(lambda);userList.forEach(System.out::println);
}
// 链式调用多个条件 LambdaQueryChainWrapper
Test
public void selectLambda3() {ListUser userList new LambdaQueryChainWrapper(userMapper).like(User::getName, 刘).ge(User::getAge, 20).list();userList.forEach(System.out::println);
}
