526互联

MyBatisPlus 注解方式实现多表关联查询

发布时间 2023-11-21 17:36:48作者: 且行且思

Mapper写法：

@Select("SELECT * FROM tableA a LEFT JOIN tableB b on a.key = b.key ${ew.customSqlSegment}")
List method1(@Param(Constants.WRAPPER) QueryWrapper wrapper);
IPage method2(Page<> page, @Param(Constants.WRAPPER) QueryWrapper wrapper);

 

需要注意：ew是wrapper定义别名,不能使用其他的替换;
需要注意：用法 ${ew.customSqlSegment} (不需要where标签包裹,切记!);
需要注意：wrapper不能为null，可以用new QueryWrapper<>();

 

entity写法：

    查询model中，如果既有A表参数，又有B表参数，需要在entity中添加字段
    返回结果vo中，和A、B表对应上的字段都会自动赋值

service写法：

    封装wrapper时，column字段最好写明表名。例：wrapper.eq(StringUtils.isNotBlank(“xxx”), “A.column”,“value”);

@Override
    public void getRecord() {

        //返回值为list
        QueryWrapper<PassRecord> wrapper = new QueryWrapper<>();
        wrapper.eq(StringUtils.isNotBlank(""), "user_name","aaa");
        wrapper.eq("p.card_id","44520xxx");
        DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMMdd HH:mm:ss");
        LocalDateTime dateTime = LocalDateTime.parse("20210611 18:04:00", dateTimeFormatter);
        Date startDate = Date.from(dateTime.atZone(ZoneId.systemDefault()).toInstant());
        LocalDateTime dateTime2 = LocalDateTime.parse("20210615 18:04:00", dateTimeFormatter);
        Date endDate = Date.from(dateTime2.atZone(ZoneId.systemDefault()).toInstant());
        wrapper.between("p.create_time",startDate, endDate);
        List<PassRecord> list = passRecordMapper.getRecordParam(wrapper);
        list.forEach(passRecord -> {
            System.out.println(passRecord.getUserName() +"=="+ passRecord.getWorkPlace());
        });

        //返回page对象
        Page<PassInfoDto> page = new Page<>();
        page.setCurrent(1L);
        page.setSize(2L);
        QueryWrapper<PassRecord> queryWrapper = new QueryWrapper<>();
        List<PassRecord> recordList = passRecordMapper.getRecordPageParam(page, queryWrapper);
        recordList.forEach(record -> {
            System.out.println(record.getUserName() + "" + record.getRecordId());
        });

        IPage<PassRecord> iPage = passRecordMapper.getRecordParamToPage(page, queryWrapper);
        System.out.println(JSON.toJSONString(iPage));
    }