使用Spring JPA通过Postgresql为Group\u设置STRING\u AGG的聚合函数

我正在尝试设置 repository 调用以检索测试结果列表的ID ids 在中使用 GROUP_BY . 我可以用 createNativeQuery 但我无法使用Spring的 JPA 使用 FUNCTION 呼叫

FUNCTION('string_agg', FUNCTION('to_char',r.id, '999999999999'), ',')) as ids

我使用的是Spring Boot 1.4、hibernate和PostgreSQL。

问题

1. 如果有人能帮我设置正确的函数调用 如以下JPA示例所示,我们将不胜感激。

更新1

在实现了自定义方言之后,它似乎试图将函数转换为 long . 功能代码是否正确?

FUNCTION('string_agg', FUNCTION('to_char',r.id, '999999999999'), ','))

更新2

在进一步研究方言之后,似乎需要为函数注册返回类型,否则它将默认为 长的 . 请参见下面的解决方案。

这是我的代码:

DTO公司

    @Data
    @NoArgsConstructor
    @AllArgsConstructor
    public class TestScriptErrorAnalysisDto {
        private String testScriptName;
        private String testScriptVersion;
        private String checkpointName;
        private String actionName;
        private String errorMessage;
        private Long count;
        private String testResultIds;
    }

控制器

@RequestMapping(method = RequestMethod.GET)
@ResponseBody
public ResponseEntity<Set<TestScriptErrorAnalysisDto>> getTestScriptErrorsByExecutionId(@RequestParam("executionId") Long executionId) throws Exception {

    return new ResponseEntity<Set<TestScriptErrorAnalysisDto>>(testScriptErrorAnalysisRepository.findTestScriptErrorsByExecutionId(executionId), HttpStatus.OK);
}

存储库尝试使用函数 不工作

    @Query(value = "SELECT new com.dto.TestScriptErrorAnalysisDto(r.testScriptName, r.testScriptVersion, c.name, ac.name, ac.errorMessage, count(*) as ec, FUNCTION('string_agg', FUNCTION('to_char',r.id, '999999999999'), ',')) "
    + "FROM Action ac, Checkpoint c, TestResult r " + "WHERE ac.status = 'Failed' " + "AND ac.checkpoint = c.id " + "AND r.id = c.testResult " + "AND r.testRunExecutionLogId = :executionId "
    + "GROUP by r.testScriptName, r.testScriptVersion, c.name, ac.name, ac.errorMessage " + "ORDER by ec desc")
Set<TestScriptErrorAnalysisDto> findTestScriptErrorsByExecutionId(@Param("executionId") Long executionId);

使用createNativeQuery的存储库 工作

    List<Object[]> errorObjects = entityManager.createNativeQuery(
            "SELECT r.test_script_name, r.test_script_version, c.name as checkpoint_name, ac.name as action_name, ac.error_message, count(*) as ec, string_agg(to_char(r.id, '999999999999'), ',') as test_result_ids "
                    + "FROM action ac, checkpoint c, test_result r " + "WHERE ac.status = 'Failed' " + "AND ac.checkpoint_id = c.id "
                    + "AND r.id = c.test_result_id " + "AND r.test_run_execution_log_id = ? "
                    + "GROUP by r.test_script_name, r.test_script_version, c.name, ac.name, ac.error_message " + "ORDER by ec desc")
    .setParameter(1, test_run_execution_log_id).getResultList();

    for (Object[] obj : errorObjects) {
        for (Object ind : obj) {
            log.debug("Value: " + ind.toString());
            log.debug("Value: " + ind.getClass());
        }
    }

这是我在函数中找到的文档

            4.6.17.3 Invocation of Predefined and User-defined Database Functions

    The invocation of functions other than the built-in functions of the Java Persistence query language is supported by means of the function_invocation syntax. This includes the invocation of predefined database functions and user-defined database functions.

     function_invocation::= FUNCTION(function_name {, function_arg}*)
     function_arg ::=
             literal |
             state_valued_path_expression |
             input_parameter |
             scalar_expression
    The function_name argument is a string that denotes the database function that is to be invoked. The arguments must be suitable for the database function that is to be invoked. The result of the function must be suitable for the invocation context.

    The function may be a database-defined function or a user-defined function. The function may be a scalar function or an aggregate function.

    Applications that use the function_invocation syntax will not be portable across databases.

    Example:

    SELECT c
    FROM Customer c
    WHERE FUNCTION(‘hasGoodCredit’, c.balance, c.creditLimit)