asked on

create Table using dates from DB as column headers

Hi all,
I have a table that displas data for last 7 days. Right now the dates in the column headers are taken from an Arraylist, what I want is , these dates should be automatically fetched from the DB.
My current code is below

<body>
<%
SimpleDateFormat df = new SimpleDateFormat("EEE - MMM d, yyyy");
Calendar origDay = Calendar.getInstance();
Calendar prevDay = (Calendar) origDay.clone();
prevDay.add(Calendar.DAY_OF_YEAR, -1);
%>
 
<div id="page-wrapper"><%@include file="header.jsp"%>
<div id="column-wrapper"><!-- begin columns --> <%@include
file="left-panel.jsp"%>
 
<div id="content"><!-- begin content column -->
<div class="inner">
<a href="index.jsp" style="text-decoration:none"><b>Home</b></a> >><a href="ExeSum.jsp" style="text-decoration:none"><b> Executive Summary</a> >> <a href="EDD.jsp" style="text-decoration:none"><b>EDD</b></a><b> >></b><a href="EDD_CORE.jsp" style="text-decoration:none"><b> EDD CORE </b></a> >><b> EDD CORE metric table</b>
<br>
<br>
<h2>EDD Core Metric Table</h2>
<%!public ArrayList getDays() {
 
ArrayList dateList = new ArrayList();
DateFormat dateFormat = new SimpleDateFormat("MM/dd/yyyy");
 
Calendar cal = Calendar.getInstance();
dateFormat.format(cal.getTime());
 
for (int i = 0; i <= 6; i++) {
cal.add(Calendar.DATE, -1);
dateList.add(dateFormat.format(cal.getTime()));
 
}
return dateList;
}%> <%!public ArrayList padZeros(ArrayList a, int count) {
for (int i = 0; i < count; i++)
a.add(new Integer(0));
return a;
}%> <%
java.util.ArrayList days = getDays();
java.util.Collections.reverse(days);
%>
<h5><b>Results as of <%=df.format(prevDay.getTime())%></b></h5>
<table border="1" cellspacing="1" bgcolor="#FFFFFF" WIDTH=99%>
<tr>
<th><b>Client Group</b></th>
<th><b> Metric </b></th>
<%
for (int i = 0; i < days.size(); i++) {
%>
<th><%=days.get(i)%></th>
<%
}
%>
<th><b>Minimum SLA </b></th>
</tr>
<%
Connection conn = null;
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(
"",
"user", "pass");
 
String sql = "", sqldata = "";
PreparedStatement pstmt = null, pstmtdata = null;
ResultSet rs = null, rsdata = null;
 
