Pagination in JPQL and Criteria API

[lukasj]

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]

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

[lukasj]

@glassfishrobot Commented Reported by c.beikov

[lukasj]

@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]

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

[lukasj]

@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:

• https://stackoverflow.com/questions/37187193/criteria-api-limit-results-in-subquery
• https://stackoverflow.com/questions/8555485/how-to-impose-limit-on-sub-query-of-jpa-query

[beikov]

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.