Reputation: 594
I have the below code to retrieve data from Oracle Table. which is working fine.
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@include file="DBCon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP Page</title>
</head>
<body><table>
<%
String a=request.getParameter("type").trim();
String b=request.getParameter("user").trim();
String c=request.getParameter("from").trim();
String d=request.getParameter("to").trim();
ResultSetMetaData rsmd;
String st="SELECT type, Hari, Rakesh, Total FROM (select (type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari, max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh, Sum(cnt) total from ( select (type),specialist, sum(update_count) cnt from scope1 where (RECVD_DATE >='"+c+"' and RECVD_DATE <='"+d+"') group by (type),specialist ) q group by (type) Union Select 'Total' as type, Sum(Hari) Hari,Sum(Rakesh) Rakesh,Sum(total) Total from ( select (type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari, max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh, Sum(cnt) total from ( select (type),specialist, sum(update_count) cnt from scope1 where (RECVD_DATE >='"+c+"' and RECVD_DATE <='"+d+"') group by (type),specialist ) q group by (type) )a) b Order by Total";
try{%>
<tr><b><%=b%></b></tr><%
//String sql=(st);
//out.print(sql);
ps1=con.prepareStatement(st);
rs1=ps1.executeQuery();
rsmd=rs1.getMetaData();
int cou=rsmd.getColumnCount();
for(int i=1;i<cou;i++){
%>
<td><%=rsmd.getColumnName(i)%>
<%
}
%>
<td>Total</td>
<%
while(rs1.next())
{
%>
<tr><td><b><%=rs1.getString(1)%></b></td><td><%=rs1.getString(2)%></td><td><%=rs1.getString(3)%></td><td><%=rs1.getString(4)%> </td></tr>
<%
}
}
catch(Exception e)
{
out.println(e);
}
%>
</table> </body>
</html>
but here, i want a help from you guys where i can make the below statement dynamic.
<tr><td><b><%=rs1.getString(1)%></b></td><td><%=rs1.getString(2)%></td><td><%=rs1.getString(3)%></td><td><%=rs1.getString(4)%> </td></tr>
and the sql statement used here is as below.
SELECT type, Hari, Rakesh, Total FROM (select (type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari, max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh, Sum(cnt) total from ( select (type),specialist, sum(update_count) cnt from scope1 where (RECVD_DATE >='"+c+"' and RECVD_DATE <='"+d+"') group by (type),specialist ) q group by (type) Union Select 'Total' as type, Sum(Hari) Hari,Sum(Rakesh) Rakesh,Sum(total) Total from ( select (type), max( decode( SPECIALIST, 'Hari', cnt, null ) ) Hari, max( decode( SPECIALIST, 'Rakesh', cnt, null ) ) Rakesh, Sum(cnt) total from ( select (type),specialist, sum(update_count) cnt from scope1 where (RECVD_DATE >='"+c+"' and RECVD_DATE <='"+d+"') group by (type),specialist ) q group by (type) )a) b Order by Total
i want the data to be retrieved something like in
<%=rs1.getString(i)%>
Thanks
Upvotes: 0
Views: 2505
Reputation: 692231
<%
for (int i = 1; i < 5; i++) {
%>
<td><%= rs1.getString(i) %></td>
<%
}
%>
Bu I would never do that like this. JSPs should not use scriptlets. And they should not execute database queries. Their only job should be to generate HTML markup using objects stored in request attributes.
The SQL query should be in a servlet or action of your preferred MVC framework. This servlet would get the data, store it in a List<MyObject>
, put this list in a request attribute, and forward to the JSP.
The JSP would then use the JSTL and the JSP EL (and other custom tags, if needed), in order to display this List<MyObject>
.
Upvotes: 1