专案上遇到要以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的转换机制。
这样就能正常执行啦!!