解决Spring data JPA使用Native Query做资料统计后的排序问题

专案上遇到要以Angular前端做複合查询,使用Spring data JPA的Native Query做资料统计,
并且支援每个栏位的分页动态排序。

会遭遇到的问题是以栏位名称传入SortString时,
由指令统计出来的success_count、failure_count、total_count,
以及来自join表格的project_id,
在执行时JPA生成SQL的机制下,
以上栏位都会被自动带入case_main的Table名 (例如:cm.success_count)
导致找不到该栏位而执行失败。

Native Query的部分

@Repositorypublic interface CaseMainCalcRepository extends JpaRepository<CaseMainCalc, String>, JpaSpecificationExecutor<CaseMainCalc> {    @Query(value = "SELECT " +            "t.t_user_id as \"user_id\", " +            "t.t_provider_id as \"provider_id\", " +            "t.t_service_id as \"service_id\", " +            "t.t_project_id as \"project_id\", " +            "sum(t.t_success_count) as \"success_count\", " +            "sum(t.t_failure_count) as \"un_failure_count\", " +            "sum(t.t_success_count) + sum(t.t_failure_count) as \"total_count\" " +            "FROM" +            "( select" +            " xcm.user_id as \"t_user_id\"," +            " xcm.provider_id as \"t_provider_id\"," +            " xcm.service_id as \"t_service_id\"," +            " xs.project as \"t_project_id\"," +            " (case xcm.case_status when '10' then count(1) else 0 end) as \"t_success_count\","             +            " (case xcm.case_status when '10' then 0 else count(1) end) as \"t_failure_count\","             +            " count(1)" +            " from xfs_case_main xcm " +            " join xfs_service xs on xcm.service_id = xs.service_id" +            " where xcm.case_create_datetime >= ?1 " +            " and xcm.case_create_datetime <= ?2 " +            " and (xcm.user_id = ?3 OR ?3 like '' ) " +            " and (xcm.provider_id = ?4 OR ?4 like '' ) " +            " and (xcm.service_id = ?5 OR ?5 like '' ) " +            " and (xs.project = ?6 OR ?6 like '' ) " +            " group by xcm.user_id ,xcm.provider_id ,xcm.service_id, xcm.case_status,                 xs.project" +            ") t group by t.t_user_id, t.t_provider_id, t.t_service_id, t.t_project_id "            , countQuery = "SELECT " +            " COUNT(*)" +            " FROM" +            " ( select" +            " xcm.user_id as \"t_user_id\"," +            " xcm.provider_id as \"t_provider_id\"," +            " xcm.service_id as \"t_service_id\"," +            " xs.project as \"t_project_id\"," +            " (case xcm.case_status when '10' then count(1) else 0 end) as \"t_success_count\","             +            " (case xcm.case_status when '10' then 0 else count(1) end) as \"t_failure_count\","             +            " count(1)" +            " from xfs_case_main xcm " +            " join xfs_service xs on xcm.service_id = xs.service_id" +            " where xcm.case_create_datetime >= ?1 " +            " and xcm.case_create_datetime <= ?2 " +            " and (xcm.user_id = ?3 OR ?3 like '' ) " +            " and (xcm.provider_id = ?4 OR ?4 like '' ) " +            " and (xcm.service_id = ?5 OR ?5 like '' ) " +            " and (xs.project = ?6 OR ?6 like '' ) " +            " group by xcm.user_id ,xcm.provider_id ,xcm.service_id, xcm.case_status,                 xs.project" +            ") t group by t.t_user_id, t.t_provider_id, t.t_service_id, t.t_project_id "            , nativeQuery = true)    Page<CaseMainCalc> getCaseCalcGroupBy(Date createDateTimeStart, Date createDateTimeEnd,           String userid, String providerId, String serviceId,      String projectId, Pageable pageable);}

Resource的部分

传入前端的查询条件物件caseMainQueryCond,使用REST request方法打到后端取得资料库资料

@GetMapping("/case-mains/statics")public ResponseEntity<List<CaseMainCalc>> getCaseStatistics(CaseMainQueryCond caseMainQueryCond) {    final List<String> sortStrings =                                                                 Arrays.stream(caseMainQueryCond.getSortString()).map(sortString -> "(" + sortString +")")    .collect(Collectors.toList());        Sort sort = JpaSort.unsafe(caseMainQueryCond.getDirection(), sortStrings);        Pageable pageable = PageRequest.of(caseMainQueryCond.getActivePage(),                           caseMainQueryCond.getPageSize(), sort);        Page<CaseMainCalc> page = caseMainCalcRepository.getCaseCalcGroupBy(    caseMainQueryCond.getCaseCreateTimeBegin(),caseMainQueryCond.getCaseCreateTimeEnd(),             caseMainQueryCond.getUserId(),caseMainQueryCond.getProviderId(),    caseMainQueryCond.getServiceId(),caseMainQueryCond.getProjectId(), pageable);        HttpHeaders headers = PaginationUtil.generatePaginationHttpHeaders(    ServletUriComponentsBuilder.fromCurrentRequest(), page);        return ResponseEntity.ok().headers(headers).body(page.getContent());}

重点在于第7行使用JpaSort.unsafe()方法,让排序条件sortStrings强制跳脱JPA的转换机制。

这样就能正常执行啦!!


关于作者: 网站小编

码农网专注IT技术教程资源分享平台,学习资源下载网站,58码农网包含计算机技术、网站程序源码下载、编程技术论坛、互联网资源下载等产品服务,提供原创、优质、完整内容的专业码农交流分享平台。

热门文章