Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package account; import com.google.gson.JsonArray; import com.google.gson.JsonObject; import java.io.IOException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLNonTransientConnectionException; import javax.servlet.ServletException; import javax.servlet.http.HttpServlet; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import support.DBHelper; import support.Library; /** * * @author bhaumik */ public class DailyCashSummary extends HttpServlet { /** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { final String from_date = request.getParameter("from_date"); final String to_date = request.getParameter("to_date"); final int branch_cd = Integer.parseInt(request.getParameter("branch_cd")); final DBHelper helper = DBHelper.GetDBHelper(); final Connection dataConnection = helper.getConnMpAdmin(); final JsonObject jResultObj = new JsonObject(); final Library lb = Library.getInstance(); if (dataConnection != null) { try { PreparedStatement pstLocal = null; String sql = "SELECT doc_date,branch_cd,SUM(CASE WHEN CRDR=0 THEN val ELSE 0 END) AS dr_bal,SUM(CASE WHEN CRDR=0 THEN val ELSE val*-1 END) AS bal" + ",SUM(CASE WHEN CRDR=1 THEN val*-1 ELSE 0 END) AS cr_bal FROM oldb2_2 \n" + " WHERE ac_cd IN (SELECT ac_cd FROM acntmst WHERE grp_cd IN (SELECT grp_cd FROM groupmst WHERE group_name='CASH')) " + " AND doc_cd <>'SL' AND doc_cd <>'CO' " + " and doc_date>='" + from_date + "' " + " and doc_date<='" + to_date + "' and branch_cd =" + branch_cd + " group by doc_date,branch_cd"; pstLocal = dataConnection.prepareStatement(sql); ResultSet viewDataRs = pstLocal.executeQuery(); JsonArray array = new JsonArray(); while (viewDataRs.next()) { JsonObject object = new JsonObject(); object.addProperty("v_date", viewDataRs.getString("doc_date")); object.addProperty("dr_bal", viewDataRs.getDouble("dr_bal")); object.addProperty("cr_bal", viewDataRs.getDouble("cr_bal")); object.addProperty("bal", viewDataRs.getDouble("bal")); object.addProperty("branch_cd", viewDataRs.getDouble("branch_cd")); array.add(object); } lb.closeResultSet(viewDataRs); lb.closeStatement(pstLocal); jResultObj.addProperty("result", 1); jResultObj.addProperty("Cause", "success"); jResultObj.add("data", array); } catch (SQLNonTransientConnectionException ex1) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", "Server is down"); } catch (SQLException ex) { jResultObj.addProperty("result", -1); jResultObj.addProperty("Cause", ex.getMessage()); } finally { lb.closeConnection(dataConnection); } } response.getWriter().print(jResultObj); } // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code."> /** * Handles the HTTP <code>GET</code> method. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } /** * Handles the HTTP <code>POST</code> method. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ @Override protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { processRequest(request, response); } /** * Returns a short description of the servlet. * * @return a String containing servlet description */ @Override public String getServletInfo() { return "Short description"; }// </editor-fold> }