PostgreSQL function string_agg in JPA

Question

In PostgreSQL, string_agg(column, separator) allows to aggregate some Strings. I try to use it with JPA but it is not a JPA standard function. Note : This is not the equivalent of CriteriaBuilder#concat(). So, I tried to tell JPA that this function exists, like this : ``` public class StringAgg extends ParameterizedFunctionExpression implements Serializable { public static final String NAME = "string_agg"; @Override public boolean isAggregation() { return true; } @Override protected boolean isStandardJpaFunction() { return false; } public StringAgg(CriteriaBuilderImpl criteriaBuilder, Expression expression, String separator) { super(criteriaBuilder, String.class, NAME, expression, new LiteralExpression(criteriaBuilder, separator)); } } ``` Then : ``` Expression exprStr = ... CriteriaBuilder cb = ... cb.construct(MyClass.class, myClass.get(MyClass_.name), myClass.get(MyClass_.surname), new StringAgg(cb, exprStr, "/")); ``` Problem, I get a NullPointerException !…

Accepted Answer

Maybe this helps you, you can invoke database functions in a JPA Criteria Query. The CriteriaBuilder Interface has a "function" method. ``` Expression function(String name, Class 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 */ 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 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); ```

PostgreSQL function string_agg in JPA

Solution

Problem