Display Data Based on Date

[kiran badi]

I have 2 columns in a table in mysql which looks sometime like below,

title       timestamp   
test1     1/7/2012 
test2     1/7/2012
test3     1/7/2012
test4     2/7/2012 
test5     3/7/2012. 

The result I am looking for is something like,

**1/7/2012**
    test1  
    test2
    test3 
**2/7/2012**
    test4 
 **3/7/2012**
    test5 

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]

Can someone reply to this.

[Wendy L Gibbons]

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

[Rob Spoor]

And you should know that PatienceIsAVirtue.

[kiran badi]

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.

String sqld = "Select Distinct DATE(timestamp) as DATE from tableA LIMIT 100";

            ResultSet rsd = stmt1.executeQuery(sqld);
            if (!rsd.next()) {
                System.out.println("no data");
                String destination = "/errorpage.jsp";
                RequestDispatcher rd = getServletContext().getRequestDispatcher(destination);
                rd.forward(request, response);
            } else {
                while (rsd.next()) {
                    String sql = "Select chousingtitle from tableA Where DATE(timestamp) = " + rsd.getDate(1);
                    ResultSet rs = stmt.executeQuery(sql);
                    while (rs.next()) {
                        System.out.println(rs.getString("title"));
                        TitleList.add(rs.getString("title"));

                    }
               //     rs.close();
            //        stmt1.close();
             //      conn.close();
                }

                int totalElements = TitleList.size();
                System.out.println(totalElements + "List Size");

                rsd.close();
                stmt.close();
                conn.close();

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 Vashko]

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]

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]

<sql:setDataSource  dataSource="jdbc/xxx"/>
        <sql:query var="ab" scope="application"  maxRows="100">
            Select Distinct DATE(timestamp) as DATE from tableA ORDER BY DATE DESC LIMIT 100;
        </sql:query>  
        <c:forEach var="row" items="${ab.rows}">
            <h1><c:out value="${row.DATE}"/></h1>
            <sql:query var="abc" scope="application"  maxRows="100">
            Select title from tableA Where DATE(timestamp) = "<c:out value="${row.DATE}"/>";
            </sql:query>                          
                <c:forEach var="row" items="${abc.rows}">
                <c:out value="${row.title}"/><br><br>
            </c:forEach>
        </c:forEach>

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.

 HashMap<ArrayList<Date>, ArrayList<String>> TitleList = new HashMap<ArrayList<Date>,ArrayList<String>>();
        ArrayList<Date> DateList = new ArrayList<Date>();
        ArrayList<String> Title = new ArrayList<String>();
        ArrayList<Date> DateListinner = new ArrayList<Date>();
        List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
        String sqld = "Select Distinct DATE(timestamp) as DATE from TABLE A ORDER BY DATE DESC LIMIT 100";
        ResultSet rsd = null;
        try {
            rsd = stmt1.executeQuery(sqld);
        } catch (SQLException ex) {
            Logger.getLogger(sortxx.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while (rsd.next()) {
                System.out.println("Testing");
                Date Date = rsd.getDate("Date");
                System.out.println(Date);
                DateList.add(Date);
                String sql = "Select title,DATE(timestamp) as DATE from TABLEA Where DATE(timestamp) = '" + Date + "'";
                //     System.out.println(sql);
                ResultSet rs = stmt.executeQuery(sql);
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                System.out.println(columnCount);
                while (rs.next()) {
                    String title = rs.getString("title");
                    Title.add(title);
                    //     System.out.println("Testing inside");
                    System.out.println(rs.getString("title"));           
                    DateListinner.add(rs.getDate("DATE"));
                    Map<String, Object> columns = new LinkedHashMap<String, Object>();
                    for (int i = 1; i <= columnCount; i++) {
                        columns.put(metaData.getColumnLabel(i), rs.getObject(i));
                    }
                    rows.add(columns);
                    TitleList.put(DateList,Title); 
                }
            }

[kiran badi]

Can some one reply to this please.

[Paul Clapham]

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]

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.