我经常听开发组长老大说不要在mapper层写非常复杂的sql，这种sql写的不能复用，可读性差，难维护（主要这公司的原始代码的sql写的都快成为了存储过程）
所以我这次就全部写的很简单sql，逻辑全写在service层。
出错代码（被注释）
package com.sf.service;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.sf.bean.Area;
import com.sf.bean.T_WADay;
import com.sf.dao.*;
import com.sf.vo.AreaToGrossVo;
import com.sf.vo.MeterToNetworkVo;
import groovy.util.IFileNameFinder;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;
/**
* @Author: bi xuan
* @Date: 2021/7/24 14:08
* @Description: 整理用水分类数据，按照生活，商业，公共等用水类型进行分类
**/
@Service
public class WaterStyleService {
@Resource
private NetWorkMeterDAO netWorkMeterDAO;
@Resource
private NetWorkDAO netWorkDAO;
@Resource
private T_WADayDAO t_waDayDAO;
@Resource
private TexingValueDAO texingValueDAO;
@Resource
private CommonDAO commonDAO;
@Resource
private AreaDAO areaDAO;
/**
* 查询当前月份
*
* @param year
* @param month
* @return
*/
public List<AreaToGrossVo> manage(Integer year,Integer month) {
//找出特性为7的出水表
List<MeterToNetworkVo> meterAndNetwork = commonDAO.getMeterAndNetwork();
//获取所有的区域
List<Integer> areaIds = meterAndNetwork.stream().map(MeterToNetworkVo::getArea_ID).distinct().collect(Collectors.toList());
List<AreaToGrossVo> areaToGrossVos = new ArrayList<>();
DecimalFormat df = new DecimalFormat("#0.00");
for (Integer areaId : areaIds) {
//获取教学类型的水表列表
List<Integer> teachMeters = meterAndNetwork.stream().filter(x -> "1".equals(x.getTexingValue())&&x.getArea_ID().equals(areaId)).map(MeterToNetworkVo::getMeter_ID).collect(Collectors.toList());
//获取公共类型的水表列表
List<Integer> commonMeters = meterAndNetwork.stream().filter(x -> "2".equals(x.getTexingValue())&&x.getArea_ID().equals(areaId)).map(MeterToNetworkVo::getMeter_ID).collect(Collectors.toList());
//获取生活类型的水表列表
List<Integer> lifeMeters = meterAndNetwork.stream().filter(x -> "2".equals(x.getTexingValue())&&x.getArea_ID().equals(areaId)).map(MeterToNetworkVo::getMeter_ID).collect(Collectors.toList());
//获取商业类型的水表列表
List<Integer> businessMeters = meterAndNetwork.stream().filter(x -> "4".equals(x.getTexingValue())&&x.getArea_ID().equals(areaId)).map(MeterToNetworkVo::getMeter_ID).collect(Collectors.toList());
AreaToGrossVo areaToGross = new AreaToGrossVo();
areaToGross.setAreaID(areaId);
// LambdaQueryWrapper<Area> areaLambdaQueryWrapper = new LambdaQueryWrapper<Area>().eq(Area::getAreaID,areaId);
Area area = areaDAO.selectById(areaId);
// Area area = areaDAO.selectOne(areaLambdaQueryWrapper);
areaToGross.setAreaName(area.getAreaName());
if (teachMeters.isEmpty()) {
areaToGross.setTeachValue("0.0");
} else {
//所有教学类型表的用量总和
// Double allTeachMeterSum = 0.0;
// for (Integer teachMeter : teachMeters) {
// LambdaQueryWrapper<T_WADay> eq = new LambdaQueryWrapper<T_WADay>().eq(T_WADay::getMeterID, teachMeter).eq(T_WADay::getSelectYear,year).eq(T_WADay::getSelectMonth,month);
// List<T_WADay> t_waDays = t_waDayDAO.selectList(eq);
// //如果数据集为空
// if (t_waDays.isEmpty()) {
// areaToGross.setTeachValue("0.0");
// } else {
// //教学类型下的某只单表的所有日期下的总和
// double sum = t_waDays.stream().mapToDouble(T_WADay::getZGross).sum();
// allTeachMeterSum += sum;
// }
// }
Double allTeachMeterSum = commonDAO.getGrossByArea(year, month, teachMeters);
areaToGross.setTeachValue(df.format(allTeachMeterSum));
}
if (commonMeters.isEmpty()) {
areaToGross.setCommonValue("0.0");
} else {
// Double allCommonMeterSum = 0.0;
// for (Integer commonMeter : commonMeters) {
// LambdaQueryWrapper<T_WADay> eq = new LambdaQueryWrapper<T_WADay>().eq(T_WADay::getMeterID, commonMeter).eq(T_WADay::getSelectYear,year).eq(T_WADay::getSelectMonth,month);
// List<T_WADay> t_waDays = t_waDayDAO.selectList(eq);
// //如果数据集为空
// if (t_waDays.isEmpty()) {
// areaToGross.setTeachValue("0.0");
// } else {
// double sum = t_waDays.stream().mapToDouble(T_WADay::getZGross).sum();
// allCommonMeterSum += sum;
// }
// }
Double allCommonMeterSum = commonDAO.getGrossByArea(year, month, teachMeters);
areaToGross.setCommonValue(df.format(allCommonMeterSum));
}
if (lifeMeters.isEmpty()) {
areaToGross.setLifeValue("0.0");
} else {
// Double allLifeMeterSum = 0.0;
// for (Integer lifeMeter : lifeMeters) {
// LambdaQueryWrapper<T_WADay> eq = new LambdaQueryWrapper<T_WADay>().eq(T_WADay::getMeterID, lifeMeter).eq(T_WADay::getSelectYear,year).eq(T_WADay::getSelectMonth,month);
// List<T_WADay> t_waDays = t_waDayDAO.selectList(eq);
// //如果数据集为空
// if (t_waDays.isEmpty()) {
// areaToGross.setTeachValue("0.0");
// } else {
// double sum = t_waDays.stream().mapToDouble(T_WADay::getZGross).sum();
// allLifeMeterSum += sum;
// }
// }
Double allLifeMeterSum = commonDAO.getGrossByArea(year, month, teachMeters);
areaToGross.setLifeValue(df.format(allLifeMeterSum));
}
if (businessMeters.isEmpty()) {
areaToGross.setBusinessValue("0.0");
} else {
// Double allBusinessMeterSum = 0.0;
// for (Integer businessMeter : businessMeters) {
// LambdaQueryWrapper<T_WADay> eq = new LambdaQueryWrapper<T_WADay>().eq(T_WADay::getMeterID, businessMeter).eq(T_WADay::getSelectYear,year).eq(T_WADay::getSelectMonth,month);
// List<T_WADay> t_waDays = t_waDayDAO.selectList(eq);
// //如果数据集为空
// if (t_waDays.isEmpty()) {
// areaToGross.setTeachValue("0.0");
// } else {
// double sum = t_waDays.stream().mapToDouble(T_WADay::getZGross).sum();
// allBusinessMeterSum += sum;
// }
// }
Double allBusinessMeterSum = commonDAO.getGrossByArea(year, month, teachMeters);
areaToGross.setBusinessValue(df.format(allBusinessMeterSum));
}
areaToGrossVos.add(areaToGross);
}
return areaToGrossVos;
}
}
<select id="getGrossByArea" resultType="java.lang.Double">
SELECT sum(ZGross) as gross FROM t_waday WHERE Meter_ID IN <foreach collection="meterIds" item="meterId" open="(" separator="," close=")">
#{meterId}
</foreach> AND SelectYear = #{year} AND SelectMonth = #{month}
</select>
发现一个sql可以代替在代码里写循环，修改后的代码运行速度
image.png
修改之前速度:
image.png
企业微信截图_16272914143625.png
其实我们的结论：我们都知道mysql的联表查询性能没有其他的sql强大，似乎pgsql的联表查询的性能最好，所以我们应该写很多表的联表的查询sql，而且在阿里巴巴的开发手册中也明示，mysql紧张超过三表的联查，但是mysql的优势就是他的单表查询能力，所以不用担心单表查询写了十分复杂的sql
网友评论