ExtJs 4 Grid Editing tutorial - Java Servlet, JSON and MySQL Part 2

Click here for previous Chapter
In the previous chapter we did all our ExtJs programming for Grid Editing, here we will implement the server side logic using Java Servlet that will get us the customer records for Grid editing. Also add or update a customer record in MySQL database depending on the request.


ExtJs 4 Grid Editing Tutorial using the CellEditing plugin, Java servlet and MySQL

Java Web Application config file web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">
  <display-name>ExtJs4_Grid_Editing</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  <servlet>
    <description>Customer Servlet</description>
    <display-name>CustomerServlet</display-name>
    <servlet-name>CustomerServlet</servlet-name>
    <servlet-class>com.as400samplecode.CustomerServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>CustomerServlet</servlet-name>
    <url-pattern>/CustomerServlet</url-pattern>
  </servlet-mapping>
  <resource-ref>
    <description>MySQL Datasource</description>
    <res-ref-name>jdbc/mysql</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
  </resource-ref>
</web-app>

Java Web Application MySQL data resource file context.xml

<?xml version="1.0" encoding="UTF-8"?>
<Context reloadable="true">
<Resource auth="Container"
name="jdbc/mysql"
type="javax.sql.DataSource"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/sakila"
username="root"
password="mysql"
maxIdle="10"
maxActive="200"
maxWait="5"
removeAbandoned="true"
removeAbandonedTimeout="1200"
/>
</Context>

Java Bean for Customer Object Customer.java

package com.as400samplecode.util;

public class Customer {
   
    boolean active = false;
    int customerId = 0;
    String firstName = null;
    String lastName = null;
    String email = null;
   
    public boolean isActive() {
        return active;
    }
    public void setActive(boolean active) {
        this.active = active;
    }
    public int getCustomerId() {
        return customerId;
    }
    public void setCustomerId(int customerId) {
        this.customerId = customerId;
    }
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }
   
}

Java Web Application utility functions to process all Ajax Requests CustomerInformation.java


The function implements the following ...
  • Get customer list for a specific store Id
  • Get total count of customer for the ExtJs Grid Paging
  • Update a customer information
  • Insert a new customer into the MySQL database
package com.as400samplecode.util;

import java.sql.Connection;         
import java.sql.ResultSet;          
import java.sql.SQLException;       
import java.sql.PreparedStatement;  
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import javax.naming.Context;        
import javax.naming.InitialContext; 
import javax.sql.DataSource;        

public class CustomerInformation {           

    Connection conn = null;            
    PreparedStatement stmt = null;     
    String sql = null;

