MyBatisPlus条件构造器

曾经终败给现在 2024-03-30 12:48 72阅读 0赞

## 一、wapper介绍 ##

![在这里插入图片描述][bb4c8773f6db4a93967ba38835825b7b.png]  
![在这里插入图片描述][bb4d1ccaa6cd403385cc324f5bf2828f.png]

## 二、AbstractWrapper ##

注意：以下条件构造器的方法入参中的 column 均表示数据库字段

### 1.ge、gt、le、lt、isNull、isNotNull ###

@Test
    public void testSelect() {
        
    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.ge("age", 28);
        List<User> users = userMapper.selectList(queryWrapper);
        System.out.println(users);
    }

### 2.eq、ne ###

注意：seletOne返回的是一条实体记录，当出现多条时会报错

@Test
    public void testSelectOne() {
        
    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("name", "Tom");
    
        User user = userMapper.selectOne(queryWrapper);
        System.out.println(user);
    }

SELECT id,name,age,email,create\_time,update\_time,deleted,version FROM user WHERE deleted=0 AND name = ?

### 3.between、notBetween ###

包含大小边界

@Test
    public void testSelectCount() {
        
    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.between("age", 20, 30);
    
        Integer count = userMapper.selectCount(queryWrapper);
        System.out.println(count);
    }

SELECT COUNT(1) FROM user WHERE deleted=0 AND age BETWEEN ? AND ?

### 4.allEq ###

@Test
    public void testSelectList() {
        
    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        Map<String, Object> map = new HashMap<>();
        map.put("id", 2);
        map.put("name", "Jack");
        map.put("age", 20);
    
        queryWrapper.allEq(map);
        List<User> users = userMapper.selectList(queryWrapper);
    
        users.forEach(System.out::println);
    }

SELECT id,name,age,email,create\_time,update\_time,deleted,version  
FROM user WHERE deleted=0 AND name = ? AND id = ? AND age = ?

### 5.like、notLike、likeLeft、likeRight ###

selectMaps返回Map集合列表

@Test
    public void testSelectMaps() {
        
    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper
            .notLike("name", "e")
            .likeRight("email", "t");
    
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);//返回值是Map列表
        maps.forEach(System.out::println);
    }

SELECT id,name,age,email,create\_time,update\_time,deleted,version  
FROM user WHERE deleted=0 AND name NOT LIKE ? AND email LIKE ?

### 6.in、notIn、inSql、notinSql、exists、notExists ###

in、notIn：

*  notIn(“age”,\{1,2,3\})—>age not in (1,2,3)
 *  notIn(“age”, 1, 2, 3)—>age not in (1,2,3)

inSql、notinSql：可以实现子查询

*  例: inSql(“age”, “1,2,3,4,5,6”)—>age in (1,2,3,4,5,6)
 *  例: inSql(“id”, “select id from table where id < 3”)—>id in (select id from table where id < 3)

@Test
    public void testSelectObjs() {
        
    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //queryWrapper.in("id", 1, 2, 3);
        queryWrapper.inSql("id", "select id from user where id < 3");
    
        List<Object> objects = userMapper.selectObjs(queryWrapper);//返回值是Object列表
        objects.forEach(System.out::println);
    }

SELECT id,name,age,email,create\_time,update\_time,deleted,version  
FROM user WHERE deleted=0 AND id IN (select id from user where id < 3)

### 7.or、and ###

注意：这里使用的是 UpdateWrapper  
不调用or则默认为使用 and 连

@Test
    public void testUpdate1() {
        
    
        //修改值
        User user = new User();
        user.setAge(99);
        user.setName("Andy");
    
        //修改条件
        UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
        userUpdateWrapper
            .like("name", "h")
            .or()
            .between("age", 20, 30);
    
        int result = userMapper.update(user, userUpdateWrapper);
    
        System.out.println(result);
    }

UPDATE user SET name=?, age=?, update\_time=? WHERE deleted=0 AND name LIKE ? OR age BETWEEN ? AND ?

### 8.嵌套or、嵌套and ###

这里使用了lambda表达式，or中的表达式最后翻译成sql时会被加上圆括号

@Test
    public void testUpdate2() {
        
    
    
        //修改值
        User user = new User();
        user.setAge(99);
        user.setName("Andy");
    
        //修改条件
        UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
        userUpdateWrapper
            .like("name", "h")
            .or(i -> i.eq("name", "李白").ne("age", 20));
    
        int result = userMapper.update(user, userUpdateWrapper);
    
        System.out.println(result);
    }

UPDATE user SET name=?, age=?, update\_time=?  
WHERE deleted=0 AND name LIKE ?  
OR ( name = ? AND age <> ? )

### 9.orderBy、orderByDesc、orderByAsc ###

@Test
    public void testSelectListOrderBy() {
        
    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("id");
    
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

SELECT id,name,age,email,create\_time,update\_time,deleted,version  
FROM user WHERE deleted=0 ORDER BY id DESC

### 10.last ###

直接拼接到 sql 的最后  
注意：只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用

@Test
    public void testSelectListLast() {
        
    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.last("limit 1");
    
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

SELECT id,name,age,email,create\_time,update\_time,deleted,version  
FROM user WHERE deleted=0 limit 1

### 11.指定要查询的列 ###

@Test
    public void testSelectListColumn() {
        
    
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("id", "name", "age");
    
        List<User> users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

SELECT id,name,age FROM user WHERE deleted=0

### 12.set、setSql ###

最终的sql会合并 user.setAge()，以及 userUpdateWrapper.set() 和 setSql() 中 的字段

@Test
    public void testUpdateSet() {
        
    
        //修改值
        User user = new User();
        user.setAge(99);
    
        //修改条件
        UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
        userUpdateWrapper
            .like("name", "h")
            .set("name", "老李头")//除了可以查询还可以使用set设置修改的字段
            .setSql(" email = '123@qq.com'");//可以有子查询
    
        int result = userMapper.update(user, userUpdateWrapper);
    }

UPDATE user SET age=?, update\_time=?, name=?, email = ‘123@qq.com’ WHERE deleted=0 AND name LIKE ?

[bb4c8773f6db4a93967ba38835825b7b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/3c23cfcbe5ec4334bb131161309aeda9.png
[bb4d1ccaa6cd403385cc324f5bf2828f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/47bcebe4e4ed46a681c23426375a52ae.png