Blog Archive

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();

    }

}

11 comments :

  1. Hi,
    I am new here, just want to start learning EXT JS, can you please put the WAR file for this example and the sql to create the correcsponding table ?
    thanks, your help is appreciated.

    ReplyDelete
  2. Hi, will it be possible to share the project source, please.

    thanks
    kumar

    ReplyDelete
  3. Project not available for download at this time.

    ReplyDelete
    Replies
    1. Now it 2014 almost 2 yrs went can you share project zip ... Please

      Delete
  4. đồng tâm
    game mu
    cho thuê nhà trọ
    cho thuê phòng trọ
    nhac san cuc manh
    số điện thoại tư vấn pháp luật miễn phí
    văn phòng luật
    tổng đài tư vấn pháp luật
    dịch vụ thành lập công ty trọn gói

    Mạnh Siêu Nhiên hít một hơi thật sau, lồng ngực phập phồng. Đột nhiên trên mặt hắn lộ ra vẻ giận dữ, quay đầu lại nhìn thẳng phía phòng của Thạch Thiên Sơn.

    - Để mình con đi thôi.
    Sở Dương khẽ nói.

    - Con? Con định xử lý thế nào?
    Mạnh Siêu Nhiên nói, sắc mặt rất khó coi.

    - Chung quy cứ để cho hắn thỏa mãn là được.
    Sở Dương nói thản nhiên.

    Ánh mắt Mạnh Siêu Nhiên yên lặng rủ xuống, cười nhẹ nói:
    - Vậy thì con đi đi.

    Hắn dừng lại một chút, lại nói tiếp:
    - Đừng nói gì quá đáng quá nhé, cho hắn một cơ hội để giải thích. Có lẽ cũng không phải là hắn ... cũng chưa biết chừng.

    Tiếng nói rất nhẹ, rất bé, cảm giác thật đắng chát cùng mệt mỏi.

    Dù sao Thạch Thiên Sơn cũng là người đồ đệ hắn đã bỏ bao công dạy dỗ bảy, tám năm nay! Sở Dương nguyện thay hắn xử lý chuyện này, cũng chính là vì hắn hiểu và thông cảm cho cám giác của Thạch Thiên Sơn.

    ReplyDelete
  5. We are offering website service or website design and much more....


    Voip Telephone Systems

    ReplyDelete