i have a servlet called NewServlet.java that takes user data from index.jsp i.e month , year and franchise num..and queries two database.


package mighty;
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<form method="post" name="frm" action="NewServlet.java">
<table border="0" width="300" align="center" bgcolor="#CDFFFF">
<tr><td colspan=2 style="font-size:12pt;color:#00000;" align="center"><h3>Search Record</h3></td></tr>
<tr><td ><b>franchise Number</b></td><td>: <input type="text" name="franno" id="franno">
<tr><td ><b>month</b></td><td>: <input type="text" name="month" id="month">
<tr><td ><b>year</b></td><td>: <input type="text" name="year" id="year">
<tr><td colspan=2 align="center"><input type="submit" name="submit" value="Submit"></td></tr>


package mighty;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

* @author nomosa
@WebServlet(name = "NewServlet", urlPatterns = {"/NewServlet"})
public class NewServlet extends HttpServlet {
//connnects to the first databse (ESR)
public static Connection getConnection(String db){
Connection con = null;
Class.forName("org.postgresql.Driver").newInstance ();
con = DriverManager.getConnection("jdbc:postgresql://" + db ,"postgres","postgres");

}catch (Exception ex){ }
return con;
//connects to the second database(FRNACHISE DB)
public static Connection getConnection2(String db){
Connection conn = null;
Class.forName("org.postgresql.Driver").newInstance ();
conn = DriverManager.getConnection("jdbc:postgresql://" + db ,"postgres","postgres");

}catch (Exception ex){ }
return conn;
* Processes requests for both HTTP
* <code>GET</code> and
* <code>POST</code> methods.
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs

protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doPost(request, response);

* Handles the HTTP
* <code>POST</code> method.
* @param request servlet request
* @param response servlet response
* @throws ServletException if a servlet-specific error occurs
* @throws IOException if an I/O error occurs
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter out = response.getWriter();

Statement st = null;
Statement st2= null;
Statement st3 = null;
Statement st4 = null;
Statement st5 = null;

try {
System.out.println("Connected to the database");
String franno = request.getParameter("franno");
String month = request.getParameter("month");
String year = request.getParameter("year");

ArrayList al=null;
ArrayList al2= null;
ArrayList al3 = null;
ArrayList al4 = null;
ArrayList al5= null;

//Array List to pint franchise number, month , year according to the user input
ArrayList difflist =new ArrayList();
String query = "select * from ticket where franno ='"+franno+"' and month='"+month+"' and year='"+year+"'order byfranno";
System.out.println("query " + query);

//Returns names of the franchises based on the farnchise number
ArrayList difflist4 =new ArrayList();
String query4 = "Select corpname from franchise where frannum = '"+franno+"'order byfranno";
System.out.println("query4 " + query4);

//array list to print ESR VALUES
ArrayList difflist2 = new ArrayList();
String query2 = "select franno, sum((ticket.sub_nontax + ticket.sub_tax + ticket.misc + ticket.disc + ticket.core_nontax + ticket.core_tax) - getxlinebycust(ticket.cust_id, ticket.inv_num::integer, date(ticket.inv_date))) AS ear-sales, date_part('month',inv_date) as salesmonth , date_part('year',inv_date) as salesyear from ticket, salesperson where ticket.mightyspid = salesperson.mightyspid and date_part('month',inv_date) = ? and date_part('year',inv_date) = ? and franno::integer in (?) group by franno, salesmonth,salesyear order by franno";
System.out.println("query2 " + query2);

//Array List to print Reported Values
ArrayList difflist3 = new ArrayList();
String query3 = "select franno,sum(territory +custodial) as reported,salesmonth,salesyear from salesdata,salesperson where salesperson.mightyspid = salesdata.mightyspid and salesmonth = ? and salesyear = ? franno::integer in (?) group by franno,salesmonth,salesyear order by franno";
System.out.println("query3 " + query3);

//ArraylIST TO Print difference in ESR-REPORTED
ArrayList difflist5 = new ArrayList();
//Note that is not a set difference, and won't output items in result2 that don't exist in result3. It's set subtraction.
String query5 = "select DISTINCT rs2.column FROM rs21 LEFT JOIN rs2 ON rs3.column = rs2.column WHERE rs2.column IS NULL order by franno";
System.out.println("query5" + query5);

Connection con = getConnection("//");
st = con.createStatement();
st2 = con.createStatement();
st3 = con.createStatement();
st5 = con.createStatement();

Connection conn= getConnection2("//");
st4 = conn.createStatement();

ResultSet rs = st.executeQuery(query);
al = new ArrayList();

System.out.println("al :: "+al);
System.out.println("difflist " + difflist);

ResultSet rs2 = st2.executeQuery(query2);
while (rs2.next() ){
al2 = new ArrayList ();
System.out.println("al2 :: "+al2);
System.out.println("difflist2 " + difflist2);

ResultSet rs3 = st3.executeQuery(query3);
while (rs3.next() ){
al3 = new ArrayList ();
System.out.println("al3 :: "+al3);
System.out.println("difflist3 " + difflist3);

ResultSet rs4 = st4.executeQuery(query4);
while (rs4.next()){
System.out.println("al4 :: "+al4);
System.out.println("difflist4 " + difflist4);

ResultSet rs5 = st5.executeQuery(query5);
while (rs5.next()){
System.out.println("al5 :: "+al5);
System.out.println("difflist5 " + difflist5);

out.println("difflist " + difflist);
out.println("difflist2 " + difflist2);
out.println("difflist3 " + difflist3);
out.println("difflist4 " + difflist4);
out.println("difflist5 " + difflist5);

System.out.println("Disconnected from database");
} catch (Exception e) {

public String getServletInfo() {
return "Happy thanks Giving to Mighty Auto!!!!";
}// </editor-fold>