Notes

package com.highradius.controller;


import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.sql.*;
import java.sql.Date;
import java.io.*;
import java.io.IOException;
import java.io.PrintWriter;
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.dbLoadingPojo;

public class retrevingDataFromTableServlet extends HttpServlet {

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


public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException{
res.setContentType("text/html");
PrintWriter pw = res.getWriter();

try {
String url = "jdbc:mysql://localhost:3306/1606535";
String userName = "devuser";
String password = "Demo@123";

// 1. Get a connection to database
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection(url, userName, password);
System.out.println("Connection established");

// 2. create a statement
Statement stmt = conn.createStatement();

// 3. execute sql query
String query = "SELECT * FROM account_doc_header;";

// process result
ResultSet results = stmt.executeQuery(query);

System.out.println("Success");

List<dbLoadingPojo> list = new ArrayList<>();

while(results.next()) {
dbLoadingPojo pojo = new dbLoadingPojo();

//account_id
pojo.setAccount_id(Integer.valueOf(results.getString(1)));

//Document_number_norm
pojo.setDocument_number_norm(results.getString(2));

//company_code
pojo.setCompany_code(results.getString(3));

//fiscal_year
pojo.setFiscal_year(results.getString(4));

//branch
pojo.setBranch(results.getString(5));

//customer_number_norm
pojo.setCustomer_number_norm(results.getString(6));

//fk_customer_map_id
pojo.setFk_customer_map_id(Integer.valueOf(results.getString(7)));

//document_date_norm
pojo.setDocument_date_norm(convertToDate(results.getString(8)));

//baseline_date_norm
pojo.setBaseline_date_norm(convertToDate(results.getString(9)));

//due_date_norm
pojo.setDue_date_norm(convertToDate(results.getString(10)));

//invoice_number_norm
pojo.setInvoice_number_norm(results.getString(11));

//open_amount_norm
pojo.setOpen_amount_norm(Double.valueOf(results.getString(12)));

//payment_terms
pojo.setPayment_terms(results.getString(13));

//clearing-date_norm
pojo.setClearing_date_norm(convertToDate(results.getString(14)));

//Is_Open
pojo.setIsOpen(Integer.valueOf(results.getString(15)));

//oder_type
pojo.setOrder_type(results.getString(16));

//order_date
pojo.setOrder_date(convertToDate(results.getString(17)));

//business_area
pojo.setBusiness_area(results.getString(18));

//ship_date
pojo.setShip_date(convertToDate(results.getString(19)));

//job_id
pojo.setJob_id(Integer.valueOf(results.getString(20)));

//tax_amount
pojo.setTax_amt(Double.valueOf(results.getString(21)));

//current_dispute_amount
pojo.setCurrent_disput_amount(Double.valueOf(results.getString(22)));

//ship_to
pojo.setShip_to(results.getString(23));

//document_id
pojo.setDocument_id(Integer.valueOf(results.getString(24)));

//document_date
pojo.setDocument_date(convertToDate(results.getString(25)));

//actual_open_amount
pojo.setActual_open_amount(Double.valueOf(results.getString(26)));

//due_date
pojo.setDue_date(convertToDate(results.getString(27)));

//invoice_age
pojo.setInvoice_age(Integer.valueOf(results.getString(28)));

//isvalid_dispute
pojo.setIsvalid_dispute(Integer.valueOf(results.getString(29)));

//retainage_amount
pojo.setRetainage_amount(Double.valueOf(results.getString(30)));

//posting_key
pojo.setPosting_key(results.getString(31));

//strategy_id
pojo.setStrategy_id(Integer.valueOf(results.getString(32)));

//currency
pojo.setCurrency(results.getString(33));

//debit_card_indicator
pojo.setDebit_credit_indicator(Integer.valueOf(results.getString(34)));

//valid_open_amount
pojo.setValid_open_amount(Double.valueOf(results.getString(35)));

//customer_name
pojo.setCustomer_name(results.getString(36));

//create_user
pojo.setCreate_user(results.getString(37));

//create_date
pojo.setCreate_date(convertToDate(results.getString(38)));

//update user
pojo.setUpdate_user(results.getString(39));

//update_date
pojo.setUpdate_date(convertToDate(results.getString(40)));

list.add(pojo);
}

Map<String, Object> dataMap = new HashMap<String, Object>();
dataMap.put("rows", list);

String jsonString = getJSONStringFromObject(dataMap);


conn.close();
} catch (Exception e) {

e.printStackTrace();
}
}
public static Date convertToDate(String startDate) {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
java.util.Date date = null;
try {
date = format.parse(startDate);
} catch (ParseException e) {
date = new java.util.Date(0);
}
java.sql.Date sql = null;
try {
sql = new java.sql.Date(date.getTime());
} catch (Exception e) {
return new Date(0);
}
return sql;
}

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