Using Spring JPA to setup a Aggregate Function for STRING_AGG with Postgresql for a Group_By

Question

I am trying to setup a repository call to retrieve the ID's of a list of test results ids used in the GROUP_BY. I can get this to work using createNativeQuery but I am unable to get this to work using Spring's JPA with the FUNCTION call.

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

I am using Spring Boot 1.4, hibernate and PostgreSQL.

Question

If someone can please help me out to setup the proper function call shown below in the JPA example it would be much appreciated.

Update 1

After implementing the custom dialect it looks like its trying to cast the function to a long. Is the Function code correct?

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

Update 2

After looking into the dialect further it looks like you need to register the return type for your function otherwise it will default to a long. See below for a solution.

Here is my code:

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;
    }

Controller

@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);
}

Repository trying to use Function Not working

    @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);

Repository using createNativeQuery working

    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());
        }
    }

Here was the documents I found on FUNCTION

            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)

Is this a typo FUNCTION('string_agFUCNTIONTION('to_char',r.id, '999999999999'), ',')) ?? — Jorge Campos
– Jorge Campos, Commented Dec 19, 2017 at 1:37
@JorgeCampos Yes it was, I have updated it but it seems to be not working still. — ALM
– ALM, Commented Dec 19, 2017 at 2:28
@DN1 "Does not work", is described above with the issue that it is trying to put FUNCTION('string_agg', FUNCTION('to_char',r.id, '999999999999'), ',')) into a long and not a string. — ALM
– ALM, Commented Dec 19, 2017 at 19:12
So raise a BUG on your chosen JPA provider. Your JPQL is perfectly valid, and it is for the JPA provider to handle the mapping into that the constructor expression — user8558216
– user8558216, Commented Dec 20, 2017 at 7:13

ALM · Accepted Answer · 2017-12-28 18:59:20Z

1

In the end the main piece which was missing was defining the functions by creating a new class to extend the PostgreSQL94Dialect. Since these functions were not defined for the dialect they were not processed in the call.

    public class MCBPostgreSQL9Dialect extends PostgreSQL94Dialect {

        public MCBPostgreSQL9Dialect() {
            super();
            registerFunction("string_agg", new StandardSQLFunction("string_agg", new org.hibernate.type.StringType()));
            registerFunction("to_char", new StandardSQLFunction("to_char"));
            registerFunction("trim", new StandardSQLFunction("trim"));
        }
    }

The other issue was that a type needed to be set for the return type of the function on registration. I was getting a long back because by default registerFunction returns a long even though string_agg would return a string in a sql query in postgres.

After updating that with new org.hibernate.type.StringType() it worked.

            registerFunction("string_agg", new StandardSQLFunction("string_agg", new org.hibernate.type.StringType()));

answered Dec 28, 2017 at 18:59

ALM

2,7057 gold badges33 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BeliliF Over a year ago

How to register 'string_agg' function for microsoft sql (mssql) ?

Collectives™ on Stack Overflow

Using Spring JPA to setup a Aggregate Function for STRING_AGG with Postgresql for a Group_By

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related