    //Get list of customers for a specific store Id with Extjs paging paramaters start and limit
    public ArrayList<Customer> getCustomers(String storeId, String start, String limit) { 

        ArrayList<Customer> customerList = new ArrayList<Customer>();   

        try {      
            Context ctx = (Context) new InitialContext().lookup("java:comp/env");
            conn = ((DataSource) ctx.lookup("jdbc/mysql")).getConnection(); 

            sql = "Select customer_id, first_name, last_name, email, address_id, active " + 
            " from customer where store_id = ? order by first_name,last_name LIMIT ?,?";                      

            stmt = conn.prepareStatement(sql);
            stmt.setInt(1,Integer.parseInt(storeId)); 
            stmt.setInt(2,Integer.parseInt(start)); 
            stmt.setInt(3,Integer.parseInt(limit)); 

            ResultSet rs = stmt.executeQuery(); 

            while(rs.next()){ 
                Customer customer = new Customer();
                customer.setActive(rs.getInt("active") == 1? true: false);
                customer.setCustomerId(rs.getInt("customer_id"));
                customer.setFirstName(rs.getString("first_name").trim());
                customer.setLastName(rs.getString("last_name").trim());
                customer.setEmail(rs.getString("email").trim());
                customerList.add(customer);
            }                                                                         

            rs.close();                                                               
            stmt.close();                                                             
            stmt = null;                                                              


            conn.close();                                                             
            conn = null;                                                   

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

        finally {                                                       
            
            if (stmt != null) {                                            
                try {                                                         
                    stmt.close();                                                
                } catch (SQLException sqlex) {                                
                    // ignore -- as we can't do anything about it here           
                }                                                             

                stmt = null;                                            
            }                                                        

            if (conn != null) {                                      
                try {                                                   
                    conn.close();                                          
                } catch (SQLException sqlex) {                          
                    // ignore -- as we can't do anything about it here     
                }                                                       

                conn = null;                                            
            }                                                        
        }              

        return customerList;

    }  

    //update the customer information
    public boolean updateCustomer(String storeId, Customer customer) { 

        boolean success = true;

        try {      
            Context ctx = (Context) new InitialContext().lookup("java:comp/env");
            conn = ((DataSource) ctx.lookup("jdbc/mysql")).getConnection(); 

            sql = "UPDATE customer set first_name = ?, last_name = ?, email = ?, active = ?" +
            " where store_id = ? and customer_id = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1,customer.getFirstName()); 
            stmt.setString(2,customer.getLastName());
            stmt.setString(3,customer.getEmail());
            stmt.setInt(4,customer.isActive()? 1: 0);
            stmt.setInt(5,Integer.parseInt(storeId)); 
            stmt.setInt(6,customer.getCustomerId());

            stmt.executeUpdate();

            stmt.close();                                                             
            stmt = null;                                                              
            conn.close();                                                             
            conn = null;                                                   

        }                                                               
        catch(Exception e){

            success = false;
            System.out.println(e);

        }                      

        finally {                                                       
            
            if (stmt != null) {                                            
                try {                                                         
                    stmt.close();                                                
                } catch (SQLException sqlex) {                                
                    // ignore -- as we can't do anything about it here           
                }                                                             

                stmt = null;                                            
            }                                                        

            if (conn != null) {                                      
                try {                                                   
                    conn.close();                                          
                } catch (SQLException sqlex) {                          
                    // ignore -- as we can't do anything about it here     
                }                                                       

                conn = null;                                            
            }                                                        
        }              

        return success;

    }  

    //this method gets us the Total Number of Items that we have in our database
    public int getTotalCount(String storeId) { 

        int totalCount = 0; 

        try {      
            Context ctx = (Context) new InitialContext().lookup("java:comp/env");
            conn = ((DataSource) ctx.lookup("jdbc/mysql")).getConnection(); 

            sql = "Select count(*) from customer where store_id = ?";                      

            stmt = conn.prepareStatement(sql);
            stmt.setInt(1,Integer.parseInt(storeId)); 

            ResultSet rs = stmt.executeQuery(); 
            while(rs.next()){ 
                totalCount = rs.getInt(1);
                break;
            }                                                                         

            rs.close();                                                               
            stmt.close();                                                             
            stmt = null;                                                              


            conn.close();                                                             
            conn = null;                                                   

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

        finally {                                                       
            
            if (stmt != null) {                                            
                try {                                                         
                    stmt.close();                                                
                } catch (SQLException sqlex) {                                
                    // ignore -- as we can't do anything about it here           
                }                                                             

                stmt = null;                                            
            }                                                        

            if (conn != null) {                                      
                try {                                                   
                    conn.close();                                          
                } catch (SQLException sqlex) {                          
                    // ignore -- as we can't do anything about it here     
                }                                                       

                conn = null;                                            
            }                                                        
        }              

        return totalCount;

    }  

    //insert new customer information
    public String insertCustomer(String storeId, Customer customer) { 

        String customerId = "";
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String todaysDate = dateFormat.format(System.currentTimeMillis());
       
        try {      
            Context ctx = (Context) new InitialContext().lookup("java:comp/env");
            conn = ((DataSource) ctx.lookup("jdbc/mysql")).getConnection(); 

            sql = "INSERT into customer " +
            "(customer_id,first_name,last_name,email,active,store_id,address_id,create_date,last_update)" +
            " values(null,?,?,?,?,?,?,?,?)";
            stmt = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            stmt.setString(1,customer.getFirstName()); 
            stmt.setString(2,customer.getLastName());
            stmt.setString(3,customer.getEmail());
            stmt.setInt(4,customer.isActive()? 1: 0);
            stmt.setInt(5,Integer.parseInt(storeId)); 
            stmt.setInt(6,5);
            stmt.setString(7, todaysDate);
            stmt.setString(8, todaysDate);

            stmt.executeUpdate();

            // Using the getGeneratedKeys() method to retrieve
            // the key(s). In this case there is only one key column
            ResultSet keyResultSet = stmt.getGeneratedKeys();
            int newCustomerId = 0;
            if (keyResultSet.next()) {
                newCustomerId = (int) keyResultSet.getInt(1);
                customerId = String.valueOf(newCustomerId);
            }

            stmt.close();                                                             
            stmt = null;                                                              
            conn.close();                                                             
            conn = null;                                                   

        }                                                               
        catch(Exception e){

            System.out.println(e);

        }                      

        finally {                                                       
            
            if (stmt != null) {                                            
                try {                                                         
                    stmt.close();                                                
                } catch (SQLException sqlex) {                                
                    // ignore -- as we can't do anything about it here           
                }                                                             

                stmt = null;                                            
            }                                                        

            if (conn != null) {                                      
                try {                                                   
                    conn.close();                                          
                } catch (SQLException sqlex) {                          
                    // ignore -- as we can't do anything about it here     
                }                                                       

                conn = null;                                            
            }                                                        
        }              

        return customerId;

    }  
}   

Java Servlet to process ExtJs Requests and send JSON object in response CustomerServlet.java

package com.as400samplecode;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.ArrayList;

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

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.JSONSerializer;

import com.as400samplecode.util.Customer;
import com.as400samplecode.util.CustomerInformation;

public class CustomerServlet extends HttpServlet {

    private static final long serialVersionUID = 1L;
    private String storeId;

    public CustomerServlet() {
        super();
    }

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

        //get the store Id from the request
        storeId = request.getParameter("store_id");

        //get out Grid paging parameters
        String start = request.getParameter("start");
        String limit = request.getParameter("limit");

        //printwriter to send the JSON response back
        PrintWriter out = response.getWriter();
        //set content type
        response.setContentType("text/html");

        //create a new JSON array to send the list of items
        JSONArray arrayObj=new JSONArray();

        //get arraylist of customers based on the request 
        CustomerInformation customerInformation = new CustomerInformation();
        ArrayList<Customer> customerList = customerInformation.getCustomers(storeId, start, limit);

        //loop thru the array list to populate the JSON array
        for(int i=0;i<customerList.size();i++){

            //get customer Object
            Customer customer = customerList.get(i);
            //this creates a JSON object from bean object
            JSONObject customerObj = JSONObject.fromObject(customer);
            //add to array list
            arrayObj.add(customerObj);
        }

        //Create a JSON object to wrap your JSOn array and provide the root element items
        JSONObject myObj = new JSONObject();
        //sets success to true
        myObj.put("success", true);
        //set the JSON root to items
        myObj.put("customers", arrayObj);
        //set the total number of Items
        myObj.put("totalCount", customerInformation.getTotalCount(storeId));

        //convert the JSON object to string and send the response back
        out.println(myObj.toString());
        out.close();
    }

    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        //get the store Id from the request
        storeId = request.getParameter("store_id");
        //get the request type update or insert
        String action = request.getParameter("action");

        //if update
        if(action.trim().equalsIgnoreCase("update")){
            updateCustomer(request, response);
        }
        //if insert
        else if(action.trim().equalsIgnoreCase("insert")){
            insertCustomer(request, response);
        }

    }

