MyBatisPlus條件構(gòu)造器的實現(xiàn)示例

更新時間：2023年12月15日 09:24:44 作者：黃晶諦

本文主要介紹了MyBatisPlus條件構(gòu)造器的實現(xiàn)示例,主要包括了QueryWrapper,UpdateWrapper,LambdaQueryWrapper,LambdaUpdateWrapper這四種,具有一定的參考價值,感興趣的可以了解下

QueryWrapper

1 查詢示例

    /**
     * ==>  Preparing: SELECT id,user_name AS name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (user_name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL)
     * ==> Parameters: %雷%(String), 10(Integer), 30(Integer)
     * <==    Columns: id, name, age, email, is_deleted
     * <==        Row: 1734579107187970050, 李雷, 12, lilei@giser.com, 0
     * <==      Total: 1
     * 用戶列表信息為：[User(id=1734579107187970050, name=李雷, age=12, email=lilei@giser.com, isDeleted=0)]
     */
    @Test
    void testQueryWrapper01(){
        // 查詢用戶名包含“雷”字，且年齡在20到30之間，郵箱不為null的用戶信息
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("user_name", "雷")
                .between("age", 10, 30)
                .isNotNull("email");

        List<User> userList = userMapper.selectList(queryWrapper);
        log.info("用戶列表信息為：{}", userList);
    }

    /**
     * SELECT id,user_name AS name,age,email,is_deleted FROM user WHERE is_deleted=0 ORDER BY age DESC,id ASC
     */
    @Test
    void testOrderBy02(){
        // 查詢用戶列表，按照年齡降序，按照ID升序排序
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.orderByDesc("age")
                .orderByAsc("id");
        List<User> userList = userMapper.selectList(queryWrapper);
        log.info("用戶列表信息為：{}", userList);
    }

組裝select子句

    /**
     * 組裝select子句
     */
    @Test
    void testCompSubSql(){
        // 只查詢部分字段
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("user_name", "age", "email");
        List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
        log.info("查詢部分字段：{}", maps);
    }

子查詢

    /**
     * 子查詢
     * ==>  Preparing: SELECT id,user_name AS name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (id IN (select id from t_user where age > 10))
     * ==> Parameters:
     * <==    Columns: id, name, age, email, is_deleted
     * <==        Row: 1, Jone, 18, test1@baomidou.com, 0
     * <==        Row: 2, 竊聽風(fēng)云, 23, giserDev@163.com, 0
     * <==        Row: 3, Tom, 28, test3@baomidou.com, 0
     * <==      Total: 3
     * 查詢結(jié)果為：[User(id=1, name=Jone, age=18, email=test1@baomidou.com, isDeleted=0), User(id=2, name=竊聽風(fēng)云, age=23, email=giserDev@163.com, isDeleted=0), User(id=3, name=Tom, age=28, email=test3@baomidou.com, isDeleted=0)]
     */
    @Test
    void testCompSubSql2(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.inSql("id", "select id from t_user where age > 10");
        List<User> userList = userMapper.selectList(queryWrapper);
        log.info("查詢結(jié)果為：{}", userList);
    }

2 刪除示例

    /**
     * ==>  Preparing: UPDATE user SET is_deleted=1 WHERE is_deleted=0 AND (user_name = ? AND age = ?)
     * ==> Parameters: 李雷(String), 12(Integer)
     * <==    Updates: 1
     */
    @Test
    void testDelete03(){
        // 刪除功能測試，此時為邏輯刪除，實際上執(zhí)行的是更新語句
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("user_name", "李雷")
                .eq("age", 12);
        int delete = userMapper.delete(queryWrapper);
        log.info("用戶列表信息為：{}", delete);
    }

3 更新示例

    /**
     * ==>  Preparing: UPDATE user SET user_name=?, age=?, email=? WHERE is_deleted=0 AND (user_name = ? AND age = ? OR email IS NULL)
     * ==> Parameters: 韓梅梅(String), 23(Integer), giserDev@163.com(String), 李雷(String), 12(Integer)
     * <==    Updates: 1
     */
    @Test
    void testUpdate01(){
        // 更新姓名為”李雷“且年齡為12歲，或郵箱為空的數(shù)據(jù)
        // case 1: 使用QueryWrapper
        // 設(shè)定更新條件
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("user_name", "李雷")
                .eq("age", 12)
                .or()
                .isNull("email");
        // 設(shè)定更新數(shù)據(jù)
        User updUser = new User();
        updUser.setEmail("giserDev@163.com");
        updUser.setName("韓梅梅");
        updUser.setAge(23);
        int update = userMapper.update(updUser, queryWrapper);
        log.info("更新條數(shù)為：{}", update);
    }

