JPA 中的 PostgreSQL 函数 string_agg [英] PostgreSQL function string_agg in JPA
问题描述
在 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.QueryTranslatorImpl.compile(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_agg的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!