• Post Reply Bookmark Topic Watch Topic
  • New Topic

How to execute multiple query of sum function in the servlet and pass it to jsp?  RSS feed

 
Priya Prasad
Ranch Hand
Posts: 31
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am trying to create a web application where I want to execute the following queries in the table called bkpl:-

1. select sum(Amount) from bkpl;
2.select sum(Amount) from bkpl where Af_Scheme_type='In-Principle' and GrantedBy='PLHO';
3..select sum(Amount) from bkpl where Af_Scheme_type='Final' and GrantedBy='PLHO';
4..select sum(Amount) from bkpl where Af_Scheme_type='In-Principle' and GrantedBy='Region';
5..select sum(Amount) from bkpl where Af_Scheme_type='Final' and GrantedBy='Region';

Now I figured out that I can use PreparedStatement but I have no idea how to fit in so many combinations with it. My piece of codes are here

in the Servlet where I could succesfully execute one query:-


import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
*
* @author Priya
*/
public class MD extends HttpServlet {

Connection conn;
Statement stmt;
ResultSet res;
DatabaseConnection dbconn;
String query1, query2, query3, query4, query5, query6, query7, query8, Unit_Name;
private double Amount;
List lst = new ArrayList();

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
PrintWriter out = response.getWriter();
try {
Unit_Name = request.getParameter("Unit_Name");
dbconn = new DatabaseConnection();
conn = dbconn.setConnection();

if (Unit_Name.equals("BKPL")) {
stmt = conn.createStatement();
query1 = "Select Sum(Amount) from bkpl;";
res = dbconn.getResult(query1, conn);
while (res.next()) {
Amount = res.getDouble(1);
System.out.println(Amount);
boolean add = lst.add(Amount);

}
res.close();
}

} catch (Exception e) {
System.out.println(e.getMessage());
RequestDispatcher rd = request.getRequestDispatcher("/error.jsp");
rd.forward(request, response);
} finally {
request.setAttribute("data", lst);
RequestDispatcher rd = request.getRequestDispatcher("/responseTotal.jsp");
rd.forward(request, response);
lst.clear();
out.close();
}
}

@Override
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}

@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}

@Override
public String getServletInfo() {
return "Short description";
}
}


and this is my jsp page where I could print the result of the query--
<%--
Document : DisplayRecord
Created on : 23 Jun, 2013, 12:57:59 PM
Author : Priya
--%>

<%@page import="java.util.List"%>
<%@page import="java.util.Iterator"%>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" type="text/css" href="style.css">
<title>View Cumulative Details</title>
</head>
<body>
<table>
<% Iterator itr;%>
<% List data = (List) request.getAttribute("data");
for (itr = data.iterator(); itr.hasNext();) {
%>
<tr>
<tr>
<td><strong>BKPL Total: </strong></td>
<td><span style="font-size:smaller; font-style:italic;"><% Double s = (Double) itr.next();%>
<%=s%></span></td>
</tr>



<%}%>
</tr>



</table>
</body>
</html>

I need to know how to run these multiple queries in the servlet and also how to print the result of those queries in the JSP page.

 
Mahender Parkipandla
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Priya Prasad wrote:
stmt = conn.createStatement();


The above statement create Statement Object. In this place you can create PreparedStatement as conn.prepareStatement(query)

Create string query as "Select * from Table where column_name_1 = ? and column_name_2 = ?"
You can set the values of columns dynamically using setter methods of PreparedStatement.

Priya Prasad wrote:
res = dbconn.getResult(query1, conn);


And by giving above details, you have not given inner details of geResult() method.

You can check more details in Java Document.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!