Notes

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

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.*;

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{
Connection dbCon = null;
String url = "jdbc:mysql://localhost:3306/fintech";
String username = "devuser";
String password = "Demo@123";
PreparedStatement pstmt = null;
ResultSet rs1=null;
ResultSet rs2=null;
ResultSet rs3=null;
String query1 = "Select account_id,document_number_norm,company_code,fiscal_year,branch,customer_number_norm,fk_customer_map_id,document_date_norm,baseline_date_norm,due_date_norm,invoice_number_norm,open_amount_norm,payment_terms,clearing_date_norm,is_open,order_type,order_date,business_area,ship_date,job_id,tax_amt,current_disput_amount,ship_to,document_id,document_date,actual_open_amount,due_date,invoice_age,isvalid_dispute,retainage_amount,posting_key,strategy_id,currency,debit_credit_indicator,valid_open_amount,customer_name from acct_doc_hdr";
String query2 = "Select account_id,document_number_norm,company_code,fiscal_year,branch,customer_number_norm,fk_customer_map_id,document_date_norm,baseline_date_norm,due_date_norm,invoice_number_norm,open_amount_norm,payment_terms,clearing_date_norm,is_open,order_type,order_date,business_area,ship_date,job_id,tax_amt,current_disput_amount,ship_to,document_id,document_date,actual_open_amount,due_date,invoice_age,isvalid_dispute,retainage_amount,posting_key,strategy_id,currency,debit_credit_indicator,valid_open_amount,customer_name from acct_doc_hdr where is_open=?" ;
try {
Class.forName("com.mysql.jdbc.Driver");
dbCon = DriverManager.getConnection(url, username, password);
java.sql.Statement stmt=dbCon.createStatement();
rs1=stmt.executeQuery(query1);
ArrayList<AcctDocHdr> list1=new ArrayList<>();
ArrayList<AcctDocHdr> list2=new ArrayList<>();
ArrayList<AcctDocHdr> list3=new ArrayList<>();
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
pstmt1 = dbCon.prepareStatement(query2);
pstmt2 = dbCon.prepareStatement(query2);
while(rs1.next())
{
AcctDocHdr temp=new AcctDocHdr();
temp.setAccountId(rs1.getLong("account_id"));
temp.setDocumentNumberNorm(rs1.getString("document_number_norm"));
temp.setCompanyCode(rs1.getString("company_code"));
temp.setFiscalYear(rs1.getString("fiscal_year"));
temp.setBranch(rs1.getString("branch"));
temp.setCustomerNumberNorm(rs1.getString("customer_number_norm"));
temp.setFkCustomerMapId(rs1.getLong("fk_customer_map_id"));
temp.setDocumentDateNorm(rs1.getDate("document_number_norm"));
temp.setBaselineDateNorm(rs1.getDate("baseline_date_norm"));
temp.setDueDateNorm(rs1.getDate("due_date_norm"));
temp.setInvoiceNumberNorm(rs1.getString("invoice_number_norm"));
temp.setOpenAmountNorm(rs1.getDouble("open_amount_norm"));
temp.setPaymentTerms(rs1.getString("payment_terms"));
temp.setClearingDateNorm(rs1.getDate("clearing_date_norm"));
temp.setIsOpen(rs1.getString("is_open"));
temp.setOrderType(rs1.getString("order_type"));
temp.setOrderDate(rs1.getDate("order_date"));
temp.setBusinessArea(rs1.getString("business_area"));
temp.setShipDate(rs1.getDate("ship_date"));
temp.setJobId(rs1.getLong("job_id"));
temp.setTaxAmt(rs1.getDouble("tax_amt"));
temp.setCurrentDisputAmount(rs1.getDouble("current_disput_amount"));
temp.setShipTo(rs1.getString("ship_to"));
temp.setDocumentId(rs1.getLong("document_id"));
temp.setDocumentDate(rs1.getDate("document_date"));
temp.setActualOpenAmount(rs1.getDouble("actual_open_amount"));
temp.setDueDate(rs1.getDate("due_date"));
temp.setInvoiceAge(rs1.getLong("invoice_age"));
temp.setIsvalidDispute(rs1.getString("isvalid_dispute"));
temp.setPostingKey(rs1.getString("posting_key"));
temp.setStrategyId(rs1.getLong("strategy_id"));
temp.setCurrency(rs1.getString("currency"));
temp.setDebitCreditIndicator(rs1.getString("debit_credit_indicator"));
temp.setValidOpenAmount(rs1.getDouble("valid_open_amount"));
temp.setCustomerName(rs1.getString("customer_name"));
temp.setRetainageAmount(rs1.getDouble("retainage_amount"));
list1.add(temp);
//System.out.println(list.size());
}
pstmt1.setString(1, "1");
rs2=pstmt1.executeQuery();
while(rs1.next())
{
AcctDocHdr temp=new AcctDocHdr();
temp.setAccountId(rs1.getLong("account_id"));
temp.setDocumentNumberNorm(rs1.getString("document_number_norm"));
temp.setCompanyCode(rs1.getString("company_code"));
temp.setFiscalYear(rs1.getString("fiscal_year"));
temp.setBranch(rs1.getString("branch"));
temp.setCustomerNumberNorm(rs1.getString("customer_number_norm"));
temp.setFkCustomerMapId(rs1.getLong("fk_customer_map_id"));
temp.setDocumentDateNorm(rs1.getDate("document_number_norm"));
temp.setBaselineDateNorm(rs1.getDate("baseline_date_norm"));
temp.setDueDateNorm(rs1.getDate("due_date_norm"));
temp.setInvoiceNumberNorm(rs1.getString("invoice_number_norm"));
temp.setOpenAmountNorm(rs1.getDouble("open_amount_norm"));
temp.setPaymentTerms(rs1.getString("payment_terms"));
temp.setClearingDateNorm(rs1.getDate("clearing_date_norm"));
temp.setIsOpen(rs1.getString("is_open"));
temp.setOrderType(rs1.getString("order_type"));
temp.setOrderDate(rs1.getDate("order_date"));
temp.setBusinessArea(rs1.getString("business_area"));
temp.setShipDate(rs1.getDate("ship_date"));
temp.setJobId(rs1.getLong("job_id"));
temp.setTaxAmt(rs1.getDouble("tax_amt"));
temp.setCurrentDisputAmount(rs1.getDouble("current_disput_amount"));
temp.setShipTo(rs1.getString("ship_to"));
temp.setDocumentId(rs1.getLong("document_id"));
temp.setDocumentDate(rs1.getDate("document_date"));
temp.setActualOpenAmount(rs1.getDouble("actual_open_amount"));
temp.setDueDate(rs1.getDate("due_date"));
temp.setInvoiceAge(rs1.getLong("invoice_age"));
temp.setIsvalidDispute(rs1.getString("isvalid_dispute"));
temp.setPostingKey(rs1.getString("posting_key"));
temp.setStrategyId(rs1.getLong("strategy_id"));
temp.setCurrency(rs1.getString("currency"));
temp.setDebitCreditIndicator(rs1.getString("debit_credit_indicator"));
temp.setValidOpenAmount(rs1.getDouble("valid_open_amount"));
temp.setCustomerName(rs1.getString("customer_name"));
temp.setRetainageAmount(rs1.getDouble("retainage_amount"));
list2.add(temp);
//System.out.println(list.size());
}
pstmt2.setString(1, "0");
rs3=pstmt2.executeQuery();
while(rs1.next())
{
AcctDocHdr temp=new AcctDocHdr();
temp.setAccountId(rs1.getLong("account_id"));
temp.setDocumentNumberNorm(rs1.getString("document_number_norm"));
temp.setCompanyCode(rs1.getString("company_code"));
temp.setFiscalYear(rs1.getString("fiscal_year"));
temp.setBranch(rs1.getString("branch"));
temp.setCustomerNumberNorm(rs1.getString("customer_number_norm"));
temp.setFkCustomerMapId(rs1.getLong("fk_customer_map_id"));
temp.setDocumentDateNorm(rs1.getDate("document_number_norm"));
temp.setBaselineDateNorm(rs1.getDate("baseline_date_norm"));
temp.setDueDateNorm(rs1.getDate("due_date_norm"));
temp.setInvoiceNumberNorm(rs1.getString("invoice_number_norm"));
temp.setOpenAmountNorm(rs1.getDouble("open_amount_norm"));
temp.setPaymentTerms(rs1.getString("payment_terms"));
temp.setClearingDateNorm(rs1.getDate("clearing_date_norm"));
temp.setIsOpen(rs1.getString("is_open"));
temp.setOrderType(rs1.getString("order_type"));
temp.setOrderDate(rs1.getDate("order_date"));
temp.setBusinessArea(rs1.getString("business_area"));
temp.setShipDate(rs1.getDate("ship_date"));
temp.setJobId(rs1.getLong("job_id"));
temp.setTaxAmt(rs1.getDouble("tax_amt"));
temp.setCurrentDisputAmount(rs1.getDouble("current_disput_amount"));
temp.setShipTo(rs1.getString("ship_to"));
temp.setDocumentId(rs1.getLong("document_id"));
temp.setDocumentDate(rs1.getDate("document_date"));
temp.setActualOpenAmount(rs1.getDouble("actual_open_amount"));
temp.setDueDate(rs1.getDate("due_date"));
temp.setInvoiceAge(rs1.getLong("invoice_age"));
temp.setIsvalidDispute(rs1.getString("isvalid_dispute"));
temp.setPostingKey(rs1.getString("posting_key"));
temp.setStrategyId(rs1.getLong("strategy_id"));
temp.setCurrency(rs1.getString("currency"));
temp.setDebitCreditIndicator(rs1.getString("debit_credit_indicator"));
temp.setValidOpenAmount(rs1.getDouble("valid_open_amount"));
temp.setCustomerName(rs1.getString("customer_name"));
temp.setRetainageAmount(rs1.getDouble("retainage_amount"));
list2.add(temp);
//System.out.println(list.size());
}

System.out.println(list.size());
ObjectMapper Obj = new ObjectMapper();
try {
String jsonStr = Obj.writeValueAsString(list);
//System.out.println(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();
}
}
}