You *have* to modify the query to sort because JDBC may not have access to all of the data from the database within the result set at any one time.
Consider query for 20 million records. JDBC will accept such a large query just fine although its not likely to give you all the records at once. The size of the data in memory, or just transferring (could be many gigs) would be crippling. Therefore, depending on how you set your fetch sizes, you may get the data one thousand records at a time. The driver will automatically contact the database for more records as you reach the end a group.
Now, to answer your original question, in order for JDBC to perform a sort it would have to retrieve 100% of the records and store them in memory, something JDBC generally will not do. The best solutions is to sort the query since the database is *FAR BETTER* at organizing indexes then anything you can do in java.
Sometime it's useful to sort result set data by different columns without quering database. For example any network operations are highly costly. I know that Y AJAX framework allows you to fill a table with data, which can be sorted by any columns. It will work well only for reasonable small result sets under 500 records and < 100 columns.
Its helpful to do it on the middletier if the logic or sorting of the column is unusual (user-defined)... but sorting on a single arbitrary columns should always be done by the database since the database is far better equipped to deal with these types of issues.
In situation like this, I try to imagine how the system would run if the database was 1 TB large and sorted across multiple clusters. In that case, an existing database index would be far better than trying to read all the data and sort it in the middle tier. Alternatively, for small sets of <1000 records, you empty the result set out and copy the entire thing into memory since its cheap to do so. Either way, the result set won't help you sort. [ July 19, 2007: Message edited by: Scott Selikoff ]
0
