Blog Archive

ExtJs 4 Tree Grid example with column headers, checkboxes and menu options using Java Servlet, JSON and MySQL - Part 3

Click here for previous Chapter
In the previous chapters we have completed our client side programming of Multi column Tree panel using ExtJs, now its time to do the necessary back-end programming. Here we are going to create a Java Object that will represent a Tree Node, the utility functions that will fetch, update, insert and delete data from MySQL database and in the end we need to define our Java Servlet that will talk to front end application.


ExtJs 4 Tree Grid with checkboxes and menu options using Java Servlet, JSON 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>ExtJsTreeColumns</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 Object representation of the Country Tree - CountryTree.java

package com.as400samplecode.util;

public class CountryTree {
   
    String id = null;
    String text = null;
    boolean leaf = false;
    String iconCls = null;
    Double surfaceArea = null;
    Double population = null;
    Double lifeExpectancy = null;
    Double gnp = null;
    String capital = null;
    boolean checked = false;
    String code = 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 getIconCls() {
        return iconCls;
    }
    public void setIconCls(String iconCls) {
        this.iconCls = iconCls;
    }
    public Double getSurfaceArea() {
        return surfaceArea;
    }
    public void setSurfaceArea(Double surfaceArea) {
        this.surfaceArea = surfaceArea;
    }
    public Double getPopulation() {
        return population;
    }
    public void setPopulation(Double population) {
        this.population = population;
    }
    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;
    }
    public String getCapital() {
        return capital;
    }
    public void setCapital(String capital) {
        this.capital = capital;
    }
    public boolean isChecked() {
        return checked;
    }
    public void setChecked(boolean checked) {
        this.checked = checked;
    }
    public String getCode() {
        return code;
    }
    public void setCode(String code) {
        this.code = code;
    }
   
}    

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;
   
    //get list of continents
    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, sum(population) as p, sum(surfaceArea) as s" +
                    " FROM country group by continent"; 
            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.setPopulation(rs.getDouble(2));
                countryTree.setSurfaceArea(rs.getDouble(3));
                countryTree.setLeaf(false);
                countryTree.setIconCls("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;

    }  
   
    //get list of regions for a given continent
    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, sum(population) as p, sum(surfaceArea) as s" +
            " FROM country where continent = ? group by continent,region"; 
   
            //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.setPopulation(rs.getDouble(2));
                countryTree.setSurfaceArea(rs.getDouble(3));
                countryTree.setLeaf(false);
                countryTree.setIconCls("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;

    } 
   
    //get list of countries for a given continent and region
    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 as c1 inner join CITY as c2 on c1.capital = c2.id " +
                    "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.setPopulation(rs.getDouble("population"));
                countryTree.setSurfaceArea(rs.getDouble("surfaceArea"));
                countryTree.setLifeExpectancy(rs.getDouble("lifeExpectancy"));
                countryTree.setGnp(rs.getDouble("gnp"));
                countryTree.setCapital(rs.getString("c2.name"));
                countryTree.setLeaf(true);
                countryTree.setIconCls("file");
                countryTree.setCode(rs.getString("code").trim());
                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;

    }  
   
    //update the country information
    public boolean updateCountry(CountryTree country) { 

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

            //you may notice that capital is not updated here as that is in a 
            //separate table, you have to get the capital id from the country table 
            //and then go and update the capital
            sql = "UPDATE country set name = ?, population = ?, surfaceArea = ?, " +
                    "lifeExpectancy = ?, gnp = ? where code = ?";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1,country.getText()); 
            stmt.setDouble(2,country.getPopulation());
            stmt.setDouble(3,country.getSurfaceArea());
            stmt.setDouble(4,country.getLifeExpectancy());
            stmt.setDouble(5,country.getGnp());
           
            stmt.setString(6,country.getCode());

            stmt.executeUpdate();
            success = true;
           
            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 success;

    }  
   
}   


Java Servlet to process Tree panel Requests and respond with JSON objects - CountryServlet.java


ExtJs 4 Tree Grid with checkboxes and menu options using Java Servlet, JSON and MySQL
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;
import com.google.gson.Gson;

public class CountryServlet extends HttpServlet {

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

        out = response.getWriter();
        response.setContentType("text/html");
        boolean success = false;

        //check if action parameter exists
        if(request.getParameter("action") != null){
            //if the request is to delete a country
            if(request.getParameter("action").trim().equalsIgnoreCase("delete")){
                String data = request.getParameter("myData").trim();
                String delims = "[/]";
                String[] tokens = data.split(delims);
                String continent = tokens[1];
                String region = tokens[2];
                String code = tokens[3];
                System.out.println("Continent: " + continent + " Region: " + region + " Country: " + code);
                //the following in not coded refer to edit section as an example
                //success = countryInformation.deleteCountry(country,continent,region);
            }
            else {
                //get form data
                String myData = request.getParameter("myData").trim();
                //convert form data to a Java Object using Gson
                Gson gson = new Gson();
                CountryTree country = gson.fromJson(myData, CountryTree.class);
                CountryInformation countryInformation = new CountryInformation();
                //if the request is to edit the country
                if(request.getParameter("action").trim().equalsIgnoreCase("edit")){
                    success = countryInformation.updateCountry(country);
                }
                //if the request is to add a country
                else if(request.getParameter("action").trim().equalsIgnoreCase("add")){
                    String parentNodeId = request.getParameter("parentNodeId").trim();
                    String delims = "[/]";
                    String[] tokens = parentNodeId.split(delims);
                    String continent = tokens[1];
                    String region = tokens[2];
                    System.out.println("Continent: " + continent + " Region: " + region);
                    //the following in not coded but you the idea from the edit
                    //success = countryInformation.addCountry(country,continent,region);
                }
            }
            JSONObject myObj = new JSONObject();
            myObj.put("success", success);
            out.println(myObj.toString());
            out.close();
        }
        //otherwise just get tree nodes
        else {
            node = request.getParameter("node").trim();
            getTreeData(node);
        }

    }

    //get tree nodes
    private void getTreeData(String node){

        String delims = "[/]";
        String[] tokens = node.split(delims);

        JSONArray arrayObj = new JSONArray();
        CountryInformation countryInformation = new CountryInformation();
        ArrayList<CountryTree> countryList = new ArrayList<CountryTree>();

        //get continents
        if(tokens.length == 1){
            countryList = countryInformation.getContinentTree(node);
        }
        //get regions
        else if(tokens.length == 2){
            String continent = tokens[1];
            countryList = countryInformation.getRegionTree(node, continent);
        }
        //get countries
        else if(tokens.length == 3){
            String continent = tokens[1];
            String region = tokens[2];
            countryList = countryInformation.getCountryTree(node, continent, region);
        }

        //go thru the CountryTree array list and create a JSON array
        for(int i=0;i<countryList.size();i++){
            CountryTree country = countryList.get(i);
            JSONObject itemObj = JSONObject.fromObject(country);
            arrayObj.add(itemObj);
        }

        //send JSON array as string
        out.println(arrayObj.toString());
        out.close();

    }

}

Country Table DB script

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`)
)