How to use rownum in querydsl

Question

How do I use the rownum in the above situation?

DataBase : Mysql

Answer 1

SqlExpressions.rowNumber() returns a ROW_NUMBER() expression. Keep in mind that this only works in querydsl-sql and not in querydsl-jpa, because JPQL (the query language for JPA) itself lacks support for window functions (and so do proprietairy JPQL extensions such as Hibernates HQL).

In order to use ROW_NUMBER inside JPA queries, a custom function must be registered for your ORM provider. You could register these functions yourself using custom functions.

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