JPA concatenate STRING column values into a single value just like an aggregate function

Question

Is it possible using JPA to have a custom aggregation function that would extend concat() so that it concatenates a column values into a single string?

Answer 1

I faced the same problem recently with JPA and H2 database. I tried to call the GROUP_CONCAT function from JPQL with a function invocation (see below) with no success. Finally, I used a native query call instead.

However, the JPA 2.1 specifications mention that you can call (function invocation) a predefined or user-defined database function either scalar or aggregate one. I have reproduced thereafter the paragraph from the specfication by highlighting the relevant part.

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)

Reference: Java Persistence 2.1, Final Release