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.
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
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`)
)
No comments:
Post a Comment
NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.