We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

create Table using dates from DB as column headers

aman0711
aman0711 asked
on
Medium Priority
521 Views
Last Modified: 2012-06-27
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

Comment
Watch Question

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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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

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

pstmt = conn.prepareStatement(sql)

to

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

and try

Author

Commented:
got,

java.lang.NullPointerException
with the following output

Author

Commented:

Author

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

Author

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

null.JPG
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();
%>

Author

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?
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..

Author

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

Author

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?
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..

Author

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

Author

Commented:
Sir, will this work:
 
http://www.snaq.net/java/DBPool/ 
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

Author

Commented:
ohk...
I will put my hands on WAS then :(
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.