Pagination in JPQL and Criteria API

lukasj commented 9 years ago

JPA supports pagination but only on the outermost query. As described in the following article from JOOQ developers, it is possible to generate proper SQL for paginated subqueries.

http://blog.jooq.org/2014/06/09/stop-trying-to-emulate-sql-offset-pagination-with-your-in-house-db-framework/

I propose to add a pagination clause to JPQL and some methods to the Criteria API.

JPQL:

limit_clause ::= [LIMIT arithmetic_expression] [OFFSET arithmetic_expression]

Replace current statements with the following. Note that subquery now also needs to support orderby_clause and that limit_clause can only be used in conjunction with orderby_clause

select_statement ::= select_clause from_clause [where_clause] [groupby_clause] [having_clause] [(orderby_clause) | (orderby_clause limit_clause)] subquery ::= simple_select_clause subquery_from_clause [where_clause] [groupby_clause] [having_clause] [(orderby_clause) | (orderby_clause limit_clause)]

Criteria API

**javax/persistence/criteria/AbstractQuery.java**AbstractQuery<T> orderBy(java.util.List<Order> o) 
AbstractQuery<T> orderBy(Order... o) ;
AbstractQuery<T> limit(Expression<? extends Number> limit);
AbstractQuery<T> offset(Expression<? extends Number> offset);

and some covariant overrides for the subquery

**javax/persistence/criteria/Subquery.java**Subquery<T> orderBy(java.util.List<Order> o) 
Subquery<T> orderBy(Order... o) ;

lukasj commented 5 years ago

Issue Imported From: https://github.com/javaee/jpa-spec/issues/88
Original Issue Raised By:@glassfishrobot
Original Issue Assigned To: @ldemichiel

lukasj commented 9 years ago

@glassfishrobot Commented Reported by c.beikov

lukasj commented 9 years ago

@glassfishrobot Commented c.beikov said: Here a little example. If I want to paginate a query like the following

SELECT
    u
FROM
    User u
LEFT JOIN FETCH
    u.loginLogs
ORDER BY
    u.name

I can't use setFirstResult/setMaxResults on the query. So I have to use the following workaround

1. Fetch ids

SELECT
    u.id
FROM
    User u
ORDER BY
    u.name

2. Fetch entities

SELECT
    u
FROM
    User u
LEFT JOIN FETCH
    u.loginLogs
ORDER BY
    u.name
WHERE
    u.id IN :ids

Where the parameter ids will be set with the result of the first query. Now if subqueries would allow some kind of LIMIT clause, I could turn that into a single query.

SELECT
    u
FROM
    User u
LEFT JOIN FETCH
    u.loginLogs
ORDER BY
    u.name
WHERE
    u.id IN (
    SELECT
        u.id
    FROM
        User u
    ORDER BY
        u.name
    LIMIT
        :maxResults
    OFFSET
        :firstResult
    )

lukasj commented 7 years ago

@glassfishrobot Commented This issue was imported from java.net JIRA JPA_SPEC-88

lukasj commented 6 years ago

@shelley-jean-baker Commented +1 for adding this functionality. I have a similar use case as discussed in this comment.

Note that the lack of this functionality has also been discussed on stackoverflow:

beikov commented 2 years ago

FWIW Hibernate 6.0 implements support for LIMIT, OFFSET and FETCH clause in HQL and the JPA Criteria API extension. If anyone is willing to work on the spec parts, Hibernate can serve as a compatible implementation.

jakartaee / persistence

Pagination in JPQL and Criteria API #88