PostgreSQL function string_agg in JPA

Maybe this helps you,

you can invoke database functions in a JPA Criteria Query.

The CriteriaBuilder Interface has a "function" method.

<T> Expression<T> function(String name,
                         Class<T> type,
                         Expression<?>... args)

Create an expression for the execution of a database function.

Parameters:
    name - function name
    type - expected result type
    args - function arguments
Returns:
    expression

Then you can try creating a CriteriaBuilder helper class to get a plain criteria Expression that you can use as usual in our criteria query

public abstract class CriteriaBuilderHelper {

    private static final String PG_STRING_AGG  = "string_agg";

    /**
    * @param cb the CriteriaBuilder to use
    * @param toJoin the string to join
    * @param delimiter the string to use
    * @return Expression<String>
    */
    public static Expression functionStringAgg(CriteriaBuilder cb, String toJoin, String delimiter) {
        return cb.function(PG_STRING_AGG, 
            String.class,
            cb.literal(toJoin),
            cb.literal(delimiter))
        );
    }
}

or you can use a custom dialect to register a new function

public class PGDialect extends PostgreSQLDialect{

    public PGDialect() {
        super();
        registerFunction("string_agg", new SQLFunctionTemplate( StandardBasicTypes.STRING, "string_agg(?1, ?2)"));
    }
}

and use it in your CriteriaBuilder as a normal function

Expression<String> functionStringAgg = cb.function( "string_agg", String.class, 
                                cb.parameter(String.class, "toJoin" ), 
                                cb.parameter(String.class, "delimiter"));

after all don't forget to set the parameter values to the your CriteriaQuery

setParameter( "toJoin", toJoin);
setParameter( "delimiter", delimiter);