代码改造实录--使用Stream替代多次数据库查询

  近来接手了一套代码,里面有很多感觉可以改进的地方,修改后做个记录。

  话不多说,直接上代码。代码的大意是对一系列对象数据按照某字段来统计另一个字段的和。原来的代码,调用了五次数据接口:

int tsjt = measureRepository.findScoreByTypeAndUserIdAndDate("tenderness", userId, date);
int tnsj = measureRepository.findScoreByTypeAndUserIdAndDate("swelling", userId, date);
int stij = measureRepository.findScoreByTypeAndUserIdAndDate("temperature", userId, date);
int leftHand = measureRepository.findScoreByCodeAndUserIdAndDate("left_grip", userId, date);
int rightHand = measureRepository.findScoreByCodeAndUserIdAndDate("right_grip", userId, date);

  对应两个数据接口:

@Query(
    value = "select ifnull(sum(score),0) as score from pride_measured where type=:type and user_id=:userId and date=:date ",
    nativeQuery = true)
int findScoreByTypeAndUserIdAndDate(String type, Integer userId, Date date);
@Query(
    value = "select ifnull(sum(score),0) as score from pride_measured where code=:code and user_id=:userId and date=:date",
    nativeQuery = true)
int findScoreByCodeAndUserIdAndDate(String code, Integer userId, Date date);Date date);

  改造为只调用一次数据查询接口,然后通过java8 Stream特性来处理:

        Measure measureQuery = new Measure();
        measureQuery.setUserId(userId);
        measureQuery.setDate(date);
        List<Measure> list = measureService.queryMeasures(measureQuery);

        Map<String, IntSummaryStatistics> measuresByType = list.stream()
            .collect(Collectors.groupingBy(Measure::getType, Collectors.summarizingInt(Measure::getScore)));
        int tsjt = measuresByType.get("tenderness") == null ? 0 : (int)measuresByType.get("tenderness").getSum();
        int tnsj = measuresByType.get("swelling") == null ? 0 : (int)measuresByType.get("swelling").getSum();
        int stij = measuresByType.get("temperature") == null ? 0 : (int)measuresByType.get("temperature").getSum();

        Map<String, Integer> measuresByCode = list.stream().filter(item -> ("grip".equals(item.getType())))
            .collect(Collectors.toMap(Measure::getCode, Measure::getScore));
        int leftHand = measuresByCode.get("left_grip") == null ? 0 : measuresByCode.get("left_grip");
        int rightHand = measuresByCode.get("right_grip") == null ? 0 : measuresByCode.get("right_grip");

  分别三次测试该方法的原来代码执行时间和现代码执行时间(其中包含无符合条件数据和有符合条件数据两种情况),原来执行时间分别为:1059ms、1281ms、1371ms,改造后执行时间分别为:928ms、922ms、1067ms,可以看到执行时间在同样情况下改造后有明显的改观。

  本次改造,除了减少了访问数据库次数节省时间外,还把处理集中到了服务程序中,便于分布式扩展。

 

代码改造实录--使用Stream替代多次数据库查询

上一篇:数据库---视图(view)


下一篇:数据库相关知识总结