Help-> sorting different table columns

[Sam Zheng]

Hi everybody,
My project calls for development of a table, with different
column such as the following:

NAME SSN SALARY AGE
andrew 111-00-1111 12345 20
tracy 201-01-0101 54545 20
daren 303-33-3333 12135 21
In the table, the SSN is unique for each row, however,
other columns may not be unique. I need to develop a
sorting scheme in the servlet/jsp for the table. For example,
when users click Salary, I need the table to be displayed
from top to bottom with Salary in ascending or descending
order. Of course, it will allow users to sort the table
according to the column they want by clicking any of the
four column titles.
For this project, I don't plan/don't want to write my own
sorting scheme. Instead, I hope to use any existing Java
classes to do the sorting (believing those will be more
efficient anyway). Thus, I don't plan to implement each
column as a single dimension array. Instead, I am thinking
to use Hashmap to store the table. What I am thinking is as
following:
(a) use SSN as the key, since it is unique
(b) for each row, I will have a key (SSN), the
value of the key in the hashmap will be a string, in
the form of, i.e., 1st row:
"andrew, 12345, 20"
However, by doing so, I encounter some serious problems.
If users click SSN, the table can be sorted, because I
can always sort the key from the hashmap, and then from
the key I get all the corresponding value from the hashmap.
However, when users click other column, such as NAME
or AGE, I have no way to sort the things. Apparently
I am in the wrong path. Do you people have any advice?
Again, I don't want to write my own sorting scheme as long
as I don't have to.
Any kind souls out there please help! Sam

[Bob Reardon]

Could you use a database table to store this information. Then you could use SQL command to get the data you need.
SELECT * FROM TABLE ORDER BY SSN (DESC)
This command will return a result set ordered by SSN. Use DESC as an optional paramter if you need the result set in descending order.
SELECT * FROM TABLE ORDER BY SALARY
The above command will return a result set ordered by SALARY.
Of course I am assuming you have the appropriate JDBC drivers.
Here is a link to the Sun tutorial
http://java.sun.com/docs/books/tutorial/jdbc/index.html
Another solution would be to use the Collections Framework for Java. I have only been working with them for the past week, but they seem pretty powerful. Here is a link to the Sun tutorial on collections:
http://java.sun.com/docs/books/tutorial/collections/index.html
The book by John Zukowski is also a good reference.
Hope this helps.
-Bob

[Sam Zheng]

To my understanding, the use of ResultSet doesn't work for
my case. In my program, I follow a servlet centric design,
which has a jsp->servlet->jsp paradigm. The servlet gets
all the data from the database using JDBC, then packs the
data and hand off to a jsp to present the result. What I am
thinking is that for these sorting requests, I don't want to
obtain another JDBC connection, instead, I use a session
tracking scheme to store these data and perform the
sorting directly on these data.
For example, suppose the initial request is to have the
output sort by NAME, I will obtain all the necessary
data through JDBC, then pack the data into a hashmap,
and hand off to a jsp. Once the data are displayed, if
users want to sort the data based on SALARY, I will not
ask the servlet to obtain another JDBC connection,
instead, it will directly perform the sorting on the
data itself, which I have deposited into the session.
If you have any better idea, please let me know.
Thanks!
Sam

[Peter Connolly]

I would suggest creating a different comparator for each of the different ways to sort. You would have to store the data in a List (a map doesn't have a notion of order). Then you could call Collections.sort(List, Comparator). This would give you the list in the order you want.
-Peter

[Peter den Haan]

Summarising your options:

• You can store your data in the session and sort, as Peter suggested, using Collections.sort() and a Comparator for each sorting field you want to support (you can easily implement sorting order using a flag in the comparator). You will need to wrap your data in an object, but that's a good idea anyway.
  
• As Bob suggested, you can query the database for every request, and leave the sorting up to the db. The fact that you use a servlet centric design (MVC?) doesn't prevent you from doing this (if it does, re-think your design).
  

Which one to choose?



• Using Java to sort session-bound data is an option only if both the amount of data to list and the number of users to be supported is limited. For large lists or high-volume websites, replicating the data in each session would consume prohibitive amounts of memory. In those cases you would have to create an application-scoped data cache (beware of threading issues though) or EJB-tier cache.
  
• Firing each separate request at the database is easy and keeps the server light, at the expense of database resources. If your website has high volume, you cannot afford a db access for each request and you'll need a cache as described above.
  

Seeing that you seem to be developing an intranet application of some kind, I would personally lean towards the second (db query) option because it's likely to turn out the simplest to develop and maintain. In all but the largest intranets, development and maintenance are far more important considerations than outright performance.
- Peter

[This message has been edited by Peter den Haan (edited June 12, 2001).]