• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to sort a ResultSet

 
Dar Var
Ranch Hand
Posts: 74
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Does anyone know how the sort a ResultSet based on data in one of the ResultSet Columns?

The ResultSet may contains a text column. I need to resort the ResultSet alphabetically based on this text column.

The resulting ResultSet should return for example:
Row Text Column
1 aaaa
2 bbbb
3 cccc
etc...
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Not an advanced Java question. Off to JDBC
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You know it is possible to sort it in the database before you run the query?
 
David O'Meara
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You know it is possible to sort it in the database before you run the query?
 
Manuel Leiria
Ranch Hand
Posts: 171
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
select * from tablexpto order by someColumn asc

is something like this that you want?
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
D Rog
Ranch Hand
Posts: 472
Linux Objective C Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4033
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic