Mybatis-Plus条件构造器和常用接口

1. wapper介绍Wrapper 条件构造抽象类最顶端父类AbstractWrapper 用于查询条件封装生成sql的where条件QueryWrapper 查询条件封装UpdateWrapperUpdate条件封装AbstractLambdaWrapper 使用Lambda语法LambdaQueryWrapper用于Lambda语法使用的查询WrapperLambdaUpdateWrapperLambda更新封装Wrapper2. QueryWrapper2.1 组装查询条件Test public void test01(){ //查询用户名包含a年龄在20到30之间并且邮箱不为null的用户信息 //SELECT id,name,age,email,is_deleted FROM t_user WHERE is_deleted0 AND (name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL) QueryWrapperUser queryWrapper new QueryWrapper(); queryWrapper.like(name, a) .between(age, 20, 30) .isNotNull(email); ListUser list userMapper.selectList(queryWrapper); list.forEach(System.out::println); }2.2 组装排序条件Test public void test02(){ //按年龄降序查询用户如果年龄相同则按id升序排列 //SELECT id,name AS name,age,email,is_deleted FROM t_user WHERE is_deleted0 ORDER BY age DESC,id ASC QueryWrapperUser queryWrapper new QueryWrapper(); queryWrapper .orderByDesc(age) .orderByAsc(id); ListUser users userMapper.selectList(queryWrapper); users.forEach(System.out::println); }2.3 组装删除条件Test public void test03(){ //删除email为空的用户逻辑删除 // 常规删除语句DELETE FROM t_user WHERE (email IS NULL) // 逻辑删除语句实质为更新UPDATE t_user SET is_deleted1 WHERE is_deleted0 AND (email IS NULL) QueryWrapperUser queryWrapper new QueryWrapper(); queryWrapper.isNull(email); //条件构造器也可以构建删除语句的条件 int result userMapper.delete(queryWrapper); System.out.println(受影响的行数 result); }2.4 条件的优先级Test public void test04() { QueryWrapperUser queryWrapper new QueryWrapper(); //将年龄大于20并且用户名中包含有a或邮箱为null的用户信息修改 //UPDATE t_user SET age?, email? WHERE (username LIKE ? AND age ? OR email IS NULL) // queryWrapper // .like(username, a) // .gt(age, 20) // .or() // .isNull(email); //lambda表达式内的逻辑优先运算 queryWrapper .like(name, a) .and(i - i.gt(age, 20).or().isNull(email)); User user new User(); user.setAge(18); user.setEmail(userqcby.com); int result userMapper.update(user, queryWrapper); System.out.println(受影响的行数 result); }2.5 组装 select 子句Test public void test05() { //查询用户信息的name和age字段 //SELECT name,age FROM t_user QueryWrapperUser queryWrapper new QueryWrapper(); queryWrapper.select(name, age); //selectMaps()返回Map集合列表通常配合select()使用避免User对象中没有被查询到的列值 为null ListMapString, Object maps userMapper.selectMaps(queryWrapper); maps.forEach(System.out::println); }2.6 实现子查询Test public void test06() { //查询id小于等于3的用户信息 //SELECT id,name,age,email,is_deleted FROM t_user WHERE (id IN (select id from t_user where id 3)) QueryWrapperUser queryWrapper new QueryWrapper(); // SQL语句字段名跟表一致而不是实体类 queryWrapper.inSql(uid, select uid from t_user where uid 3); ListUser list userMapper.selectList(queryWrapper); list.forEach(System.out::println); }3. UpdateWrapperTest public void test07() { //将年龄大于20或邮箱为null并且用户名中包含有a的用户信息修改 //组装set子句以及修改条件 UpdateWrapperUser updateWrapper new UpdateWrapper(); //lambda表达式内的逻辑优先运算 updateWrapper .set(age, 18) .set(email, userqcby.com) .like(name, a) .and(i - i.gt(age, 20).or().isNull(email)); //这里必须要创建User对象否则无法应用自动填充。如果没有自动填充可以设置为null //UPDATE t_user SET name?, age?,email? WHERE (name LIKE ? AND (age ? OR email IS NULL)) //User user new User(); //user.setName(张三); //int result userMapper.update(user, updateWrapper); //UPDATE t_user SET age?,email? WHERE (name LIKE ? AND (age ? OR email IS NULL)) int result userMapper.update(null, updateWrapper); System.out.println(result); }4. Condition在真正开发的过程中组装条件是常见的功能而这些条件数据来源于用户输入是可选的因此在组装这些条件时必须先判断用户是否选择了这些条件若选择则需要组装该条件若 没有选择则一定不能组装以免影响SQL执行的结果思路一Test public void test08() { //定义查询条件有可能为null用户未输入或未选择 String name null; Integer ageBegin 10; Integer ageEnd 24; QueryWrapperUser queryWrapper new QueryWrapper(); //StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace) 构成 if(StringUtils.isNotBlank(name)){ queryWrapper.like(name,a); } if(ageBegin ! null){ queryWrapper.ge(age, ageBegin); } if(ageEnd ! null){ queryWrapper.le(age, ageEnd); } //SELECT id,name,age,email,is_deleted FROM t_user WHERE (age ? AND age ?) ListUser users userMapper.selectList(queryWrapper); users.forEach(System.out::println); }思路二Test public void test08UseCondition() { //定义查询条件有可能为null用户未输入或未选择 String name null; Integer ageBegin 10; Integer ageEnd 24; QueryWrapperUser queryWrapper new QueryWrapper(); //StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace) 构成 queryWrapper .like(StringUtils.isNotBlank(name), name, a) // ge大于等于 .ge(ageBegin ! null, age, ageBegin) // le小于等于 .le(ageEnd ! null, age, ageEnd); //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (age ? AND age ?) ListUser users userMapper.selectList(queryWrapper); users.forEach(System.out::println); }5.LambdaQueryWrapperTest public void test09() { //定义查询条件有可能为null用户未输入 String name a; Integer ageBegin 10; Integer ageEnd 24; // Lambda 表达式方式指定字段避免使用字符串提高类型安全性和可读性常用于 MyBatis-Plus 的条件查询 LambdaQueryWrapperUser queryWrapper new LambdaQueryWrapper(); //避免使用字符串表示字段防止运行时错误 queryWrapper .like(StringUtils.isNotBlank(name), User::getName, name) .ge(ageBegin ! null, User::getAge, ageBegin) .le(ageEnd ! null, User::getAge, ageEnd); ListUser users userMapper.selectList(queryWrapper); users.forEach(System.out::println); }6.LambdaUpdateWrapperTest public void test10() { //组装set子句 // MyBatis-Plus 提供的一个更新条件构造器它是 UpdateWrapper 的 Lambda 表达式版本 // 使用 Lambda 表达式来引用实体类的字段从而避免了硬编码字符串字段名提高了类型安全性并减少了运行时错误的风险 LambdaUpdateWrapperUser updateWrapper new LambdaUpdateWrapper(); updateWrapper .set(User::getAge, 18) .set(User::getEmail, userqcby.com) .like(User::getName, a) .and(i - i.lt(User::getAge, 24).or().isNull(User::getEmail)); //lambda 表达式内的逻辑优先运算 User user new User(); int result userMapper.update(user, updateWrapper); System.out.println(受影响的行数 result); }