Notes

package com.highradius.controller;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
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.DataModel;

/**
* @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<>();

String path = request.getRequestURI().split("/")[2];

String start = request.getParameter("start");
String limit = "10";

String query="";
String countQuery="";

if(path.equals("ai_userdata.do")) {
query = "SELECT * FROM acct_doc_hdr LIMIT "+start+", "+limit+";";
countQuery = "SELECT COUNT(1) FROM acct_doc_hdr;";
}
else if(path.equals("oi_userdata.do")) {
query = "SELECT * FROM acct_doc_hdr WHERE isOpen = 1 LIMIT "+start+", "+limit+";";
countQuery = "SELECT COUNT(1) FROM acct_doc_hdr WHERE isOpen = 1;";
}
else if(path.equals("ci_userdata.do")) {
query = "SELECT * FROM acct_doc_hdr WHERE isOpen = 0 LIMIT "+start+", "+limit+";";
countQuery = "SELECT COUNT(1) FROM acct_doc_hdr WHERE isOpen = 0;";
}

List<DataModel> dataList = getDataFromDb(query);

data.put("rows", dataList);
data.put("results", getTotalCount(countQuery));


String jsonString = getJSONStringFromObject(data);

// Set response content type
response.setContentType("application/json");
try {
response.getWriter().write(jsonString);
} catch(IOException e) {
LOG.error("Unable to write json to HttpResponse" , e);
}


}


private int getTotalCount(String query) {
// TODO Auto-generated method stub
Connection dbCon = null;
String url = "jdbc:mysql://localhost:3306/db s1.4";
int count=0;

try {

Class.forName("com.mysql.jdbc.Driver");
dbCon = DriverManager.getConnection(url, "devuser", "Demo@123");
Statement stmt = null;
stmt = dbCon.createStatement();
ResultSet rs = stmt.executeQuery(query);

while(rs.next()) {
count = rs.getInt(1);
}
}
catch(Exception e) {
e.printStackTrace();
}
return count;
}


private List<DataModel> getDataFromDb(String query) {
// TODO Auto-generated method stub
List<DataModel> dataList = new ArrayList<>();
Connection dbCon = null;
String url = "jdbc:mysql://localhost:3306/db s1.4";

try {

Class.forName("com.mysql.jdbc.Driver");
dbCon = DriverManager.getConnection(url, "devuser", "Demo@123");
Statement stmt = null;

stmt = dbCon.createStatement();

ResultSet rs = stmt.executeQuery(query);

while(rs.next()) {

DataModel model = new DataModel();

model.setId(rs.getInt("id"));
model.setAccountId(rs.getInt("account_id"));
model.setDocumentNumberNorm(rs.getString("document_number_norm"));
model.setCompanyCode(rs.getString("company_code"));
model.setFiscalYear(rs.getString("fiscal_year"));
model.setBranch(rs.getString("branch"));
model.setCustomerNumberNorm(rs.getString("customer_number_norm"));
model.setFkCustomerMapId(rs.getInt("fk_customer_map_id"));
model.setDocumentDateNorm(rs.getString("document_date_norm"));
model.setBaselineDateNorm(rs.getString("baseline_date_norm"));
model.setDueDateNorm(rs.getString("due_date_norm"));
model.setInvoiceNumberNorm(rs.getString("invoice_number_norm"));
model.setOpenAmountNorm(rs.getDouble("open_amount_norm"));
model.setPaymentTerms(rs.getString("payment_terms"));
model.setClearingDateNorm(rs.getString("clearing_date_norm"));
model.setIsOpen(rs.getInt("isOpen"));
model.setOrderType(rs.getString("order_type"));
model.setOrderDate(rs.getString("order_date"));
model.setBusinessArea(rs.getString("business_area"));
model.setShipDate(rs.getString("ship_date"));
model.setJobId(rs.getInt("job_id"));
model.setTaxAmt(rs.getDouble("tax_amt"));
model.setCurrentDisputAmount(rs.getDouble("current_disput_amount"));
model.setShipTo(rs.getString("ship_to"));
model.setDocumentId(rs.getInt("document_id"));
model.setDocument_date(rs.getString("document_date"));
model.setActualOpenAmount(rs.getDouble("actual_open_amount"));
model.setDueDate(rs.getString("due_date"));
model.setInvoiceAge(rs.getInt("invoice_age"));
model.setIsvaliDispute(rs.getInt("isvalid_dispute"));
model.setPostingKey(rs.getString("posting_key"));
model.setStrategyId(rs.getInt("strategy_id"));
model.setCurrency(rs.getString("currency"));
model.setDebitCreditIndicator(rs.getString("debit_credit_indicator"));
model.setValidOpenAmount(rs.getDouble("valid_open_amount"));
model.setCustomerName(rs.getString("customer_name"));
model.setRetainageAmount(rs.getDouble("retainage_amount"));

System.out.println(model.getIsOpen());

dataList.add(model);
}
}
catch(Exception e) {
e.printStackTrace();
}

return dataList;
}


/**
* 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;
}

}