Notes

package com.highradius.model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.*;
import java.util.*;
import java.io.IOException;
import com.fasterxml.jackson.databind.ObjectMapper;

public class DbRead extends HttpServlet {

@Override
public void doGet (HttpServletRequest request,HttpServletResponse response)
throws ServletException, IOException{
String start=request.getParameter("start");
String limit=request.getParameter("limit");
String invoicetype=request.getParameter("invoicetype");
Map<String, Object> data = new HashMap<>();
ResultSet n=null;
long size=0;
Connection dbCon = null;
String url = "jdbc:mysql://localhost:3306/fintech";
String username = "devuser";
String password = "Demo@123";
try {
Class.forName("com.mysql.jdbc.Driver");
dbCon = DriverManager.getConnection(url, username, password);
ArrayList<AcctDocHdr> list=new ArrayList<>();
System.out.println(invoicetype);
if(invoicetype.equals("all"))
{
String query1 = "Select * from acct_doc_hdr limit ?,? ";
PreparedStatement pstmt = null;
String query2="Select * from acct_doc_hdr";
Statement stmt=dbCon.createStatement();
n=stmt.executeQuery(query2);

while(n.next())
size++;

pstmt = dbCon.prepareStatement(query1);
pstmt.setInt(1, Integer.parseInt(start));
pstmt.setInt(2, Integer.parseInt(limit));
ResultSet rs=pstmt.executeQuery();
while(rs.next())
{
AcctDocHdr temp=new AcctDocHdr();
temp.setAccountId(rs.getLong("account_id"));
temp.setDocumentNumberNorm(rs.getString("document_number_norm"));
temp.setCompanyCode(rs.getString("company_code"));
temp.setFiscalYear(rs.getString("fiscal_year"));
temp.setBranch(rs.getString("branch"));
temp.setCustomerNumberNorm(rs.getString("customer_number_norm"));
temp.setFkCustomerMapId(rs.getLong("fk_customer_map_id"));
temp.setDocumentDateNorm(rs.getDate("document_number_norm"));
temp.setBaselineDateNorm(rs.getDate("baseline_date_norm"));
temp.setDueDateNorm(rs.getDate("due_date_norm"));
temp.setInvoiceNumberNorm(rs.getString("invoice_number_norm"));
temp.setOpenAmountNorm(rs.getDouble("open_amount_norm"));
temp.setPaymentTerms(rs.getString("payment_terms"));
temp.setClearingDateNorm(rs.getDate("clearing_date_norm"));
temp.setIsOpen(rs.getString("is_open"));
temp.setOrderType(rs.getString("order_type"));
temp.setOrderDate(rs.getDate("order_date"));
temp.setBusinessArea(rs.getString("business_area"));
temp.setShipDate(rs.getDate("ship_date"));
temp.setJobId(rs.getLong("job_id"));
temp.setTaxAmt(rs.getDouble("tax_amt"));
temp.setCurrentDisputAmount(rs.getDouble("current_disput_amount"));
temp.setShipTo(rs.getString("ship_to"));
temp.setDocumentId(rs.getLong("document_id"));
temp.setDocumentDate(rs.getDate("document_date"));
temp.setActualOpenAmount(rs.getDouble("actual_open_amount"));
temp.setDueDate(rs.getDate("due_date"));
temp.setInvoiceAge(rs.getLong("invoice_age"));
temp.setIsvalidDispute(rs.getString("isvalid_dispute"));
temp.setPostingKey(rs.getString("posting_key"));
temp.setStrategyId(rs.getLong("strategy_id"));
temp.setCurrency(rs.getString("currency"));
temp.setDebitCreditIndicator(rs.getString("debit_credit_indicator"));
temp.setValidOpenAmount(rs.getDouble("valid_open_amount"));
temp.setCustomerName(rs.getString("customer_name"));
temp.setRetainageAmount(rs.getDouble("retainage_amount"));
list.add(temp);
//System.out.println(list.size());
}
}
else if(invoicetype.compareTo("open")==0)
{
String query1 = "Select * from acct_doc_hdr where is_open=? limit ?,? ";
PreparedStatement pstmt = null;
String query2="Select * from acct_doc_hdr where is_open= ?";
PreparedStatement stmt=dbCon.prepareStatement(query2);
stmt.setString(1, "1");
n=stmt.executeQuery();
while(n.next())
size++;
pstmt = dbCon.prepareStatement(query1);
pstmt.setString(1, "1");
pstmt.setInt(2, Integer.parseInt(start));
pstmt.setInt(3, Integer.parseInt(limit));
ResultSet rs=pstmt.executeQuery();
while(rs.next())
{
AcctDocHdr temp=new AcctDocHdr();
temp.setAccountId(rs.getLong("account_id"));
temp.setDocumentNumberNorm(rs.getString("document_number_norm"));
temp.setCompanyCode(rs.getString("company_code"));
temp.setFiscalYear(rs.getString("fiscal_year"));
temp.setBranch(rs.getString("branch"));
temp.setCustomerNumberNorm(rs.getString("customer_number_norm"));
temp.setFkCustomerMapId(rs.getLong("fk_customer_map_id"));
temp.setDocumentDateNorm(rs.getDate("document_number_norm"));
temp.setBaselineDateNorm(rs.getDate("baseline_date_norm"));
temp.setDueDateNorm(rs.getDate("due_date_norm"));
temp.setInvoiceNumberNorm(rs.getString("invoice_number_norm"));
temp.setOpenAmountNorm(rs.getDouble("open_amount_norm"));
temp.setPaymentTerms(rs.getString("payment_terms"));
temp.setClearingDateNorm(rs.getDate("clearing_date_norm"));
temp.setIsOpen(rs.getString("is_open"));
temp.setOrderType(rs.getString("order_type"));
temp.setOrderDate(rs.getDate("order_date"));
temp.setBusinessArea(rs.getString("business_area"));
temp.setShipDate(rs.getDate("ship_date"));
temp.setJobId(rs.getLong("job_id"));
temp.setTaxAmt(rs.getDouble("tax_amt"));
temp.setCurrentDisputAmount(rs.getDouble("current_disput_amount"));
temp.setShipTo(rs.getString("ship_to"));
temp.setDocumentId(rs.getLong("document_id"));
temp.setDocumentDate(rs.getDate("document_date"));
temp.setActualOpenAmount(rs.getDouble("actual_open_amount"));
temp.setDueDate(rs.getDate("due_date"));
temp.setInvoiceAge(rs.getLong("invoice_age"));
temp.setIsvalidDispute(rs.getString("isvalid_dispute"));
temp.setPostingKey(rs.getString("posting_key"));
temp.setStrategyId(rs.getLong("strategy_id"));
temp.setCurrency(rs.getString("currency"));
temp.setDebitCreditIndicator(rs.getString("debit_credit_indicator"));
temp.setValidOpenAmount(rs.getDouble("valid_open_amount"));
temp.setCustomerName(rs.getString("customer_name"));
temp.setRetainageAmount(rs.getDouble("retainage_amount"));
list.add(temp);
//System.out.println(list.size());
}
}
else if(invoicetype.compareTo("close")==0)
{
String query1 = "Select * from acct_doc_hdr where is_open=? limit ?,? ";
PreparedStatement pstmt = null;
String query2="Select * from acct_doc_hdr where is_open=?";
PreparedStatement stmt=dbCon.prepareStatement(query2);
stmt.setString(1, "0");
n=stmt.executeQuery();
while(n.next())
size++;
pstmt = dbCon.prepareStatement(query1);
pstmt.setString(1, "0");
pstmt.setInt(2, Integer.parseInt(start));
pstmt.setInt(3, Integer.parseInt(limit));
ResultSet rs=pstmt.executeQuery();
while(rs.next())
{
AcctDocHdr temp=new AcctDocHdr();
temp.setAccountId(rs.getLong("account_id"));
temp.setDocumentNumberNorm(rs.getString("document_number_norm"));
temp.setCompanyCode(rs.getString("company_code"));
temp.setFiscalYear(rs.getString("fiscal_year"));
temp.setBranch(rs.getString("branch"));
temp.setCustomerNumberNorm(rs.getString("customer_number_norm"));
temp.setFkCustomerMapId(rs.getLong("fk_customer_map_id"));
temp.setDocumentDateNorm(rs.getDate("document_number_norm"));
temp.setBaselineDateNorm(rs.getDate("baseline_date_norm"));
temp.setDueDateNorm(rs.getDate("due_date_norm"));
temp.setInvoiceNumberNorm(rs.getString("invoice_number_norm"));
temp.setOpenAmountNorm(rs.getDouble("open_amount_norm"));
temp.setPaymentTerms(rs.getString("payment_terms"));
temp.setClearingDateNorm(rs.getDate("clearing_date_norm"));
temp.setIsOpen(rs.getString("is_open"));
temp.setOrderType(rs.getString("order_type"));
temp.setOrderDate(rs.getDate("order_date"));
temp.setBusinessArea(rs.getString("business_area"));
temp.setShipDate(rs.getDate("ship_date"));
temp.setJobId(rs.getLong("job_id"));
temp.setTaxAmt(rs.getDouble("tax_amt"));
temp.setCurrentDisputAmount(rs.getDouble("current_disput_amount"));
temp.setShipTo(rs.getString("ship_to"));
temp.setDocumentId(rs.getLong("document_id"));
temp.setDocumentDate(rs.getDate("document_date"));
temp.setActualOpenAmount(rs.getDouble("actual_open_amount"));
temp.setDueDate(rs.getDate("due_date"));
temp.setInvoiceAge(rs.getLong("invoice_age"));
temp.setIsvalidDispute(rs.getString("isvalid_dispute"));
temp.setPostingKey(rs.getString("posting_key"));
temp.setStrategyId(rs.getLong("strategy_id"));
temp.setCurrency(rs.getString("currency"));
temp.setDebitCreditIndicator(rs.getString("debit_credit_indicator"));
temp.setValidOpenAmount(rs.getDouble("valid_open_amount"));
temp.setCustomerName(rs.getString("customer_name"));
temp.setRetainageAmount(rs.getDouble("retainage_amount"));
list.add(temp);
//System.out.println(list.size());
}
}

data.put("rows",list);
data.put("results", size);
ObjectMapper Obj = new ObjectMapper();
try {
String jsonStr = Obj.writeValueAsString(list);

// Set response content type
response.setContentType("application/json");
response.getWriter().write(jsonStr);
} catch(IOException e) {
e.printStackTrace();
}
}catch (Exception e) {
e.printStackTrace();
} finally {
closeResource(dbCon);
}
}
static void closeResource(Connection dbCon) {
try {
if (dbCon != null)
dbCon.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}