在 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);更多推荐
JPA 中的 PostgreSQL 函数 string
发布评论