The reason I would like to see this feature (instead of adding the LIMIT
manually) is for cross database compatibility (which is the essence of
JDBC). Basically, setMaxRows is portable, LIMIT is not. Since I am part
of a team developing a cross-database application in which performance
is often important, this feature is quite important to us. Currently
postgres is slow for us on simple index queries on large data sets (1.8
seconds for the first 100 primary keys only of a table of 43000 rows);
and unfortunately, these kinds of queries are very common in our
application.
JDBC is a little too low-level to give true database independence; you
can write portable queries, but you're severely restricted when it comes
to functionality supported by most databases but not in a standardized
way, such as limits, locking, performance hinting, sequences/serials,
etc.
For simple, non-performance critical apps you can mostly get away with
it (as we did for a while with some of our products). But for anything
more sophisticated, your application really needs a way to deal with
database-specific SQL.
On newer projects we use Hibernate HQL, which has been a major boon in
terms of database portability and performance.
24 messages in org.postgresql.pgsql-jdbcRe: Limit vs setMaxRows issue
