用 EntityManager 做复杂统计、分页、排序、查询、筛选MySQL转自定义对象（其中某些字段数据库中没有）及优化方案，附实验结果

JPA 复杂统计、查询MySQL转自定义对象（其中某些字段数据库中没有）及优化方案

使用EntityManager进行复杂查询

​
@Service
@Slf4j
public class statisticService {
​
    @Autowired
    private EntityManager entityManager;
​
    public Page<RecordMerchantVO> queryMerchantRecord(QueryRecordReq req) {
        long start = System.currentTimeMillis();
        String sortWord = req.getSortWord();
        String direction = req.getDirection();
        //新建一个页面，存放页面信息     
        Pageable page = new PageRequest(req.getPage() - 1, req.getSize());
        if (StringUtils.isNotBlank(req.getStoreName()) || StringUtils.isNotBlank(req.getMerchantId()) || StringUtils.isNotBlank(req.getStoreId())) {
            String storeId = getStoreId(req.getStoreId(), req.getMerchantId(), req.getStoreName());
            if (StringUtils.isEmpty(storeId)) {
                return new PageImpl<>(new ArrayList<>(), page, 0);
            }
            req.setStoreId(storeId);
        }
        //criteriaBuilder用于构建CriteriaQuery的构建器对象
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        //相当于select storeId 、merchantId、店铺总烹饪的菜谱数、 失败菜谱数、菜譜烹飪成功次數
        /**
         * select count(1) as total,
         * sum(case status when 3 then 1 else 0 end ) as errorCounts,
         * sum(case status when 2 then 1 else 0 end) as success,
         * store_id,merchant_id,
         * COUNT(DISTINCT DATE(task_date))
         * from recipe_cook_task
         * group by store_id, merchant_id
         * limit page,size
         */
        
        //criteriaQuery包含查询语句的各个部分，如where、max、sum、groupBy、orderBy等
        CriteriaQuery<RecordMerchantResult> criteriaQuery = cb.createQuery(RecordMerchantResult.class);
        //获取查询实例的属性，select * from recipe_cook_task （数据库实体）、统计单列多值
        Root<RecipeCookTask> root = criteriaQuery.from(RecipeCookTask.class);
        criteriaQuery.multiselect(root.get("storeId"),
                root.get("merchantId"),
               cb.count(cb.selectCase().when(root.get("status").in(Arrays.asList(RecipeCookTask.STATUS.FAILURE, RecipeCookTask.STATUS.CANCEL)), 1).otherwise(cb.nullLiteral(Number.class))),
                cb.count(cb.selectCase().when(root.get("status").in(Arrays.asList(RecipeCookTask.STATUS.FINISHED)), 1).otherwise(cb.nullLiteral(Number.class))),
                cb.count(root.get("storeId")),
                cb.countDistinct((root.get("taskDate")))
        );
        List<Predicate> predicateList = new ArrayList<>();
        if (!StringUtils.isEmpty(req.getStoreId())) {
            predicateList.add(cb.equal(root.get("storeId").as(String.class), req.getStoreId()));
        }
        if (!StringUtils.isEmpty(req.getMerchantId())) {
            predicateList.add(cb.like(root.get("merchantId").as(String.class), "%" + req.getMerchantId().trim() + "%"));
        }
        if (req.getStartTime() != null && req.getEndTime() != null) {
            log.info("开始时间：" + new Date(req.getStartTime()));
            log.info("结束时间：" + new Date(req.getEndTime()));
            predicateList.add(cb.greaterThanOrEqualTo(root.get("createTime").as(Date.class), new Date(req.getStartTime())));
            predicateList.add(cb.lessThanOrEqualTo(root.get("createTime").as(Date.class), new Date(req.getEndTime())));
        }
        Predicate[] p = new Predicate[predicateList.size()];
        criteriaQuery.where(cb.and(predicateList.toArray(p)));
        criteriaQuery.groupBy(root.get("storeId"), root.get("merchantId"));
        // orderBy 相关字段必须在查询中
        List<RecordMerchantResult> counts = entityManager.createQuery(criteriaQuery).getResultList();
        log.info("排序前{}", counts.stream().map(RecordMerchantResult::getCuisineCount).collect(Collectors.toList()));
        long start0 = System.currentTimeMillis();
        log.info("耗费时间：{}", start0 - start);
        if ("DESC".equals(direction)) {
            if (StringUtils.equals(sortWord, "cuisineCount")) {
                counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getCuisineCount).reversed()).collect(Collectors.toList());
            }
            if (StringUtils.equals(sortWord, "errorCuisineCount")) {
                counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getErrorCuisineCount).reversed()).collect(Collectors.toList());
            }
            if (StringUtils.equals(sortWord, "successCuisineCount")) {
                counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getSuccessCuisineCount).reversed()).collect(Collectors.toList());
            }
            if (StringUtils.equals(sortWord, "deviceUsageDays")) {
                counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getDeviceUsageDays).reversed()).collect(Collectors.toList());
            }
        }
