JPA 中的 PostgreSQL 函数 string

本文介绍了JPA 中的 PostgreSQL 函数 string_agg的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在 PostgreSQL 中，string_agg(column, separator) 允许聚合一些字符串.我尝试将它与 JPA 一起使用，但它不是 JPA 标准函数.

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.

注意:这不是等价于CriteriaBuilder#concat().

所以，我试图告诉 JPA 这个函数存在，就像这样:

So, I tried to tell JPA that this function exists, like this :

public class StringAgg extends ParameterizedFunctionExpression<String> 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<String> expression, String separator) { super(criteriaBuilder, String.class, NAME, expression, new LiteralExpression(criteriaBuilder, separator)); } }

然后:

Expression<String> exprStr = ... CriteriaBuilder cb = ... cb.construct(MyClass.class, myClass.get(MyClass_.name), myClass.get(MyClass_.surname), new StringAgg(cb, exprStr, "/"));

问题，我得到一个 NullPointerException ！

java.lang.NullPointerException: null at org.hibernate.internal.util.ReflectHelper.getConstructor(ReflectHelper.java:355) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.ast.tree.ConstructorNode.resolveConstructor(ConstructorNode.java:179) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.ast.tree.ConstructorNode.prepare(ConstructorNode.java:152) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.ast.HqlSqlWalker.processConstructor(HqlSqlWalker.java:1028) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExpr(HqlSqlBaseWalker.java:2279) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectExprList(HqlSqlBaseWalker.java:2145) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectClause(HqlSqlBaseWalker.java:1451) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:571) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:299) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:247) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:261) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.hql.internal.ast.QueryTranslatorImplpile(QueryTranslatorImpl.java:141) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:119) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:87) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:288) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:223) ~[hibernate-core-4.3.0.Beta3.jar:4.3.0.Beta3]

调试器显示cb.construct()的最后一个Selection(new StringAgg(cb, exprStr, "/"))被忽略.因此，搜索到的构造函数是 MyClass(String,String) 而不是 MyClass(String, String, String).

The debugger shows that the last Selection of cb.construct() (new StringAgg(cb, exprStr, "/")) is ignored. As a consequence, the searched constructor is MyClass(String,String) instead of MyClass(String, String, String).

StringAgg 的实现有什么问题吗?有人已经尝试在 JPA 中使用 string_agg 了吗?

Is there something wrong in the implementation of StringAgg? Did someone already tried to use string_agg in JPA?

解决方案(感谢 vzamanillo)

Solution (thanks to vzamanillo)

扩展方言:

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

在 persistence.xml

<properties> <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/> <property name="hibernate.dialect" value="path.to.PGDialect"/>

然后使用 CriteriaBuilder#function() :

Expression<String> exprStr = ... CriteriaBuilder cb = ... cb.construct(MyClass.class, myClass.get(MyClass_.name), myClass.get(MyClass_.surname), cb.function( "string_agg", myColPath, cb.literal("delimiter" )));

为了简化它，我创建了一个辅助方法:

To ease it, I created a helper method :

public static Expression<String> strAgg(CriteriaBuilder cb, Expression<String> expression, String delimiter) { return cb.function( "string_agg", String.class, expression, cb.literal(delimiter)); }

所以代码变成:

Expression<String> exprStr = ... CriteriaBuilder cb = ... cb.construct(MyClass.class, myClass.get(MyClass_.name), myClass.get(MyClass_.surname), strAgg(cb, myColPath, "delimiter"));

推荐答案

也许这对你有帮助，

您可以在 JPA 条件查询中调用数据库函数.

you can invoke database functions in a JPA Criteria Query.

CriteriaBuilder 接口有一个 "函数" 方法.

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

然后您可以尝试创建一个 CriteriaBuilder 帮助器类来获得一个简单的标准表达式，您可以像往常一样在我们的标准查询中使用

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

并在您的 CriteriaBuilder 中将其用作普通函数

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

毕竟不要忘记将参数值设置为您的 CriteriaQuery

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

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