如何使用listagg和querydsl？

Question

我有如下所示的查询；

SELECT 
   .
   .
   LISTAGG(DISTINCT CC.POPULATION, ', ') WITHIN GROUP (ORDER BY CC.POPULATION ASC),
   .
   .
FROM COUNTRY C
     JOIN CITY CC ON C.ID = CC.COUNTRY_ID
--WHERE 
GROUP BY C.ID;

我应该使用querydsl实现自定义过滤和排序操作，但是我得到了“LISTAGG找不到模式”错误

JPAQuery<Tuple> jpaQuery = jpaQueryFactory.select(         
            SQLExpressions.listagg(QCity.city.POPULATION, ",")
            .withinGroup()
            .orderBy(QCity.city.POPULATION.asc())
    )
            .from(QCountry.country)
            .join(QCity.city).on(QCountry.country.id.eq(QCity.city.countryId))
            //.where(custom filtering)
            .groupBy(QCountry.country.id);

jpaQuery.fetch();

我试图添加这样的自定义模板，但我无法成功。我的querydsl-sql版本也是4.2.1。

StringTemplate customPopulationTemplate = Expressions.stringTemplate(
            "(LISTAGG(DISTINCT {0},',') WITHIN GROUP (ORDER BY {0} ASC))", QCity.city.population);

Answer 1

由于Hibernate 5.2.18，即使您是通过JPA引导，也可以使用MetadataBuilderContributor实用程序自定义MetadataBuilder。

MetadataBuilderContributor接口可以这样实现：

public class SqlFunctionsMetadataBuilderContributor
    implements MetadataBuilderContributor {
     
 @Override
 public void contribute(MetadataBuilder metadataBuilder) {
    metadataBuilder.applySqlFunction(
        "group_concat",
        new StandardSQLFunction(
            "group_concat",
            StandardBasicTypes.STRING
        )
    );
 }
}

而且，我们可以通过SqlFunctionsMetadataBuilderContributor配置属性提供hibernate.metadata_builder_contributor：

<property>
    name="hibernate.metadata_builder_contributor"
    value="com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor"
</property>

参考资料：https://vladmihalcea.com/hibernate-sql-function-jpql-criteria-api-query/

Answer 2

JPQL规范中没有包含窗口函数，因此在任何JPA实现中都没有窗口函数。您可以使用custom functions自己注册这些函数。

但是，在此之后，这些函数仍然无法在QueryDSL中访问。您在这里窃取SQLExpressions以获得一个窗口表达式。这些方法存在于SQLExpressions中是有原因的:它们只使用querydsl-sql而不使用querydsl-jpa (同样，因为JPA本身不支持窗口函数)。因此，在注册自定义函数之后，您仍然需要扩展JPQLTemplates以包含自定义窗口函数的模板。

你会这样做的：

public class MyTemplates extends JPQLTemplates {

    public MyTemplates() {
        add(SQLOps.ROWNUMBER, "ROW_NUMBER({0})");
    }

}

然后按以下方式使用：

new JPAQuery(entityManager, new MyTemplates()).from(entity).select(rowNumber())

或者，您可以查看blaze-persistence-querydsl扩展，它对JPQL的窗口函数(和许多其他特性)提供了开箱即用的支持。例如：

QCat cat = QCat.cat;

BlazeJPAQuery<Tuple> query = new BlazeJPAQuery<Tuple>(entityManager, criteriaBuilderFactory).from(cat)
    .select(cat.name, JPQLNextExpressions.rowNumber(), JPQLNextExpressions.lastValue(cat.name).over().partitionBy(cat.id));

List<Tuple> fetch = query.fetch();