Hi,
We actually do use a part of hibernate (the dialects) with some custom additions, especially for creating tables, indexes, etc. However, since the user of the application creates the data model (and changes it runtime), and since the user is actually working in a tables and columns paradigm, we cannot really use the ORM or HQL parts of hibernate.
Anyway, apart from the joys (i.e. difficulties) in writing portable SQL (oh what fun ;-)), I still think it would be a nice improvement to the JDBC driver is setMaxRows did actually hard limit the number of returned rows, considering it hardly make sense in this case for postgres to prepare to return more rows than specified.
I'm willing to help or even code it if someone points me in the right direction. The JDBC part should be easy; I'm not sure about the postgres side, but I imagine it's not too difficult either. The hard part is probably getting the protocol extended and getting people to agree that it's a good idea to change the protocol, etc... ;-) (although it's a pretty non-intrusive, backwards compatible change).
Regards,
Sebastiaan
Mark Lewis wrote:
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
