專案上遇到要以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的部分

@Repository
public 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的轉換機制。

這樣就能正常執行啦!!