    private void updateCustomer(HttpServletRequest request, HttpServletResponse response) throws IOException{

        //get the record Information 
        String recordInfo = request.getParameter("recordInfo");

        //parse JSON object and populate the Customer bean 
        JSONObject customerObj = (JSONObject) JSONSerializer.toJSON(recordInfo); 
        Customer customer = (Customer) JSONObject.toBean(customerObj, Customer.class);

        CustomerInformation customerInformation = new CustomerInformation();
        boolean success = customerInformation.updateCustomer(storeId,customer);

        PrintWriter out = response.getWriter();
        response.setContentType("text/html");

        //send response back whether the request was successful
        JSONObject myObj = new JSONObject();
        myObj.put("success", success);
        out.println(myObj.toString());
        out.close();

    }

    private void insertCustomer(HttpServletRequest request, HttpServletResponse response) throws IOException{

        //get the record Information 
        String recordInfo = request.getParameter("recordInfo");

        //parse JSON object and populate the Customer bean 
        JSONObject customerObj = (JSONObject) JSONSerializer.toJSON(recordInfo); 
        Customer customer = (Customer) JSONObject.toBean(customerObj, Customer.class);

        CustomerInformation customerInformation = new CustomerInformation();
        String customerId = customerInformation.insertCustomer(storeId,customer);

        PrintWriter out = response.getWriter();
        response.setContentType("text/html");

        //send response back whether the request was successful
        JSONObject myObj = new JSONObject();
        if(customerId.isEmpty()){
            myObj.put("success", false);
        }
        else {
            myObj.put("success", true);
            myObj.put("customerId", customerId);
        }
        out.println(myObj.toString());
        out.close();

    }

}

No comments:

Post a Comment

NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.