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.

Solution 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();

Solution 2

There is an ANSI standard syntax from SQL:2008:

SELECT t.* 
  FROM TABLE t
 FETCH FIRST 10 ROWS ONLY

...but it's not supported on most databases at this time.

Solution 3

There is no portable way of doing that on plain SQL, because different SQL Engines use different syntaxes for that.

Use a Database Abstraction Layer, or DBAL.

http://en.wikipedia.org/wiki/Database_abstraction_layer

http://jonasbandi.net/wiki/index.php/ORM_Solutions_for_Java

Solution 4

If you want a portable way, you need to move up an abstraction layer, as there's no portable SQL way(not one that databases actually implement anyways) - and use ORM mappers like e.g hibernate.

If you do need raw JDBC, you'll have to write specific SQL for eache specific database - which is often the case anyway as writing 100% portabl SQL is pretty hard in all but the trivial cases.

The last resort is to run the query without any restrictions and just iterate over the 10 first results you get back - though this doesn't leverage the database capabilities and would be quite bad if your query results in many rows.

Solution 5

No. Thats why database abstraction layers like Hibernate contains SQL dialects where you choose the one to use with your database.

View more solutions

Comments

[Jherico]

Actually, rather than having to do the whole mapping thing, you can use the Dialect classes in Hibernate to modify SQL to include a limit. Dialect defines a getLimitString() method you can use to extract the mechanism for building limits for various DBs

[Truong Ha]

This is one of the beauties of ORM. In MySql, you can also do this with the LIMIT command.