Notes

package com.highradius.controller;

import java.io.*;
import java.util.*;
import java.sql.*;
import java.text.SimpleDateFormat;

import javax.servlet.*;
import javax.servlet.http.*;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.highradius.model.AcctDocHdr;
public class UserDataServ extends HttpServlet {
private ServletConfig config;
// Setting JSP page
String page = "DataPage.jsp";
public void init(ServletConfig config)
throws ServletException {
this.config = config;
}

public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

PrintWriter out = response.getWriter();
String url = "jdbc:mysql://localhost:3306/1605134";
String username = "devuser";
String password = "Demo@123";
PreparedStatement pstmt = null;
Connection connection = null;
ResultSet rs = null;
ResultSet rs1 = null;
response.setContentType("text/html");
String start = request.getParameter("start");
String limit = request.getParameter("limit");
String type = request.getParameter("invoicestype");
System.out.println(type);
List<AcctDocHdr> dataList = new ArrayList<AcctDocHdr>();
Map<String, Object> data = new HashMap<>();
long size = 0;

try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection(url, username, password);
// Select the data from the database

if(type.equals("all")) {

String sql = "select id,customer_name, customer_number_norm, document_number_norm, company_code, fiscal_year, branch, document_date_norm, baseline_date_norm, due_date_norm, invoice_number_norm, open_amount_norm, payment_terms, clearing_date_norm, isOpen, 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, currency,debit_credit_indicator, valid_open_amount from acctdochdr limit ?,?";
pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, Integer.parseInt(start));
pstmt.setInt(2, Integer.parseInt(limit));
pstmt.executeQuery();
rs = pstmt.getResultSet();

String total = "select * from acctdochdr";
pstmt = connection.prepareStatement(total);
pstmt.executeQuery();
rs1 = pstmt.getResultSet();
while(rs1.next()) {
size++;
}
}
else if(type.equals("open")) {
String sql = "select id,customer_name, customer_number_norm, document_number_norm, company_code, fiscal_year, branch, document_date_norm, baseline_date_norm, due_date_norm, invoice_no_norm, open_amount_norm, payment_terms, clearing_date_norm, isOpen, 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, currency, "
+ "debit_credit_indicator, valid_open_amount from acctdochdr where isOpen=1 limit ?,?";
pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, Integer.parseInt(start));
pstmt.setInt(2, Integer.parseInt(limit));
pstmt.executeQuery();
rs = pstmt.getResultSet();

String total = "select * from acct_doc_hdr where isOpen=1";
pstmt = connection.prepareStatement(total);
pstmt.executeQuery();
rs1 = pstmt.getResultSet();
while(rs1.next()) {
size++;
}
System.out.println(+size);
}
else{
String sql = "select id,customer_name, customer_number_norm, document_number_norm, company_code, fiscal_year, branch, document_date_norm, baseline_date_norm, due_date_norm, invoice_no_norm, open_amount_norm, payment_terms, clearing_date_norm, isOpen, 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, currency, "
+ "debit_credit_indicator, valid_open_amount from acctdochdr where isOpen=0 limit ?,?";
pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, Integer.parseInt(start));
pstmt.setInt(2, Integer.parseInt(limit));
pstmt.executeQuery();
rs = pstmt.getResultSet();

String total = "select * from acctdochdr where isOpen=0";
pstmt = connection.prepareStatement(total);
pstmt.executeQuery();
rs1 = pstmt.getResultSet();
while(rs1.next()) {
size++;
}
System.out.println(+size);
}

while (rs.next()) {
// Add records into data list
AcctDocHdr obj = new AcctDocHdr();
obj.setId(rs.getInt(1));
obj.setCustomerName(rs.getString(2));
obj.setCustomerNumberNorm(rs.getString(3));
obj.setDocumentNumberNorm(rs.getString(4));
obj.setCompanyCode(rs.getString(5));
obj.setFiscalYear(rs.getString(6));
obj.setBranch(rs.getString(7));
obj.setDocumentDate(rs.getDate(8));
obj.setBaselineDateNorm(rs.getDate(9));
obj.setDueDateNorm(rs.getDate(10));
obj.setInvoiceNumberNorm(rs.getString(11));
obj.setOpenAmountNorm(rs.getDouble(12));
obj.setPaymentTerms(rs.getString(13));
obj.setClearingDateNorm(rs.getDate(14));
obj.setOpen(rs.getBoolean(15));
obj.setOrderType(rs.getString(16));
obj.setOrderDate(rs.getDate(17));
obj.setBusinessArea(rs.getString(18));
obj.setShipDate(rs.getDate(19));
obj.setJobId(rs.getInt(20));
obj.setTaxAmt(rs.getDouble(21));
obj.setCurrentDisputAmount(rs.getDouble(22));
obj.setShipTo(rs.getString(23));
obj.setDocumentId(rs.getInt(24));
obj.setDocumentDate(rs.getDate(25));
obj.setActualOpenAmount(rs.getDouble(26));
obj.setDueDate(rs.getDate(27));
obj.setInvoiceAge(rs.getInt(28));
obj.setIsvalidDispute(rs.getBoolean(29));
obj.setRetainageAmount(rs.getString(30));
obj.setPostingKey(rs.getString(31));
obj.setCurrency(rs.getString(32));
obj.setDebitCreditIndicator(rs.getString(33));
obj.setValidOpenAmount(rs.getDouble(34));
dataList.add(obj);
}
} catch (Exception e) {
System.out.println("Exception is ;" + e);
}finally {
try{
if(connection != null)
connection.close();
}catch(Exception e) {
e.printStackTrace();
}
try {
if(rs!=null)
rs.close();
}catch(Exception e) {
e.printStackTrace();
}
}
data.put("rows", dataList);
data.put("results", size);
String jsonString = getJSONStringFromObject(data);

response.setContentType("application/json");
try {
response.getWriter().write(jsonString);
} catch(IOException e) {
}
}

private String getJSONStringFromObject(Object object) {
ObjectMapper mapper = new ObjectMapper();
String jsonString = null;
SimpleDateFormat date = new SimpleDateFormat("yy-mm-dd");
mapper.setDateFormat(date);

try {
jsonString = mapper.writeValueAsString(object);
} catch (Exception e) {
}
return jsonString;
}

}