QueryDSL 4 with RowNumber Window function

Question

I'm using query dsl with spring data.

Environment:

    <querydsl-apt.version>4.1.4</querydsl-apt.version>
    <querydsl-jpa.version>4.1.4</querydsl-jpa.version>
    <querydsl-sql.version>4.1.4</querydsl-sql.version>
    <spring>4.3.3.RELEASE</spring>

Query:

JPAQueryFactory query = new JPAQueryFactory(getEntityManager());

SimpleExpression<Long> rowNumber = SQLExpressions.rowNumber()
        .over()
        .orderBy(qServiceExecution.updatedAt.asc()).as("rowNumber");

List<Tuple> response = query.select(qServiceExecution.id, SQLExpressions.rowNumber()
                .over()
                .orderBy(qServiceExecution.updatedAt.asc()))
        .from(qServiceExecution)
        .fetch();

Exception:

Root cause: java.lang.IllegalArgumentException: No pattern found for ROWNUMBER
    at com.querydsl.core.support.SerializerBase.visitOperation(SerializerBase.java:280) ~[querydsl-core-4.1.4.jar:na]
    at com.querydsl.jpa.JPQLSerializer.visitOperation(JPQLSerializer.java:437) ~[querydsl-jpa-4.1.4.jar:na]
    at com.querydsl.core.support.SerializerBase.visit(SerializerBase.java:231) ~[querydsl-core-4.1.4.jar:na]
    at com.querydsl.core.support.SerializerBase.visit(SerializerBase.java:31) ~[querydsl-core-4.1.4.jar:na]



Spring error: No pattern found for ROWNUMBER; nested exception is java.lang.IllegalArgumentException: No pattern found for ROWNUMBER
    at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:384) ~[spring-orm-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:246) ~[spring-orm-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491) ~[spring-orm-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]

Query DSL documentation: http://www.querydsl.com/static/querydsl/latest/reference/html/ch02s03.html#d0e1276

Other stack overflow question: QueryDSL Window functions

Any suggestion?

Its long back but can you remember how did you fix this issue. Any suggestions.?! — Arun Selva, Nov 27 '19 at 08:28

score 1 · Answer 1 · answered Jun 04 '20 at 16:58

Window functions are not included in the JPQL specification and as such not available in any JPA implementation. You could register these functions yourself using custom functions.

However, after this, these functions still won't be accessible in QueryDSL. You're stealing from the SQLExpressions here to obtain a window expression. These methods live in SQLExpressions for a reason: they only work with querydsl-sql and not with querydsl-jpa (again, because JPA itself does not support window functions). So after registering your custom function, you will still have to extend JPQLTemplates to include the template for your custom window function.

You'd do this like this:

public class MyTemplates extends JPQLTemplates {

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

}

And then use it as follows:

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

With the Spring integration in the middle however, the templates are somewhat more difficult to bind to the query I think.

Alternatively you could look into the blaze-persistence-querydsl extension, which has out of the box support for window functions (and many other features) for JPQL. For example:

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

score 0 · Answer 2 · answered Dec 08 '21 at 19:41

Sorry for being late but I had the same problem, I resolved it by adding configuration files from there

then injected CriteriaBuilderFactory

 @Inject
CriteriaBuilderFactory cbf;

At the beginning and then write any query with window function like this

QProductHistory his =QProductHistory.productHistory;
SimpleExpression<Long> rown =  JPQLNextExpressions.rowNumber().over().partitionBy(his.esn).as("rown");
BlazeJPAQuery<Tuple> bl_qry = new BlazeJPAQuery<Tuple>(entityManager, cbf).from(his)
            .select(his.esn, rown);

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

QueryDSL 4 with RowNumber Window function

2 Answers2