Blog Archive

ExtJs 4 cascading ComboBox example using Java Servlet and MySQL - Part 3

Click here for previous Chapter
In previous chapters we have defined our ExtJs sample application for cascading ComboBoxes. In this chapter we are going to define our Web application that will process the JSON request made by the ComboBox stores.


ExtJs 4 cascading ComboBox example using Java Servlet

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_Cascading_ComboBoxes</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>Country Servlet</description>
    <display-name>CountryServlet</display-name>
    <servlet-name>CountryServlet</servlet-name>
    <servlet-class>com.as400samplecode.CountryServlet</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>CountryServlet</servlet-name>
    <url-pattern>/CountryServlet</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/world"
username="root"
password="mysql"
maxIdle="10"
maxActive="200"
maxWait="5"
removeAbandoned="true"
removeAbandonedTimeout="1200"
/>
</Context>

Java Bean for Country file Country.java

package com.as400samplecode.util;

public class Country {
   
    String code = null;
    String name = null;
    String continent = null;
    String region = null;
    Double lifeExpectancy = null;
    Double gnp = null;
   
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getContinent() {
        return continent;
    }
    public void setContinent(String continent) {
        this.continent = continent;
    }
    public String getRegion() {
        return region;
    }
    public void setRegion(String region) {
        this.region = region;
    }
    public Double getLifeExpectancy() {
        return lifeExpectancy;
    }
    public void setLifeExpectancy(Double lifeExpectancy) {
        this.lifeExpectancy = lifeExpectancy;
    }
    public Double getGnp() {
        return gnp;
    }
    public void setGnp(Double gnp) {
        this.gnp = gnp;
    }

   
}    

Java Web Application utility functions to process the ComboBox store Ajax Request CountryInformation.java

package com.as400samplecode.util;

import java.sql.Connection;         
import java.sql.ResultSet;          
import java.sql.SQLException;       
import java.sql.PreparedStatement;  
import java.util.ArrayList;

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

public class CountryInformation {           

    Connection conn = null;            
    PreparedStatement stmt = null;     
    String sql = null;
   
    public ArrayList<Country> getContinents(String start, String limit) { 

        ArrayList<Country> countryList = new ArrayList<Country>();   

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

            sql = "Select distinct(continent) as c from COUNTRY order by concat(c) LIMIT ?,?"; 
            stmt = conn.prepareStatement(sql);
            stmt.setInt(1, Integer.parseInt(start));
            stmt.setInt(2, Integer.parseInt(limit));
            ResultSet rs = stmt.executeQuery(); 
           
            while(rs.next()){ 
                Country country = new Country();
                country.setContinent(rs.getString(1).trim());
                countryList.add(country);
            }                                                                         

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

    }  

    public ArrayList<Country> getRegions(String start, String limit, String continent) { 

        ArrayList<Country> countryList = new ArrayList<Country>();   

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

            sql = "Select distinct(region) as r from COUNTRY where continent = ? order by r LIMIT ?,?"; 
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, continent.trim());
            stmt.setInt(2, Integer.parseInt(start));
            stmt.setInt(3, Integer.parseInt(limit));
            ResultSet rs = stmt.executeQuery(); 
           
            while(rs.next()){ 
                Country country = new Country();
                country.setRegion(rs.getString(1).trim());
                countryList.add(country);
            }                                                                         

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

    }  

    public ArrayList<Country> getCountries(String start, String limit, String continent, String region) { 

        ArrayList<Country> countryList = new ArrayList<Country>();   

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

            sql = "Select * from COUNTRY where continent = ? and region = ? order by name,code LIMIT ?,?"; 
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, continent.trim());
            stmt.setString(2, region.trim());
            stmt.setInt(3, Integer.parseInt(start));
            stmt.setInt(4, Integer.parseInt(limit));
            ResultSet rs = stmt.executeQuery(); 
           
            while(rs.next()){ 
                Country country = new Country();
                country.setCode(rs.getString("code").trim());
                country.setName(rs.getString("name").trim());
                country.setContinent(rs.getString("continent").trim());
                country.setRegion(rs.getString("region").trim());
                country.setLifeExpectancy(rs.getString("lifeExpectancy") == null ? new Double(0) : Double.parseDouble(rs.getString("lifeExpectancy").trim()));
                country.setGnp(rs.getString("gnp") == null ? new Double(0)  : Double.parseDouble(rs.getString("gnp").trim()));
                countryList.add(country);
            }                                                                         

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

    }  


}   

Java Servlet to process ExtJs Requests and send JSON object in response CountryServlet.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 com.as400samplecode.util.Country;
import com.as400samplecode.util.CountryInformation;

public class CountryServlet extends HttpServlet {
   
    private static final long serialVersionUID = 1L;
    private String action;
    private String start;
    private String limit;
    private String continent;
    private String region;
    private PrintWriter out;
   
    public CountryServlet() {
        super();
    }

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

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

        action = request.getParameter("action");
        start = request.getParameter("start");
        limit = request.getParameter("limit");
        continent = request.getParameter("continent");
        region = request.getParameter("region");
       
        out = response.getWriter();
        response.setContentType("text/html");
       
        JSONObject myObj = new JSONObject();
        if(action.trim().equalsIgnoreCase("getContinents")){
            myObj = getContinents();
        }
        if(action.trim().equalsIgnoreCase("getRegions")){
            myObj = getRegions();
        }   
        if(action.trim().equalsIgnoreCase("getCountries")){
            myObj = getCountries();
        }   
       
        out.println(myObj.toString());
        out.close();

    }

    //Get JSON object for list of continents
    private JSONObject getContinents() throws IOException{

        JSONArray arrayObj=new JSONArray();

        CountryInformation countryInformation = new CountryInformation();
        ArrayList<Country> countryList = countryInformation.getContinents(start, limit);
        for(int i=0;i<countryList.size();i++){
            Country country = countryList.get(i);
            JSONObject itemObj = JSONObject.fromObject(country);
            arrayObj.add(itemObj);
        }

        JSONObject myObj = new JSONObject();
        myObj.put("success", true);
        myObj.put("continents", arrayObj);
       
        return myObj;
       
    }   

    //Get JSON object for list of regions
    private JSONObject getRegions() throws IOException{

        JSONArray arrayObj=new JSONArray();

        CountryInformation countryInformation = new CountryInformation();
        ArrayList<Country> countryList = countryInformation.getRegions(start, limit, continent);
        for(int i=0;i<countryList.size();i++){
            Country country = countryList.get(i);
            JSONObject itemObj = JSONObject.fromObject(country);
            arrayObj.add(itemObj);
        }

        JSONObject myObj = new JSONObject();
        myObj.put("success", true);
        myObj.put("regions", arrayObj);
       
        return myObj;
       
    }   

    //Get JSON object for list of countries
    private JSONObject getCountries() throws IOException{

        JSONArray arrayObj=new JSONArray();

        CountryInformation countryInformation = new CountryInformation();
        ArrayList<Country> countryList = countryInformation.getCountries(start, limit, continent, region);
        for(int i=0;i<countryList.size();i++){
            Country country = countryList.get(i);
            JSONObject itemObj = JSONObject.fromObject(country);
            arrayObj.add(itemObj);
        }

        JSONObject myObj = new JSONObject();
        myObj.put("success", true);
        myObj.put("countries", arrayObj);
       
        return myObj;
       
    }   
}