Blog Archive

ExtJs 4 XML Tree view example - Part 2

Click here for previous Chapter
In the previous chapter we did all our ExtJs programming for displaying a Tree view from XML data received from our AJAX request made by the Tree store. Here we will implement the server side logic using Java Servlet that will send us the MySQL data in an XML format that will represent a Tree Node and its children.


Tree view using XML data from 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>ExtJs_XML_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 XML representation of Country Tree CountryXMLTree.java

package com.as400samplecode.util;

public class CountryXMLTree {
   
    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<CountryXMLTree> getContinentTree(String node) { 

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

        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()){ 
                CountryXMLTree countryTree = new CountryXMLTree();
                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<CountryXMLTree> getRegionTree(String node, String continent) { 

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

        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()){ 
                CountryXMLTree countryTree = new CountryXMLTree();
                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<CountryXMLTree> getCountryTree(String node, String continent, String region) { 

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

        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()){ 
                CountryXMLTree countryTree = new CountryXMLTree();
                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 XML 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 com.as400samplecode.util.CountryXMLTree;
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");
        response.setContentType("text/xml");
        response.setHeader("Cache-control", "no-cache, no-store");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Expires", "-1");

       
        CountryInformation countryInformation = new CountryInformation();
        ArrayList<CountryXMLTree> countryList = new ArrayList<CountryXMLTree>();
       
        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);
        }
       
        out.println("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
        out.println("<CountryData>");

        for(int i=0;i<countryList.size();i++){

            CountryXMLTree country = countryList.get(i);
            out.println("<Record>");
            out.println("<id>" + country.getId() + "</id>");
            out.println("<text>" + country.getText() + "</text>");
            out.println("<leaf>" + country.isLeaf() + "</leaf>");
            out.println("<cls>" + country.getCls() + "</cls>");
            out.println("</Record>");

        }

        out.println("</CountryData>");
        out.close();

    }
   
}