MyBatisPlus条件构造器和常用接口

最新推荐文章于 2024-07-29 11:41:34 发布

顾城猿

最新推荐文章于 2024-07-29 11:41:34 发布

阅读量425

点赞数 9

文章标签： java mybatis spring boot

本文链接：https://blog.csdn.net/qq_62525547/article/details/136263802

版权

前置配置文章

一、wapper介绍

wrapper的继承体系：

Wrapper ：条件构造抽象类，最顶端父类
- AbstractWrapper ：用于查询条件封装，生成 sql 的 where 条件
  - QueryWrapper ：查询条件封装
  - UpdateWrapper ： Update条件封装
- AbstractLambdaWrapper ：使用Lambda语法
  - LambdaQueryWrapper ：用于Lambda语法使用的查询Wrapper
  - LambdaUpdateWrapper ： Lambda更新封装Wrapper

二、QueryWrapper

1.常用方法

2.关于QueryWrapper的一些测试案例

package com.qcby;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.qcby.SpringBoot.mapper.UserMapper;
import com.qcby.SpringBoot.pojo.User;
import com.qcby.SpringBoot.service.UserService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.*;

@SpringBootTestpublic class MybatisPlusTest {
    @Autowired    private UserMapper userMapper;

    //测试条件构造器    
    @Test    
    public void test01(){
        //查询用户名包含a，年龄在20到30之间，并且邮箱不为null的用户信息        
        //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 AND (username LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name", "a")
                .between("age", 20, 30)
                .isNotNull("email");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }
    @Test    
    public void test02(){
        //按年龄降序查询用户，如果年龄相同则按id升序排列        
        //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE is_deleted=0 ORDER BY age DESC,id ASC        
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("age")
                .orderByAsc("id");
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }
    @Test    
    public void test03(){
        //删除email为空的用户，并非真正的删除，而是逻辑删除        
        //DELETE FROM t_user WHERE (email IS NULL)        
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.isNull("email");
        //条件构造器也可以构建删除语句的条件        
        int result = userMapper.delete(queryWrapper);
        System.out.println("受影响的行数：" + result);
    }
    @Test    
    public void test04() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //将（年龄大于20并且用户名中包含有a）或邮箱为null的用户信息修改        
        //UPDATE t_user SET age=?, email=? WHERE (username LIKE ? AND age > ? OR email IS NULL)        
        queryWrapper                
                .like("name", "a")
                .gt("age", 20)
                .or()
                .isNull("email");
        User user = new User();
        user.setAge(18);
        user.setEmail("user@atguigu.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("受影响的行数：" + result);
    }
    @Test    
    public void test05() {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //将用户名中包含有a并且（年龄大于20或邮箱为null）的用户信息修改        
        //UPDATE t_user SET age=?, email=? WHERE (username LIKE ? AND (age > ? OR email IS NULL))        
        //lambda表达式内的逻辑优先运算        
        //lambda中的i仍然表示该wrapper对象        
        queryWrapper.like("name", "a")
                .and(i -> i.gt("age", 20).or().isNull("email"));
        User user = new User();
        user.setAge(18);
        user.setEmail("user@atguigu.com");
        int result = userMapper.update(user, queryWrapper);
        System.out.println("受影响的行数：" + result);
    }
    @Testpublic 
    void test06() {
        //查询用户信息的username和age字段    
        //SELECT username,age FROM t_user    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("name", "age");
        //selectMaps()返回Map集合列表，通常配合select()使用，避免User对象中没有被查询到的列值 为null    
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper); maps.forEach(System.out::println);
    }
    @Testpublic 
    void test07() {
        //查询id小于等于3的用户信息    
        //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (id IN (select id from t_user where id <= 3))    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.inSql("id", "select id from t_user where id <= 3");
        List<User> list = userMapper.selectList(queryWrapper);
        list.forEach(System.out::println);
    }
}

3.UpdateWrapper

@Testpublic 
void test08() {
    //将（年龄大于20或邮箱为null）并且用户名中包含有a的用户信息修改    
    //组装set子句以及修改条件    
    UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
    //lambda表达式内的逻辑优先运算    
    updateWrapper            
    .set("age", 18)
            .set("email", "user@atguigu.com")
            .like("name", "a")
            .and(i -> i.gt("age", 20).or().isNull("email"));
    //这里必须要创建User对象，否则无法应用自动填充。如果没有自动填充，可以设置为null    
    //UPDATE t_user SET username=?, age=?,email=? WHERE (username 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 (username LIKE ? AND (age > ? OR email IS NULL))    
    int result = userMapper.update(null, updateWrapper);
    System.out.println(result);
}

4.condition

条件判断的两种格式：

@Test
public void test09() {
    //定义查询条件，有可能为null（用户未输入或未选择）    String username = null;
    Integer ageBegin = 10;
    Integer ageEnd = 24;
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace) 构成    
    if(StringUtils.isNotBlank(username)){
        queryWrapper.like("name","a");
    }
    if(ageBegin != null){
        queryWrapper.ge("age", ageBegin);
    }
    if(ageEnd != null){
        queryWrapper.le("age", ageEnd);
    }
    //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (age >= ? AND age <= ?)    
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}
@Test
public void test08UseCondition() {
    //定义查询条件，有可能为null（用户未输入或未选择）    
    String username = null;
    Integer ageBegin = 10;
    Integer ageEnd = 24;
    QueryWrapper<User> queryWrapper = new QueryWrapper<>();
    //StringUtils.isNotBlank()判断某字符串是否不为空且长度不为0且不由空白符(whitespace) 构成    
    queryWrapper            
    .like(StringUtils.isNotBlank(username), "name", "a")
            .ge(ageBegin != null, "age", ageBegin)
            .le(ageEnd != null, "age", ageEnd);
    //SELECT id,username AS name,age,email,is_deleted FROM t_user WHERE (age >= ? AND age <= ?)    
    List<User> users = userMapper.selectList(queryWrapper);
    users.forEach(System.out::println);
}

5.LambdaQueryWrapper和LambdaUpdateWrapper

@Testpublic 
void test11() {
    //组装set子句    
    LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
    updateWrapper            
            .set(User::getAge, 18)
            .set(User::getEmail, "user@atguigu.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);
}