try {
sql = "SELECT CLIENT_GRP, SUCCESS_RATE, VOLUME, NVL(TO_CHAR(d, 'mm/dd/yyyy'), ' ') EVENT_DATE from  VSLAND.APM_WEB_EDDCORE_METRICPAGE_V, (SELECT TRUNC(SYSDATE - 1) - LEVEL + 1 d FROM DUAL CONNECT BY LEVEL <= 7) WHERE EVENT_DATE(+) = d ORDER BY CLIENT_GRP, d";
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery(sql);
ArrayList success = new ArrayList();
ArrayList volume = new ArrayList();
ArrayList hightlight = new ArrayList();
String chggrp = "";
if (rs.next()) {
chggrp = rs.getString(1);
}
boolean loop = true;
int rowcolor = 0; /// color code
while (loop) {
boolean innerloop = true;
%>
<%
if (rowcolor % 2 == 0) {
%>
<tr>
<%
} else {
%>
 
<tr style='background-color:#EDEDED'>
<%
}
%>
<th rowspan=2><%=chggrp%></th>
<td>Volume</td>
<%
java.text.DecimalFormat nf = new java.text.DecimalFormat(
"##.00");
java.text.DecimalFormat nv = new java.text.DecimalFormat(
"##,##,###");
 
while (innerloop) {
float f = rs.getFloat(2);
 
if (f < 87)
hightlight.add(new Integer(1)); // true
else
hightlight.add(new Integer(0)); // false
success.add(new Float(f));
 
volume.add(new Integer(rs.getInt(3)));
 
if (!rs.next()) {
innerloop = false;
loop = false;
break;
}
if (!rs.getString(1).equals(chggrp))
innerloop = false;
chggrp = rs.getString(1);
}
success = padZeros(success, 7 - success.size());
hightlight = padZeros(hightlight, 7 - hightlight.size());
volume = padZeros(volume, 7 - volume.size());
for (int v = 0; v < volume.size(); v++) {
 
out.print("<td>" + nv.format(volume.get(v)) + "</td>");
}
///////// NEW /////////
out.print("<td>&nbsp;</td>");
///////// NEW /////////
%>
</tr>
<%
if (rowcolor % 2 == 0) {    /// color code
%>
<tr>
 
<%
} else {
%>
 
<tr style='background-color:#EDEDED'>
<%
}
%>
<td>Success</td>
 
<%
for (int s = 0; s < success.size(); s++) {
 
int h = ((Integer) hightlight.get(s)).intValue();
if (h == 1)
///////// NEW /////////
//                                        out.print("<td style='background-color:#E33740'>"
out.print("<td style='background-color:#FF0000'>"
///////// NEW /////////
 
+ nf.format(success.get(s))
+ "%</span></td>");
else
out.print("<td>" + nf.format(success.get(s))
+ "%</td>");
}
 
out.print("<td>87%</td>");
%>
</tr>
<%
volume.clear();
success.clear();
hightlight.clear();
rowcolor++;
 
}
 
conn.close();
rs.close();
pstmt.close();
 
} catch (Exception e) {
 
e.printStackTrace();
conn = null;
 
}
%>
 
</table>

Open in new window

ASKER CERTIFIED SOLUTION

Kuldeepchaturvedi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aman0711

ASKER

Sir, ran this code.
got the same error u r talking about.

java.sql.SQLException: Invalid operation for forward only resultset : beforeFirst

Kuldeepchaturvedi

Hmm change your

pstmt = conn.prepareStatement(sql)

to

pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

and try

aman0711

ASKER

got,

java.lang.NullPointerException
with the following output

aman0711

ASKER

.
null.JPG

aman0711

ASKER

Sir, i guess thr is problem with the table on the backend... let me check it and get back to you

aman0711

ASKER

sir, it ran this time.
attached is the output.

null.JPG

SOLUTION

Kuldeepchaturvedi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

aman0711

ASKER

Thank you sir,
It worked perfectly. I have to ask you for the logic, how we did it.
But I am still facing the problem of data overlapping?

Kuldeepchaturvedi

Well logic was simple, As I have written in my previous post. We do have date in your query.. only thing was that we need to go them horizontal..
thats what we did in the first loop

while(rs.next())..

now once we reached 7th date we broke the loop ( it was necessary because our resultset is coming back with multiple rows for multiple companies)..

once we made the horizontal row, we needed to reset the resultset back to the start position
rs.beforeFirst();
after that its the old code ( no changes in the old code)..

As far as data overlapping, it will still happen because there is no connection between multiple data rows that we are recieving and the date columns we have created..

Its gonna be a complex workaround to make it happen is jsp..(its doable though)

easier way will be to fill the backend table with zeros or something for the dates on which data is unavailable..

aman0711

ASKER

Yes sir. thats what I told mngr and We are going with filling in zeros whenever thr is no data.

aman0711

ASKER

Sir another thing,
As you saw in the code, we are maknig connections in every JSP page and I still didnt figure out, how to access the DataSource on the server. I saw the connection pooling bean somewhere. Is it a good approach? and can we do it?

Kuldeepchaturvedi

as I have said before also, you need to use connection pool. Or atleast a bean with simple connection, Connection on every page is never a good idea..

aman0711

ASKER

ok, I will google some connection pooling code. Will show you if I find one.

aman0711

ASKER

Sir, will this work:

http://www.snaq.net/java/DBPool/

Kuldeepchaturvedi

Thats an overkill for you IMO, Also it will need about equal amount of work to set it up as it would take to make your own pool work in your WASD.

Will it work for you? yes it will work

aman0711

ASKER

ohk...
I will put my hands on WAS then :(