ExtJs Grid JSON Java Servlet example with Grid Filter using TriggerField

5:19 PM 1 Comment

In the previous chapter we have looked into the sample source codes for creating the grid panel using ExtJs, here we are going to work on the Java Servlet program that will get us the data from MySQL database in the form of a JSON object.


ExtJs Grid JSON Java Servlet example with Grid Filter using TriggerField

Step 7: MySQL JDBC data source 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>

Step 8: 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_Grid_Filter</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>

Step 9: Source for Country Java Bean 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;

    }

} 

Step 10: Source for Country Utility program to get MySQL data 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> getItems(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 * from COUNTRY order by name,code";                   

            stmt = conn.prepareStatement(sql);

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

    public int getTotalCount() {
        int totalCount = 0;

        try {   

            Context ctx = (Context) new InitialContext().lookup("java:comp/env");

            conn = ((DataSource) ctx.lookup("jdbc/mysql")).getConnection();

            sql = "Select count(*) from COUNTRY";                   

            stmt = conn.prepareStatement(sql);

            ResultSet rs = stmt.executeQuery();

            while(rs.next()){

                totalCount = rs.getInt(1);

                break;

            }                                                                      

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

    }
}

Step 11: Source for Country Servlet program to process request and pass JSON object 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;

    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 {

        String start = request.getParameter("start");

        String limit = request.getParameter("limit");

        PrintWriter out = response.getWriter();

        response.setContentType("text/html");

        JSONArray arrayObj=new JSONArray();

        CountryInformation countryInformation = new CountryInformation();

        ArrayList<Country> countryList = countryInformation.getItems(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("countries", arrayObj);

        myObj.put("totalCount", countryInformation.getTotalCount());

        out.println(myObj.toString());

        out.close();

    }

}