How to use listagg in hibernate

Question

I am trying to use listagg function to generate a list of users.

Consider the following table

 ROLE_ID    ENAME
---------- ----------
       4    CLARK
       4    KING
       4    MILLER
       7    ADAMS
       9    FORD
       9    JONES

On using the following query in the SQL Developer

SELECT ROLE_ID,
       LISTAGG(ENAME, ',') WITHIN GROUP (ORDER BY ENAME) AS EMPLOYEES 
FROM USERS 
GROUP BY ROLE_ID;

will result in the following in the console output

ROLE_ID EMPLOYEES
-----------------------------
4        CLARK,KING,MILLER
7        ADAMS
9        FORD, JONES

My goal is try doing this in hibernate but I am not sure how to proceed. Any help is appreciated.

Answer 1

For anyone having problems using listagg as part of a formula, registering the "within" keyword with Hibernate solved the problem for me. (Using Hibernate 5.0.7.)

public class CustomOracleDialect extends Oracle10gDialect {
    public CustomOracleDialect() {
        super();

        registerKeyword("within");
    }
}

Prior to this, Hibernate would break the query by attempting to prefix the "within" keyword with the outer table's alias. Once the keyword was registered the following works:

@Formula("(select (listagg(l.serial_number, ', ') within group(order by d.serial_number))\n"
        + "from order_lines l\n"
        + "where l.order_id = id\n"
        + "group by l.order_id)")
private String serialNumbers;

Note that if you try to assign an alias to the listagg expression it still breaks the query, as Hibernate tries to prepend it with the table's alias as well. This of course is not an issue with formula queries, but if using listagg as part of a named query you may need to omit the alias and reference the column by position within the result set.