aspose file tools*
The moose likes JDBC and the fly likes Display Data Based on Date Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Display Data Based on Date" Watch "Display Data Based on Date" New topic
Author

Display Data Based on Date

kiran badi
Greenhorn

Joined: Oct 26, 2009
Posts: 26
I have 2 columns in a table in mysql which looks sometime like below,



The result I am looking for is something like,



Can we iterate resultsets object to get the data displayed in this format.How is this normally done, is this done at DB Level or needs to formatted via application code.
kiran badi
Greenhorn

Joined: Oct 26, 2009
Posts: 26
Can someone reply to this.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

i would use the order by clause in the sql statement, and then perform processing on date changed.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19718
    
  20

And you should know that PatienceIsAVirtue.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
kiran badi
Greenhorn

Joined: Oct 26, 2009
Posts: 26
Yes I have order by clause Wendy.Please do look at what I am requiring.Order by does give me what I want,but it do not completely do what I am looking for.I need for each day, display all the records for that day.Move to next date, display all records for that date and continue in descending order.
I have 2 columns in my table, title and timestamp in mysql,I am converting timestamp to date and then for each date displaying all the titles in desc order.Currentdate being on the top.

For resolving this after trying various solutions and failing miserably, I feel now this cannot be done in the single query,

So I thought let me do it with 2 queries and feed the result of first query to second one and then do it.



Of course still I am working on this.But would be happy if someone has better way to do it.All I am looking for is some logic which will scale.Requirements looks simple,but for some reason I am just not able to figure this out.

Yes Rob, I understand one needs to have patience and I do have it.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Kiran, please click on and read the PatienceIsAVirtue link carefully. It will help you understand why Rob has put it up; I think it was perfectly appropriate.

Wendy's reply is perhaps a little bit terse, but it does contain everything you need to process your original requirement. And yes, it is doable with one query, with a little help on the processing side. You'll go like this:

1) Declare a variable to hold last processed date. Initialize it to some value which is guaranteed not to occur in the processed rows.

2) Select all the record you want to obtain and order them by date first, then by any other criteria you want to apply inside that date.

3) Loop through the records:
  3a) Whenever the current record's date differs from the last processed date, print out the date header and store the new date into the last processed date variable.
  3b) Print out the other information from the record.

That's about all. However, you code sample contains a LIMIT clause on the dates, which was missing from your original problem description. It cannot be easily incorporated into the SQL query in the processing I've described. If that one is necessary, I'd suggest to open the query for all records and stop processing once 100 different days are processed.

This will probably scale better than your current approach, unless the LIMIT clause causes only a small fraction of the rows in the table to be read.
kiran badi
Greenhorn

Joined: Oct 26, 2009
Posts: 26
Martin, I did try the approach you suggested,

But please do look at the timestamp field, its always going to be unique.So its necessary for me to convert timestamp to Date.

>>1) Declare a variable to hold last processed date. Initialize it to some value which is guaranteed not to occur in the processed rows.

Just for clarification, its insert time along with date.Its mysql timestamp field. Lets say I initialized it to current date thats is 2012-07-23.

>>3a) Whenever the current record's date differs from the last processed date, print out the date header and store the new date into the last processed date variable.

again lets say that there is some inserts from 23th july morning, so its alway going to find some values.So lets change to change value to null, since its nonnull field, I am sure we will not come across this value. Then I run the query

Select Date(ctimestamp),title from table A Limit 100;
I loop through this resultset and compare Date with null value, so for each comparision its not going to match,so I have timestamp/title getting added to list for each loop.In this list I will have many duplicates records coming up something

Date1, title1,Date1,title2,Date1,title3..

This is not what I require.I need for each Date1, It should have records title1,title2,title 3 etc.

If my understanding is incorrect, can you give me some tips as how what values should I use to initialise date variables.

I think getting less data from DB helps in first place then doing processing on the code and limit processing records to 100.Thats the reason I use Limit.








kiran badi
Greenhorn

Joined: Oct 26, 2009
Posts: 26


This is what I got it finally with JSTL though it seems to meet my requirement,I am for some reason unable to do this via java class.Queries do return the data in the format I want now.But for some reason I am unable to push it to view in the manner I need it.Tried experimenting with lists/maps/arrrays ,but still not luck.Can someone have a look and give me some shout on this please.

Here is my all experiments done so far get this implemented in some DAO Class.I dont want this feature to implemented with JSTL.
kiran badi
Greenhorn

Joined: Oct 26, 2009
Posts: 26
Can some one reply to this please.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18651
    
    8

kiran badi wrote:Can some one reply to this please.


I don't see a question. You claim you have it working in JSTL and you display some Java code which looks sort of equivalent to me. Although I haven't spent a lot of time looking at it, because you haven't said what your problem with it is.
kiran badi
Greenhorn

Joined: Oct 26, 2009
Posts: 26
Thanks Paul for replying.Yes JSTL code does meet my requirement,but I am somewhat not comfortable in implementing this with JSTL.I was looking to implement this
as part of model class.My problem is that I am not able to build the proper list datewise for some reason.Every time I add resultset to list or map, I lose the relationship
between date and its corresponding titles.
This is one of my core requirements for my site,and its very huge site which I am working on.so I expect this part of code to be repeated across 100's my products.
I want this to be implemented on the lines of valuelist pattern.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Display Data Based on Date