Blog Archive

ExtJs 4 Tree Panel sample code - Java Servlet, JSON and MySQL Part 2

Click here for previous Chapter
In previous chapters we have defined our ExtJs sample application for Continent Tree. In this chapter we are going to define our Web application that will process the Ajax request made by the Tree store and send JSON response.


ExtJs 4 Tree Panel example using Java Servlet JSON response 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>ExtJs_Tree</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 Tree CountryTree.java

package com.as400samplecode.util;

public class CountryTree {
   
    String id = null;
    String text = null;
    boolean leaf = false;
    String cls = null;
   
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getText() {
        return text;
    }
    public void setText(String text) {
        this.text = text;
    }
    public boolean isLeaf() {
        return leaf;
    }
    public void setLeaf(boolean leaf) {
        this.leaf = leaf;
    }
    public String getCls() {
        return cls;
    }
    public void setCls(String cls) {
        this.cls = cls;
    }
   
}    

Java Web Application utility functions to process the Tree 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<CountryTree> getContinentTree(String node) { 

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

        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"; 
            stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery(); 
           
            while(rs.next()){ 
                CountryTree countryTree = new CountryTree();
                countryTree.setId(node + "/" + rs.getString(1).trim());
                countryTree.setText(rs.getString(1).trim());
                countryTree.setLeaf(false);
                countryTree.setCls("folder");
                countryList.add(countryTree);
            }                                                                         

            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<CountryTree> getRegionTree(String node, String continent) { 

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

        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 = ?"; 
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, continent.trim());
            ResultSet rs = stmt.executeQuery(); 
           
            while(rs.next()){ 
                CountryTree countryTree = new CountryTree();
                countryTree.setId(node + "/" + rs.getString(1).trim());
                countryTree.setText(rs.getString(1).trim());
                countryTree.setLeaf(false);
                countryTree.setCls("folder");
                countryList.add(countryTree);
            }                                                                         

            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<CountryTree> getCountryTree(String node, String continent, String region) { 

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

        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 = ?"; 
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, continent.trim());
            stmt.setString(2, region.trim());
            ResultSet rs = stmt.executeQuery(); 
           
            while(rs.next()){ 
                CountryTree countryTree = new CountryTree();
                countryTree.setId(node + "/" + rs.getString("code").trim());
                countryTree.setText(rs.getString("name").trim());
                countryTree.setLeaf(true);
                countryTree.setCls("file");
                countryList.add(countryTree);
            }                                                                         

            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.CountryTree;
import com.as400samplecode.util.CountryInformation;

public class CountryServlet extends HttpServlet {
   
    private static final long serialVersionUID = 1L;
    private String node;
   
    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 {

        node = request.getParameter("node").trim();
       
        String delims = "[/]";
        String[] tokens = node.split(delims);
       
       
        PrintWriter out = response.getWriter();
        response.setContentType("text/html");
       
        JSONArray arrayObj = new JSONArray();
        CountryInformation countryInformation = new CountryInformation();
        ArrayList<CountryTree> countryList = new ArrayList<CountryTree>();
       
        if(tokens.length == 1){
            countryList = countryInformation.getContinentTree(node);
        }
        else if(tokens.length == 2){
            String continent = tokens[1];
            countryList = countryInformation.getRegionTree(node, continent);
        }
        else if(tokens.length == 3){
            String continent = tokens[1];
            String region = tokens[2];
            countryList = countryInformation.getCountryTree(node, continent, region);
        }
       
        for(int i=0;i<countryList.size();i++){
            CountryTree country = countryList.get(i);
            JSONObject itemObj = JSONObject.fromObject(country);
            arrayObj.add(itemObj);
        }
       
        out.println(arrayObj.toString());
        out.close();

    }
   
}

Recommended Reading