CODEWITHSUNDEEP
oracle-databasejspjdbc
Rakesh
Rakesh

Reputation: 594

Retrieve data from tables into jsp dynamically

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

Answers (1)

JB Nizet
JB Nizet

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

Related Questions