Offset of type Long in Java Object Relational Mapping Frameworks

Offset of type Long in Java Object Relational Mapping Frameworks

Posted on by Toma Velev

To this day amazes me that all Java Object Relational Mapping Frameworks use 32bit Integer as parameter to the offset you could pass to the database.

  1. Spring’s Pageable uses int internally — so it cannot handle offsets beyond Integer.MAX_VALUE.
  2. (JPA API also expects int for offset and limit. https://docs.oracle.com/javaee/5/api/javax/persistence/Query.html#setFirstResult(int)
  3. Hibrnate https://docs.jboss.org/hibernate/orm/6.6/javadocs/org/hibernate/query/Page.html#page(int,int)

🔍 Breakdown:

1. Pageable uses int page and size

PageRequest.of(int page, int size)
  • The page number and size are both int, so the maximum offset is:
int maxOffset = Integer.MAX_VALUE = 2_147_483_647
  • Trying to go beyond that — e.g., page = 2_147_483_648 / size — will cause an IllegalArgumentException or overflow.

2. JPA / Hibernate API also uses int:

When you do:

query.setFirstResult(offset);
query.setMaxResults(limit);

These methods accept int parameters. So even if you somehow calculated a larger long offset, JPA will truncate or fail.

🛠️ Workarounds

✅ A. Use keyset pagination (a.k.a. “seek” pagination)

Instead of relying on offset-based pagination (which becomes slow anyway with large datasets), you paginate based on a unique key, like an id.

Example:

@Query("SELECT o FROM Order o WHERE o.id > :lastId ORDER BY o.id ASC")
List<Order> findNextPage(@Param("lastId") Long lastId, Pageable pageable);
  • This avoids the use of OFFSET entirely.
  • Much faster on large datasets.
  • Works safely even if the dataset has billions of rows.

✅ B. Use native queries with OFFSET manually

If you’re controlling the query directly and absolutely must use an offset > Integer.MAX_VALUE, you can write a native query and pass the OFFSET as a String:

@Query(value = "SELECT * FROM big_table OFFSET :offset LIMIT :limit", nativeQuery = true)
List<Entity> findPage(@Param("offset") String offset, @Param("limit") int limit);
  • This is hacky and might require tuning your JDBC driver or use of PreparedStatement.setLong().
  • Not guaranteed to work across all JPA providers or drivers.

🚨 UX

  • Avoid using huge offsets
  • Switch to keyset pagination or use time/id filtering for large data ranges.

Yes, I know it is uncommon and it falls out of a Good User Experience to go to a page two million and beyond. But, the mere need of SQL Native items somehow breaks the value of the ORMs.