​
        if ("ASC".equals(direction)) {
            if (StringUtils.equals(sortWord, "cuisineCount")) {
                counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getCuisineCount)).collect(Collectors.toList());
                log.info("排序后ASC{}", counts.stream().map(RecordMerchantResult::getCuisineCount).collect(Collectors.toList()));
            }
            if (StringUtils.equals(sortWord, "errorCuisineCount")) {
                counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getErrorCuisineCount)).collect(Collectors.toList());
            }
            if (StringUtils.equals(sortWord, "successCuisineCount")) {
                counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getSuccessCuisineCount)).collect(Collectors.toList());
            }
            if (StringUtils.equals(sortWord, "deviceUsageDays")) {
                counts = counts.stream().sorted(Comparator.comparing(RecordMerchantResult::getDeviceUsageDays)).collect(Collectors.toList());
            }
        }
​
​
        //sql查询对象
        TypedQuery<RecordMerchantResult> createQuery = entityManager.createQuery(criteriaQuery);
        //设置分页参数
        createQuery.setFirstResult((req.getPage() - 1) * req.getSize());
        createQuery.setMaxResults(req.getSize());
​
        final List<RecordMerchantResult> list = counts;
        log.info("排序后{}", counts.stream().map(RecordMerchantResult::getCuisineCount).collect(Collectors.toList()));
        final List<List<RecordMerchantResult>> partition = Lists.partition(list, page.getPageSize());
        // 得到分页数据（默认0条，必须判空）
        List<RecordMerchantResult> pageContent;
        if (CollectionUtils.isEmpty(list)) {
            return new PageImpl<>(new ArrayList<>(0), page, 0);
        } else {
            pageContent = partition.get(page.getPageNumber());
        }
        //返回查询的分页结果，createQuery.getResultList()为分页查询的结果对象，counts.size()为设置分页参数之前查询的总数
        List<RecordMerchantVO> vos = new ArrayList<>(pageContent.size());
        long start1 = System.currentTimeMillis();
        Map<String, String> getStoreId2StoreNameMap = storeId2storeNameMap;
​
        for (int i = 0; i < pageContent.size(); i++) {
            RecordMerchantVO recordMerchantVO = new RecordMerchantVO();
            recordMerchantVO.setCuisineCount(pageContent.get(i).getCuisineCount())
                    .setErrorCuisineCount(pageContent.get(i).getErrorCuisineCount())
                    .setMerchantId(pageContent.get(i).getMerchantId())
                    .setStoreId(pageContent.get(i).getStoreId())
                    .setSuccessCuisineCount(pageContent.get(i).getSuccessCuisineCount())
                    .setDeviceUsageDays(pageContent.get(i).getDeviceUsageDays());
            if (storeId2storeNameMap.keySet().contains(pageContent.get(i).getStoreId())) {
                recordMerchantVO.setStoreName(getStoreId2StoreNameMap.get(pageContent.get(i).getStoreId()));
            }
            vos.add(recordMerchantVO);
        }
        long start2 = System.currentTimeMillis();
        log.info("耗费时间商业云加上数据组装：{}", start2 - start1);
        PageImpl<RecordMerchantVO> pageData = new PageImpl<>(vos, page, counts.size());
        return pageData;
    }
​
}

 

自定义对象

@Data
public class RecordMerchantResult {
    String storeId;
    String merchantId;
    long errorCuisineCount;
    long cuisineCount;
    long successCuisineCount;
    long deviceUsageDays;
    public RecordMerchantResult(String storeId, String merchantId,long errorCuisineCount,long successCuisineCount, long cuisineCount,long deviceUsageDays){
        this.storeId = storeId;
        this.merchantId = merchantId;
        this.errorCuisineCount = errorCuisineCount;
        this.cuisineCount = cuisineCount;
        this.successCuisineCount = successCuisineCount;
        this.deviceUsageDays = deviceUsageDays;
    }
}

展示对象

@Data
@AllArgsConstructor
@NoArgsConstructor
@Accessors(chain = true)
public class RecordMerchantVO {
    String storeId;
    String merchantId;
    long errorCuisineCount;
    long cuisineCount;
    long successCuisineCount;
    String storeName;
    long deviceUsageDays;
​
}
​

注意

1、getStoreId2StoreNameMap 店铺Id与店铺名称MAP
2、自定义对象的参数顺序类型必须与SQL搜索出来的数据对应，不然会报错，不能进行数据赋值

遇到的坑：

org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate appropriate constructor on class ****. Expected arguments are: ***********
​
自己声明构造函数且自定义的类字段顺序、数据类型对应于sql返回的数据
​

 

优化方案

storeName字段在当前表中未记录、需要第三方接口查询！

本地声明ConcurrentHashMap 存储数据、在类构造后直接存于类中、且定时去获取最新店铺数据以优化接口响应速度，按此方案设计，响应降至毫秒级！

