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


8 comments :

  1. what is the name of columns in the table COUNTRY????

    ReplyDelete
    Replies
    1. CREATE TABLE `country` (
      `Code` char(3) NOT NULL DEFAULT '',
      `Name` char(52) NOT NULL DEFAULT '',
      `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
      `Region` char(26) NOT NULL DEFAULT '',
      `SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
      `IndepYear` smallint(6) DEFAULT NULL,
      `Population` int(11) NOT NULL DEFAULT '0',
      `LifeExpectancy` float(3,1) DEFAULT NULL,
      `GNP` float(10,2) DEFAULT NULL,
      `GNPOld` float(10,2) DEFAULT NULL,
      `LocalName` char(45) NOT NULL DEFAULT '',
      `GovernmentForm` char(45) NOT NULL DEFAULT '',
      `HeadOfState` char(60) DEFAULT NULL,
      `Capital` int(11) DEFAULT NULL,
      `Code2` char(2) NOT NULL DEFAULT '',
      PRIMARY KEY (`Code`)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1$$

      Delete
  2. very nice tutorial but I am new to java and sencha and have tried running this over ecllipes could and there is some missing configuration ... can you please upload the project folder in rar or zip format so that i can download it....

    ReplyDelete
  3. This is a very good tutorial for new user of ExtJS tree panel with Serverlet, learnt a lot from this serials.

    ReplyDelete
  4. please share code and database dump

    ReplyDelete
  5. please share the corresponding js files.

    ReplyDelete
  6. can you please give the insert statement extract of the above example?

    ReplyDelete