> 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.
-- Mark Lewis