为何getFirstResult()与getMaxResults()无法返回数据库指定行范围数据？

Hey there, let's break down why your pagination isn't behaving as expected—this is a super common gotcha with Hibernate (or any ORM) pagination!

The #1 Culprit: Missing a Stable, Explicit ORDER BY Clause #

Looking at your query, you've left ORDER BY... incomplete, and that's almost certainly the root issue. Pagination relies entirely on a consistent, predictable row order to work correctly. Without a clear ORDER BY that uniquely defines the sequence of rows, the database (and Hibernate) can't guarantee the same order of results across queries.

Even if your rows look ordered by timestamp in your database, without explicitly defining that sort, the database might return rows in an arbitrary order (like physical storage order) that can change unexpectedly.

To fix this, finish your ORDER BY with fields that create a stable sort. Since your timestamp values are sequential here, adding that plus your article primary key (to handle edge cases where two rows might share the same timestamp) will ensure consistent ordering:

Query query = session.createQuery("select a from Article a where a.lang=:lang ORDER BY a.timestamp ASC, a.article ASC");

Double-Check Your Pagination Math #

Another common mistake is mixing up row numbers and zero-based offsets:

• getFirstResult(int offset) takes a zero-based starting position, not the row number. For example, if you want to get rows starting at your database's article=5 (the 6th row overall), you'd use setFirstResult(5), not 6.
• getMaxResults(int limit) defines how many rows to fetch after the offset. So to get rows 5 through 7 (3 total), you'd set setMaxResults(3).

Here's a concrete example to fetch rows where article is 5,6,7:

Query query = session.createQuery("select a from Article a where a.lang=:lang ORDER BY a.timestamp ASC, a.article ASC");
query.setParameter("lang", "English");
query.setFirstResult(5); // Start at the 6th row (0-indexed offset)
query.setMaxResults(3); // Fetch 3 rows total
List<Article> paginatedResults = query.list();

Quick Sanity Check #

If you've fixed the ORDER BY and your math checks out, verify that:

• Your lang parameter is correctly set (you're filtering for "English" as expected)
• The timestamp column in your database is a datetime type (not a string, which would sort lexicographically instead of chronologically)

内容的提问来源于stack exchange，提问作者Samttha Biby