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.
Ranch Hand Scavenger Hunt
Number Posts (31/100)
First Post