Pagination in JPQL and Criteria API

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/](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 orderBy(java.util.List o)
AbstractQuery orderBy(Order... o) ;
AbstractQuery limit(Expression limit);
AbstractQuery offset(Expression offset);
```

and some covariant overrides for the subquery

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

Start with the proposed JPQL select_statement and subquery grammar, then review javax/persistence/criteria/AbstractQuery.java and Subquery.java. Determine how pagination and subquery ordering should be represented in the JPQL and Criteria APIs; done means the specification and API changes consistently support the proposed LIMIT and OFFSET behavior.

Written by the indexing model from the issue text.