• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 506
  • Last Modified:

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

0
aman0711
Asked:
aman0711
  • 11
  • 6
2 Solutions
 
KuldeepchaturvediCommented:
well you are fetching the date in your query so we should be able to use it..
only trouble is that the date needs to go horizontal on your table and rest of the data needs to go vertical..

if your resultset is not FORWARD_ONLY.. then we can parse thru that twice ( once getting the the dates and then running thru rest of the code..

try the code below.. (I have edited the code in the box only, so every chance that I made some syntax mistake)



<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 padZeros(ArrayList a, int count) {
for (int i = 0; i < count; i++)
a.add(new Integer(0));
return a;
}%>
<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>
<%
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 = "";
while(rs.next() {
%>
<th><%=rs.getString("EVENT_DATE")%></th>
<%
}
rs.beforeFirst();
%>
<th><b>Minimum SLA </b></th>
</tr>
<%
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> </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

0
 
aman0711Author Commented:
Sir, ran this code.
got the same error u r talking about.

java.sql.SQLException: Invalid operation for forward only resultset : beforeFirst
0
 
KuldeepchaturvediCommented:
Hmm change your

pstmt = conn.prepareStatement(sql)

to

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

and try
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
aman0711Author Commented:
got,

java.lang.NullPointerException
with the following output
0
 
aman0711Author Commented:
0
 
aman0711Author Commented:
Sir, i guess thr is problem with the table on the backend... let me check it and get back to you
0
 
aman0711Author Commented:
sir, it ran this time.
attached is the output.

null.JPG
0
 
KuldeepchaturvediCommented:
oops, I forgot that we were repeating the data for multiple companies to the date is repeating as well...

we need to break the upper loop at 7th date..
modify below area
String chggrp = "";
int days=0;
while(rs.next() {
%>
<th><%=rs.getString("EVENT_DATE")%></th>
<%
days++
if(days==7)
break;
}
rs.beforeFirst();
%>
0
 
aman0711Author Commented:
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?
0
 
KuldeepchaturvediCommented:
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..
0
 
aman0711Author Commented:
Yes sir. thats what I told mngr and We are going with filling in zeros whenever thr is no data.
0
 
aman0711Author Commented:
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?
0
 
KuldeepchaturvediCommented:
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..
0
 
aman0711Author Commented:
ok, I will google some connection pooling code. Will show you if I find one.
0
 
aman0711Author Commented:
Sir, will this work:
 
http://www.snaq.net/java/DBPool/ 
0
 
KuldeepchaturvediCommented:
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
0
 
aman0711Author Commented:
ohk...
I will put my hands on WAS then :(
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now