Mybatis-Plus使用进阶

逻辑条件

        // A and B :: (area_type = ? AND name LIKE ?)
        LambdaQueryWrapper<AreaMp> rapper = Wrappers.<AreaMp>lambdaQuery()
                .select(AreaMp::getId, AreaMp::getName)
                .eq(AreaMp::getAreaType, 2)
                .like(AreaMp::getName, "河");

        areaMpDao.list(rapper);
        rapper.clear();

        //  A or B  :: (area_type = ? OR name LIKE ?)
        rapper
                .eq(AreaMp::getAreaType, 2)
                .or()
                .like(AreaMp::getName, "河");
        areaMpDao.list(rapper);
        rapper.clear();

        // A and B  or C and D :: ((area_type = ? AND name LIKE ?) OR (area_type IN (?,?) AND name LIKE ?))
        rapper
                .nested(r -> r.eq(AreaMp::getAreaType, 2).like(AreaMp::getName, "河"))
                .or(r -> r.in(AreaMp::getAreaType, 2, 3).like(AreaMp::getName, "山"));
        areaMpDao.list(rapper);
        rapper.clear();

        //  (A or B ) and (C or D) :: ((area_type = ? OR name LIKE ?) AND (area_type IN (?,?) OR name LIKE ?))
        rapper
                .nested(r -> r.eq(AreaMp::getAreaType, 2).or().like(AreaMp::getName, "河"))
                .and(r -> r.in(AreaMp::getAreaType, 2, 3).or().like(AreaMp::getName, "山"));
        areaMpDao.list(rapper);

group

        LambdaQueryWrapper<AreaMp> lq = new LambdaQueryWrapper<>();
        lq.select(AreaMp::getAreaType, AreaMp::getIsPrimary)
                .groupBy(AreaMp::getAreaType, AreaMp::getIsPrimary)
                .orderByAsc(AreaMp::getAreaType) // count desc
                .last("limit 10");

        // SELECT area_type,is_primary FROM area GROUP BY area_type,is_primary ORDER BY area_type ASC limit 10
        List<Map<String, Object>> map = areaMpDao.listMaps(lq);


        Wrapper<AreaMp> qw = Wrappers.<AreaMp>query()
                .select("count(*) as nums, area_type")
                .orderByDesc("nums")

                .lambda()
                .eq(AreaMp::getIsPrimary, 0)
                .groupBy(AreaMp::getAreaType)
                .having("nums > 5")
//                .having("nums > {0}",5)
                .last("limit 10");


        // SELECT count(*) as nums, area_type FROM area WHERE (is_primary = ?) GROUP BY area_type HAVING nums > 5 ORDER BY nums DESC limit 10
        List<Map<String, Object>> maps = areaMpDao.listMaps(qw);

        // map 转 bean
        List<GroupInfo> collect = maps.stream()
                .map(v -> JSON.parseObject(JSON.toJSONString(v), GroupInfo.class))
                .collect(Collectors.toList());

distinct

        QueryWrapper<AreaMp> wrapper = Wrappers.<AreaMp>query()
                .select("distinct area_type");
        // SELECT distinct area_type FROM area
        List<Map<String, Object>> maps = areaMpDao.listMaps(wrapper);


        wrapper.clear();

        wrapper.lambda()
        .select(AreaMp::getAreaType)
                .groupBy(AreaMp::getAreaType)
                .orderByAsc(AreaMp::getAreaType);
        // SELECT area_type FROM area GROUP BY area_type ORDER BY area_type ASC
        List<Map<String, Object>> mapList = areaMpDao.listMaps(wrapper);

exists

        LambdaQueryWrapper<AreaMp> wrapper = Wrappers.<AreaMp>lambdaQuery()
                .select(AreaMp::getAreaType).groupBy(AreaMp::getAreaType)
                .exists("select 1 from area b where b.id= area.parent_id");

        // SELECT area_type FROM area WHERE 
        // (EXISTS (select 1 from area b where b.id= area.parent_id)) 
        // GROUP BY area_type
        List<Map<String, Object>> maps = areaMpDao.listMaps(wrapper);


        wrapper.clear();
        wrapper
                .select(AreaMp::getAreaType).groupBy(AreaMp::getAreaType)
                .notExists("select 1 from area b where b.id= area.parent_id");
        // SELECT area_type FROM area WHERE 
        // (NOT EXISTS (select 1 from area b where b.id= area.parent_id)) 
        // GROUP BY area_type
        List<Map<String, Object>> maps2 = areaMpDao.listMaps(wrapper);

inSql

        LambdaQueryWrapper<AreaMp> wrapper = Wrappers.<AreaMp>lambdaQuery()
                .inSql(AreaMp::getParentId,"select id from area where area_type = 2")
                .last("limit 10");

        // SELECT id,name,area_type,parent_id,is_primary,pinyin_initial,full_pinyin,full_word_pinyin,addtime,modtime FROM area
        // WHERE (parent_id IN (select id from area where area_type = 2)) limit 10
        List<AreaMp> list = areaMpDao.list(wrapper);

apply

        LambdaQueryWrapper<AreaMp> wrapper = Wrappers.<AreaMp>lambdaQuery()
                .apply("area_type = {0}",2);
        // SELECT id,name,area_type,parent_id,is_primary,pinyin_initial,full_pinyin,full_word_pinyin,addtime,modtime FROM area 
        // WHERE (area_type = ?)
        List<AreaMp> list = areaMpDao.list(wrapper);

上一篇

Mybatis-Plus使用基础

Reference

• https://github.com/baomidou/mybatis-plus-samples