MybatisPlus條件優(yōu)先級：lambda表達(dá)式內(nèi)的邏輯優(yōu)先運算

    /**
     * ==>  Preparing: UPDATE user SET user_name=?, age=?, email=? WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
     * ==> Parameters: 竊聽風(fēng)云(String), 23(Integer), giserDev@163.com(String), %J%(String), 18(Integer)
     * <==    Updates: 1
     */
    @Test
    void testUpdate02(){
        // 將用戶名中包含有J并且（年齡大于18或郵箱為null）的用戶信息修改
        // case 2: lambda表達(dá)式內(nèi)的邏輯優(yōu)先運算
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like("user_name", "J")
                .and(
                        wrapper -> wrapper.gt("age", 18)
                                .or()
                                .isNull("email")
                );
        // 設(shè)定更新數(shù)據(jù)
        User updUser = new User();
        updUser.setEmail("giserDev@163.com");
        updUser.setName("竊聽風(fēng)云");
        updUser.setAge(23);
        int update = userMapper.update(updUser, queryWrapper);
        log.info("更新條數(shù)為：{}", update);
    }
}

UpdateWrapper

    /**
     * UPDATE user SET user_name=?,email=? WHERE is_deleted=0 AND (user_name = ? AND (age > ? AND email IS NULL))
     */
    @Test
    void testUpdateWrapper(){
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("user_name", "kitty")
                .and(wrapper -> wrapper.gt("age", 10).isNull("email"));
        updateWrapper.set("user_name", "gis2023").set("email", "gis2023@163.com");
        int update = userMapper.update(updateWrapper);
        log.info("更新條數(shù)為：{}", update);
//        int update1 = userMapper.update(null, updateWrapper);
//        log.info("更新條數(shù)為：{}", update1);
    }

4 條件組裝

示例一

    /**
     * ==>  Preparing: SELECT id,user_name AS name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (user_name LIKE ? AND age >= ? AND age <= ?)
     * ==> Parameters: %張三%(String), 10(Integer), 20(Integer)
     * <==      Total: 0
     */
    @Test
    void testDynamicSqlWrapper(){
        String username = "張三";
        Integer ageBgn = 10;
        Integer ageEnd = 20;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        if (StringUtils.isNotBlank(username)){
            queryWrapper.like("user_name", username);
        }

        if (ageBgn != null){
            queryWrapper.ge("age", ageBgn);
        }

        if (ageEnd != null){
            queryWrapper.le("age", ageEnd);
        }

        List<User> userList = userMapper.selectList(queryWrapper);
        log.info("查詢記錄為：{}", userList);
    }

示例二使用condition

    /**
     * 使用condition組裝條件
     * ==>  Preparing: SELECT id,user_name AS name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (user_name LIKE ? AND age >= ? AND age <= ?)
     * ==> Parameters: %張三%(String), 10(Integer), 20(Integer)
     * <==      Total: 0
     */
    @Test
    void testDynamicSqlWrapper02(){
        String username = "張三";
        Integer ageBgn = 10;
        Integer ageEnd = 20;
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(username), "user_name", username)
                .ge(ageBgn != null, "age", ageBgn)
                .le(ageEnd != null, "age", ageEnd);

        List<User> userList = userMapper.selectList(queryWrapper);
        log.info("查詢記錄為：{}", userList);
    }

LambdaQueryWrapper

    /**
     *
     * ==>  Preparing: SELECT id,user_name AS name,age,email,is_deleted FROM user WHERE is_deleted=0 AND (user_name LIKE ? AND age >= ? AND age <= ?)
     * ==> Parameters: %張三%(String), 10(Integer), 20(Integer)
     * <==      Total: 0
     */
    @Test
    void testDynamicSqlWrapper03(){
        String username = "張三";
        Integer ageBgn = 10;
        Integer ageEnd = 20;
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        lambdaQueryWrapper.like(StringUtils.isNotBlank(username), User::getName, username)
                .ge(ageBgn != null, User::getAge, ageBgn)
                .le(ageEnd != null, User::getAge, ageEnd);

        List<User> userList = userMapper.selectList(lambdaQueryWrapper);
        log.info("查詢記錄為：{}", userList);
    }

LambdaUpdateWrapper

    /**
     * ==>  Preparing: UPDATE user SET user_name=?,email=? WHERE is_deleted=0 AND (user_name = ? AND (age > ? AND email IS NULL))
     * ==> Parameters: gis2023(String), gis2023@163.com(String), kitty(String), 10(Integer)
     * <==    Updates: 0
     */
    @Test
    void testUpdateWrapper02(){
        LambdaUpdateWrapper<User> lambdaUpdateWrapper = new LambdaUpdateWrapper<>();
        lambdaUpdateWrapper.eq(User::getName, "kitty")
                .and(wrapper -> wrapper.gt(User::getAge, 10).isNull(User::getEmail));
        lambdaUpdateWrapper.set(User::getName, "gis2023").set(User::getEmail, "gis2023@163.com");
        int update = userMapper.update(lambdaUpdateWrapper);
        log.info("更新條數(shù)為：{}", update);
//        int update1 = userMapper.update(null, lambdaUpdateWrapper);
//        log.info("更新條數(shù)為：{}", update1);
    }