Notes

package com.highradius.controller;

//import java.beans.Statement;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.highradius.model.User;

//import extjs.pojo;

/**
* @author HRC
*
*/
public class UserDataServlet extends HttpServlet {

/**
*
*/
private static final long serialVersionUID = -7024426494285503866L;
private static final Logger LOG = LogManager.getLogger(UserDataServlet.class);

@Override
public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

Map<String, Object> data = new HashMap<>();
List<User> users = new ArrayList<>();
int total =0;
Connection dbCon=null;
PreparedStatement pstmt=null, pstmt1=null;
//Statement stmnt=null;
ResultSet rs=null, rs1=null;
String url="jdbc:mysql://localhost:3306/076";
String username="devuser";
String password="Demo@123";
int start = Integer.parseInt(request.getParameter("start"));
int limit = Integer.parseInt(request.getParameter("limit"));

String paramQuery="Select * from acct_doc_header limit ?,?";
String paramQueryTotal="Select count(*) from acct_doc_header";
//ArrayList<String> createlist = new ArrayList<>();
try
{
Class.forName("com.mysql.jdbc.Driver");
dbCon=DriverManager.getConnection(url,username,password);
pstmt=dbCon.prepareStatement(paramQuery);
pstmt1=dbCon.prepareStatement(paramQueryTotal);
pstmt.setInt(1, start);
pstmt.setInt(2, limit);
System.out.println("Database Connection");

rs=pstmt.executeQuery();
rs1=pstmt1.executeQuery();
//List<User> arrli = new ArrayList<>();
if(rs1.next()) {
total = rs1.getInt(1);
}

while(rs.next())
{
User fetch = new User();
fetch.setId(rs.getInt(1));
fetch.setAccountId(rs.getInt(2));
fetch.setDocumentNumberNorm(rs.getString(3));
fetch.setCompanyCode(rs.getString(4));
fetch.setFiscalYear(rs.getString(5));
fetch.setBranch(rs.getString(6));
fetch.setCustomerNumberNorm(rs.getString(7));
fetch.setFkCustomerMapId(rs.getInt(8));
fetch.setDocumentDateNorm(rs.getDate(9));
fetch.setBaselineDateNorm(rs.getDate(10));
fetch.setDueDateNorm(rs.getDate(11));
fetch.setInvoiceNumberNorm(rs.getString(12));
fetch.setOpenAmountNorm(rs.getDouble(13));
fetch.setPaymentTerms(rs.getString(14));
fetch.setClearingDateNorm(rs.getDate(15));
fetch.setIsopen(rs.getInt(16));
fetch.setOrderType(rs.getString(17));
fetch.setOrderDate(rs.getDate(18));
fetch.setBusinessArea(rs.getString(19));
fetch.setShipDate(rs.getDate(20));
fetch.setJobId(rs.getInt(21));
fetch.setTaxAmount(rs.getDouble(22));
fetch.setCurrentDisputeAmount(rs.getDouble(23));
fetch.setShipTo(rs.getString(24));
fetch.setDocumentId(rs.getInt(25));
fetch.setDocumentDate(rs.getDate(26));
fetch.setActualOpenAmount(rs.getDouble(27));
fetch.setDueDate(rs.getDate(28));
fetch.setInvoiceAge(rs.getInt(29));
fetch.setIsvalidDispute(rs.getInt(30));
fetch.setPostingKey(rs.getString(31));
fetch.setFkStrategyId(rs.getInt(32));
fetch.setCurrency(rs.getString(33));
fetch.setDebitCreditIndicator(rs.getInt(34));
fetch.setValidOpenAmount(rs.getDouble(35));
fetch.setCustomerName(rs.getString(36));
//fetch.setRetainage_Amount(rs.getDouble("ret_amt"));
users.add(fetch);
}

data.put("rows", users);
data.put("results", total);
String jsonString = getJSONStringFromObject(data);

// Set response content type
response.setContentType("application/json");
response.getWriter().write(jsonString);
}
catch(Exception e)
{
System.out.println("Hell1o"+e);
e.printStackTrace();
}
finally
{
closeResource(rs);
closeResource(pstmt);
closeResource(dbCon);
}
}



private void closeResource(Connection dbCon) {
try {
if(dbCon!=null)
{
dbCon.close();
}

}catch(Exception e)
{
System.out.println("Hello"+e);
}

}


private void closeResource(PreparedStatement pstmt) {
try {
if(pstmt!=null)
{
pstmt.close();
}

}catch(Exception e)
{
System.out.println(e);
}


}


private void closeResource(ResultSet rs) {
try {
if(rs!=null)
{
rs.close();
}

}catch(Exception e)
{
System.out.println(e);
}


}


/**
* Convert Object into JSON String
*
* @param Object
* @return JSON String
*/
private String getJSONStringFromObject(Object object) {
ObjectMapper mapper = new ObjectMapper();
String jsonString = null;
try {
jsonString = mapper.writeValueAsString(object);
} catch (Exception e) {
LOG.error("Error while parasing object to json" , e);
}
return jsonString;
}

}