Is there a portable way to have "SELECT FIRST 10 * FROM T" semantic?

Question

I want to read data in blocks of say 10k records from a database.

I found Result limits on wikipedia and it seems obvious that this can't done with sql in a portable way.

Another approach could be JdbcTemplate which offers many methods for queries, but how could I decide that enough rows have been read. Through the callbacks like RowMapper and ResultSetExtractor it can't be indicated, that enough data has been read.

EDIT: I was looking for a solution for JdbcTemplate This post suggests to use setMaxRows which I had overlooked.

Answer 1

Grab Hibernate or JPA. Both are familiar with various database dialects and will handle the nasty DB specifics under the hoods transparently.

In Hibernate you can paginate using Criteria#setFirstResult() and Criteria#setMaxResults(). E.g.

List users = session.createCriteria(User.class)
    .addOrder(Order.asc("id"))
    .setFirstResult(0) // Index of first row to be retrieved.
    .setMaxResults(10) // Amount of rows to be retrieved.
    .list();

In JPA you can do similar using Query#setFirstResult() and Query#setMaxResults().

List users = em.createQuery("SELECT u FROM User u ORDER BY u.id");
    .setFirstResult(0) // Index of first row to be retrieved.
    .setMaxResults(10) // Amount of rows to be retrieved.
    .getResultList();