Sorting SQL query results in Java

Question

Firstly my apologies for putting some large text here.

Below is the result by executing SQL query in Java but I want it to short it out using timestamp.

For eg. TimeStamp in the below 1st line text is - 040501(HH:mm:ss) (like wise all data contains timestamp) (Sorting should be done by using timestamp parameters) (Each line given below is single row from database)

I am executing 3 queries one by one in java.once i executed first query i am writing to text file and then executing 2nd query, writing to same text file.. likewise i am doing. In that case how to sort it out.

12010051104050131331GZM4         7000000           1    FCFR

120100511040501912828MP2        11590000           0    NOTY

120100511040501312938VF7          366140   .96808795    FGPC

120100511040501912828KA7         6580000           0    NOTY

120100511040501912828JH4          490000           0    NOTY

120100511160528912810PV4        83227500     1.03581    TRIB

120100511160538912795W31               0           1    BILL

120100511160540912828MP2       455784400           0    NOTY

120100511160545912795W31               0           1    BILL

  220100511 040501         2101000

  220100511 040501        51037707

  220100511 040502          700149

  220100511 040502         4289000

  220100511 060514        71616600

  220100511 060514       722453500

the result i would expect is...

 12010051104050131331GZM4         7000000           1    FCFR

 120100511040501912828MP2        11590000           0    NOTY

 120100511040501312938VF7          366140   .96808795    FGPC

 120100511040501912828MP2        11590000           0    NOTY

 120100511040501912828JH4          490000           0    NOTY

  20100511040501         2101000

  20100511040501        51037707

  20100511040502         4289000

  20100511040502          700149

  20100511060514       722453500

  20100511060514        71616600

  20100511160528912810PV4        83227500     1.03581    TRIB

  20100511160538912795W31               0           1    BILL

  20100511160540912828MP2       455784400           0    NOTY

  20100511160545912795W31               0           1    BILL

Please help me out guys. i am fighting for this very long time. Thanks for your help in advance.

Answer 1

Ideally, the table would have a TIMESTAMP column that you can use to ORDER BY in the SQL query; that would be much better than sorting in Java.

It's not obvious to me what the structure of your table is right now, what the columns are, etc. If they aren't normalized properly, then your best course of action would be to do that first, instead of keeping it like a mess that it is and make queries miserable.

Answer 2

Best option is to use ORDER BY in the SQL query. Because, you access one element in a result set at once. So, get all the elements and write to text file with out doing any operations.

Answer 3

So you data is a single column which contains a string with data as well as timestamp?

Is it fixed width? I mean eg: does timestamp always start at 10th character and end at 15th position?

This looks like output from some legacy Mainframe Application, they usually have fixed width formats..

Assuming that's the case, you can write a small wrapper class and make objects of that class from the resultset of your queries. Your wrapper class should also implement comparable (where you would do implement the compare method based on the extracted timestamp).

Then all you need to do is put all your objects in any java collection like List, and do a collection.sort().

Answer 4

Whether you do the sort in SQL or Java, first you must be able to reliably identify and extract the timestamp. The format with which you've displayed the data rows suggest that they could be treated as fixed length records, so you could read them a record at a time into a class that might implement the Comparable interface, in which you might use a substring to slice out the timestamp text, create real dates or timestamps from that text and compare them. Then you could use the standard libraries to sort either the rows in a collection or array. Personally, I'd still do the sort in SQL, via a similar mechanism, assuming you are getting all your data from a single database and are using a fairly sophisticated DB you could create a single result by unioning your 3 queries together, do a calculated field for the timestamp, then order by that calculated field. Some DBs don't allow direct sorting by calculated fields, but then you can use it as an inline view, like "select field, calcfield from (big select with calculated field) order by calcfield". hth