MyBatis-Plus 自定义 SQL 实现多表复杂查询_mybatis-plus mapper 写复杂sql

发起调用的 service 部分

public IPage<ElevatorMonitorStatusVO> listByCriteria(int page, int size, String registrationCode, String administrativeRegion,
                                                         String usingCorp, String installLocation, String maintenanceCorpName, String deviceType) {

        Page<ElevatorMonitorStatusVO> queryPage = new Page<>(page, size);

        QueryWrapper<Object> queryWrapper = new QueryWrapper<>();
        queryWrapper.like(StringUtils.isNotBlank(registrationCode), "t_elevator.registration_code", registrationCode);
        queryWrapper.like(StringUtils.isNotBlank(administrativeRegion), "t_using_elevator_corporation.administrative_region", administrativeRegion);
        queryWrapper.like(StringUtils.isNotBlank(usingCorp), "t_using_elevator_corporation.`name`", usingCorp);
        queryWrapper.like(StringUtils.isNotBlank(installLocation), "t_elevator.installation_location", installLocation);
        queryWrapper.like(StringUtils.isNotBlank(maintenanceCorpName), "t_maintenance_corporation.corp_name", maintenanceCorpName);
        queryWrapper.like(StringUtils.isNotBlank(deviceType), "t_device.device_type", deviceType);

        IPage<ElevatorMonitorStatusVO> pageResult = elevatorService.queryRealtime(queryPage, queryWrapper);
        List<ElevatorMonitorStatusVO> list = pageResult.getRecords();

        if (CollectionUtils.isNotEmpty(list)) {
            Map<String, String> realtimeStatusMap = statusMonitorService.getRealtimeStatus().stream()
                    .collect(Collectors.toMap(ElevatorRealtimeStatus::getRegistrationCode, ElevatorRealtimeStatus::getUpdateTime));
            list.forEach(item -> assembleStatus(realtimeStatusMap, item));
        }

        return pageResult;
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

service 部分

    @Transactional(propagation = Propagation.SUPPORTS)
    @Override
    public IPage<ElevatorMonitorStatusVO> queryRealtime(Page<ElevatorMonitorStatusVO> page, Wrapper<Object> wrapper) {
        return elevatorMonitorStatusVOMapper.queryRealtime(page, wrapper);
    }
1
2
3
4
5

mapper 部分

public interface ElevatorMonitorStatusVOMapper extends BaseMapper<ElevatorMonitorStatusVO> {

    IPage<ElevatorMonitorStatusVO> queryRealtime(Page<ElevatorMonitorStatusVO> page, @Param(Constants.WRAPPER) Wrapper<Object> wrapper);
}
1
2
3
4

<select id="queryRealtime" resultType="cn.yangxianda.elevator.vo.ElevatorMonitorStatusVO" parameterType="map">
        SELECT
            t_elevator.registration_code AS registrationCode,
            t_using_elevator_corporation.administrative_region AS administrativeRegion,
            t_using_elevator_corporation.`name` AS usingCorp,
            t_elevator.installation_location AS installLocation,
            t_maintenance_corporation.corp_name AS maintenanceCorpName,
            t_device.device_type AS deviceType,
            t_device.communication_line AS communicateLine
        FROM
            elevator t_elevator
        LEFT JOIN
            using_elevator_corporation t_using_elevator_corporation
        ON
            t_elevator.using_elevator_corporation_id = t_using_elevator_corporation.using_elevator_corporation_id
        LEFT JOIN
            maintenance_corporation t_maintenance_corporation
        ON
            t_elevator.maintenance_corporation_id = t_maintenance_corporation.corp_id
        LEFT JOIN
            elevator_device_relat t_elevator_device_relat
        ON
            t_elevator.elevator_id = t_elevator_device_relat.elevator_id
        LEFT JOIN
            device t_device
        ON
            t_elevator_device_relat.device_id = t_device.device_id
        ${ew.customSqlSegment}
    </select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29