How do you do a limit query in JPQL or HQL?

В Hibernate 3 есть ли способ выполнить эквивалент следующего MySQL limit в HQL?

select * from a_table order by a_table_column desc limit 0, 20;

Я не хочу использовать setMaxResults, если это возможно. Это определенно было возможно в более старой версии Hibernate / HQL, но, похоже, оно исчезло.

Это было опубликовано на форуме Hibernate несколько лет назад, когда его спросили, почему это сработало в Hibernate 2, но не в Hibernate 3:

Предложение Limit в HQL никогда не поддерживалось. Предполагается, что вы используете setMaxResults() .

Итак, если это сработало в Hibernate 2, похоже, это было по совпадению, а не по замыслу. Я думаю, это произошло потому, что анализатор HQL Hibernate 2 заменил бы биты запроса, которые он распознал как HQL, и оставил остальное как есть, чтобы вы могли незаметно использовать какой-нибудь собственный SQL. Однако в Hibernate 3 есть надлежащий анализатор AST HQL, и он намного менее снисходителен.

Я думаю, Query.setMaxResults() это действительно ваш единственный вариант.

 // SQL: SELECT * FROM table LIMIT start, maxRows;

Query q = session.createQuery("FROM table");
q.setFirstResult(start);
q.setMaxResults(maxRows);

Если вы не хотите использовать setMaxResults() для Query объекта, вы всегда можете вернуться к использованию обычного SQL.

Методы setFirstResult и setMaxResults Query

Для JPA и Hibernate Query, setFirstResult метод является эквивалентом OFFSET, а setMaxResults метод является эквивалентом LIMIT:

List<Post> posts = entityManager.createQuery("""
    select p
    from Post p
    order by p.createdOn
    """)
.setFirstResult(10)
.setMaxResults(10)
.getResultList();

LimitHandler Абстракция

Hibernate LimitHandler определяет логику разбивки на страницы для конкретной базы данных, и, как показано на следующей диаграмме, Hibernate поддерживает множество параметров разбивки на страницы для конкретной базы данных:

Теперь, в зависимости от используемой вами базовой системы реляционных баз данных, приведенный выше запрос JPQL будет использовать правильный синтаксис разбивки на страницы.

MySQL

SELECT p.id AS id1_0_,
       p.created_on AS created_2_0_,
       p.title AS title3_0_
FROM post p
ORDER BY p.created_on
LIMIT ?, ?

PostgreSQL

SELECT p.id AS id1_0_,
       p.created_on AS created_2_0_,
       p.title AS title3_0_
FROM post p
ORDER BY p.created_on
LIMIT ?
OFFSET ?

SQL Server

SELECT p.id AS id1_0_,
       p.created_on AS created_on2_0_,
       p.title AS title3_0_
FROM post p
ORDER BY p.created_on
OFFSET ? ROWS 
FETCH NEXT ? ROWS ONLY

Oracle

SELECT *
FROM (
    SELECT 
        row_.*, rownum rownum_
    FROM (
        SELECT 
            p.id AS id1_0_,
            p.created_on AS created_on2_0_,
            p.title AS title3_0_
        FROM post p
        ORDER BY p.created_on
    ) row_
    WHERE rownum <= ?
)
WHERE rownum_ > ?

Преимущество использования setFirstResult и setMaxResults заключается в том, что Hibernate может генерировать синтаксис разбивки на страницы для любых поддерживаемых реляционных баз данных.

И, вы не ограничены только запросами JPQL. Вы можете использовать setFirstResult и setMaxResults метод seven для собственных запросов SQL.

Собственные SQL-запросы

Вам не нужно жестко кодировать разбивку на страницы для конкретной базы данных при использовании собственных SQL-запросов. Hibernate может добавить это к вашим запросам.

Итак, если вы выполняете этот SQL-запрос в PostgreSQL:

List<Tuple> posts = entityManager.createNativeQuery(
    SELECT
       p.id AS id,
       p.title AS title
    from post p
    ORDER BY p.created_on
    """, Tuple.class)
.setFirstResult(10)
.setMaxResults(10)
.getResultList();

Hibernate преобразует его следующим образом:

SELECT p.id AS id,
       p.title AS title
FROM post p
ORDER BY p.created_on
LIMIT ?
OFFSET ?

Круто, правда?

Помимо разбивки на страницы на основе SQL

Разбивка на страницы хороша, когда вы можете индексировать критерии фильтрации и сортировки. Если ваши требования к разбивке на страницы подразумевают динамическую фильтрацию, гораздо лучше использовать решение с инвертированным индексом, такое как ElasticSearch.