多表join反范式的java实现复杂范例！

依赖的父表检索条件多时，不适合java层去实现数据筛选，过程非常复杂，且容易逻辑判断出错！

依赖的父表检索条件多时，不适合java层去实现数据筛选，过程非常复杂，且容易逻辑判断出错！

如果依赖的父表没有检索条件，都是基于被检索表的字段筛选，那么用java实现就比较简单。

范式约定的sql示例：

 

java代码实现多表join示例：

public List<ReportSensorDay> reportSensorDay(ReportSensorDay reportGroup) {

        DevSiteConfig siteDo = devSiteConfigDao.lambdaQuery().eq(StringUtils.isNotEmpty(reportGroup.getSiteCode()), DevSiteConfig::getSiteCode, reportGroup.getSiteCode())
                .one();
        List<DevUnit> unitSelectList = null;
        if (ObjectUtils.isNotEmpty(siteDo)) {
            unitSelectList = devUnitDao.lambdaQuery().eq(ObjectUtils.isNotEmpty(siteDo), DevUnit::getSiteCode, siteDo.getSiteCode()).list();
        }
        // 如果用户选择站点，就需要计算unitSelectCodes
        List<String> unitSelectCodes = null;
        if (!CollectionUtils.isEmpty(unitSelectList)) {
            unitSelectCodes = unitSelectList.stream().map(DevUnit::getUnitCode).collect(Collectors.toList());
        }

        PageUtils.startPage();
        List<ReportSensorDay> reportList = reportSensorDayDao.lambdaQuery()
                .eq(StringUtils.isNotEmpty(reportGroup.getSensorCode()), ReportSensorDay::getSensorCode, reportGroup.getSensorCode())
                .eq(StringUtils.isNotEmpty(reportGroup.getSensorType()), ReportSensorDay::getSensorType, reportGroup.getSensorType())
                .eq(StringUtils.isNotEmpty(reportGroup.getIsException()), ReportSensorDay::getIsException, reportGroup.getIsException())
                .eq(StringUtils.isNotEmpty(reportGroup.getUnitCode()), ReportSensorDay::getUnitCode, reportGroup.getUnitCode())
                .between(reportGroup.getStartTime() != null && reportGroup.getEndTime() != null, ReportSensorDay::getCreateTime,
                        reportGroup.getStartTime(), reportGroup.getEndTime())
                .in(!CollectionUtils.isEmpty(unitSelectCodes), ReportSensorDay::getUnitCode, unitSelectCodes)
                .orderByDesc(ReportSensorDay::getUpdateTime)
                .list();

        if (CollectionUtils.isEmpty(reportList)) {
            return reportList;
        }

        //不管是否过滤查询，都需要进行以下信息关联匹配
        List<String> sensorCodes = reportList.stream().map(ReportSensorDay::getSensorCode).collect(Collectors.toList());
        List<DevSensor> sensorDoList = devSensorDao.lambdaQuery().in(DevSensor::getSensorCode, sensorCodes).list();
        List<String> unitCodes = sensorDoList.stream().map(DevSensor::getUnitCode).collect(Collectors.toList());
        List<DevUnit> unitDoList = devUnitDao.lambdaQuery().in(DevUnit::getUnitCode, unitCodes).list();
        List<String> siteCodes = unitDoList.stream().map(DevUnit::getSiteCode).collect(Collectors.toList());
        List<DevSiteConfig> siteDoList = devSiteConfigDao.lambdaQuery().in(DevSiteConfig::getSiteCode, siteCodes).list();

        reportList.stream().forEach(report -> {
            Optional<DevSensor> sensorFirst = sensorDoList.stream().filter(sensor -> StringUtils.equals(sensor.getSensorCode(), report.getSensorCode())).findFirst();
            if (sensorFirst.isPresent()) {
                DevSensor devSensor = sensorFirst.get();
                report.setSensorType(devSensor.getSensorTypeName());
            }

            Optional<DevSiteConfig> siteFirst = null;
            Optional<DevUnit> unitFirst = unitDoList.stream().filter(unit -> StringUtils.equals(unit.getUnitCode(), report.getUnitCode())).findFirst();
            if (unitFirst != null && unitFirst.isPresent()){
                DevUnit devUnit = unitFirst.get();
                report.setServerName(devUnit.getServerName());
                report.setUnitName(devUnit.getUnitName());
                siteFirst = siteDoList.stream().filter(site -> StringUtils.equals(site.getSiteCode(), devUnit.getSiteCode())).findFirst();
            }

            if (siteFirst != null && siteFirst.isPresent()){
                DevSiteConfig siteConfig = siteFirst.get();
                report.setSiteName(siteConfig.getSiteName());
                report.setSiteCode(siteConfig.getSiteCode());
            }

        });

        return reportList;
    }