实验结果：

{
  "code": 0,
  "msg": null,
  "data": {
    "content": [
      {
        "storeId": "283491778840092672",
        "merchantId": "**",
        "errorCuisineCount": 29,
        "cuisineCount": 42,
        "successCuisineCount": 4,
        "storeName": "**",
        "deviceUsageDays": 4,
        "recipeUsedCount": 17
        
      },
      {
        "storeId": "304108006227476480",
        "merchantId": "**",
        "errorCuisineCount": 508,
        "cuisineCount": 830,
        "successCuisineCount": 250,
        "storeName": "**",
        "deviceUsageDays": 23,
        "recipeUsedCount": 19
        
      },
      {
        "storeId": "306209606672760832",
        "merchantId": "**",
        "errorCuisineCount": 6,
        "cuisineCount": 10,
        "successCuisineCount": 4,
        "storeName": "**",
        "deviceUsageDays": 2,
        "recipeUsedCount": 7
        
      },
      {
        "storeId": "392849008856612864",
        "merchantId": null,
        "errorCuisineCount": 0,
        "cuisineCount": 1,
        "successCuisineCount": 1,
        "storeName": null,
        "deviceUsageDays": 1,
        "recipeUsedCount": 1
      },
      {
        "storeId": "393998821044756480",
        "merchantId": "null",
        "errorCuisineCount": 0,
        "cuisineCount": 1,
        "successCuisineCount": 0,
        "storeName": "**",
        "deviceUsageDays": 1,
        "recipeUsedCount": 1
      },
      {
        "storeId": "408183319466156032",
        "merchantId": "**",
        "errorCuisineCount": 5,
        "cuisineCount": 11,
        "successCuisineCount": 6,
        "storeName": "**",
        "deviceUsageDays": 3,
        "recipeUsedCount": 2
      }
    ],
    "totalPages": 1,
    "last": true,
    "totalElements": 6,
    "number": 0,
    "size": 10,
    "sort": [
      {
        "direction": "DESC",
        "property": "createTime",
        "ignoreCase": false,
        "nullHandling": "NATIVE",
        "descending": true,
        "ascending": false
      }
    ],
    "numberOfElements": 6,
    "first": true
  },
  "page": null
}

 

补充：

@Service
public class OrderQueryService {

    @Autowired
    private EntityManager entityManager;

    /**
     * select type,max(price) maxPrice,sum(price) sumPrice from Orders group by type
     */
    public Page<OrderInfo> groupBy(int page, int size){        
        //新建一个页面，存放页面信息
        Pageable page = new PageRequest(page - 1, size);
        //criteriaBuilder用于构建CriteriaQuery的构建器对象
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        //criteriaQuery包含查询语句的各个部分，如where、max、sum、groupBy、orderBy等
        CriteriaQuery<OrderInfo> criteriaQuery = criteriaBuilder.createQuery(OrderInfo.class);
        //获取查询实例的属性，select * from Orders
        Root<Order> root = criteriaQuery.from(Order.class);
        //相当于select type,max(price) maxPrice,sum(price) sumPrice from Orders中select 与 from之间的部分
        criteriaQuery.multiselect(root.get("type"), criteriaBuilder.max(root.get("price")), criteriaBuilder.sum(root.get("price")));
        //where type = 1
        criteriaQuery.where(criteriaBuilder.equal(root.get("type"), 1));
        //group by type
        criteriaQuery.groupBy(root.get("type"));
        //criteriaQuery拼成的sql是select type,max(price) maxPrice,sum(price) sumPrice from Orders group by type；查询出的列与对象OrderInfo的属性 一 一 对应
        //记录当前sql查询结果总条数
        List<OrderInfo> counts = entityManager.createQuery(criteriaQuery).getResultList();
        //sql查询对象
        TypedQuery<OrderInfo> createQuery = entityManager.createQuery(criteriaQuery);
        //设置分页参数 offset
        createQuery.setFirstResult(pageable.getOffset);
        createQuery.setMaxResults(pageable.getPageSize());
        //返回查询的分页结果，createQuery.getResultList()为分页查询的结果对象，counts.size()为设置分页参数之前查询的总数
        /**
        *  querySql + whereSql + groupBy + orderBy 拼接的SQL语句
        Query query = entityManager.createNativeQuery(querySql + whereSql + groupBy + orderBy);                               query.unwrap(SQLQuery.class).setResultTransformer(Transformers.aliasToBean(OrderInfo.class));
        **/
        return new PageImpl<OrderInfo>(createQuery.getResultList(), pageable, counts.size());
    }
}

其中 // 结果转换  可自定义对象但是类型必须与数据库返回的类型完全一致，且自定义的构造函数字段类型顺序一致；需声明全参构造函数及无参构造函数

 

其中原生SQL的时候：引用《Spring_Boot_2精髓_从构建小系统到架构分布式大系统.pdf》