mybatis-plus系统化学习之查询专题
1.背景
查询在实际生产中用得最多,也最灵活.
2.查询案例
表结构:
CREATE TABLE `sys_user` ( `id` int(32) NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(64) DEFAULT NULL, `parent_id` int(32) DEFAULT NULL COMMENT '领导id', `version` int(64) DEFAULT NULL, `gender` int(32) DEFAULT NULL, `age` int(32) DEFAULT NULL, `position` varchar(64) DEFAULT NULL, `account` varchar(255) DEFAULT NULL, `password` varchar(225) DEFAULT NULL, `status` varchar(64) DEFAULT NULL, `create_time` datetime DEFAULT NULL, `type` varchar(64) DEFAULT NULL COMMENT '类型', `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4;
需求:
1.最简单的根据id查询 2.根据多个id批量查询 3.map条件查询 条件构造器 4.名字中包含 东平 并且年龄小于等于30 5.名字中包含"东平"并且龄大于等于18且小于等于30并且account不为空 6.名字为姓 "李" 或者 年龄大于等于30，按照年龄降序排列，年龄相同按照id升序排列 7.查询年龄大于等于18岁,并且领导为 张三丰 的员工 8.查询姓"李" 并且 (年龄小于30 或 account不为空） 9.查询姓"李" 或者 (年龄小于30 并且 account不为空） 10.查询(年龄小于30 或者 account不为空) 并且 姓"李" 11.查询年龄大于18岁的 12查询(年龄大于15岁并且有领导的员工)按照领导分组，每组的平均年龄、最大年龄、最小年龄,并且只取平均年龄大于18的组。 13.lambda构造器(最大的优点就是列名写错了有提示,用户与普通的QueryWrapper用法一样) 查询姓"李" 并且 年龄小于30
实现代码:
package com.ldp.demo01; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper; import com.ldp.entity.SysUser; import com.ldp.mapper.SysUserMapper; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import java.util.Arrays; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.function.Consumer; /** * @author 姿势帝-博客园 * @address https://www.cnblogs.com/newAndHui/ * @WeChat 851298348 * @create 11/06 10:27 * @description <p> * 在讲查询的时候,我先把实体与表还原成最初的样子(即重新导入表结构和数据java实体),避免造成学习干扰 * 这个章节将系统化的讲解查询 * 需求如下: * 最简单的根据id查询 * * </p> */ @RunWith(SpringRunner.class) @SpringBootTest public class Test03Select { @Autowired private SysUserMapper sysUserMapper; /** * 最简单的根据id查询 * <p> * ==> Preparing: SELECT id,version,age,gender,name,position,account,password,status,type,create_time,update_time FROM sys_user WHERE id=? * ==> Parameters: 20(Integer) */ @Test public void selectById() { SysUser sysUser = sysUserMapper.selectById(20); System.out.println("sysUser=" + sysUser); } /** * 根据多个id批量查询 * <p> * ==> Preparing: SELECT id,version,age,gender,name,position,account,password,status,type,create_time,update_time FROM sys_user WHERE id IN ( ? , ? , ? , ? ) * ==> Parameters: 17(Integer), 18(Integer), 19(Integer), 20(Integer) */ @Test public void selectBatchIds() { List<Integer> asList = Arrays.asList(17, 18, 19, 20); List<SysUser> sysUsers = sysUserMapper.selectBatchIds(asList); System.out.println("list=" + sysUsers); } /** * map条件查询 * 注意:map条件中的key是用数据库中的字段对应,而不是与java中的实体对应 * ==> Preparing: SELECT id,version,age,gender,name,position,account,password,status,type,create_time,update_time FROM sys_user WHERE name = ? AND age = ? * ==> Parameters: 李东平(String), 18(String) */ @Test public void selectByMap() { Map<String, Object> columnMap = new HashMap<>(); columnMap.put("name", "李东平"); columnMap.put("age", "18"); // columnMap 拼接的条件为 WHERE name = ? AND age = ? List<SysUser> sysUsers = sysUserMapper.selectByMap(columnMap); System.out.println("list=" + sysUsers); } /** * 条件构造器 * <p> * 1、名字中包含 东平 并且年龄小于等于30 * name like '%东平%' and age<=30 * ==> Preparing: SELECT id,version,age,gender,name,position,account,password,status,type,create_time,update_time FROM sys_user * WHERE (name LIKE ? AND age <= ?) * ==> Parameters: %东平%(String), 30(Integer) * <p> * 备注: * 等于的英语单词是:equals * 小于的英文:less than * le表示小于等于 <= * lt表示小于 < * <p> * 同样的道理 * 大于的英文为 greater than * gt 表示大于 > * ge 表示大于等于 >= */ @Test public void testQueryWrapper1() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); queryWrapper.like("name", "东平"); queryWrapper.le("age", 30); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); } /** * 名字中包含"东平"并且龄大于等于18且小于等于30并且account不为空 * ==> Preparing: SELECT id,version,age,gender,name,position,account,password,status,type,create_time,update_time * FROM sys_user WHERE (name LIKE ? AND age >= ? AND age <= ? AND account IS NOT NULL) * ==> Parameters: %东平%(String), 30(Integer), 40(Integer) */ @Test public void testQueryWrapper2() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); queryWrapper.like("name", "东平"); queryWrapper.ge("age", 30); queryWrapper.le("age", 40); // condition = true表示加上这个条件, condition = false表示 不 加上这个条件 queryWrapper.isNotNull(true, "account"); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); } /** * 名字为姓 "李" 或者 年龄大于等于30，按照年龄降序排列，年龄相同按照id升序排列 * ==> Preparing: SELECT id,version,age,gender,name,position,account,password,status,type,create_time,update_time * FROM sys_user WHERE (name LIKE ? OR age >= ?) ORDER BY age DESC,id ASC * ==> Parameters: 李%(String), 30(Integer) */ @Test public void testQueryWrapper3() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); queryWrapper.likeRight(true, "name", "李"); queryWrapper.or(); queryWrapper.ge("age", 30); queryWrapper.orderByDesc("age"); queryWrapper.orderByAsc("id"); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); } /** * 查询年龄大于等于18岁,并且领导为 张三丰 的员工 * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE (age >= ? AND parent_id in (select id from sys_user where `name`=?)) * ==> Parameters: 18(Integer), 张三丰(String) */ @Test public void testQueryWrapper4() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); queryWrapper.ge("age", 18); // 特别注意 这个地方不需要在写 and ,参数下标从0开始 queryWrapper.apply("parent_id in (select id from sys_user where `name`={0})", "张三丰"); List list = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + list); } /** * 查询姓"李" 并且 (年龄小于30 或 account不为空） * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE (name LIKE ? AND (age < ? OR account IS NOT NULL)) * ==> Parameters: 李%(String), 30(Integer) */ @Test public void testQueryWrapper5() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); queryWrapper.likeRight(true, "name", "李"); // 嵌套条件 Consumer<QueryWrapper> consumer = queryWrapper2 -> { queryWrapper2.lt("age", 30); queryWrapper2.or(); queryWrapper2.isNotNull("account"); }; queryWrapper.and(consumer); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); } /** * 第二种实现 * 嵌套条件这好,个人建议使用这种,自己写条件sql,更直观好理解 * 查询姓"李" 并且 (年龄小于30 或 account不为空） * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE (name LIKE ? AND (age < ? OR account IS NOT NULL)) * ==> Parameters: 李%(String), 30(Integer) */ @Test public void testQueryWrapper52() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); queryWrapper.likeRight(true, "name", "李"); queryWrapper.apply(" (age < {0} OR account IS NOT NULL)", 30); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); } /** * 方式一 * 查询姓"李" 或者 (年龄小于30 并且 account不为空） * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE (name LIKE ? OR (age < ? and account IS NOT NULL)) * ==> Parameters: 李%(String), 30(Integer) */ @Test public void testQueryWrapper6() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); queryWrapper.likeRight(true, "name", "李"); // 嵌套条件 Consumer<QueryWrapper> consumer = queryWrapper2 -> { queryWrapper2.lt("age", 30); queryWrapper2.isNotNull("account"); }; queryWrapper.or(consumer); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); } /** * 方式二 * 查询姓"李" 或者 (年龄小于30 并且 account不为空） * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE (name LIKE ? OR (age < ? and account IS NOT NULL)) * ==> Parameters: 李%(String), 30(Integer) */ @Test public void testQueryWrapper62() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); queryWrapper.likeRight(true, "name", "李"); queryWrapper.or(); queryWrapper.apply(" (age < {0} and account IS NOT NULL)", 30); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); } /** * 查询(年龄小于30 或者 account不为空) 并且 姓"李" * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE ((age < ? OR account IS NOT NULL) AND name LIKE ?) * ==> Parameters: 30(Integer), 李%(String) */ @Test public void testQueryWrapper7() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); // 嵌套条件 Consumer<QueryWrapper> consumer = queryWrapper2 -> { queryWrapper2.lt("age", 30); queryWrapper2.or(); queryWrapper2.isNotNull("account"); }; queryWrapper.and(consumer); queryWrapper.likeRight("name", "李"); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); } /** * 方式二写sql的方式 * 查询(年龄小于30 或者 account不为空) 并且 姓"李" * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE ((age < ? OR account IS NOT NULL) AND name LIKE ?) * ==> Parameters: 30(Integer), 李%(String) */ @Test public void testQueryWrapper72() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); // 嵌套条件 queryWrapper.apply(" (age < {0} OR account IS NOT NULL)", 30); queryWrapper.likeRight("name", "李"); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); } /** * 查询年龄大于18岁的 */ @Test public void testQueryWrapper8() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); queryWrapper.eq("age", 15); queryWrapper.eq("name", "张无忌"); // 返回多条会报错,只返回一条或者无数据不报错,sql语句中没有limit SysUser sysUser = sysUserMapper.selectOne(queryWrapper); System.out.println("sysUser=" + sysUser); } /** * <p> * 查询(年龄大于15岁并且有领导的员工)按照领导分组，每组的平均年龄、最大年龄、最小年龄,并且只取平均年龄大于18的组。 * <p> * ==> Preparing: SELECT parent_id,AVG(age) avg,MAX(age) max,MIN(age) min FROM sys_user WHERE (age > ? AND parent_id IS NOT NULL) GROUP BY parent_id HAVING AVG(age)>? * ==> Parameters: 15(Integer), 18(Integer) * <== Columns: parent_id, avg, max, min * <== Row: 1, 58.5455, 99, 18 * <== Total: 1 */ @Test public void test9() { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); // 需要查询的列 queryWrapper.select("parent_id", "AVG(age) avg", "MAX(age) max", "MIN(age) min"); // 查询条件 queryWrapper.gt("age", 15); queryWrapper.isNotNull("parent_id"); // 分组 queryWrapper.groupBy("parent_id"); // 分组后的条件 queryWrapper.having("AVG(age)>{0}", 18); // 执行查询 List list = sysUserMapper.selectMaps(queryWrapper); System.out.println("list=" + list); } /** * lambda构造器(最大的优点就是列名写错了有提示,用户与普通的QueryWrapper用法一样) * 查询姓"李" 并且 年龄小于30 * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE (name LIKE ? AND age < ?) * ==> Parameters: 李%(String), 30(Integer) */ @Test public void test10() { LambdaQueryWrapper<SysUser> queryWrapper = new LambdaQueryWrapper<>(); queryWrapper.likeRight(SysUser::getName, "李"); queryWrapper.lt(SysUser::getAge, 30); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); } }
3.高级查询条件
代码:
/** * 高级查询条件控制 * 需求: * 实际生产中我们要求传入了参数则加入where 条件,否则不加入 * 查询姓 "李" 的员工 */ @Test public void test1() { String name = ""; methodTest1(name); } /** * name 有值时才加入条件 * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user * ==> Parameters: * * @param name */ public void methodTest1(String name) { QueryWrapper queryWrapper = new QueryWrapper<SysUser>(); queryWrapper.like(!StrUtil.isEmpty(name), "name", name); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); }
4.java实体作为条件
1.使用默认的=#{}作为where条件
/** * java实体作为条件 * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE age=? AND name=? * ==> Parameters: 18(Integer), 张无忌(String) */ @Test public void test2() { SysUser sysUser = new SysUser().setName("张无忌").setAge(18); QueryWrapper queryWrapper = new QueryWrapper<>(sysUser); //queryWrapper.setEntity(sysUser); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); }
2.修改默认的条件方式
首先在字段name上加注解
然后代码如下:
/** * java实体作为条件(修改默认条件方式) * 例如name要求以like作为条件(如果SqlCondition常量中没有的,可以自己增加) * @TableField(condition = SqlCondition.LIKE) * private String name; * * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE name LIKE CONCAT('%',?,'%') * ==> Parameters: 无忌(String) */ @Test public void test2_1() { SysUser sysUser = new SysUser().setName("无忌"); QueryWrapper queryWrapper = new QueryWrapper<>(sysUser); //queryWrapper.setEntity(sysUser); List<SysUser> sysUsers = sysUserMapper.selectList(queryWrapper); System.out.println("list=" + sysUsers); }
5.使用原来的mybatis写sql实现
1.在SysUserMapper中添加接口
/** * 查询用户列表(mybatis自己写sql的方式) * * @param sysUser * @return */ List<SysUser> queryListUser(@Param("sysUser") SysUser sysUser);
2.编写xml的sql
<select id="queryListUser" resultType="com.ldp.entity.SysUser"> SELECT * FROM sys_user <where> <if test="sysUser.name!=null and sysUser.name!=''"> name = #{sysUser.name} </if> </where> </select>
3.使用
/** * 自己写sql实现 * ==> Preparing: SELECT * FROM sys_user WHERE name = ? * ==> Parameters: 李东平(String) */ @Test public void test1() { SysUser sysUser = new SysUser().setName("李东平"); List<SysUser> list = sysUserMapper.queryListUser(sysUser); System.out.println("list=" + list); }
6.使用Wrapper自定义SQL
这种方式在实际生产中一般不用,大家知道有这知识点就可以了.
1.在SysUserMapper中编写接口和sql
/** * 使用 Wrapper 自定义SQL * * @param queryWrapper * @return 注意: * 1. Constants.WRAPPER = ew * 2. sql语句也可以写在xml中(这里不演示) * 3. sql语句中没有where */ @Select("select * from sys_user ${ew.customSqlSegment}") List<SysUser> selectListMy(@Param("ew") Wrapper<SysUser> queryWrapper);
2.使用
/** * 使用 Wrapper 自定义SQL * ==> Preparing: SELECT * FROM sys_user WHERE name = ? * ==> Parameters: 李东平(String) */ @Test public void test2() { QueryWrapper<SysUser> wrapper = new QueryWrapper<SysUser>().lt("age", 50); List<SysUser> list = sysUserMapper.selectListMy(wrapper); System.out.println("list=" + list); }
7.分页查询
1.添加一个MybatisPlusConfig配置文件代码
package com.ldp.config; import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor; import org.mybatis.spring.annotation.MapperScan; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; /** * @author 姿势帝-博客园 * @address https://www.cnblogs.com/newAndHui/ * @WeChat 851298348 * @create 12/06 4:45 * @description */ @Configuration @MapperScan("com.ldp.mapper") public class MybatisPlusConfig { /** * 分页插件 * * @return */ @Bean public PaginationInterceptor paginationInterceptor() { PaginationInterceptor paginationInterceptor = new PaginationInterceptor(); return paginationInterceptor; } }
2.使用
/** * 分页测试 * 查询一共有多少条 * ==> Preparing: SELECT COUNT(1) FROM sys_user WHERE (age < ?) * ==> Parameters: 60(Integer) * * 查询列表 * ==> Preparing: SELECT id,version,age,gender,name,parent_id,position,account,password,status,type,create_time,update_time FROM sys_user WHERE (age < ?) LIMIT ?,? * ==> Parameters: 60(Integer), 4(Long), 2(Long) */ @Test public void test1() { QueryWrapper<SysUser> wrapper = new QueryWrapper<>(); wrapper.lt("age",60); Page<SysUser> page = new Page<>(3,2); IPage<SysUser> pageResult = sysUserMapper.selectPage(page, wrapper); System.out.println("list=" + pageResult.getRecords()); }
3.自己写sql的分页
a.编写接口
/** * 自己写sql实现分页 * @param sysUser * @return */ IPage<SysUser> queryPage(Page<SysUser> page,@Param("sysUser") SysUser sysUser);
b.编写sql
<select id="queryPage" resultType="com.ldp.entity.SysUser"> SELECT * FROM sys_user <where> <if test="sysUser.name!=null and sysUser.name!=''"> name = #{sysUser.name} </if> </where> </select>
c.使用
/** * 测试自己的sql分页 */ @Test public void test2() { SysUser sysUser = new SysUser().setName(""); Page<SysUser> page = new Page<>(3,2); IPage<SysUser> pageResult = sysUserMapper.queryPage(page,sysUser); System.out.println("list=" + pageResult.getRecords()); }
