Posts Tagged ‘extjs

24
Jul
11

Live Search Functionality using ExtJS

This page describes the process of developing a live search page Extjs and Java Servlets. This is similar to the live search / type ahead functionality offered by google. This page contains instructions on how to create a complete working example in about a 1/2 hour using jetty servlet engine and the Java HyperSQL in-memory database.

Requirements

  • Maven 2
  • Java 5 or above

Background

Type ahead functionality allows users to obtain a list of possible options as they type. Chances that the item the user is looking for increases with each keystroke.

A static working example is located here.

Live search results are typically displayed in combo boxes. A combo boxes are a mix between a drop down box and a text field. The ext-js framework enhances the basic HTML drop down to allow for type ahead functionality.

Simple Database Driven Search

Next we will take the example presented above and enhance it to retrieve results from a servlet invocation. The servlet will call the database to return results.

Smarter Database Driven Search

The data returned after you start to type may sometimes overwhelm the user. A better way to return results is to display them based on ranking. Each word in our sample table also has a rank column. We will start to use this to get smarter search results.

Pagination

The example implemented here uses pagination. The user is able to page thru search results. This capability is enabled by sending back “totalCount” value that represents the total search results available from the database. The “start” and “limit” parameters passed to the servlet help the system “window” the results so that only the required rows get sent to the browser.

In the example below, in order to get the “totalCount” I had to execute the query 2 times. Once to get the count and the other time to get the actual data.

Not all databases support “start” and “limit” fields. Check with your database vendor for availability.

Implementation

The project described here is composed of:

  1. index.jsp that displays the extjs component
  2. Servlet that does the search against the database and return JSON
  3. HSQLDB (HyperSQL) In memory database to hold the data
  4. Spring Framework so that we can use the SimpleJdbcTemplate

Start a new Project

We will be creating this project using Maven2. If you have not used maven before please see my maven2 tutorial on the right nav of this site.

mvn archetype:generate -DarchetypeArtifactId=maven-archetype-webapp

groupId: com.test
artifactId: live-search

Answer the rest of the questions with defaults “Just hit the enter key”,

cd to the project base folder.

cd live-search

Create the necessary directories.

mkdir -p src/main/java/com/test
mkdir -p src/main/resources/com/test
mkdir -p src/main/resources/db

Modify the project Configuration

The pom.xml file should look something like this.

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.test</groupId>
  <artifactId>live-search</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>live-search Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
        <dependency>
            <groupId>org.json</groupId>
            <artifactId>json</artifactId>
            <version>20090211</version>
        </dependency>
        <dependency>
            <groupId>org.apache.ibatis</groupId>
            <artifactId>ibatis-sqlmap</artifactId>
            <version>2.3.4.726</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring</artifactId>
            <version>2.5.6</version>
        </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
    </dependency>        
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.14</version>
        </dependency>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>2.2.4</version>
        </dependency>
        <dependency>
            <groupId>org.apache.ddlutils</groupId>
            <artifactId>ddlutils</artifactId>
            <version>1.0</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.0</version>
            <scope>provided</scope>
        </dependency>
  </dependencies>
  <build>
    <finalName>live-search</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.0.2</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.mortbay.jetty</groupId>
                <artifactId>jetty-maven-plugin</artifactId>
				<version>7.0.0.v20091005</version>
                <configuration>
                    <scanIntervalSeconds>2</scanIntervalSeconds>
                    <webAppConfig>
                        <contextPath>/</contextPath>
                    </webAppConfig>
                    <stopKey>s</stopKey>
                </configuration>
            </plugin>
        </plugins>
  </build>
</project>

Each key press initiates an Ajax request to the server. The server sends back a small list of possible options.

Servlet

The following servlet runs queries against the database and returns results to the caller. In a standard web application you would follow the MVC design pattern. To keep the example short I have done it all in the servlet.

src/main/java/com/test/SearchServlet.java

package com.test;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.context.ApplicationContext;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.web.context.support.WebApplicationContextUtils;

public class SearchServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private SimpleJdbcTemplate jdbcTemplate;

	@Override
	public void init(ServletConfig config) throws ServletException {
		super.init(config);
		ApplicationContext context = WebApplicationContextUtils
			.getRequiredWebApplicationContext(config.getServletContext());
		
		jdbcTemplate = (SimpleJdbcTemplate) context.getBean("jdbcTemplate");		
	}
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {

		String query = req.getParameter("query");		
		if(query==null || "".equals(query.trim())) return;		
		int offset = 0, limit = 0;
		
		try {
			offset = Integer.parseInt(req.getParameter("start"));
		} catch (Exception ex) {}
		try {
			limit = Integer.parseInt(req.getParameter("limit"));
		} catch (Exception ex) {}

		if(limit <=0 ) { limit = 20; }
		
		// get the overall total count
		final List<JSONObject> resultList = new ArrayList<JSONObject>();
		jdbcTemplate.<JSONObject> query(
				"SELECT distinct word_x FROM word_lemmatised " +
				"WHERE word_x like ? order by word_x limit ? offset ?;",
				new ParameterizedRowMapper<JSONObject>() {
					public JSONObject mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						JSONObject jsonWord = new JSONObject();
						try {
							jsonWord.put("word_x", rs.getString(1));
							resultList.add(jsonWord);
						} catch (JSONException e) {}
						return jsonWord;
					}
				}, query+"%", limit, offset);
		
		int totalCount = jdbcTemplate.queryForInt(
				"SELECT count(distinct word_x) FROM word_lemmatised "
						+ "WHERE word_x like ?", query + "%");
		boolean success = true;
		
		
		JSONObject response = new JSONObject();		
		try {
			response.put("success", success);
			response.put("totalCount", totalCount);
			response.put("records", resultList);			
		} catch (JSONException e) {}
		resp.getWriter().print(response.toString());
	}

	public void setJdbcTemplate(SimpleJdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	public SimpleJdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}
}

src/main/webapp/WEB-INF/web.xml

<!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
	<display-name>Archetype Created Web Application</display-name>

	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>classpath:applicationContext.xml</param-value>
	</context-param>

	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>
    <listener>
        <listener-class>com.test.DBLifecycleContextListener</listener-class>
    </listener>
    
	<servlet>
		<servlet-name>searchServlet</servlet-name>
		<servlet-class>com.test.SearchServlet</servlet-class>
	</servlet>

	<servlet-mapping>
		<servlet-name>searchServlet</servlet-name>
		<url-pattern>/app/searchServlet</url-pattern>
	</servlet-mapping>
</web-app>

Infrastructure classes

The following classes and configuration files support the application.

src/main/java/com/test/DBLifecycleContextListener.java

package com.test;
 
import java.io.InputStreamReader;
 
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;
 
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.io.DatabaseIO;
import org.apache.ddlutils.model.Database;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;
 
/**
 * This listener initializes or gracefully shuts down the database based on
 * events from the web application.
 */
public class DBLifecycleContextListener implements ServletContextListener {
    private DataSource dataSource = null;
    private WebApplicationContext springContext = null;
 
    public void contextDestroyed(ServletContextEvent event) {
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(dataSource);
        template.update("SHUTDOWN;");
        System.out.println("HSQLDB was shutdown. ");
    }
 
    public void contextInitialized(ServletContextEvent event) {
        springContext = WebApplicationContextUtils
                .getWebApplicationContext(event.getServletContext());
        dataSource = (DataSource) springContext.getBean("dataSource");
 
        Platform platform = PlatformFactory
                .createNewPlatformInstance(dataSource);
 
        Database database = new DatabaseIO().read(new InputStreamReader(
                getClass().getResourceAsStream("/ddl.xml")));
 
        System.out.println(database);
 
        platform.alterTables(database, false);
 
        System.out.println("HSQLDB is ready: " + platform);
 
    }
 
}

HyperSQL in memory database Setup

The following defines the schema that will be used to create the in-memory tables in the Java HyperSQL database.

src/main/resources/ddl.xml

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="liveSearch">
  <table name="word_lemmatised">
    <column name="word_x"
            type="VARCHAR"
            primaryKey="true"
            size="255"
            required="true"/>
    <column name="wordClass"
            type="VARCHAR"
            primaryKey="true"
            size="10"
            required="true"/>
    <column name="frequency"
            type="INTEGER"
            required="true"/>
    <column name="sortOrder"
            type="INTEGER"
            required="true"/>            

    <index name="word_lemmatised_frequency">
	<index-column name="frequency"/>
    </index>
    <index name="word_lemmatised_sortOrder">
	<index-column name="sortOrder"/>
    </index>

  </table>
 
</database>

The following data is the word list used to populate the in-memory table. This table will be queried by the servlet so it can return json back to the extjs component.

Copy the contents of this file into:

src/main/resources/db/liveSearch.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE WORD_LEMMATISED(WORD_X VARCHAR(255) NOT NULL,WORDCLASS VARCHAR(10) NOT NULL,FREQUENCY INTEGER NOT NULL,SORTORDER INTEGER NOT NULL,PRIMARY KEY(WORD_X,WORDCLASS))
CREATE INDEX WORD_LEMMATISED_FREQUENCY ON WORD_LEMMATISED(FREQUENCY)
CREATE INDEX WORD_LEMMATISED_SORTORDER ON WORD_LEMMATISED(SORTORDER)
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO WORD_LEMMATISED VALUES('a','det',2186369,5)
INSERT INTO WORD_LEMMATISED VALUES('abandon','v',4249,2107)
INSERT INTO WORD_LEMMATISED VALUES('abbey','n',1110,5204)
INSERT INTO WORD_LEMMATISED VALUES('ability','n',10468,966)
INSERT INTO WORD_LEMMATISED VALUES('able','a',30454,321)
INSERT INTO WORD_LEMMATISED VALUES('abnormal','a',809,6277)
INSERT INTO WORD_LEMMATISED VALUES('abolish','v',1744,3862)
INSERT INTO WORD_LEMMATISED VALUES('abolition','n',1154,5085)
INSERT INTO WORD_LEMMATISED VALUES('abortion','n',1471,4341)
INSERT INTO WORD_LEMMATISED VALUES('about','adv',52561,179)
INSERT INTO WORD_LEMMATISED VALUES('about','prep',144554,69)
INSERT INTO WORD_LEMMATISED VALUES('above','a',2139,3341)
INSERT INTO WORD_LEMMATISED VALUES('abroad','adv',3941,2236)
INSERT INTO WORD_LEMMATISED VALUES('abruptly','adv',1146,5106)
INSERT INTO WORD_LEMMATISED VALUES('absence','n',5949,1602)
INSERT INTO WORD_LEMMATISED VALUES('absent','a',1504,4266)
INSERT INTO WORD_LEMMATISED VALUES('absolute','a',3489,2435)
INSERT INTO WORD_LEMMATISED VALUES('absolutely','adv',5782,1651)
INSERT INTO WORD_LEMMATISED VALUES('absorb','v',2684,2907)
INSERT INTO WORD_LEMMATISED VALUES('absorption','n',932,5769)
INSERT INTO WORD_LEMMATISED VALUES('abstract','a',1605,4083)
INSERT INTO WORD_LEMMATISED VALUES('absurd','a',966,5655)
INSERT INTO WORD_LEMMATISED VALUES('abuse','n',3428,2461)
INSERT INTO WORD_LEMMATISED VALUES('abuse','v',1364,4570)
INSERT INTO WORD_LEMMATISED VALUES('academic','a',4594,1974)
INSERT INTO WORD_LEMMATISED VALUES('academy','n',1025,5453)
INSERT INTO WORD_LEMMATISED VALUES('accelerate','v',1114,5188)
INSERT INTO WORD_LEMMATISED VALUES('accent','n',1842,3707)
INSERT INTO WORD_LEMMATISED VALUES('accept','v',20373,507)
INSERT INTO WORD_LEMMATISED VALUES('acceptable','a',3647,2351)
INSERT INTO WORD_LEMMATISED VALUES('acceptance','n',2702,2895)
INSERT INTO WORD_LEMMATISED VALUES('access','n',10099,997)
INSERT INTO WORD_LEMMATISED VALUES('access','v',1389,4516)
INSERT INTO WORD_LEMMATISED VALUES('accessible','a',1637,4024)
INSERT INTO WORD_LEMMATISED VALUES('accident','n',8374,1207)
INSERT INTO WORD_LEMMATISED VALUES('accommodate','v',2065,3418)
INSERT INTO WORD_LEMMATISED VALUES('accommodation','n',4305,2085)
INSERT INTO WORD_LEMMATISED VALUES('accompany','v',4885,1892)
INSERT INTO WORD_LEMMATISED VALUES('accomplish','v',968,5648)
INSERT INTO WORD_LEMMATISED VALUES('accord','n',947,5723)
INSERT INTO WORD_LEMMATISED VALUES('accord','v',976,5613)
INSERT INTO WORD_LEMMATISED VALUES('accordance','n',2042,3443)
INSERT INTO WORD_LEMMATISED VALUES('according','prep',15722,646)
INSERT INTO WORD_LEMMATISED VALUES('accordingly','adv',2288,3214)
INSERT INTO WORD_LEMMATISED VALUES('account','n',19260,536)
INSERT INTO WORD_LEMMATISED VALUES('account','v',6130,1562)
INSERT INTO WORD_LEMMATISED VALUES('accountability','n',1184,5011)
INSERT INTO WORD_LEMMATISED VALUES('accountant','n',2199,3289)
INSERT INTO WORD_LEMMATISED VALUES('accounting','a',1640,4018)
INSERT INTO WORD_LEMMATISED VALUES('accumulate','v',1084,5279)
INSERT INTO WORD_LEMMATISED VALUES('accumulation','n',987,5570)
INSERT INTO WORD_LEMMATISED VALUES('accuracy','n',1692,3934)
INSERT INTO WORD_LEMMATISED VALUES('accurate','a',2928,2743)
INSERT INTO WORD_LEMMATISED VALUES('accurately','adv',1423,4436)
INSERT INTO WORD_LEMMATISED VALUES('accusation','n',1153,5088)
INSERT INTO WORD_LEMMATISED VALUES('accuse','v',4047,2190)
INSERT INTO WORD_LEMMATISED VALUES('accused','a',1387,4521)
INSERT INTO WORD_LEMMATISED VALUES('achieve','v',16628,611)
INSERT INTO WORD_LEMMATISED VALUES('achievement','n',4586,1980)
INSERT INTO WORD_LEMMATISED VALUES('acid','n',5897,1613)
INSERT INTO WORD_LEMMATISED VALUES('acknowledge','v',4151,2139)
INSERT INTO WORD_LEMMATISED VALUES('acquaintance','n',920,5809)
INSERT INTO WORD_LEMMATISED VALUES('acquire','v',6354,1512)
INSERT INTO WORD_LEMMATISED VALUES('acquisition','n',3245,2546)
INSERT INTO WORD_LEMMATISED VALUES('acre','n',2150,3327)
INSERT INTO WORD_LEMMATISED VALUES('across','adv',3439,2457)
INSERT INTO WORD_LEMMATISED VALUES('across','prep',21763,474)
INSERT INTO WORD_LEMMATISED VALUES('act','n',22657,455)
INSERT INTO WORD_LEMMATISED VALUES('act','v',15620,654)
INSERT INTO WORD_LEMMATISED VALUES('action','n',26894,371)
INSERT INTO WORD_LEMMATISED VALUES('activate','v',1293,4708)
INSERT INTO WORD_LEMMATISED VALUES('active','a',7290,1332)
INSERT INTO WORD_LEMMATISED VALUES('actively','adv',1493,4294)
INSERT INTO WORD_LEMMATISED VALUES('activist','n',1285,4736)
INSERT INTO WORD_LEMMATISED VALUES('activity','n',23105,440)
INSERT INTO WORD_LEMMATISED VALUES('actor','n',3540,2413)
INSERT INTO WORD_LEMMATISED VALUES('actress','n',1051,5376)
INSERT INTO WORD_LEMMATISED VALUES('actual','a',6849,1414)
INSERT INTO WORD_LEMMATISED VALUES('actually','adv',25990,385)
INSERT INTO WORD_LEMMATISED VALUES('acute','a',2294,3205)
INSERT INTO WORD_LEMMATISED VALUES('adapt','v',2687,2906)
INSERT INTO WORD_LEMMATISED VALUES('adaptation','n',1132,5153)
INSERT INTO WORD_LEMMATISED VALUES('add','v',27367,363)
INSERT INTO WORD_LEMMATISED VALUES('added','a',1334,4624)
INSERT INTO WORD_LEMMATISED VALUES('addition','n',10664,949)
INSERT INTO WORD_LEMMATISED VALUES('additional','a',7364,1319)
INSERT INTO WORD_LEMMATISED VALUES('address','n',6112,1566)
INSERT INTO WORD_LEMMATISED VALUES('address','v',5872,1625)
INSERT INTO WORD_LEMMATISED VALUES('adequate','a',3571,2397)
INSERT INTO WORD_LEMMATISED VALUES('adequately','adv',1149,5097)
INSERT INTO WORD_LEMMATISED VALUES('adjacent','a',1624,4047)
INSERT INTO WORD_LEMMATISED VALUES('adjective','n',1012,5496)
INSERT INTO WORD_LEMMATISED VALUES('adjust','v',2697,2898)
INSERT INTO WORD_LEMMATISED VALUES('adjustment','n',2114,3370)
INSERT INTO WORD_LEMMATISED VALUES('administer','v',1795,3769)
INSERT INTO WORD_LEMMATISED VALUES('administration','n',6865,1409)
INSERT INTO WORD_LEMMATISED VALUES('administrative','a',3548,2407)
INSERT INTO WORD_LEMMATISED VALUES('administrator','n',1483,4314)
INSERT INTO WORD_LEMMATISED VALUES('admiration','n',966,5654)
INSERT INTO WORD_LEMMATISED VALUES('admire','v',2168,3312)
INSERT INTO WORD_LEMMATISED VALUES('admission','n',2742,2861)
INSERT INTO WORD_LEMMATISED VALUES('admit','v',10905,925)
INSERT INTO WORD_LEMMATISED VALUES('adopt','v',8549,1186)
INSERT INTO WORD_LEMMATISED VALUES('adoption','n',1544,4191)
INSERT INTO WORD_LEMMATISED VALUES('adult','a',1547,4182)
INSERT INTO WORD_LEMMATISED VALUES('adult','n',6855,1411)
INSERT INTO WORD_LEMMATISED VALUES('advance','n',5055,1840)
INSERT INTO WORD_LEMMATISED VALUES('advance','v',3032,2683)
INSERT INTO WORD_LEMMATISED VALUES('advanced','a',3183,2583)
INSERT INTO WORD_LEMMATISED VALUES('advantage','n',10285,981)
INSERT INTO WORD_LEMMATISED VALUES('adventure','n',1993,3491)
INSERT INTO WORD_LEMMATISED VALUES('adverse','a',1184,5010)
INSERT INTO WORD_LEMMATISED VALUES('advertise','v',2099,3386)
INSERT INTO WORD_LEMMATISED VALUES('advertisement','n',2136,3344)
INSERT INTO WORD_LEMMATISED VALUES('advertising','n',3582,2392)
INSERT INTO WORD_LEMMATISED VALUES('advice','n',10437,971)
INSERT INTO WORD_LEMMATISED VALUES('advise','v',5055,1839)
INSERT INTO WORD_LEMMATISED VALUES('adviser','n',3191,2580)
INSERT INTO WORD_LEMMATISED VALUES('advisory','a',1500,4277)
INSERT INTO WORD_LEMMATISED VALUES('advocate','n',911,5848)
INSERT INTO WORD_LEMMATISED VALUES('advocate','v',1636,4029)
INSERT INTO WORD_LEMMATISED VALUES('aesthetic','a',1027,5444)
INSERT INTO WORD_LEMMATISED VALUES('affair','n',10561,954)
INSERT INTO WORD_LEMMATISED VALUES('affect','v',12867,789)
INSERT INTO WORD_LEMMATISED VALUES('affection','n',1601,4092)
INSERT INTO WORD_LEMMATISED VALUES('affinity','n',847,6101)
INSERT INTO WORD_LEMMATISED VALUES('afford','v',5346,1769)
INSERT INTO WORD_LEMMATISED VALUES('afraid','a',5967,1596)
INSERT INTO WORD_LEMMATISED VALUES('after','conj',30855,312)
INSERT INTO WORD_LEMMATISED VALUES('after','prep',85939,111)
INSERT INTO WORD_LEMMATISED VALUES('afternoon','n',8934,1128)
INSERT INTO WORD_LEMMATISED VALUES('afterwards','adv',4544,1991)
INSERT INTO WORD_LEMMATISED VALUES('again','adv',59829,160)
INSERT INTO WORD_LEMMATISED VALUES('against','prep',56208,169)
INSERT INTO WORD_LEMMATISED VALUES('age','n',25340,399)
INSERT INTO WORD_LEMMATISED VALUES('age','v',1565,4142)
INSERT INTO WORD_LEMMATISED VALUES('aged','prep',3096,2641)
INSERT INTO WORD_LEMMATISED VALUES('agency','n',9579,1054)
INSERT INTO WORD_LEMMATISED VALUES('agenda','n',2427,3069)
INSERT INTO WORD_LEMMATISED VALUES('agent','n',8100,1233)
INSERT INTO WORD_LEMMATISED VALUES('aggregate','a',1078,5294)
INSERT INTO WORD_LEMMATISED VALUES('aggression','n',1243,4845)
INSERT INTO WORD_LEMMATISED VALUES('aggressive','a',1925,3581)
INSERT INTO WORD_LEMMATISED VALUES('ago','adv',19808,522)
INSERT INTO WORD_LEMMATISED VALUES('agony','n',1017,5475)
INSERT INTO WORD_LEMMATISED VALUES('agree','v',23497,428)
INSERT INTO WORD_LEMMATISED VALUES('agreed','a',1106,5218)
INSERT INTO WORD_LEMMATISED VALUES('agreement','n',15627,651)
INSERT INTO WORD_LEMMATISED VALUES('agricultural','a',3162,2593)
INSERT INTO WORD_LEMMATISED VALUES('agriculture','n',3858,2265)
INSERT INTO WORD_LEMMATISED VALUES('ahead','adv',8809,1145)
INSERT INTO WORD_LEMMATISED VALUES('aid','n',7903,1249)
INSERT INTO WORD_LEMMATISED VALUES('aid','v',2086,3398)
INSERT INTO WORD_LEMMATISED VALUES('aids','n',2463,3042)
INSERT INTO WORD_LEMMATISED VALUES('aim','n',7460,1306)
INSERT INTO WORD_LEMMATISED VALUES('aim','v',7638,1281)
INSERT INTO WORD_LEMMATISED VALUES('air','n',19046,544)
INSERT INTO WORD_LEMMATISED VALUES('aircraft','n',6200,1548)
INSERT INTO WORD_LEMMATISED VALUES('airline','n',1982,3500)
INSERT INTO WORD_LEMMATISED VALUES('airport','n',2756,2855)
INSERT INTO WORD_LEMMATISED VALUES('alarm','n',2322,3169)
INSERT INTO WORD_LEMMATISED VALUES('alarm','v',1222,4914)
INSERT INTO WORD_LEMMATISED VALUES('albeit','conj',1406,4477)
INSERT INTO WORD_LEMMATISED VALUES('album','n',2581,2957)
INSERT INTO WORD_LEMMATISED VALUES('alcohol','n',3066,2662)
INSERT INTO WORD_LEMMATISED VALUES('alert','a',810,6273)
INSERT INTO WORD_LEMMATISED VALUES('alert','v',1125,5170)
INSERT INTO WORD_LEMMATISED VALUES('alien','a',868,6003)
INSERT INTO WORD_LEMMATISED VALUES('alike','adv',1270,4773)
INSERT INTO WORD_LEMMATISED VALUES('alive','a',4254,2103)
INSERT INTO WORD_LEMMATISED VALUES('all','adv',55704,171)
INSERT INTO WORD_LEMMATISED VALUES('all','det',230737,43)
INSERT INTO WORD_LEMMATISED VALUES('allegation','n',2124,3360)
INSERT INTO WORD_LEMMATISED VALUES('allege','v',1820,3731)
INSERT INTO WORD_LEMMATISED VALUES('alleged','a',1728,3887)
INSERT INTO WORD_LEMMATISED VALUES('allegedly','adv',1041,5398)
INSERT INTO WORD_LEMMATISED VALUES('alliance','n',3518,2422)
INSERT INTO WORD_LEMMATISED VALUES('allied','a',972,5630)
INSERT INTO WORD_LEMMATISED VALUES('allocate','v',2445,3060)
INSERT INTO WORD_LEMMATISED VALUES('allocation','n',2305,3193)
INSERT INTO WORD_LEMMATISED VALUES('allow','v',33687,279)
INSERT INTO WORD_LEMMATISED VALUES('allowance','n',3411,2472)
INSERT INTO WORD_LEMMATISED VALUES('ally','n',2381,3105)
INSERT INTO WORD_LEMMATISED VALUES('almost','adv',31588,300)
INSERT INTO WORD_LEMMATISED VALUES('alone','a',7088,1365)
INSERT INTO WORD_LEMMATISED VALUES('alone','adv',6262,1528)
INSERT INTO WORD_LEMMATISED VALUES('along','adv',8081,1235)
INSERT INTO WORD_LEMMATISED VALUES('along','prep',16233,627)
INSERT INTO WORD_LEMMATISED VALUES('alongside','prep',2720,2883)
INSERT INTO WORD_LEMMATISED VALUES('aloud','adv',999,5533)
INSERT INTO WORD_LEMMATISED VALUES('already','adv',34292,275)
INSERT INTO WORD_LEMMATISED VALUES('alright','a',3592,2386)
INSERT INTO WORD_LEMMATISED VALUES('alright','adv',4777,1926)
INSERT INTO WORD_LEMMATISED VALUES('also','adv',124884,81)
INSERT INTO WORD_LEMMATISED VALUES('altar','n',969,5643)
INSERT INTO WORD_LEMMATISED VALUES('alter','v',4061,2183)
INSERT INTO WORD_LEMMATISED VALUES('alteration','n',1527,4226)
INSERT INTO WORD_LEMMATISED VALUES('alternative','a',5098,1828)
INSERT INTO WORD_LEMMATISED VALUES('alternative','n',5228,1797)
INSERT INTO WORD_LEMMATISED VALUES('alternatively','adv',1723,3894)
INSERT INTO WORD_LEMMATISED VALUES('although','conj',43635,215)
INSERT INTO WORD_LEMMATISED VALUES('altogether','adv',3218,2558)
INSERT INTO WORD_LEMMATISED VALUES('aluminium','n',995,5546)
INSERT INTO WORD_LEMMATISED VALUES('always','adv',46228,201)
INSERT INTO WORD_LEMMATISED VALUES('amateur','a',1025,5452)
INSERT INTO WORD_LEMMATISED VALUES('amateur','n',900,5879)
INSERT INTO WORD_LEMMATISED VALUES('amazing','a',1873,3658)
INSERT INTO WORD_LEMMATISED VALUES('ambassador','n',1264,4788)
INSERT INTO WORD_LEMMATISED VALUES('ambiguity','n',1058,5345)
INSERT INTO WORD_LEMMATISED VALUES('ambiguous','a',837,6146)
INSERT INTO WORD_LEMMATISED VALUES('ambition','n',2319,3177)
INSERT INTO WORD_LEMMATISED VALUES('ambitious','a',1526,4228)
INSERT INTO WORD_LEMMATISED VALUES('ambulance','n',1690,3936)
INSERT INTO WORD_LEMMATISED VALUES('amend','v',1386,4525)
INSERT INTO WORD_LEMMATISED VALUES('amendment','n',2574,2960)
INSERT INTO WORD_LEMMATISED VALUES('amid','prep',1096,5243)
INSERT INTO WORD_LEMMATISED VALUES('among','prep',22864,450)
INSERT INTO WORD_LEMMATISED VALUES('amongst','prep',4552,1989)
INSERT INTO WORD_LEMMATISED VALUES('amount','n',16138,629)
INSERT INTO WORD_LEMMATISED VALUES('amount','v',3633,2358)
INSERT INTO WORD_LEMMATISED VALUES('amp','n',878,5960)
INSERT INTO WORD_LEMMATISED VALUES('ample','a',822,6211)
INSERT INTO WORD_LEMMATISED VALUES('amuse','v',1093,5255)
INSERT INTO WORD_LEMMATISED VALUES('amusement','n',1027,5443)
INSERT INTO WORD_LEMMATISED VALUES('an','det',343063,33)
INSERT INTO WORD_LEMMATISED VALUES('analogy','n',1098,5238)
INSERT INTO WORD_LEMMATISED VALUES('analyse','v',4106,2166)
INSERT INTO WORD_LEMMATISED VALUES('analysis','n',14149,732)
INSERT INTO WORD_LEMMATISED VALUES('analyst','n',1586,4109)
INSERT INTO WORD_LEMMATISED VALUES('ancestor','n',1249,4821)
INSERT INTO WORD_LEMMATISED VALUES('ancient','a',4981,1860)
INSERT INTO WORD_LEMMATISED VALUES('and','conj',2687863,4)
INSERT INTO WORD_LEMMATISED VALUES('angel','n',1897,3616)
INSERT INTO WORD_LEMMATISED VALUES('anger','n',3291,2530)
INSERT INTO WORD_LEMMATISED VALUES('anger','v',892,5902)
INSERT INTO WORD_LEMMATISED VALUES('angle','n',3644,2352)
INSERT INTO WORD_LEMMATISED VALUES('angrily','adv',1093,5254)
INSERT INTO WORD_LEMMATISED VALUES('angry','a',4282,2095)
INSERT INTO WORD_LEMMATISED VALUES('animal','n',15250,671)
INSERT INTO WORD_LEMMATISED VALUES('ankle','n',1363,4573)
INSERT INTO WORD_LEMMATISED VALUES('anniversary','n',2144,3332)
INSERT INTO WORD_LEMMATISED VALUES('announce','v',12582,815)
INSERT INTO WORD_LEMMATISED VALUES('announcement','n',2758,2852)
INSERT INTO WORD_LEMMATISED VALUES('annoy','v',1116,5186)
INSERT INTO WORD_LEMMATISED VALUES('annual','a',7785,1265)
INSERT INTO WORD_LEMMATISED VALUES('annually','adv',1092,5258)
INSERT INTO WORD_LEMMATISED VALUES('anonymous','a',1112,5194)
INSERT INTO WORD_LEMMATISED VALUES('another','det',60182,159)
INSERT INTO WORD_LEMMATISED VALUES('answer','n',12596,810)
INSERT INTO WORD_LEMMATISED VALUES('answer','v',10140,989)
INSERT INTO WORD_LEMMATISED VALUES('ant','n',998,5539)
INSERT INTO WORD_LEMMATISED VALUES('antibody','n',1528,4221)
INSERT INTO WORD_LEMMATISED VALUES('anticipate','v',2316,3184)
INSERT INTO WORD_LEMMATISED VALUES('anticipation','n',835,6155)
INSERT INTO WORD_LEMMATISED VALUES('anxiety','n',3129,2616)
INSERT INTO WORD_LEMMATISED VALUES('anxious','a',3088,2645)
INSERT INTO WORD_LEMMATISED VALUES('any','det',123655,83)
INSERT INTO WORD_LEMMATISED VALUES('anybody','pron',4952,1872)
INSERT INTO WORD_LEMMATISED VALUES('anyone','pron',14956,686)
INSERT INTO WORD_LEMMATISED VALUES('anything','pron',28321,347)
INSERT INTO WORD_LEMMATISED VALUES('anyway','adv',12232,837)
INSERT INTO WORD_LEMMATISED VALUES('anywhere','adv',4105,2167)
INSERT INTO WORD_LEMMATISED VALUES('apart','adv',10040,1004)
INSERT INTO WORD_LEMMATISED VALUES('apartment','n',1901,3612)
INSERT INTO WORD_LEMMATISED VALUES('apologise','v',1057,5353)
INSERT INTO WORD_LEMMATISED VALUES('apology','n',1195,4984)
INSERT INTO WORD_LEMMATISED VALUES('appalling','a',1027,5442)
INSERT INTO WORD_LEMMATISED VALUES('apparatus','n',1099,5236)
INSERT INTO WORD_LEMMATISED VALUES('apparent','a',5306,1782)
INSERT INTO WORD_LEMMATISED VALUES('apparently','adv',7696,1272)
INSERT INTO WORD_LEMMATISED VALUES('appeal','n',10020,1006)
INSERT INTO WORD_LEMMATISED VALUES('appeal','v',4764,1930)
INSERT INTO WORD_LEMMATISED VALUES('appear','v',30595,319)
INSERT INTO WORD_LEMMATISED VALUES('appearance','n',6606,1462)
INSERT INTO WORD_LEMMATISED VALUES('appendix','n',1884,3643)
INSERT INTO WORD_LEMMATISED VALUES('appetite','n',1055,5363)
INSERT INTO WORD_LEMMATISED VALUES('apple','n',2996,2704)
INSERT INTO WORD_LEMMATISED VALUES('applicable','a',1419,4443)
INSERT INTO WORD_LEMMATISED VALUES('applicant','n',2179,3305)
INSERT INTO WORD_LEMMATISED VALUES('application','n',16281,623)
INSERT INTO WORD_LEMMATISED VALUES('applied','a',1155,5081)
INSERT INTO WORD_LEMMATISED VALUES('apply','v',18982,547)
INSERT INTO WORD_LEMMATISED VALUES('appoint','v',6407,1500)
INSERT INTO WORD_LEMMATISED VALUES('appointed','a',1606,4080)
INSERT INTO WORD_LEMMATISED VALUES('appointment','n',5911,1610)
INSERT INTO WORD_LEMMATISED VALUES('appraisal','n',1092,5257)
INSERT INTO WORD_LEMMATISED VALUES('appreciate','v',4312,2082)
INSERT INTO WORD_LEMMATISED VALUES('appreciation','n',1329,4640)
INSERT INTO WORD_LEMMATISED VALUES('approach','n',16331,621)
INSERT INTO WORD_LEMMATISED VALUES('approach','v',7432,1310)
INSERT INTO WORD_LEMMATISED VALUES('appropriate','a',11010,917)
INSERT INTO WORD_LEMMATISED VALUES('appropriately','adv',891,5905)
INSERT INTO WORD_LEMMATISED VALUES('approval','n',4050,2187)
INSERT INTO WORD_LEMMATISED VALUES('approve','v',5312,1780)
INSERT INTO WORD_LEMMATISED VALUES('approved','a',911,5847)
INSERT INTO WORD_LEMMATISED VALUES('approximately','adv',2837,2798)
INSERT INTO WORD_LEMMATISED VALUES('aquarium','n',912,5843)
INSERT INTO WORD_LEMMATISED VALUES('arbitrary','a',1091,5262)
INSERT INTO WORD_LEMMATISED VALUES('arc','n',842,6125)
INSERT INTO WORD_LEMMATISED VALUES('arch','n',1248,4825)
INSERT INTO WORD_LEMMATISED VALUES('archaeological','a',826,6187)
INSERT INTO WORD_LEMMATISED VALUES('archbishop','n',1109,5206)
INSERT INTO WORD_LEMMATISED VALUES('architect','n',2572,2961)
INSERT INTO WORD_LEMMATISED VALUES('architectural','a',885,5929)
INSERT INTO WORD_LEMMATISED VALUES('architecture','n',3018,2688)
INSERT INTO WORD_LEMMATISED VALUES('archive','n',1124,5172)
INSERT INTO WORD_LEMMATISED VALUES('area','n',58449,162)
INSERT INTO WORD_LEMMATISED VALUES('arena','n',952,5704)
INSERT INTO WORD_LEMMATISED VALUES('argue','v',14196,728)
INSERT INTO WORD_LEMMATISED VALUES('argument','n',12125,843)
INSERT INTO WORD_LEMMATISED VALUES('arise','v',8860,1139)
INSERT INTO WORD_LEMMATISED VALUES('arm','n',20089,513)
INSERT INTO WORD_LEMMATISED VALUES('arm','v',1533,4214)
INSERT INTO WORD_LEMMATISED VALUES('armchair','n',886,5924)
INSERT INTO WORD_LEMMATISED VALUES('armed','a',3344,2505)
INSERT INTO WORD_LEMMATISED VALUES('army','n',12379,826)
INSERT INTO WORD_LEMMATISED VALUES('around','adv',23106,439)
INSERT INTO WORD_LEMMATISED VALUES('around','prep',22180,461)
INSERT INTO WORD_LEMMATISED VALUES('arouse','v',1308,4675)
INSERT INTO WORD_LEMMATISED VALUES('arrange','v',7021,1379)
INSERT INTO WORD_LEMMATISED VALUES('arrangement','n',9054,1113)
INSERT INTO WORD_LEMMATISED VALUES('array','n',1258,4803)
INSERT INTO WORD_LEMMATISED VALUES('arrest','n',2095,3391)
INSERT INTO WORD_LEMMATISED VALUES('arrest','v',4059,2185)
INSERT INTO WORD_LEMMATISED VALUES('arrival','n',3815,2278)
INSERT INTO WORD_LEMMATISED VALUES('arrive','v',14093,735)
INSERT INTO WORD_LEMMATISED VALUES('arrow','n',1621,4058)
INSERT INTO WORD_LEMMATISED VALUES('art','n',20168,509)
INSERT INTO WORD_LEMMATISED VALUES('article','n',9710,1037)
INSERT INTO WORD_LEMMATISED VALUES('articulate','v',821,6218)
INSERT INTO WORD_LEMMATISED VALUES('artificial','a',1901,3611)
INSERT INTO WORD_LEMMATISED VALUES('artist','n',8105,1232)
INSERT INTO WORD_LEMMATISED VALUES('artistic','a',1553,4170)
INSERT INTO WORD_LEMMATISED VALUES('as','adv',101583,98)
INSERT INTO WORD_LEMMATISED VALUES('as','conj',364164,31)
INSERT INTO WORD_LEMMATISED VALUES('as','prep',201968,48)
INSERT INTO WORD_LEMMATISED VALUES('ascertain','v',842,6124)
INSERT INTO WORD_LEMMATISED VALUES('ash','n',1051,5375)
INSERT INTO WORD_LEMMATISED VALUES('ashamed','a',1100,5233)
INSERT INTO WORD_LEMMATISED VALUES('aside','adv',3607,2375)
INSERT INTO WORD_LEMMATISED VALUES('ask','v',60879,154)
INSERT INTO WORD_LEMMATISED VALUES('asleep','a',2463,3041)
INSERT INTO WORD_LEMMATISED VALUES('aspect','n',11643,871)
INSERT INTO WORD_LEMMATISED VALUES('aspiration','n',1269,4777)
INSERT INTO WORD_LEMMATISED VALUES('assault','n',2395,3094)
INSERT INTO WORD_LEMMATISED VALUES('assault','v',919,5814)
INSERT INTO WORD_LEMMATISED VALUES('assemble','v',1621,4057)
INSERT INTO WORD_LEMMATISED VALUES('assembly','n',5847,1630)
INSERT INTO WORD_LEMMATISED VALUES('assert','v',2012,3477)
INSERT INTO WORD_LEMMATISED VALUES('assertion','n',1119,5180)
INSERT INTO WORD_LEMMATISED VALUES('assess','v',6088,1570)
INSERT INTO WORD_LEMMATISED VALUES('assessment','n',7602,1290)
INSERT INTO WORD_LEMMATISED VALUES('asset','n',5721,1663)
INSERT INTO WORD_LEMMATISED VALUES('assign','v',1778,3802)
INSERT INTO WORD_LEMMATISED VALUES('assignment','n',1707,3919)
INSERT INTO WORD_LEMMATISED VALUES('assist','v',4021,2202)
INSERT INTO WORD_LEMMATISED VALUES('assistance','n',4344,2064)
INSERT INTO WORD_LEMMATISED VALUES('assistant','a',1745,3860)
INSERT INTO WORD_LEMMATISED VALUES('assistant','n',2655,2921)
INSERT INTO WORD_LEMMATISED VALUES('associate','n',1047,5383)
INSERT INTO WORD_LEMMATISED VALUES('associate','v',8743,1156)
INSERT INTO WORD_LEMMATISED VALUES('associated','a',1447,4391)
INSERT INTO WORD_LEMMATISED VALUES('association','n',13471,757)
INSERT INTO WORD_LEMMATISED VALUES('assume','v',11044,912)
INSERT INTO WORD_LEMMATISED VALUES('assumption','n',5554,1705)
INSERT INTO WORD_LEMMATISED VALUES('assurance','n',2100,3384)
INSERT INTO WORD_LEMMATISED VALUES('assure','v',3116,2625)
INSERT INTO WORD_LEMMATISED VALUES('astonishing','a',848,6096)
INSERT INTO WORD_LEMMATISED VALUES('asylum','n',974,5620)
INSERT INTO WORD_LEMMATISED VALUES('at','prep',534162,19)
INSERT INTO WORD_LEMMATISED VALUES('athlete','n',812,6264)
INSERT INTO WORD_LEMMATISED VALUES('atmosphere','n',4902,1889)
INSERT INTO WORD_LEMMATISED VALUES('atom','n',1473,4331)
INSERT INTO WORD_LEMMATISED VALUES('atomic','a',1106,5217)
INSERT INTO WORD_LEMMATISED VALUES('attach','v',4821,1915)
INSERT INTO WORD_LEMMATISED VALUES('attachment','n',959,5677)
INSERT INTO WORD_LEMMATISED VALUES('attack','n',9963,1012)
INSERT INTO WORD_LEMMATISED VALUES('attack','v',6586,1464)
INSERT INTO WORD_LEMMATISED VALUES('attacker','n',919,5813)
INSERT INTO WORD_LEMMATISED VALUES('attain','v',1182,5016)
INSERT INTO WORD_LEMMATISED VALUES('attainment','n',948,5717)
INSERT INTO WORD_LEMMATISED VALUES('attempt','n',11877,857)
INSERT INTO WORD_LEMMATISED VALUES('attempt','v',9873,1022)
INSERT INTO WORD_LEMMATISED VALUES('attempted','a',849,6090)
INSERT INTO WORD_LEMMATISED VALUES('attend','v',8801,1148)
INSERT INTO WORD_LEMMATISED VALUES('attendance','n',1889,3633)
INSERT INTO WORD_LEMMATISED VALUES('attention','n',13968,740)
INSERT INTO WORD_LEMMATISED VALUES('attitude','n',10758,938)
INSERT INTO WORD_LEMMATISED VALUES('attract','v',6410,1498)
INSERT INTO WORD_LEMMATISED VALUES('attraction','n',2366,3124)
INSERT INTO WORD_LEMMATISED VALUES('attractive','a',5152,1814)
INSERT INTO WORD_LEMMATISED VALUES('attribute','n',1260,4798)
INSERT INTO WORD_LEMMATISED VALUES('attribute','v',2311,3187)
INSERT INTO WORD_LEMMATISED VALUES('auction','n',1466,4352)
INSERT INTO WORD_LEMMATISED VALUES('audience','n',6500,1480)
INSERT INTO WORD_LEMMATISED VALUES('audit','n',2136,3343)
INSERT INTO WORD_LEMMATISED VALUES('auditor','n',1679,3955)
INSERT INTO WORD_LEMMATISED VALUES('aunt','n',1536,4211)
INSERT INTO WORD_LEMMATISED VALUES('author','n',6852,1413)
INSERT INTO WORD_LEMMATISED VALUES('authorise','v',968,5647)
INSERT INTO WORD_LEMMATISED VALUES('authority','n',31231,303)
INSERT INTO WORD_LEMMATISED VALUES('automatic','a',2245,3242)
INSERT INTO WORD_LEMMATISED VALUES('automatically','adv',2787,2832)
INSERT INTO WORD_LEMMATISED VALUES('autonomous','a',1073,5305)
INSERT INTO WORD_LEMMATISED VALUES('autonomy','n',1788,3783)
INSERT INTO WORD_LEMMATISED VALUES('autumn','n',3917,2250)
INSERT INTO WORD_LEMMATISED VALUES('availability','n',1912,3602)
INSERT INTO WORD_LEMMATISED VALUES('available','a',27184,367)
INSERT INTO WORD_LEMMATISED VALUES('avenue','n',943,5736)
INSERT INTO WORD_LEMMATISED VALUES('average','a',5922,1607)
INSERT INTO WORD_LEMMATISED VALUES('average','n',4104,2168)
INSERT INTO WORD_LEMMATISED VALUES('aviation','n',925,5794)
INSERT INTO WORD_LEMMATISED VALUES('avoid','v',11750,866)
INSERT INTO WORD_LEMMATISED VALUES('await','v',1958,3536)
INSERT INTO WORD_LEMMATISED VALUES('awake','a',1040,5404)
INSERT INTO WORD_LEMMATISED VALUES('awake','v',825,6193)
INSERT INTO WORD_LEMMATISED VALUES('award','n',13274,764)
INSERT INTO WORD_LEMMATISED VALUES('award','v',6625,1458)
INSERT INTO WORD_LEMMATISED VALUES('aware','a',10764,936)
INSERT INTO WORD_LEMMATISED VALUES('awareness','n',3516,2424)
INSERT INTO WORD_LEMMATISED VALUES('away','adv',50294,188)
INSERT INTO WORD_LEMMATISED VALUES('awful','a',2960,2731)
INSERT INTO WORD_LEMMATISED VALUES('awkward','a',1489,4301)
INSERT INTO WORD_LEMMATISED VALUES('axis','n',1019,5469)
INSERT INTO WORD_LEMMATISED VALUES('aye','interjecti',5166,1811)
INSERT INTO WORD_LEMMATISED VALUES('baby','n',11503,883)
INSERT INTO WORD_LEMMATISED VALUES('back','a',2335,3160)
INSERT INTO WORD_LEMMATISED VALUES('back','adv',75494,118)
INSERT INTO WORD_LEMMATISED VALUES('back','n',24095,415)
INSERT INTO WORD_LEMMATISED VALUES('back','v',4391,2048)
INSERT INTO WORD_LEMMATISED VALUES('background','n',6923,1398)
INSERT INTO WORD_LEMMATISED VALUES('backing','n',1887,3636)
INSERT INTO WORD_LEMMATISED VALUES('backwards','adv',1802,3757)
INSERT INTO WORD_LEMMATISED VALUES('bacon','n',1037,5411)
INSERT INTO WORD_LEMMATISED VALUES('bacteria','n',1265,4786)
INSERT INTO WORD_LEMMATISED VALUES('bad','a',25608,393)
INSERT INTO WORD_LEMMATISED VALUES('badly','adv',4329,2072)
INSERT INTO WORD_LEMMATISED VALUES('bag','n',6955,1389)
INSERT INTO WORD_LEMMATISED VALUES('bail','n',811,6268)
INSERT INTO WORD_LEMMATISED VALUES('bake','v',930,5773)
INSERT INTO WORD_LEMMATISED VALUES('balance','n',8601,1179)
INSERT INTO WORD_LEMMATISED VALUES('balance','v',2361,3132)
INSERT INTO WORD_LEMMATISED VALUES('balanced','a',898,5884)
INSERT INTO WORD_LEMMATISED VALUES('balcony','n',1127,5165)
INSERT INTO WORD_LEMMATISED VALUES('ball','n',8636,1170)
INSERT INTO WORD_LEMMATISED VALUES('ballet','n',1340,4614)
INSERT INTO WORD_LEMMATISED VALUES('balloon','n',1030,5436)
INSERT INTO WORD_LEMMATISED VALUES('ballot','n',1055,5362)
INSERT INTO WORD_LEMMATISED VALUES('ban','n',2468,3037)
INSERT INTO WORD_LEMMATISED VALUES('ban','v',2851,2791)
INSERT INTO WORD_LEMMATISED VALUES('banana','n',924,5797)
INSERT INTO WORD_LEMMATISED VALUES('band','n',9003,1117)
INSERT INTO WORD_LEMMATISED VALUES('bang','n',980,5597)
INSERT INTO WORD_LEMMATISED VALUES('bang','v',1197,4976)
INSERT INTO WORD_LEMMATISED VALUES('bank','n',20946,491)
INSERT INTO WORD_LEMMATISED VALUES('banker','n',1348,4602)
INSERT INTO WORD_LEMMATISED VALUES('banking','n',1681,3950)
INSERT INTO WORD_LEMMATISED VALUES('bankruptcy','n',1066,5321)
INSERT INTO WORD_LEMMATISED VALUES('banner','n',857,6051)
INSERT INTO WORD_LEMMATISED VALUES('bar','n',9969,1011)
INSERT INTO WORD_LEMMATISED VALUES('bar','v',1210,4947)
INSERT INTO WORD_LEMMATISED VALUES('bare','a',2291,3211)
INSERT INTO WORD_LEMMATISED VALUES('barely','adv',2293,3206)
INSERT INTO WORD_LEMMATISED VALUES('bargain','n',1229,4886)
INSERT INTO WORD_LEMMATISED VALUES('bargain','v',893,5898)
INSERT INTO WORD_LEMMATISED VALUES('barn','n',1465,4356)
INSERT INTO WORD_LEMMATISED VALUES('barrel','n',1411,4464)
INSERT INTO WORD_LEMMATISED VALUES('barrier','n',3011,2691)
INSERT INTO WORD_LEMMATISED VALUES('base','n',9410,1070)
INSERT INTO WORD_LEMMATISED VALUES('base','v',18742,556)
INSERT INTO WORD_LEMMATISED VALUES('basement','n',852,6077)
INSERT INTO WORD_LEMMATISED VALUES('basic','a',10860,928)
INSERT INTO WORD_LEMMATISED VALUES('basically','adv',3116,2624)
INSERT INTO WORD_LEMMATISED VALUES('basin','n',1314,4661)
INSERT INTO WORD_LEMMATISED VALUES('basis','n',14420,715)
INSERT INTO WORD_LEMMATISED VALUES('basket','n',1658,3983)
INSERT INTO WORD_LEMMATISED VALUES('bass','n',1036,5416)
INSERT INTO WORD_LEMMATISED VALUES('bastard','n',1841,3709)
INSERT INTO WORD_LEMMATISED VALUES('bat','n',1249,4820)
INSERT INTO WORD_LEMMATISED VALUES('batch','n',977,5609)
INSERT INTO WORD_LEMMATISED VALUES('bath','n',3484,2439)
INSERT INTO WORD_LEMMATISED VALUES('bathroom','n',2792,2828)
INSERT INTO WORD_LEMMATISED VALUES('battery','n',2050,3433)
INSERT INTO WORD_LEMMATISED VALUES('battle','n',7063,1372)
INSERT INTO WORD_LEMMATISED VALUES('battle','v',983,5583)
INSERT INTO WORD_LEMMATISED VALUES('bay','n',2097,3387)
INSERT INTO WORD_LEMMATISED VALUES('be','v',4239632,2)
INSERT INTO WORD_LEMMATISED VALUES('beach','n',4125,2154)
INSERT INTO WORD_LEMMATISED VALUES('beam','n',1540,4205)
INSERT INTO WORD_LEMMATISED VALUES('bean','n',1767,3824)
INSERT INTO WORD_LEMMATISED VALUES('bear','n',1786,3788)
INSERT INTO WORD_LEMMATISED VALUES('bear','v',17461,592)
INSERT INTO WORD_LEMMATISED VALUES('beard','n',980,5596)
INSERT INTO WORD_LEMMATISED VALUES('bearing','n',1466,4351)
INSERT INTO WORD_LEMMATISED VALUES('beast','n',1232,4877)
INSERT INTO WORD_LEMMATISED VALUES('beat','n',1425,4430)
INSERT INTO WORD_LEMMATISED VALUES('beat','v',8106,1231)
INSERT INTO WORD_LEMMATISED VALUES('beautiful','a',8670,1162)
INSERT INTO WORD_LEMMATISED VALUES('beautifully','adv',1241,4849)
INSERT INTO WORD_LEMMATISED VALUES('beauty','n',4445,2027)
INSERT INTO WORD_LEMMATISED VALUES('because','conj',103003,96)
INSERT INTO WORD_LEMMATISED VALUES('become','v',67219,130)
INSERT INTO WORD_LEMMATISED VALUES('bed','n',17947,578)
INSERT INTO WORD_LEMMATISED VALUES('bedroom','n',5865,1626)
INSERT INTO WORD_LEMMATISED VALUES('bee','n',1237,4859)
INSERT INTO WORD_LEMMATISED VALUES('beef','n',1516,4243)
INSERT INTO WORD_LEMMATISED VALUES('beer','n',3762,2307)
INSERT INTO WORD_LEMMATISED VALUES('before','adv',6285,1522)
INSERT INTO WORD_LEMMATISED VALUES('before','conj',30731,314)
INSERT INTO WORD_LEMMATISED VALUES('before','prep',51259,185)
INSERT INTO WORD_LEMMATISED VALUES('beg','v',1885,3642)
INSERT INTO WORD_LEMMATISED VALUES('begin','v',43740,214)
INSERT INTO WORD_LEMMATISED VALUES('beginning','n',8018,1240)
INSERT INTO WORD_LEMMATISED VALUES('behalf','n',4012,2206)
INSERT INTO WORD_LEMMATISED VALUES('behave','v',3082,2650)
INSERT INTO WORD_LEMMATISED VALUES('behaviour','n',12853,793)
INSERT INTO WORD_LEMMATISED VALUES('behind','adv',2795,2823)
INSERT INTO WORD_LEMMATISED VALUES('behind','prep',20694,498)
INSERT INTO WORD_LEMMATISED VALUES('being','n',3732,2324)
INSERT INTO WORD_LEMMATISED VALUES('belief','n',7509,1302)
INSERT INTO WORD_LEMMATISED VALUES('believe','v',34603,273)
INSERT INTO WORD_LEMMATISED VALUES('bell','n',2758,2851)
INSERT INTO WORD_LEMMATISED VALUES('belly','n',959,5676)
INSERT INTO WORD_LEMMATISED VALUES('belong','v',5716,1664)
INSERT INTO WORD_LEMMATISED VALUES('below','adv',8587,1182)
INSERT INTO WORD_LEMMATISED VALUES('below','prep',5748,1656)
INSERT INTO WORD_LEMMATISED VALUES('belt','n',2328,3163)
INSERT INTO WORD_LEMMATISED VALUES('bench','n',2320,3173)
INSERT INTO WORD_LEMMATISED VALUES('bend','n',881,5946)
INSERT INTO WORD_LEMMATISED VALUES('bend','v',3453,2454)
INSERT INTO WORD_LEMMATISED VALUES('beneath','prep',4917,1883)
INSERT INTO WORD_LEMMATISED VALUES('beneficial','a',1375,4544)
INSERT INTO WORD_LEMMATISED VALUES('beneficiary','n',876,5973)
INSERT INTO WORD_LEMMATISED VALUES('benefit','n',15251,670)
INSERT INTO WORD_LEMMATISED VALUES('benefit','v',4262,2101)
INSERT INTO WORD_LEMMATISED VALUES('beside','prep',5785,1649)
INSERT INTO WORD_LEMMATISED VALUES('besides','adv',1765,3830)
INSERT INTO WORD_LEMMATISED VALUES('besides','prep',848,6095)
INSERT INTO WORD_LEMMATISED VALUES('best','adv',9790,1027)
INSERT INTO WORD_LEMMATISED VALUES('bet','n',950,5710)
INSERT INTO WORD_LEMMATISED VALUES('bet','v',2292,3209)
INSERT INTO WORD_LEMMATISED VALUES('betray','v',1310,4670)
INSERT INTO WORD_LEMMATISED VALUES('better','adv',15626,652)
INSERT INTO WORD_LEMMATISED VALUES('between','prep',91141,105)
INSERT INTO WORD_LEMMATISED VALUES('beyond','adv',1062,5333)
INSERT INTO WORD_LEMMATISED VALUES('beyond','prep',10705,945)
INSERT INTO WORD_LEMMATISED VALUES('bias','n',1372,4548)
INSERT INTO WORD_LEMMATISED VALUES('bible','n',2075,3408)
INSERT INTO WORD_LEMMATISED VALUES('bicycle','n',1134,5144)
INSERT INTO WORD_LEMMATISED VALUES('bid','n',3316,2521)
INSERT INTO WORD_LEMMATISED VALUES('bid','v',1756,3843)
INSERT INTO WORD_LEMMATISED VALUES('big','a',33300,282)
INSERT INTO WORD_LEMMATISED VALUES('bike','n',2224,3266)
INSERT INTO WORD_LEMMATISED VALUES('bile','n',1228,4892)
INSERT INTO WORD_LEMMATISED VALUES('bill','n',10125,993)
INSERT INTO WORD_LEMMATISED VALUES('bin','n',1040,5403)
INSERT INTO WORD_LEMMATISED VALUES('bind','v',6517,1472)
INSERT INTO WORD_LEMMATISED VALUES('binding','a',1246,4832)
INSERT INTO WORD_LEMMATISED VALUES('biography','n',988,5567)
INSERT INTO WORD_LEMMATISED VALUES('biological','a',1965,3523)
INSERT INTO WORD_LEMMATISED VALUES('biology','n',1029,5440)
INSERT INTO WORD_LEMMATISED VALUES('bird','n',9021,1115)
INSERT INTO WORD_LEMMATISED VALUES('birth','n',5889,1615)
INSERT INTO WORD_LEMMATISED VALUES('birthday','n',3352,2500)
INSERT INTO WORD_LEMMATISED VALUES('biscuit','n',1552,4172)
INSERT INTO WORD_LEMMATISED VALUES('bishop','n',3620,2366)
INSERT INTO WORD_LEMMATISED VALUES('bit','n',30675,315)
INSERT INTO WORD_LEMMATISED VALUES('bitch','n',1006,5510)
INSERT INTO WORD_LEMMATISED VALUES('bite','n',887,5920)
INSERT INTO WORD_LEMMATISED VALUES('bite','v',2885,2765)
INSERT INTO WORD_LEMMATISED VALUES('bitter','a',2384,3103)
INSERT INTO WORD_LEMMATISED VALUES('bitterly','adv',1097,5240)
INSERT INTO WORD_LEMMATISED VALUES('bizarre','a',1054,5369)
INSERT INTO WORD_LEMMATISED VALUES('black','a',20609,502)
INSERT INTO WORD_LEMMATISED VALUES('black','n',2310,3189)
INSERT INTO WORD_LEMMATISED VALUES('blade','n',1442,4400)
INSERT INTO WORD_LEMMATISED VALUES('blame','n',852,6076)
INSERT INTO WORD_LEMMATISED VALUES('blame','v',4248,2108)
INSERT INTO WORD_LEMMATISED VALUES('blank','a',1499,4279)
INSERT INTO WORD_LEMMATISED VALUES('blanket','n',1545,4184)
INSERT INTO WORD_LEMMATISED VALUES('blast','n',975,5616)
INSERT INTO WORD_LEMMATISED VALUES('blast','v',866,6009)
INSERT INTO WORD_LEMMATISED VALUES('bleak','a',916,5825)
INSERT INTO WORD_LEMMATISED VALUES('bleed','v',896,5889)
INSERT INTO WORD_LEMMATISED VALUES('bless','v',1228,4891)
INSERT INTO WORD_LEMMATISED VALUES('blessing','n',938,5754)
INSERT INTO WORD_LEMMATISED VALUES('blind','a',2674,2912)
INSERT INTO WORD_LEMMATISED VALUES('blink','v',896,5888)
INSERT INTO WORD_LEMMATISED VALUES('block','n',5705,1669)
INSERT INTO WORD_LEMMATISED VALUES('block','v',2688,2904)
INSERT INTO WORD_LEMMATISED VALUES('bloke','n',1597,4095)
INSERT INTO WORD_LEMMATISED VALUES('blonde','a',912,5842)
INSERT INTO WORD_LEMMATISED VALUES('blood','n',10176,986)
INSERT INTO WORD_LEMMATISED VALUES('bloody','a',5261,1791)
INSERT INTO WORD_LEMMATISED VALUES('bloody','adv',2016,3470)
INSERT INTO WORD_LEMMATISED VALUES('blow','n',2372,3118)
INSERT INTO WORD_LEMMATISED VALUES('blow','v',5013,1856)
INSERT INTO WORD_LEMMATISED VALUES('blue','a',9089,1109)
INSERT INTO WORD_LEMMATISED VALUES('blue','n',2455,3053)
INSERT INTO WORD_LEMMATISED VALUES('board','n',17878,581)
INSERT INTO WORD_LEMMATISED VALUES('board','v',850,6086)
INSERT INTO WORD_LEMMATISED VALUES('boast','v',1179,5026)
INSERT INTO WORD_LEMMATISED VALUES('boat','n',7373,1317)
INSERT INTO WORD_LEMMATISED VALUES('bodily','a',817,6240)
INSERT INTO WORD_LEMMATISED VALUES('body','n',32231,294)
INSERT INTO WORD_LEMMATISED VALUES('boil','v',1148,5101)
INSERT INTO WORD_LEMMATISED VALUES('boiler','n',878,5959)
INSERT INTO WORD_LEMMATISED VALUES('bold','a',1430,4419)
INSERT INTO WORD_LEMMATISED VALUES('bolt','n',1281,4748)
INSERT INTO WORD_LEMMATISED VALUES('bomb','n',3703,2333)
INSERT INTO WORD_LEMMATISED VALUES('bomb','v',1202,4960)
INSERT INTO WORD_LEMMATISED VALUES('bomber','n',1003,5524)
INSERT INTO WORD_LEMMATISED VALUES('bond','n',3753,2313)
INSERT INTO WORD_LEMMATISED VALUES('bone','n',4665,1955)
INSERT INTO WORD_LEMMATISED VALUES('bonus','n',1364,4569)
INSERT INTO WORD_LEMMATISED VALUES('book','n',37675,252)
INSERT INTO WORD_LEMMATISED VALUES('book','v',1894,3623)
INSERT INTO WORD_LEMMATISED VALUES('booking','n',1391,4510)
INSERT INTO WORD_LEMMATISED VALUES('booklet','n',1210,4946)
INSERT INTO WORD_LEMMATISED VALUES('boom','n',1491,4297)
INSERT INTO WORD_LEMMATISED VALUES('boost','n',978,5603)
INSERT INTO WORD_LEMMATISED VALUES('boost','v',1739,3873)
INSERT INTO WORD_LEMMATISED VALUES('boot','n',3930,2240)
INSERT INTO WORD_LEMMATISED VALUES('border','n',5028,1853)
INSERT INTO WORD_LEMMATISED VALUES('bored','a',1040,5402)
INSERT INTO WORD_LEMMATISED VALUES('boring','a',1531,4217)
INSERT INTO WORD_LEMMATISED VALUES('borough','n',2243,3246)
INSERT INTO WORD_LEMMATISED VALUES('borrow','v',3353,2499)
INSERT INTO WORD_LEMMATISED VALUES('boss','n',3739,2320)
INSERT INTO WORD_LEMMATISED VALUES('both','adv',27644,358)
INSERT INTO WORD_LEMMATISED VALUES('both','det',41162,227)
INSERT INTO WORD_LEMMATISED VALUES('bother','v',4048,2188)
INSERT INTO WORD_LEMMATISED VALUES('bottle','n',5872,1624)
INSERT INTO WORD_LEMMATISED VALUES('bottom','a',2301,3198)
INSERT INTO WORD_LEMMATISED VALUES('bottom','n',5441,1742)
INSERT INTO WORD_LEMMATISED VALUES('bounce','v',977,5608)
INSERT INTO WORD_LEMMATISED VALUES('boundary','n',4416,2036)
INSERT INTO WORD_LEMMATISED VALUES('bourgeois','a',929,5782)
INSERT INTO WORD_LEMMATISED VALUES('bow','n',1357,4585)
INSERT INTO WORD_LEMMATISED VALUES('bow','v',1359,4578)
INSERT INTO WORD_LEMMATISED VALUES('bowel','n',1441,4404)
INSERT INTO WORD_LEMMATISED VALUES('bowl','n',2631,2935)
INSERT INTO WORD_LEMMATISED VALUES('bowl','v',1020,5466)
INSERT INTO WORD_LEMMATISED VALUES('bowler','n',997,5541)
INSERT INTO WORD_LEMMATISED VALUES('box','n',11292,900)
INSERT INTO WORD_LEMMATISED VALUES('boxing','n',902,5872)
INSERT INTO WORD_LEMMATISED VALUES('boy','n',21205,486)
INSERT INTO WORD_LEMMATISED VALUES('boyfriend','n',1285,4735)
INSERT INTO WORD_LEMMATISED VALUES('bracket','n',1017,5474)
INSERT INTO WORD_LEMMATISED VALUES('brain','n',5586,1698)
INSERT INTO WORD_LEMMATISED VALUES('brake','n',974,5619)
INSERT INTO WORD_LEMMATISED VALUES('branch','n',8456,1196)
INSERT INTO WORD_LEMMATISED VALUES('brand','n',1819,3733)
INSERT INTO WORD_LEMMATISED VALUES('brandy','n',959,5675)
INSERT INTO WORD_LEMMATISED VALUES('brass','n',1433,4416)
INSERT INTO WORD_LEMMATISED VALUES('brave','a',1760,3838)
INSERT INTO WORD_LEMMATISED VALUES('breach','n',3407,2474)
INSERT INTO WORD_LEMMATISED VALUES('bread','n',3780,2294)
INSERT INTO WORD_LEMMATISED VALUES('break','n',4271,2099)
INSERT INTO WORD_LEMMATISED VALUES('break','v',19512,532)
INSERT INTO WORD_LEMMATISED VALUES('breakdown','n',1591,4104)
INSERT INTO WORD_LEMMATISED VALUES('breakfast','n',4314,2080)
INSERT INTO WORD_LEMMATISED VALUES('breast','n',2648,2926)
INSERT INTO WORD_LEMMATISED VALUES('breath','n',5347,1768)
INSERT INTO WORD_LEMMATISED VALUES('breathe','v',3372,2492)
INSERT INTO WORD_LEMMATISED VALUES('breed','n',1745,3859)
INSERT INTO WORD_LEMMATISED VALUES('breed','v',1974,3513)
INSERT INTO WORD_LEMMATISED VALUES('breeding','n',1403,4488)
INSERT INTO WORD_LEMMATISED VALUES('breeze','n',1455,4376)
INSERT INTO WORD_LEMMATISED VALUES('brewery','n',1012,5495)
INSERT INTO WORD_LEMMATISED VALUES('brick','n',2675,2911)
INSERT INTO WORD_LEMMATISED VALUES('bride','n',1111,5201)
INSERT INTO WORD_LEMMATISED VALUES('bridge','n',5367,1761)
INSERT INTO WORD_LEMMATISED VALUES('brief','a',4977,1861)
INSERT INTO WORD_LEMMATISED VALUES('briefly','adv',3280,2531)
INSERT INTO WORD_LEMMATISED VALUES('brigade','n',1245,4839)
INSERT INTO WORD_LEMMATISED VALUES('bright','a',6190,1552)
INSERT INTO WORD_LEMMATISED VALUES('brilliant','a',3498,2432)
INSERT INTO WORD_LEMMATISED VALUES('bring','v',43894,211)
INSERT INTO WORD_LEMMATISED VALUES('broad','a',6447,1488)
INSERT INTO WORD_LEMMATISED VALUES('broadcast','n',946,5726)
INSERT INTO WORD_LEMMATISED VALUES('broadcast','v',1116,5185)
INSERT INTO WORD_LEMMATISED VALUES('broadly','adv',1602,4091)
INSERT INTO WORD_LEMMATISED VALUES('brochure','n',1176,5037)
INSERT INTO WORD_LEMMATISED VALUES('broken','a',2479,3027)
INSERT INTO WORD_LEMMATISED VALUES('broker','n',1100,5232)
INSERT INTO WORD_LEMMATISED VALUES('bronze','n',1209,4951)
INSERT INTO WORD_LEMMATISED VALUES('brother','n',11757,864)
INSERT INTO WORD_LEMMATISED VALUES('brow','n',1194,4986)
INSERT INTO WORD_LEMMATISED VALUES('brown','a',4300,2088)
INSERT INTO WORD_LEMMATISED VALUES('brush','n',1686,3941)
INSERT INTO WORD_LEMMATISED VALUES('brush','v',2115,3369)
INSERT INTO WORD_LEMMATISED VALUES('bubble','n',843,6120)
INSERT INTO WORD_LEMMATISED VALUES('bucket','n',1401,4490)
INSERT INTO WORD_LEMMATISED VALUES('budget','n',9311,1078)
INSERT INTO WORD_LEMMATISED VALUES('build','v',23931,418)
INSERT INTO WORD_LEMMATISED VALUES('builder','n',1795,3768)
INSERT INTO WORD_LEMMATISED VALUES('building','n',20770,496)
INSERT INTO WORD_LEMMATISED VALUES('bulb','n',1030,5435)
INSERT INTO WORD_LEMMATISED VALUES('bulk','n',2056,3424)
INSERT INTO WORD_LEMMATISED VALUES('bull','n',1257,4807)
INSERT INTO WORD_LEMMATISED VALUES('bullet','n',1227,4894)
INSERT INTO WORD_LEMMATISED VALUES('bulletin','n',861,6036)
INSERT INTO WORD_LEMMATISED VALUES('bump','v',817,6239)
INSERT INTO WORD_LEMMATISED VALUES('bunch','n',1344,4610)
INSERT INTO WORD_LEMMATISED VALUES('bundle','n',881,5945)
INSERT INTO WORD_LEMMATISED VALUES('burden','n',2988,2710)
INSERT INTO WORD_LEMMATISED VALUES('bureau','n',1636,4028)
INSERT INTO WORD_LEMMATISED VALUES('bureaucracy','n',1509,4256)
INSERT INTO WORD_LEMMATISED VALUES('bureaucratic','a',927,5789)
INSERT INTO WORD_LEMMATISED VALUES('burial','n',1052,5373)
INSERT INTO WORD_LEMMATISED VALUES('burn','n',904,5868)
INSERT INTO WORD_LEMMATISED VALUES('burn','v',5091,1829)
INSERT INTO WORD_LEMMATISED VALUES('burning','a',1252,4813)
INSERT INTO WORD_LEMMATISED VALUES('burst','n',936,5761)
INSERT INTO WORD_LEMMATISED VALUES('burst','v',2476,3030)
INSERT INTO WORD_LEMMATISED VALUES('bury','v',2987,2713)
INSERT INTO WORD_LEMMATISED VALUES('bus','n',6761,1428)
INSERT INTO WORD_LEMMATISED VALUES('bush','n',1933,3567)
INSERT INTO WORD_LEMMATISED VALUES('business','n',38204,244)
INSERT INTO WORD_LEMMATISED VALUES('businessman','n',1895,3621)
INSERT INTO WORD_LEMMATISED VALUES('busy','a',5221,1801)
INSERT INTO WORD_LEMMATISED VALUES('but','conj',459622,23)
INSERT INTO WORD_LEMMATISED VALUES('butter','n',2044,3439)
INSERT INTO WORD_LEMMATISED VALUES('butterfly','n',1136,5132)
INSERT INTO WORD_LEMMATISED VALUES('button','n',2503,3012)
INSERT INTO WORD_LEMMATISED VALUES('buy','v',25582,394)
INSERT INTO WORD_LEMMATISED VALUES('buyer','n',4285,2092)
INSERT INTO WORD_LEMMATISED VALUES('by','adv',2898,2760)
INSERT INTO WORD_LEMMATISED VALUES('by','prep',517171,20)
INSERT INTO WORD_LEMMATISED VALUES('bye','interjecti',1626,4042)

The following is the spring application configuration file. If you are using mySQL to connect then just comment out the HSQLDB and comment in the mysql Configuration entry.

src/main/resources/applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
 
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
 
http://www.springframework.org/schema/context
 
http://www.springframework.org/schema/context/spring-context-2.5.xsd">


	<!-- Use the following for the HSQLDB in memory Java database (default) -->
	<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	    <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
	    <property name="url" value="jdbc:hsqldb:file:src/main/resources/db/liveSearch"/>
	    <property name="username" value="sa"/>
	    <property name="password" value=""/>
	</bean>
 
    <!-- uncomment this to use mySQL and comment out the HSQLDB above
	<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
	    <property name="url" value="jdbc:mysql://xxxx:3306/xxxx"/>
	    <property name="username" value="xxxx"/>
	    <property name="password" value="xxx"/>
	</bean>
 	-->
	 
	<bean name="jdbcTemplate" class="org.springframework.jdbc.core.simple.SimpleJdbcTemplate">
	    <constructor-arg><ref bean="dataSource"/></constructor-arg>
	</bean>
</beans>

Homepage

The following page presents the user with an extjs combo box that makes ajax calls to the servlet to get search results.

src/main/webapp/index.jsp

<html>
<head>
    <title>ComboBox - Live Search</title>
    <link rel="stylesheet" type="text/css"
        href="http://dev.sencha.com/deploy/ext-3.3.1/resources/css/ext-all.css"/>
 
    <script type="text/javascript"
        src="http://dev.sencha.com/deploy/ext-3.3.1/adapter/ext/ext-base-debug.js">
    </script>
    <script type="text/javascript"
        src="http://dev.sencha.com/deploy/ext-3.3.1/ext-all-debug.js">
    </script>

	<script type="text/javascript">
		Ext.onReady(function(){
		 
			// This reader helps process search result data being 
			// returned after the search.
			var myReader = new Ext.data.JsonReader({
				root: 'records',
				totalProperty: 'totalCount',
				fields: [
				        {name: 'word_x', type: 'string'}
				    ]
			});
		 
			// This represents the URL that returns the search results.
			var myStore = new Ext.data.Store({
				// url : 'response.do' // url: uses POST by default
				proxy: new Ext.data.HttpProxy({  // force it to use GET method
					url:    '/app/searchServlet',
					method: 'GET'
				}),
			    reader : myReader
			});
			// Custom rendering Template
			var resultTpl = new Ext.XTemplate(
				'<tpl for="."><div class="search-item">',
				    '<h3>{word_x}</h3>',
				'</div></tpl>'
			);

			var search = new Ext.form.ComboBox({			
				store: myStore,
				displayField:'title',
				typeAhead: false,
				loadingText: 'Searching...',
				width: 570,
				minChars: 2,
				pageSize:5,
				hideTrigger:true,
				tpl: resultTpl,
				applyTo: 'search',
				itemSelector: 'div.search-item',
				onSelect: function(record){ 
					alert(record.data.word_x + ' was selected...');
					// override default onSelect to do redirect
//				    window.location =
//				        String.format('http://extjs.com/forum/showthread.php?t={0}&p={1}', 
//				        	record.data.topicId, record.id);
				}
			});
		});	
	</script>

    <style type="text/css">
        p { width:650px; }
		.search-item {
			font:normal 11px tahoma, arial, helvetica, sans-serif;
			padding:3px 10px 3px 10px;
			border:1px solid #fff;
			border-bottom:1px solid #eeeeee;
			white-space:normal;
			color:#555;
		}
		.search-item h3 {
			display:block;
			font:inherit;
			font-weight:bold;
			color:#222;
		}

		.search-item h3 span {
			float: right;
			font-weight:normal;
			margin:0 0 5px 5px;
			width:100px;
			display:block;
			clear:none;
		}
    </style>     
</head>
 
<body>
<p>
    <b>Combo with Templates and Ajax</b><br />
	
	Type any word that begins with the letters "a" or "b". for example "ab", "be" ...
	
</p>

<!-- The box wrap markup embedded instead of using Element.boxWrap() -->
<div style="width:600px;">
    <div class="x-box-tl"><div class="x-box-tr"><div class="x-box-tc"></div></div></div>
    <div class="x-box-ml"><div class="x-box-mr"><div class="x-box-mc">
        <h3 style="margin-bottom:5px;">Search the Ext Forums</h3>
        <input type="text" size="40" name="search" id="search" />
        <div style="padding-top:4px;">
            Live search requires a minimum of 2 characters.
        </div>
 
    </div></div></div>
    <div class="x-box-bl"><div class="x-box-br"><div class="x-box-bc"></div></div></div>
</div>

</body>
</html>

Test the application

To test the application we will start the jetty servlet engine.

mvn clean compile jetty:run

Navigate to the page and start typing. Search results should start to show.

http://localhost:8080/

25
May
11

Creating a Mock Data Provider

This page describes the process of creating a Java Servlet that returns mock data in JSON format, based on a query string provided by the client .

Background

Developers sometimes find themselves in situations where the back-end is still a work in progress. There also may be situations where (due to security restrictions) you don’t have access to the back end at all! User interface components require data in order for them to display properly on the page.

This page will describe a process of creating a MOCK data layer to read / generate data that you can use to feed your extJS components while they are being developed.

We will test the code by creating a simple client that makes an AJAX call to the Resource to display data.

In order to make this tutorial portable we will use HyperSQL Java in memory database to store the data.

This page uses the following frameworks/libraries

Solution

Create a Java Web Application that will allow the client to specify a query in Hibernate SQL Query Language (HSQL). The servlet will issue the call to get the data and return the JSON back to the caller.

WARNING: Don’t use this in production code. This is only scaffolding code to assist you in retrieving data to assist in developing the User Interface. Once your Interface is finished the servlet can simply be removed from your application.

Create the project

Create a new Project using a Maven archetype.

mvn archetype:generate -DarchetypeArtifactId=maven-archetype-webapp

groupId: com.test
artifactId: mock-data-provider

Answer the rest of the questions with defaults “Just hit the enter key”,

cd to the project base folder.

cd mock-data-provider

Since this is a web project maven2 does not create the java source folder.

Create missing folders now.
mkdir -p src/main/java/com/test
mkdir -p src/main/resources/com/test
mkdir -p src/main/resources/META-INF

Modify the project Configuration

The pom.xml file should look something like this.

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.test</groupId>
  <artifactId>mock-data-provider</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>mock-data-provider Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
        <dependency>
            <groupId>org.json</groupId>
            <artifactId>json</artifactId>
            <version>20090211</version>
        </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate</artifactId>
      <version>3.2.6.ga</version>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-entitymanager</artifactId>
      <version>3.3.1.ga</version>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-annotations</artifactId>
      <version>3.3.1.GA</version>
    </dependency>
    <dependency>
      <groupId>commons-collections</groupId>
      <artifactId>commons-collections</artifactId>
      <version>3.2.1</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.9</version>
    </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring</artifactId>
            <version>2.5.6</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>2.5.6</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.14</version>
        </dependency>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>1.8.0.10</version>
        </dependency>
        <dependency>
            <groupId>org.apache.ddlutils</groupId>
            <artifactId>ddlutils</artifactId>
            <version>1.0</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.0</version>
            <scope>provided</scope>
        </dependency>
 
  </dependencies>
  <build>
    <finalName>mock-data-provider</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.0.2</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.mortbay.jetty</groupId>
                <artifactId>jetty-maven-plugin</artifactId>
				<version>7.0.0.v20091005</version>
                <configuration>
                    <scanIntervalSeconds>2</scanIntervalSeconds>
                    <webAppConfig>
                        <contextPath>/dataProvider</contextPath>
                    </webAppConfig>
                    <stopKey>s</stopKey>
                </configuration>
            </plugin>
        </plugins>
  </build>
</project>

Database Setup and Configuration

The following configuration file is used by Apache DDLUtils to generate apply the schema changes each time the application restarts. Changes are made incrementally and every attempt is made to preserve the data. For more information about DDLUtils please see my other tutorial.

src/main/resources/ddl.xml

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">

<database name="mockdb">
    <table name="countries">
        <column name="name" type="VARCHAR" size="50" required="true" primaryKey="true" />
        <column name="shortName" type="CHAR" size="2" required="true" />
        <index name="country_x">
            <index-column name="name" />
        </index>
    </table>
</database>

JPA Configuration

src/main/resources/META-INF/persistence.xml

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
             version="2.0">
   <persistence-unit name="manager1">
      <provider>org.hibernate.ejb.HibernatePersistence</provider>
      <properties>
         <property name="hibernate.archive.autodetection" value="class, hbm"/>
         <property name="hibernate.show_sql" value="true"/>
         <property name="hibernate.format_sql" value="true"/>
         <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/>
         <property name="hibernate.connection.url" value="jdbc:hsqldb:file:src/main/webapp/WEB-INF/db/mockdb"/>
         <property name="hibernate.connection.username" value="sa"/>
         <property name="hibernate.connection.password" value=""/>
         <property name="hibernate.connection.pool_size" value="0"/>
 
         <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
         <property name="hibernate.max_fetch_depth" value="3"/>
 
         <property name="hibernate.hbm2ddl.auto" value="validate"/>
 <!--
    * validate: validate the schema, makes no changes to the database.
    * update: update the schema.
    * create: creates the schema, destroying previous data.
    * create-drop: drop the schema at the end of the session.
-->
      </properties>
   </persistence-unit>
</persistence>

Shutting down the database gracefully

Since we are working with HSQLDB, it is critical that we gracefully shut-down the database when the application stops. Otherwise the data that was cached in memory will be lost.

We will use a “ServletContextListener” to listen for the application shut-down event. This will allow us to execute code to graceful shut-down the HSQLDB database.

Spring Configuration

Most of the spring configuration was done using Java Annotations, therefore this file is not too large.

src/main/resources/applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:context="http://www.springframework.org/schema/context"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
 
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
 
http://www.springframework.org/schema/context
 
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
 
    <context:annotation-config/>
    <context:component-scan base-package="com.test"/>
 
    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalEntityManagerFactoryBean" />
    <bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />
 
</beans>

Java Code

src/main/java/com/test/Country.java

package com.test;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="countries")
public class Country {
	
	private String name;
	
	private String shortName;
	
	public Country() {
		super();
	}
	public Country(String name, String shortName) {		
		this.name = name;
		this.shortName = shortName;		
	}
	
	public void setName(String name) {
		this.name = name;
	}
	@Id
	public String getName() {
		return name;
	}
	public void setShortName(String shortName) {
		this.shortName = shortName;
	}

	@Column(columnDefinition="CHAR", length=2)
	public String getShortName() {
		return shortName;
	}
	

}

src/main/java/com/test/DBLifecycleContextListener.java

package com.test;
 
import java.io.InputStreamReader;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;

import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.io.DatabaseIO;
import org.apache.ddlutils.model.Database;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
 
/**
 * This listener initializes or gracefully shuts down the database based on
 * events from the web application.
 */

public class DBLifecycleContextListener implements ServletContextListener {
    private DataSource dataSource = null;
 
    public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public void contextDestroyed(ServletContextEvent event) {
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(dataSource);
        template.update("SHUTDOWN;");
        System.out.println("Database Successfully Shutdown.");
    }

    public void contextInitialized(ServletContextEvent event) {
   		try {
			Class.forName("org.hsqldb.jdbcDriver");
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		}

		dataSource = new DriverManagerDataSource(
				"jdbc:hsqldb:file:src/main/webapp/WEB-INF/db/mockdb", "sa", "");
		Platform platform = PlatformFactory
				.createNewPlatformInstance(dataSource);
 
        Database database = new DatabaseIO().read(new InputStreamReader(
                getClass().getResourceAsStream("/ddl.xml")));
 
        platform.alterTables(database, false);
    }
}

src/main/java/com/test/MockDataManager.java

package com.test;

import java.util.List;

public interface MockDataManager {
	public List<Object> getList(String query);
}

src/main/java/com/test/MockDataManagerImpl.java

package com.test;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.springframework.stereotype.Repository;

@Repository("mockDataManager")
public class MockDataManagerImpl implements MockDataManager {
    private EntityManager entityManager;
    
    
    @PersistenceContext
    public void setEntityManager(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
    
    
	@Override
	public List<Object> getList(String query) {
		return entityManager.createQuery(query).getResultList();
	}

}

src/main/java/com/test/MockDataServlet.java

package com.test;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.context.ApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

public class MockDataServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	private MockDataManager mockDataManager;
	
	@Override
	public void init(ServletConfig config) throws ServletException {
		super.init(config);
		ApplicationContext applicationContext = WebApplicationContextUtils
				.getWebApplicationContext(config.getServletContext());
		mockDataManager = (MockDataManager)applicationContext.getBean("mockDataManager");		
	}
	@Override
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String query = request.getParameter("q");
		request.setAttribute("q", query);
		request.setAttribute("encodedq", query);
		if(query != null && !"".equals(query.trim())) {
			List stateList = mockDataManager.getList(query);
			System.out.println(stateList.size());
			JSONArray array = new JSONArray();
			
			for (Object object : stateList) {
				array.put(new JSONObject(object));
			}
			System.out.println(array.toString());
			
			PrintWriter writer = response.getWriter();
			String output = array.toString();
			writer.write(output);
			request.setAttribute("output", output);			
		}
		
	}
	
}

Web Configuration

src/main/webapp/WEB-INF/web.xml

<!DOCTYPE web-app PUBLIC
 "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
 "http://java.sun.com/dtd/web-app_2_3.dtd" >

<web-app>
  <display-name>Archetype Created Web Application</display-name>

    <context-param>
        <param-name>contextConfigLocation</param-name>
        <param-value>classpath:applicationContext.xml</param-value>
    </context-param>
 
    <listener>
        <listener-class>com.test.DBLifecycleContextListener</listener-class>
    </listener>
    <listener>
        <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
    </listener>
  
  <servlet>
  	<servlet-name>mockDataServlet</servlet-name>
  	<servlet-class>com.test.MockDataServlet</servlet-class>
  </servlet>
  
  <servlet-mapping>
  	<servlet-name>mockDataServlet</servlet-name>
  	<url-pattern>/mockData</url-pattern>  	
  </servlet-mapping>
</web-app>

src/main/webapp/index.jsp

<jsp:useBean id="q" scope="request" class="java.lang.String"/>

<html>
<body>
<form action="mockData" method="get">
	<label>Enter Hibernate Query Example: select c from Country c where name like 'B%'</label><br/>
	<textarea name="q" cols="80" rows="5"><%=q%></textarea>
	<br/>
	<input type="submit" value="Submit"/>	
</form>
</body>
</html>

Test the application

Clean the project compile and start Jetty Servlet Engine.

mvn clean compile jetty:run

Shutdown the application

We shut down the application. This will ensure that the proper data file were created. We will open these data files and modify them inserting our data.

Insert the data

src/main/webapp/WEB-INF/db/mockdb.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE COUNTRIES(NAME VARCHAR(50) NOT NULL PRIMARY KEY,SHORTNAME CHAR(2) NOT NULL)
CREATE INDEX COUNTRY_X ON COUNTRIES(NAME)
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO COUNTRIES VALUES('AFGHANISTAN','AF')
INSERT INTO COUNTRIES VALUES('ALBANIA','AL')
INSERT INTO COUNTRIES VALUES('ALGERIA','DZ')
INSERT INTO COUNTRIES VALUES('AMERICAN SAMOA','AS')
INSERT INTO COUNTRIES VALUES('ANDORRA','AD')
INSERT INTO COUNTRIES VALUES('ANGOLA','AO')
INSERT INTO COUNTRIES VALUES('ANGUILLA','AI')
INSERT INTO COUNTRIES VALUES('ANTARCTICA','AQ')
INSERT INTO COUNTRIES VALUES('ANTIGUA AND BARBUDA','AG')
INSERT INTO COUNTRIES VALUES('ARGENTINA','AR')
INSERT INTO COUNTRIES VALUES('ARMENIA','AM')
INSERT INTO COUNTRIES VALUES('ARUBA','AW')
INSERT INTO COUNTRIES VALUES('AUSTRALIA','AU')
INSERT INTO COUNTRIES VALUES('AUSTRIA','AT')
INSERT INTO COUNTRIES VALUES('AZERBAIJAN','AZ')
INSERT INTO COUNTRIES VALUES('BAHAMAS','BS')
INSERT INTO COUNTRIES VALUES('BAHRAIN','BH')
INSERT INTO COUNTRIES VALUES('BANGLADESH','BD')
INSERT INTO COUNTRIES VALUES('BARBADOS','BB')
INSERT INTO COUNTRIES VALUES('BELARUS','BY')
INSERT INTO COUNTRIES VALUES('BELGIUM','BE')
INSERT INTO COUNTRIES VALUES('BELIZE','BZ')
INSERT INTO COUNTRIES VALUES('BENIN','BJ')
INSERT INTO COUNTRIES VALUES('BERMUDA','BM')
INSERT INTO COUNTRIES VALUES('BHUTAN','BT')
INSERT INTO COUNTRIES VALUES('BOLIVIA, PLURINATIONAL STATE OF','BO')
INSERT INTO COUNTRIES VALUES('BONAIRE, SAINT EUSTATIUS AND SABA','BQ')
INSERT INTO COUNTRIES VALUES('BOSNIA AND HERZEGOVINA','BA')
INSERT INTO COUNTRIES VALUES('BOTSWANA','BW')
INSERT INTO COUNTRIES VALUES('BOUVET ISLAND','BV')
INSERT INTO COUNTRIES VALUES('BRAZIL','BR')
INSERT INTO COUNTRIES VALUES('BRITISH INDIAN OCEAN TERRITORY','IO')
INSERT INTO COUNTRIES VALUES('BRUNEI DARUSSALAM','BN')
INSERT INTO COUNTRIES VALUES('BULGARIA','BG')
INSERT INTO COUNTRIES VALUES('BURKINA FASO','BF')
INSERT INTO COUNTRIES VALUES('BURUNDI','BI')
INSERT INTO COUNTRIES VALUES('CAMBODIA','KH')
INSERT INTO COUNTRIES VALUES('CAMEROON','CM')
INSERT INTO COUNTRIES VALUES('CANADA','CA')
INSERT INTO COUNTRIES VALUES('CAPE VERDE','CV')
INSERT INTO COUNTRIES VALUES('CAYMAN ISLANDS','KY')
INSERT INTO COUNTRIES VALUES('CENTRAL AFRICAN REPUBLIC','CF')
INSERT INTO COUNTRIES VALUES('CHAD','TD')
INSERT INTO COUNTRIES VALUES('CHILE','CL')
INSERT INTO COUNTRIES VALUES('CHINA','CN')
INSERT INTO COUNTRIES VALUES('CHRISTMAS ISLAND','CX')
INSERT INTO COUNTRIES VALUES('COCOS (KEELING) ISLANDS','CC')
INSERT INTO COUNTRIES VALUES('COLOMBIA','CO')
INSERT INTO COUNTRIES VALUES('COMOROS','KM')
INSERT INTO COUNTRIES VALUES('CONGO','CG')
INSERT INTO COUNTRIES VALUES('CONGO, THE DEMOCRATIC REPUBLIC OF THE','CD')
INSERT INTO COUNTRIES VALUES('COOK ISLANDS','CK')
INSERT INTO COUNTRIES VALUES('COSTA RICA','CR')
INSERT INTO COUNTRIES VALUES('CROATIA','HR')
INSERT INTO COUNTRIES VALUES('CUBA','CU')
INSERT INTO COUNTRIES VALUES('CURA\u00c7AO','CW')
INSERT INTO COUNTRIES VALUES('CYPRUS','CY')
INSERT INTO COUNTRIES VALUES('CZECH REPUBLIC','CZ')
INSERT INTO COUNTRIES VALUES('C\u00d4TE D''IVOIRE','CI')
INSERT INTO COUNTRIES VALUES('DENMARK','DK')
INSERT INTO COUNTRIES VALUES('DJIBOUTI','DJ')
INSERT INTO COUNTRIES VALUES('DOMINICA','DM')
INSERT INTO COUNTRIES VALUES('DOMINICAN REPUBLIC','DO')
INSERT INTO COUNTRIES VALUES('ECUADOR','EC')
INSERT INTO COUNTRIES VALUES('EGYPT','EG')
INSERT INTO COUNTRIES VALUES('EL SALVADOR','SV')
INSERT INTO COUNTRIES VALUES('EQUATORIAL GUINEA','GQ')
INSERT INTO COUNTRIES VALUES('ERITREA','ER')
INSERT INTO COUNTRIES VALUES('ESTONIA','EE')
INSERT INTO COUNTRIES VALUES('ETHIOPIA','ET')
INSERT INTO COUNTRIES VALUES('FALKLAND ISLANDS (MALVINAS)','FK')
INSERT INTO COUNTRIES VALUES('FAROE ISLANDS','FO')
INSERT INTO COUNTRIES VALUES('FIJI','FJ')
INSERT INTO COUNTRIES VALUES('FINLAND','FI')
INSERT INTO COUNTRIES VALUES('FRANCE','FR')
INSERT INTO COUNTRIES VALUES('FRENCH GUIANA','GF')
INSERT INTO COUNTRIES VALUES('FRENCH POLYNESIA','PF')
INSERT INTO COUNTRIES VALUES('FRENCH SOUTHERN TERRITORIES','TF')
INSERT INTO COUNTRIES VALUES('GABON','GA')
INSERT INTO COUNTRIES VALUES('GAMBIA','GM')
INSERT INTO COUNTRIES VALUES('GEORGIA','GE')
INSERT INTO COUNTRIES VALUES('GERMANY','DE')
INSERT INTO COUNTRIES VALUES('GHANA','GH')
INSERT INTO COUNTRIES VALUES('GIBRALTAR','GI')
INSERT INTO COUNTRIES VALUES('GREECE','GR')
INSERT INTO COUNTRIES VALUES('GREENLAND','GL')
INSERT INTO COUNTRIES VALUES('GRENADA','GD')
INSERT INTO COUNTRIES VALUES('GUADELOUPE','GP')
INSERT INTO COUNTRIES VALUES('GUAM','GU')
INSERT INTO COUNTRIES VALUES('GUATEMALA','GT')
INSERT INTO COUNTRIES VALUES('GUERNSEY','GG')
INSERT INTO COUNTRIES VALUES('GUINEA','GN')
INSERT INTO COUNTRIES VALUES('GUINEA-BISSAU','GW')
INSERT INTO COUNTRIES VALUES('GUYANA','GY')
INSERT INTO COUNTRIES VALUES('HAITI','HT')
INSERT INTO COUNTRIES VALUES('HEARD ISLAND AND MCDONALD ISLANDS','HM')
INSERT INTO COUNTRIES VALUES('HOLY SEE (VATICAN CITY STATE)','VA')
INSERT INTO COUNTRIES VALUES('HONDURAS','HN')
INSERT INTO COUNTRIES VALUES('HONG KONG','HK')
INSERT INTO COUNTRIES VALUES('HUNGARY','HU')
INSERT INTO COUNTRIES VALUES('ICELAND','IS')
INSERT INTO COUNTRIES VALUES('INDIA','IN')
INSERT INTO COUNTRIES VALUES('INDONESIA','ID')
INSERT INTO COUNTRIES VALUES('IRAN, ISLAMIC REPUBLIC OF','IR')
INSERT INTO COUNTRIES VALUES('IRAQ','IQ')
INSERT INTO COUNTRIES VALUES('IRELAND','IE')
INSERT INTO COUNTRIES VALUES('ISLE OF MAN','IM')
INSERT INTO COUNTRIES VALUES('ISRAEL','IL')
INSERT INTO COUNTRIES VALUES('ITALY','IT')
INSERT INTO COUNTRIES VALUES('JAMAICA','JM')
INSERT INTO COUNTRIES VALUES('JAPAN','JP')
INSERT INTO COUNTRIES VALUES('JERSEY','JE')
INSERT INTO COUNTRIES VALUES('JORDAN','JO')
INSERT INTO COUNTRIES VALUES('KAZAKHSTAN','KZ')
INSERT INTO COUNTRIES VALUES('KENYA','KE')
INSERT INTO COUNTRIES VALUES('KIRIBATI','KI')
INSERT INTO COUNTRIES VALUES('KOREA, DEMOCRATIC PEOPLE''S REPUBLIC OF','KP')
INSERT INTO COUNTRIES VALUES('KOREA, REPUBLIC OF','KR')
INSERT INTO COUNTRIES VALUES('KUWAIT','KW')
INSERT INTO COUNTRIES VALUES('KYRGYZSTAN','KG')
INSERT INTO COUNTRIES VALUES('LAO PEOPLE''S DEMOCRATIC REPUBLIC','LA')
INSERT INTO COUNTRIES VALUES('LATVIA','LV')
INSERT INTO COUNTRIES VALUES('LEBANON','LB')
INSERT INTO COUNTRIES VALUES('LESOTHO','LS')
INSERT INTO COUNTRIES VALUES('LIBERIA','LR')
INSERT INTO COUNTRIES VALUES('LIBYAN ARAB JAMAHIRIYA','LY')
INSERT INTO COUNTRIES VALUES('LIECHTENSTEIN','LI')
INSERT INTO COUNTRIES VALUES('LITHUANIA','LT')
INSERT INTO COUNTRIES VALUES('LUXEMBOURG','LU')
INSERT INTO COUNTRIES VALUES('MACAO','MO')
INSERT INTO COUNTRIES VALUES('MACEDONIA, THE FORMER YUGOSLAV REPUBLIC OF','MK')
INSERT INTO COUNTRIES VALUES('MADAGASCAR','MG')
INSERT INTO COUNTRIES VALUES('MALAWI','MW')
INSERT INTO COUNTRIES VALUES('MALAYSIA','MY')
INSERT INTO COUNTRIES VALUES('MALDIVES','MV')
INSERT INTO COUNTRIES VALUES('MALI','ML')
INSERT INTO COUNTRIES VALUES('MALTA','MT')
INSERT INTO COUNTRIES VALUES('MARSHALL ISLANDS','MH')
INSERT INTO COUNTRIES VALUES('MARTINIQUE','MQ')
INSERT INTO COUNTRIES VALUES('MAURITANIA','MR')
INSERT INTO COUNTRIES VALUES('MAURITIUS','MU')
INSERT INTO COUNTRIES VALUES('MAYOTTE','YT')
INSERT INTO COUNTRIES VALUES('MEXICO','MX')
INSERT INTO COUNTRIES VALUES('MICRONESIA, FEDERATED STATES OF','FM')
INSERT INTO COUNTRIES VALUES('MOLDOVA, REPUBLIC OF','MD')
INSERT INTO COUNTRIES VALUES('MONACO','MC')
INSERT INTO COUNTRIES VALUES('MONGOLIA','MN')
INSERT INTO COUNTRIES VALUES('MONTENEGRO','ME')
INSERT INTO COUNTRIES VALUES('MONTSERRAT','MS')
INSERT INTO COUNTRIES VALUES('MOROCCO','MA')
INSERT INTO COUNTRIES VALUES('MOZAMBIQUE','MZ')
INSERT INTO COUNTRIES VALUES('MYANMAR','MM')
INSERT INTO COUNTRIES VALUES('NAMIBIA','NA')
INSERT INTO COUNTRIES VALUES('NAURU','NR')
INSERT INTO COUNTRIES VALUES('NEPAL','NP')
INSERT INTO COUNTRIES VALUES('NETHERLANDS','NL')
INSERT INTO COUNTRIES VALUES('NEW CALEDONIA','NC')
INSERT INTO COUNTRIES VALUES('NEW ZEALAND','NZ')
INSERT INTO COUNTRIES VALUES('NICARAGUA','NI')
INSERT INTO COUNTRIES VALUES('NIGER','NE')
INSERT INTO COUNTRIES VALUES('NIGERIA','NG')
INSERT INTO COUNTRIES VALUES('NIUE','NU')
INSERT INTO COUNTRIES VALUES('NORFOLK ISLAND','NF')
INSERT INTO COUNTRIES VALUES('NORTHERN MARIANA ISLANDS','MP')
INSERT INTO COUNTRIES VALUES('NORWAY','NO')
INSERT INTO COUNTRIES VALUES('OMAN','OM')
INSERT INTO COUNTRIES VALUES('PAKISTAN','PK')
INSERT INTO COUNTRIES VALUES('PALAU','PW')
INSERT INTO COUNTRIES VALUES('PALESTINIAN TERRITORY, OCCUPIED','PS')
INSERT INTO COUNTRIES VALUES('PANAMA','PA')
INSERT INTO COUNTRIES VALUES('PAPUA NEW GUINEA','PG')
INSERT INTO COUNTRIES VALUES('PARAGUAY','PY')
INSERT INTO COUNTRIES VALUES('PERU','PE')
INSERT INTO COUNTRIES VALUES('PHILIPPINES','PH')
INSERT INTO COUNTRIES VALUES('PITCAIRN','PN')
INSERT INTO COUNTRIES VALUES('POLAND','PL')
INSERT INTO COUNTRIES VALUES('PORTUGAL','PT')
INSERT INTO COUNTRIES VALUES('PUERTO RICO','PR')
INSERT INTO COUNTRIES VALUES('QATAR','QA')
INSERT INTO COUNTRIES VALUES('ROMANIA','RO')
INSERT INTO COUNTRIES VALUES('RUSSIAN FEDERATION','RU')
INSERT INTO COUNTRIES VALUES('RWANDA','RW')
INSERT INTO COUNTRIES VALUES('R\u00c9UNION','RE')
INSERT INTO COUNTRIES VALUES('SAINT BARTH\u00c9LEMY','BL')
INSERT INTO COUNTRIES VALUES('SAINT HELENA, ASCENSION AND TRISTAN DA CUNHA','SH')
INSERT INTO COUNTRIES VALUES('SAINT KITTS AND NEVIS','KN')
INSERT INTO COUNTRIES VALUES('SAINT LUCIA','LC')
INSERT INTO COUNTRIES VALUES('SAINT MARTIN (FRENCH PART)','MF')
INSERT INTO COUNTRIES VALUES('SAINT PIERRE AND MIQUELON','PM')
INSERT INTO COUNTRIES VALUES('SAINT VINCENT AND THE GRENADINES','VC')
INSERT INTO COUNTRIES VALUES('SAMOA','WS')
INSERT INTO COUNTRIES VALUES('SAN MARINO','SM')
INSERT INTO COUNTRIES VALUES('SAO TOME AND PRINCIPE','ST')
INSERT INTO COUNTRIES VALUES('SAUDI ARABIA','SA')
INSERT INTO COUNTRIES VALUES('SENEGAL','SN')
INSERT INTO COUNTRIES VALUES('SERBIA','RS')
INSERT INTO COUNTRIES VALUES('SEYCHELLES','SC')
INSERT INTO COUNTRIES VALUES('SIERRA LEONE','SL')
INSERT INTO COUNTRIES VALUES('SINGAPORE','SG')
INSERT INTO COUNTRIES VALUES('SINT MAARTEN (DUTCH PART)','SX')
INSERT INTO COUNTRIES VALUES('SLOVAKIA','SK')
INSERT INTO COUNTRIES VALUES('SLOVENIA','SI')
INSERT INTO COUNTRIES VALUES('SOLOMON ISLANDS','SB')
INSERT INTO COUNTRIES VALUES('SOMALIA','SO')
INSERT INTO COUNTRIES VALUES('SOUTH AFRICA','ZA')
INSERT INTO COUNTRIES VALUES('SOUTH GEORGIA AND THE SOUTH SANDWICH ISLANDS','GS')
INSERT INTO COUNTRIES VALUES('SPAIN','ES')
INSERT INTO COUNTRIES VALUES('SRI LANKA','LK')
INSERT INTO COUNTRIES VALUES('SUDAN','SD')
INSERT INTO COUNTRIES VALUES('SURINAME','SR')
INSERT INTO COUNTRIES VALUES('SVALBARD AND JAN MAYEN','SJ')
INSERT INTO COUNTRIES VALUES('SWAZILAND','SZ')
INSERT INTO COUNTRIES VALUES('SWEDEN','SE')
INSERT INTO COUNTRIES VALUES('SWITZERLAND','CH')
INSERT INTO COUNTRIES VALUES('SYRIAN ARAB REPUBLIC','SY')
INSERT INTO COUNTRIES VALUES('TAIWAN, PROVINCE OF CHINA','TW')
INSERT INTO COUNTRIES VALUES('TAJIKISTAN','TJ')
INSERT INTO COUNTRIES VALUES('TANZANIA, UNITED REPUBLIC OF','TZ')
INSERT INTO COUNTRIES VALUES('THAILAND','TH')
INSERT INTO COUNTRIES VALUES('TIMOR-LESTE','TL')
INSERT INTO COUNTRIES VALUES('TOGO','TG')
INSERT INTO COUNTRIES VALUES('TOKELAU','TK')
INSERT INTO COUNTRIES VALUES('TONGA','TO')
INSERT INTO COUNTRIES VALUES('TRINIDAD AND TOBAGO','TT')
INSERT INTO COUNTRIES VALUES('TUNISIA','TN')
INSERT INTO COUNTRIES VALUES('TURKEY','TR')
INSERT INTO COUNTRIES VALUES('TURKMENISTAN','TM')
INSERT INTO COUNTRIES VALUES('TURKS AND CAICOS ISLANDS','TC')
INSERT INTO COUNTRIES VALUES('TUVALU','TV')
INSERT INTO COUNTRIES VALUES('UGANDA','UG')
INSERT INTO COUNTRIES VALUES('UKRAINE','UA')
INSERT INTO COUNTRIES VALUES('UNITED ARAB EMIRATES','AE')
INSERT INTO COUNTRIES VALUES('UNITED KINGDOM','GB')
INSERT INTO COUNTRIES VALUES('UNITED STATES','US')
INSERT INTO COUNTRIES VALUES('UNITED STATES MINOR OUTLYING ISLANDS','UM')
INSERT INTO COUNTRIES VALUES('URUGUAY','UY')
INSERT INTO COUNTRIES VALUES('UZBEKISTAN','UZ')
INSERT INTO COUNTRIES VALUES('VANUATU','VU')
INSERT INTO COUNTRIES VALUES('VENEZUELA, BOLIVARIAN REPUBLIC OF','VE')
INSERT INTO COUNTRIES VALUES('VIET NAM','VN')
INSERT INTO COUNTRIES VALUES('VIRGIN ISLANDS, BRITISH','VG')
INSERT INTO COUNTRIES VALUES('VIRGIN ISLANDS, U.S.','VI')
INSERT INTO COUNTRIES VALUES('WALLIS AND FUTUNA','WF')
INSERT INTO COUNTRIES VALUES('WESTERN SAHARA','EH')
INSERT INTO COUNTRIES VALUES('YEMEN','YE')
INSERT INTO COUNTRIES VALUES('ZAMBIA','ZM')
INSERT INTO COUNTRIES VALUES('ZIMBABWE','ZW')

Start the server again (see above) and Navigate to: http://localhost:8080/dataProvider/

Creating a WAR file

If you want to run the application in another environment you can create a war file and drop it into a server of your choice. This method will also package up any runtime dependencies and put them in the /WEB-INF/lib folder of the WAR file.

mvn clean compile package

The war file should appear in the “target/” folder.

Additional Topics: Custom JSON

Since reflection is being used to create the JSON, the field names will represent the name of the attributes in the bean. Currently there is no easy way around this… We can build wrappers that map bean attributes to json attributes but this will require alot of work. If you guys have any suggestions on how this can be done please comment below.

A few reasons why you may want to build a custom mapping layer is:

  1. You may not want to return all the attributes of the bean
  2. You want to rename the field names to something other than the class attribute names.
01
May
11

ExtJS RESTful State Provider

This page describes the process of extending the ExtJS framework to store user preferences in a database instead of browser cookies. For those that want instant gratification, a complete working version of the project is available at the following SVN location. http://ext-jsf.googlecode.com/svn/trunk/wordpress/2011/04/extjs-restful-provider/. Get the project and skip to the Test the Application section of this page.

Background

ExtJS allows components to be customized by the end user. The Extjs grid panel is a good example. The default behaviour of the grid panel allows the user to show or hide columns and change column sorting. However when the user refreshes the page the default settings get restored. In order to have these settings persisted often times developers use the Ext.state.CookieProvider class. This class uses Browser Cookies to store the preference settings. If you have many of these components on your page you may end up storing allot of data on the client browser. Some users also have security enabled software that clean cookie’s often causing these preferences to be lost.

A more effective method is to store these settings on the server side. This page describes how to create a simple database table and RESTful Servlet to allow the extJS component to store this information on the server side.

Requirements

Maven 2

Create a new project

Create a new Project using a Maven archetype.

mvn archetype:generate -DarchetypeArtifactId=maven-archetype-webapp

groupId: com.test
artifactId: extjs-database-provider

Answer the rest of the questions with defaults “Just hit the enter key”,

cd to the project base folder.

cd extjs-database-provider

Since this is a web project maven2 does not create the java source folder.

Create missing folders now.
mkdir -p src/main/java/com/test
mkdir -p src/main/resources/com/test
mkdir -p src/main/webapp/WEB-INF/jsp

Modify the project Configuration

The pom.xml file should look something like this.

pom.xml

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.test</groupId>
  <artifactId>extjs-database-provider</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>extjs-database-provider Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
        <dependency>
            <groupId>org.json</groupId>
            <artifactId>json</artifactId>
            <version>20090211</version>
        </dependency>
        <dependency>
            <groupId>org.apache.ibatis</groupId>
            <artifactId>ibatis-sqlmap</artifactId>
            <version>2.3.4.726</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring</artifactId>
            <version>2.5.6</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>2.5.6</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.14</version>
        </dependency>
        <dependency>
            <groupId>org.hsqldb</groupId>
            <artifactId>hsqldb</artifactId>
            <version>1.8.0.10</version>
        </dependency>
        <dependency>
            <groupId>org.apache.ddlutils</groupId>
            <artifactId>ddlutils</artifactId>
            <version>1.0</version>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.0</version>
            <scope>provided</scope>
        </dependency>

  </dependencies>
  <build>
    <finalName>extjs-database-provider</finalName>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>2.0.2</version>
                <configuration>
                    <source>1.6</source>
                    <target>1.6</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.mortbay.jetty</groupId>
                <artifactId>jetty-maven-plugin</artifactId>
				<version>7.0.0.v20091005</version>
                <configuration>
                    <scanIntervalSeconds>2</scanIntervalSeconds>
                    <webAppConfig>
                        <contextPath>/dbProvider</contextPath>
                    </webAppConfig>
                    <stopKey>s</stopKey>
                </configuration>
            </plugin>
        </plugins>
  </build>
</project>

RESTful Provider

The following snippit of code is central to the topic of this blog. It extends the Ext.state.Provider class and implements additional functionality that allows changes to preferences to be sent to the server side.

src/main/webapp/RESTfulProvider.js

/**
 * Provider implementation that stores preferences using a 
 * RESTful Resource.
 */
Ext.state.RESTfulProvider = Ext.extend(Ext.state.Provider, {

    constructor : function(config){ 
        Ext.state.RESTfulProvider.superclass.constructor.call(this);
        this.url = '/';        
        Ext.apply(this, config);
        this.state = this.readPreferenceDB(this);
    },
    set : function(name, value){
    	if(typeof value == "undefined" || value === null) {
    		this.clear(name);
    		return;
    	}
    	this.setPreference(name, value);
	    Ext.state.RESTfulProvider.superclass.set.call(this, name, value);	       		
    },
    clear : function(name){
    	this.clearPreference(name);
        Ext.state.RESTfulProvider.superclass.clear.call(this, name);
    },
    readPreferenceDB : function(provider){
		var xmlhttp = {};
		if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safari
		  xmlhttp=new XMLHttpRequest();
		} else { // code for IE6, IE5
			xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
		}
		xmlhttp.open("GET",this.url,false); // make a synchronous request
		xmlhttp.send(null);
		
		var prefs = {};
		if(xmlhttp.status == 200) {
			console.log("response Text: " + xmlhttp.responseText);
			var responseObjArr = Ext.util.JSON.decode(xmlhttp.responseText);			

			// do null checks.
	    	if(typeof responseObjArr == "undefined" || responseObjArr === null) {
	    		return prefs;	    		
	    	}
			var size = responseObjArr.length;
			
			for(var i=0; i<size;i++) {
				var responseObj = responseObjArr[i]; 
				for(key in responseObj) {				
					var value = responseObj[key];
					console.log("key: " + key + ", value: " + value);
					prefs[key] = this.decodeValue(value);
				}				
			}
		} else {
			// there was an error
		}


		return prefs;
    },
    setPreference : function(name, value) {
    	var restfulProvider = this;
	    Ext.Ajax.request({ // makes an async request
	       url: this.url,
	       method: 'POST',
	       params: 'name=' + escape(name) + '&value=' + escape(this.encodeValue(value)),
	       success: function(response, options) { // call back for the async request
	       		console.log('value asynchronously set in database' + response.responseText);
	       },
	       failure: function(response, options) { 
	       		//console.log('defaulting to cookies.');
	       }      
	    });    	
    },
    clearPreference : function(name){
	    Ext.Ajax.request({ // makes an async request
		       url: this.url + "/" + escape(name),
		       method: 'DELETE',
		       success: function(response, options) { // call back for the async request
		       		console.log('value asynchronously deleted in database' + response.responseText);
		       },
		       failure: function(response, options) { 
		       		//console.log('defaulting to cookies.');
		       }      
		    });    	    	
    }
});

Database Setup and Configuration

The following configuration file is used by Apache DDLUtils to generate apply the schema changes each time the application restarts. Changes are made incrementally and every attempt is made to preserve the data. For more information about DDLUtils please see my other tutorial.

src/main/resources/ddl.xml

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
 
<database name="preferencedb">
	<table name="ExtState">
		<column name="domain" type="VARCHAR" size="50" required="true"	primaryKey="true" />
		<column name="userName" type="VARCHAR" size="50" required="true" primaryKey="true" />
		<column name="name" type="VARCHAR" size="20" required="true" primaryKey="true"/>
		<!--Store timestamp as well???-->
		<column name="timestamp" type="TIMESTAMP" />
		<column name="value" type="VARCHAR" size="255" />
		<index name="ExtState_x">
			<index-column name="domain" />
			<index-column name="userName" />
		</index>
	</table>
</database>

Since we are working with HSQLDB, it is critical that we gracefully shut-down the database when the application stops. Otherwise the data that was cached in memory will be lost.

We will use a “ServletContextListener” to listen for the application shut-down event. This will allow us to execute code to graceful shut-down the HSQLDB database.
src/main/java/com/test/DBLifecycleContextListener.java

package com.test;
 
import java.io.InputStreamReader;
 
import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.sql.DataSource;
 
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.io.DatabaseIO;
import org.apache.ddlutils.model.Database;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.web.context.WebApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;
 
/**
 * This listener initializes or gracefully shuts down the database based on
 * events from the web application.
 */
public class DBLifecycleContextListener implements ServletContextListener {
    private DataSource dataSource = null;
    private WebApplicationContext springContext = null;
 
    public void contextDestroyed(ServletContextEvent event) {
        SimpleJdbcTemplate template = new SimpleJdbcTemplate(dataSource);
        template.update("SHUTDOWN;");
        System.out.println("Database Successfully Shutdown.");
    }
 
    public void contextInitialized(ServletContextEvent event) {
        springContext = WebApplicationContextUtils
                .getWebApplicationContext(event.getServletContext());
        dataSource = (DataSource) springContext.getBean("dataSource");
 
        Platform platform = PlatformFactory
                .createNewPlatformInstance(dataSource);
 
        Database database = new DatabaseIO().read(new InputStreamReader(
                getClass().getResourceAsStream("/ddl.xml")));
 
        platform.alterTables(database, false);
    }
}

Spring Application

The Spring application consists of a

  • ExtState – POJO that represents an individual state item
  • ExtStateModel – Service Interface
  • ExtStateModelImpl – Implementation of the Service
  • ExtStateDataManager – DataManager Interface
  • ExtStateDataManagerImpl – DataManager Implementation

The majority of the configuration is Annotation Based. For more information on annotation based spring configuration please see my other tutorial “using annotations in the spring framework“. Also checkout the following: “hello world spring MVC with annotations“.

ExtState

src/main/java/com/test/ExtState.java

package com.test;

import java.util.Date;

/**
 * This class represents an individual state of a ExtJS component.
 */
public class ExtState {
	private String domain;
	private String userName;
	private String name;
	private String value;
	private Date timestamp = new Date(); // default current timestamp
	
	public String getDomain() {
		return domain;
	}
	public void setDomain(String domain) {
		this.domain = domain;
	}
	public String getUserName() {
		return userName;
	}
	public void setUserName(String userName) {
		this.userName = userName;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getValue() {
		return value;
	}
	public void setValue(String value) {
		this.value = value;
	}
	public Date getTimestamp() {
		return timestamp;
	}
	public void setTimestamp(Date timestamp) {
		this.timestamp = timestamp;
	}	
}

Spring Model classes

The following interface and implementation classes are currently being used a pass-thru to the data manager. Its not adding any value to the application but once we start using Spring Transactions we will be glad we did it this way.

src/main/java/com/test/ExtStateModel.java

package com.test;

import java.util.List;

/**
 * Manages the CRUD operations for the ExtJS State for a given domain user.
 */
public interface ExtStateModel {
	    public ExtState getOne(String domain, String userName, String name);
	 
	    public List<ExtState> get(String domain, String userName);

	    public ExtState post(ExtState extState);
	 
	    public void put(ExtState extState);
	 
		public void delete(ExtState extState);	    	
}

src/main/java/com/test/ExtStateModelImpl.java

package com.test;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.stereotype.Service;

@Service
public class ExtStateModelImpl implements ExtStateModel {

	private ExtStateDataManager extStateDataManager;

	
	private void validateInput(ExtState extState) {
		if (extState.getDomain() == null
				|| "".equals(extState.getDomain().trim())) {
			throw new IllegalArgumentException("Domain is required.");
		}
		if (extState.getUserName() == null
				|| "".equals(extState.getUserName().trim())) {
			throw new IllegalArgumentException("Username is required.");
		}
		if (extState.getName() == null || "".equals(extState.getName().trim())) {
			throw new IllegalArgumentException("name is required.");
		}		
	}
	
	@Override
	public ExtState post(ExtState extState) {
		validateInput(extState);
		ExtState extStateResult = extStateDataManager.read(extState.getDomain(), extState.getUserName(),
				extState.getName());
		
		if(extStateResult==null) { // create one
			extState = extStateDataManager.create(extState);			
		} else {
			extStateDataManager.update(extState);			
		}

		return extState;
	}

	@Override
	public void delete(ExtState extState) {
		validateInput(extState);
		extStateDataManager.delete(extState);
	}

	@Override
	public List<ExtState> get(String domain, String userName) {
		return extStateDataManager.readAll(domain, userName);
	}

	@Required
	@Autowired
	public void setExtStateDataManager(ExtStateDataManager extStateDataManager) {
		this.extStateDataManager = extStateDataManager;
	}

	@Override
	public ExtState getOne(String domain, String userName, String name) {
		return extStateDataManager.read(domain, userName, name);
	}

	@Override
	public void put(ExtState extState) {
		post(extState);
	}

}

src/main/java/com/test/ExtStateDataManager.java

package com.test;

import java.util.List;

public interface ExtStateDataManager {

	public ExtState create(ExtState item);

	public ExtState read(String domain, String userName, String name);

	public List<ExtState> readAll(String domain, String userName);

	public void update(ExtState extState);

	public void delete(ExtState extState);
}

src/main/java/com/test/ExtStateDataManagerImpl.java

package com.test;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.orm.ibatis.SqlMapClientTemplate;
import org.springframework.stereotype.Repository;

@Repository("extStateDataManager")
public class ExtStateDataManagerImpl implements ExtStateDataManager {

    private SqlMapClientTemplate sqlMapClientTemplate;

    public SqlMapClientTemplate getSqlMapClientTemplate() {
        return sqlMapClientTemplate;
    }
 
    @Autowired
    @Required
    public void setSqlMapClientTemplate(SqlMapClientTemplate sqlMapClientTemplate) {
        this.sqlMapClientTemplate = sqlMapClientTemplate;
    }
    
	@Override
	public ExtState create(ExtState extState) {
        getSqlMapClientTemplate().insert("extStateInsert", extState);
        return extState;
	}

	@Override
	public void delete(ExtState extState) {
        if(extState != null) {
            getSqlMapClientTemplate().delete("extStateDelete", extState);
        }

	}

	@Override
	public ExtState read(String domain, String userName, String name) {
		ExtState extState = new ExtState();
		extState.setDomain(domain);
		extState.setUserName(userName);
		extState.setName(name);
		
        return (ExtState)getSqlMapClientTemplate().queryForObject("extStateInfo", extState);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ExtState> readAll(String domain, String userName) {
        List<ExtState> list = null;
        
		ExtState extState = new ExtState();
		extState.setDomain(domain);
		extState.setUserName(userName);
		
        list = getSqlMapClientTemplate().queryForList("allExtState", extState, 0, 1000); 
        return list;
	}

	@Override
	public void update(ExtState extState) {
        getSqlMapClientTemplate().update("extStateUpdate", extState);

	}	

}

Spring Configuration

Most of the spring configuration was done using Java Annotations, therefore this file is not too large.

src/main/resources/applicationContext.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:context="http://www.springframework.org/schema/context"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
 
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
 
http://www.springframework.org/schema/context
 
http://www.springframework.org/schema/context/spring-context-2.5.xsd">
 
	<context:annotation-config/>
    <context:component-scan base-package="com.test"/>

	<bean name="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
        <property name="url" value="jdbc:hsqldb:file:src/main/webapp/WEB-INF/db/testdb"/>
        <property name="username" value="sa"/>
        <property name="password" value=""/>
	</bean>
	
<!-- Used for Ibatis -->
    <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
        <property name="configLocation" value="/WEB-INF/mapper-config.xml"/>
        <property name="dataSource" ref="dataSource"/>
        <property name="useTransactionAwareDataSource" value="true"/>
    </bean>
    <bean id="sqlMapClientTemplate" class="org.springframework.orm.ibatis.SqlMapClientTemplate">
        <property name="sqlMapClient" ref="sqlMapClient"/>
    </bean>
    	
</beans>

Integration with IBATIS

The following needs to be defined in the spring configuration. The SQLMapClientTemplate is the primary class that will be used by our data-mangers to query the database thru SQL statements and stored procedure calls. It needs a reference to sqlMapClient that we just created.

The following is the ibatis main configuration file. Since we are using spring framework there is not much configuration that you need to put in here. All that is required is that ibatis knows where the xml files for all the Data Managers are located.

For more information on ibatis please see my other tutorial “ibatis 2.3 spring 2.5 hello world

src/main/webapp/WEB-INF/mapper-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
        PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
        "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
<sqlMapConfig>
    <sqlMap resource="com/test/extStateDataManager.xml"/>
</sqlMapConfig>

src/main/resources/com/test/extStateDataManager.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
        PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
        "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="ExtState">
 
<typeAlias alias="ExtState" type="com.test.ExtState" />
 
    <select id="allExtState" resultClass="ExtState" parameterClass="ExtState">
        SELECT domain as domain,
        	userName as userName,
        	name as name,
        	value as value,
        	timestamp as timestamp
        FROM ExtState where domain = #domain# and userName = #userName#;
    </select>
    <select id="extStateInfo" parameterClass="ExtState" resultClass="ExtState">
        SELECT domain as domain,
        	userName as userName,
        	name as name,
        	value as value,
        	timestamp as timestamp
        FROM ExtState
        WHERE domain = #domain# and userName = #userName# and name = #name#;
    </select>
  
    <insert id="extStateInsert" parameterClass="ExtState">
        INSERT INTO ExtState (domain, userName, name, value, timestamp)
        VALUES ( #domain#, #userName#, #name#, #value#, #timestamp#);
    </insert>
    <update id="extStateUpdate" parameterClass="ExtState">
        UPDATE ExtState SET domain = #domain#, userName = #userName#, 
        	name = #name#, value = #value#, timestamp = #timestamp#
        WHERE domain = #domain# and userName = #userName# and name = #name#;
    </update>
    <delete id="extStateDelete" parameterClass="ExtState">
        DELETE FROM ExtState 
        WHERE domain = #domain# and userName = #userName# and name = #name#;
    </delete>
</sqlMap>

Servlet

The following servlet takes requests to save preferences from the Restful State Provider and calls the data-managers to save it to the database.

src/main/java/com/test/DBPrefServlet.java

package com.test;

import java.io.IOException;
import java.io.PrintWriter;
import java.security.Principal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.springframework.context.ApplicationContext;
import org.springframework.web.context.support.WebApplicationContextUtils;

public class DBPrefServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	private ExtStateModel extStateModel;

	// domain is application wide.
	private static final String domain = "com.test";

	
	@Override
	public void init(ServletConfig config) throws ServletException {
		ApplicationContext context = WebApplicationContextUtils
				.getWebApplicationContext(config.getServletContext());
		extStateModel = (ExtStateModel) context.getBean("extStateModelImpl");
		super.init(config);
	}
	
	@Override
	public void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {

		Principal userBean = (Principal)req.getSession().getAttribute("userBean");
		if (userBean == null || userBean.getName() == null
				|| "".equals(userBean.getName().trim())) {
			// not authorized return HTTP 401      
			resp.sendError(HttpServletResponse.SC_UNAUTHORIZED);
			return;
		}

		String userName = userBean.getName();

		List<ExtState> extStateList = new ArrayList<ExtState>(0);

		final String pattern = "/*";  
		String[] names = { "name" };
		Map<String, String> pathMap = PathUtil.getPathVariables(pattern, names,
		req.getPathInfo());
		String name = pathMap.get("name");
		 
		if(name == null || "".equals(name.trim())) {
			// get all results
			extStateList = extStateModel.get(domain, userName);    	  
		} else {
			// get results for provided name 
			ExtState extState = extStateModel.getOne(domain, userName, name);
			if(extState != null) {
				extStateList = Collections.singletonList(extState);
			}
		}
		 
		if(extStateList == null || extStateList.size() == 0) {
			// nothing found return a HTTP 404
			resp.sendError(HttpServletResponse.SC_NOT_FOUND,
				"Ext preference not found for the currently logged in user.");
		} 
		else { // this is for debugging and other api usage.
			JSONArray jsonArray = new JSONArray();
			for(ExtState extState : extStateList) {
				JSONObject jsonObject = new JSONObject();
				try {
					jsonObject.put(extState.getName(), extState.getValue());
				} catch (JSONException e) {
					throw new RuntimeException(e);
				}
				jsonArray.put(jsonObject);
			}
			PrintWriter writer = resp.getWriter();
			writer.write(jsonArray.toString());
		}
	}
	
	@Override
	public void doPut(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {

		
		Principal userBean = (Principal)req.getSession().getAttribute("userBean");
		if (userBean == null || userBean.getName() == null
				|| "".equals(userBean.getName().trim())) {
			resp.sendError(HttpServletResponse.SC_UNAUTHORIZED);
			return;
		}

		String userName = userBean.getName();
		
		String name = req.getParameter("name");
		String value = req.getParameter("value");
		
		//TODO: validate name and value!		
		ExtState extState = new ExtState();
		
		extState.setDomain(domain);
		extState.setUserName(userName);
		extState.setName(name);
		extState.setValue(value);
		
		extStateModel.put(extState);
		
		PrintWriter writer = resp.getWriter();
		writer.write(new JSONObject(extState).toString());			

	}
	
	@Override
	public void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		doPut(req, resp);
	}
	
	@Override
	public void doDelete(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {

		Principal userBean = (Principal)req.getSession().getAttribute("userBean");
		if (userBean == null || userBean.getName() == null
				|| "".equals(userBean.getName().trim())) {
			resp.sendError(HttpServletResponse.SC_UNAUTHORIZED);
			return;
		}

		String userName = userBean.getName();

		final String pattern = "/*";  
		String[] names = { "name" };
		
		Map<String, String> pathMap = PathUtil.getPathVariables(pattern, names,
		req.getPathInfo());
		String name = pathMap.get("name");

		if(name==null || "".equals(name.trim())) {
			resp.sendError(HttpServletResponse.SC_BAD_REQUEST, "name is required");
			return;
		}   
		ExtState extState = new ExtState();
		extState.setDomain(domain);
		extState.setUserName(userName);
		extState.setName(name);
		
		extStateModel.delete(extState);				
	}
}

Login Servlet

This servlet is used to log the user into the system and create a HttpSession.

src/main/java/com/test/LoginServlet.java

package com.test;

import java.io.IOException;
import java.security.Principal;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

public class LoginServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	@Override
	public void doGet(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		
		final String pattern = "/*";  
		String[] names = { "userName" };
		Map<String, String> pathMap = PathUtil.getPathVariables(pattern, names,
		req.getPathInfo());
		String userName = pathMap.get("userName");

		if(userName==null || "".equals(userName.trim())) {
			resp.sendError(HttpServletResponse.SC_BAD_REQUEST, "");
			return;
		}
		Principal userBean = (Principal)req.getSession().getAttribute("userBean");
		if(userBean == null || !userName.equals(userBean.getName())) {
			resp.sendError(HttpServletResponse.SC_UNAUTHORIZED);
			return;			
		}		
	}
	
	@Override
	public void doPost(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		
		String userName = req.getParameter("userName");
		String password = req.getParameter("password");		
		// TODO: validate input...
		
		if(!"tiger".equals(password)) {
			resp.sendError(HttpServletResponse.SC_UNAUTHORIZED);
			return;
		} 
		
		// for testing purposes allow any username 
		// to be set if password is correct.
		req.getSession().setAttribute("userBean", new UserBean(userName));
		System.out.println("set the userbean in session");
	}
	
	@Override
	public void doDelete(HttpServletRequest req, HttpServletResponse resp)
			throws ServletException, IOException {
		req.getSession().removeAttribute("userBean");
	}
}

src/main/java/com/test/UserBean.java

package com.test;

import java.security.Principal;

public class UserBean implements Principal {

	private String name;
	
	public UserBean() {
		super();
	}
	public UserBean(String name) {
		this.setName(name);
	}
	
	@Override
	public String getName() {
		return name;
	}
	
	public void setName(String name) {
		this.name = name;
	}
}

PathUtil

For more information on this Util class click here.

src/main/java/com/test/PathUtil.java

package com.test;
 
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
 
/**
 * Helper class to allow URL path's to be parsed into
 * EL Expression type tokens.
 */
public class PathUtil {
    public static final Map<String, String> getPathVariables(String patternStr,
            String[] names, String path) {
        String regExPattern = patternStr.replace("*", "(.*)");
 
        Map<String, String> tokenMap = new HashMap<String, String>();
 
        if(path==null || "".equals(path.trim())) {
        	return tokenMap;
        }
        
        Pattern p = Pattern.compile(regExPattern);
 
        Matcher matcher = p.matcher(path);
 
        if (matcher.find()) {
            // Get all groups for this match
            for (int i = 0; i <= matcher.groupCount(); i++) {
                String groupStr = matcher.group(i);
                if (i != 0) {
                    tokenMap.put(names[i - 1], groupStr);
                }
            }
        }
        return tokenMap;
    }
}

Main Page

The following is the main page of the application. It sets up 2 grid components with 2 different stores. It hooks those components up to the Restful provider.

src/main/webapp/index.jsp

<html>
<head>

<link rel="stylesheet" type="text/css"
	href="http://dev.sencha.com/deploy/dev/resources/css/ext-all.css" />

<script type="text/javascript"
	src="http://dev.sencha.com/deploy/dev/adapter/ext/ext-base-debug.js">
</script>
<script type="text/javascript"
	src="http://dev.sencha.com/deploy/dev/ext-all-debug.js">
</script>


<script type="text/javascript">
Ext.Ajax.on('requestcomplete', function(conn, response, options) {
	var responsediv = Ext.get('responsediv');
	responsediv.dom.innerHTML = response.responseText;
}, this);
Ext.Ajax.on('requestexception', function(conn, response, options) {
	var responsediv = Ext.get('responsediv');
	responsediv.dom.innerHTML = response.responseText;
}, this);

var submitForm = function(formid, resource, method) {
	Ext.Ajax.request({
	   url: resource,
	   method: method,
	   form: formid,
	   params: ''
	});	
};
</script>
<script type="text/javascript" src='RESTfulProvider.js'></script>
     
<script type="text/javascript">
function buildWindow() { 
	var arrayData = [
		['Jay Garcia',    'MD'],
		['Aaron Baker',   'VA'],
		['Susan Smith',   'DC'],
		['Mary Stein',    'DE'],
		['Bryan Shanley', 'NJ'],
		['Nyri Selgado',  'CA']
	];
	 
	var store = new Ext.data.ArrayStore({
	  data   : arrayData,
	  fields : ['fullName', 'state']
	});
	
	var store2 = new Ext.data.ArrayStore({
	  data   : arrayData,
	  fields : ['fullName', 'state']
	});
	 
	var cm = new Ext.grid.ColumnModel([{
        header    : 'Full Name',
        sortable  : true,
        dataIndex : 'fullName'
    },
    {
	    header    : 'State',
		renderer : function(value, cell) {
	    	return '<a href="#">' + value + '</a>'
		},
        dataIndex : 'state'
    }
]);
	var cm2 = new Ext.grid.ColumnModel([{
        header    : 'Full Name',
        sortable  : true,
        dataIndex : 'fullName'
    },
    {
	    header    : 'State',
		renderer : function(value, cell) {
	    	return '<a href="#">' + value + '</a>'
		},
        dataIndex : 'state'
    }
]);
	
	var dbProvider = new Ext.state.RESTfulProvider({url : 'extState/'});
	
	Ext.state.Manager.setProvider(dbProvider); 
	
	var grid = new Ext.grid.GridPanel({
		title		: 'Stateful Grid',
		renderTo	: 'grid-panel',
		autoHeight	: true,
		width		: 250,
		stateful	: true,
		stateId		: 'my_grid_panel',                       
		store		: store,
		colModel	: cm2
	});
	
	var grid2 = new Ext.grid.GridPanel({
		title		: 'Stateful Grid2',
		renderTo	: 'grid-panel2',
		autoHeight	: true,
		width		: 250,
		stateful	: true,
		stateId		: 'my_grid_panel2',                       
		store		: store2,
		colModel	: cm
	});
}
 
Ext.onReady(buildWindow);    
</script>
</head>
<body>

<h3>RESTful State Provider Example</h3>
<br></br>
This page stores grid preferences in a HyperSQL database.
<br/><br/>
<strong>Instructions:</strong>
<ol>
<li>1. Login by entering a username.</li>
<li>2. Click on the first Name. Or remove a column.</li>
<li>3. Refresh the page. Preferences should maintain</li>
<li>4. Logout and login as another user.</li>
<li>5. Repeat steps 2, 3</li>
<li>6. Login as the first user.</li>
<li>7. Preferences should have remained the same.</li>
<li>8. click GET in the extState Manager form to inspect database values</li>
<li>9. Use FireBug to see Ajax Communication</li>
<li>10. Shutdown the server and view the database /WEB-INF/db/testdb.script</li>
</ol>

<br/>

<div style="float:left;padding-right:50px;" id="grid-panel"></div>
<div style="float:left;padding-right:50px;" id="grid-panel2"></div>

<h2>Login Form</h2>

<form id='form1'>
<label>userName</label><input name="userName"
	type="text" /><br />
<label>password</label><input name="password"
	type="password" value="tiger"/><br />

<input value="Check Status" type="button" onclick="submitForm('', 'login/' + escape(this.form.userName.value), 'GET');" />
<input value="Login" type="button" onclick="submitForm('form1', 'login', 'POST');" /> 
<input value="Logout" type="button" onclick="submitForm('form1', 'login/', 'DELETE');" />
</form>

<h3>extState Manager</h3>
<form id='form2'><label>name</label><input name="name"
	type="text" /><br />
<label>value</label><input name="value" type="text" /><br />
<input value="GET" type="button"
	onclick="submitForm('', 'extState/' + escape(this.form.name.value), 'GET');" />
<input value="POST" type="button"
	onclick="submitForm('form2', 'extState', 'POST');" /> 
<input
	value="DELETE" type="button"
	onclick="submitForm('form2', 'extState/' + escape(this.form.name.value), 'DELETE');" />
</form>

<h3>Response:</h3>
<div id="responsediv"></div>

</body>
</html>

Web Application Configuration

src/main/webapp/WEB-INF/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>Archetype Created Web Application</display-name>

	<context-param>
		<param-name>contextConfigLocation</param-name>
		<param-value>classpath:applicationContext.xml</param-value>
	</context-param>

	<listener>
		<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
	</listener>

	<listener>
		<listener-class>com.test.DBLifecycleContextListener</listener-class>
	</listener>

    <servlet>
        <servlet-name>dbPrefServlet</servlet-name>
        <servlet-class>com.test.DBPrefServlet</servlet-class>
    </servlet>
    <servlet>
        <servlet-name>loginServlet</servlet-name>
        <servlet-class>com.test.LoginServlet</servlet-class>
    </servlet>
 
    <servlet-mapping>
        <servlet-name>loginServlet</servlet-name>
        <url-pattern>/login/*</url-pattern>
    </servlet-mapping>
 
    <servlet-mapping>
        <servlet-name>dbPrefServlet</servlet-name>
        <url-pattern>/extState/*</url-pattern>
    </servlet-mapping>

</web-app>

Test the application

Start Jetty Servlet Engine

mvn clean compile jetty:run

Test the application

Navigate to: http://localhost:8080/dbProvider/ and enter some data

The page will not store preferences unless you login. Enter an ID and click login. You can also switch to another user by logging out and logging in as another user. No need to provide password since it is already pre-filled. Switching between users will allow you to test the ability to customize the components per-user basis.

Test the following as well.

  1. then shutting down jetty – CTRL-C
  2. verify the data survived in the src/main/webapp/WEB-INF/db directory.

Creating a WAR file

If you want to run the application in another environment you can create a war file and drop it into a server of your choice. This method will also package up any runtime dependencies and put them in the /WEB-INF/lib folder of the WAR file.

mvn clean compile package

The war file should appear in the “target/” folder.

26
Apr
11

ExtJS Download

Since ExtJS does not provide a public Subversion repository the following site has archived versions of the extJS framework.

http://code.google.com/p/extjs-public/

At the above location you will find:

Just type the following to get the code in a directory you want to download the framework to.

svn co <link from above>
26
Apr
11

ExtJS not working on your page?

If you are experiencing the following issues today:

  1. Ext.grid.ColumnModel is not a constructor
  2. You are using a ServerProxy but have not supplied it with a url.

Chances are that you have pointed your script tags directly to the Sencha site. (http://dev.sencha.com/deploy/&#8230;). Sencha has recently updated their code located at “http://dev.sencha.com/deploy/dev&#8221; to version 4. Much of the API is not compatible with the earlier versions.

The fix for this is to download the extJS library and scripts to your own server and change the URL to reference them locally.

23
Apr
11

Nested JSON

This page describes the process of performing CRUD operations on Java Beans that have Nested Properties using ExtJS.

Requirements

The Item bean contains a length, width, and height property. We will move those attributes to a new bean called Dimension and add a dimension attribute to the Item class.

This will result in the editor Grid submitting the following xml in the Request Body:

<?xml version="1.0" encoding="UTF-8"?>
	<item>
		<itemId>5</itemId>
		<name>tes</name>
		<description>111111</description>
		<stockQty>2</stockQty>
		<color>blue</color>
		<dimension.length>1</dimension.length>
		<dimension.width>1</dimension.width>
		<dimension.height>1</dimension.height>
	</item>

The following changes need to be done:

  1. Create a new Dimension class, modify the Item class to use it.
  2. Modify the IBATIS sql map xml files
  3. Modify ExtJS JsonReader to read the nested information

src/main/java/com/test/Dimension.java

package com.test;

import java.math.BigDecimal;

public class Dimension {
    private BigDecimal length;
    private BigDecimal width;
    private BigDecimal height;
    
    
	public BigDecimal getLength() {
		return length;
	}
	public void setLength(BigDecimal length) {
		this.length = length;
	}
	public BigDecimal getWidth() {
		return width;
	}
	public void setWidth(BigDecimal width) {
		this.width = width;
	}
	public BigDecimal getHeight() {
		return height;
	}
	public void setHeight(BigDecimal height) {
		this.height = height;
	}
}

Item

Replace the implementation of Item.java with the following.

src/main/java/com/test/Item.java

package com.test;
 
import java.math.BigDecimal;
import javax.xml.bind.annotation.XmlRootElement;
 
/**
 * Represents an item in inventory or in a shopping cart.
 */
@XmlRootElement
public class Item {
    private Integer itemId;
    private String oemId;
    private String name;
    private String description;
    private String imageURL;
    private String color;
    private BigDecimal price;
    private Dimension dimension = new Dimension();
     
    /**
     * Weight in grams
     */
    private BigDecimal weight;
    /**
     * Quantity in stock.
     */
    private Integer stockQty;
 
    public Item() {
        super();
    }
 
    public Item(Integer id, String name) {
        this.name = name;
        this.itemId = id;
    }
 
    public Integer getItemId() {
        return itemId;
    }
    public void setItemId(Integer itemId) {
        this.itemId = itemId;
    }
    public String getOemId() {
        return oemId;
    }
    public void setOemId(String oemId) {
        this.oemId = oemId;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public String getColor() {
        return color;
    }
    public void setColor(String color) {
        this.color = color;
    }
    public String getImageURL() {
        return imageURL;
    }
    public void setImageURL(String imageURL) {
        this.imageURL = imageURL;
    }
    public BigDecimal getPrice() {
        return price;
    }
    public void setPrice(BigDecimal price) {
        this.price = price;
    }

    public BigDecimal getWeight() {
        return weight;
    }
    public void setWeight(BigDecimal weight) {
        this.weight = weight;
    }
 
    public Integer getStockQty() {
        return stockQty;
    }
 
    public void setStockQty(Integer stockQty) {
        this.stockQty = stockQty;
    }

    public void setDimension(Dimension dimension) {
		this.dimension = dimension;
    }

    public Dimension getDimension() {
		return dimension;
    }
}

IBATIS SQL Mapper

Change the SQL mapper configuration file to look like this…

src/main/resources/com/test/ItemDataManager.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
        PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
        "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="Item">
 
<typeAlias alias="Item" type="com.test.Item" />

    <resultMap id="itemResultMap" class="Item">
		<result property="itemId" column="itemId"/>
		<result property="oemId" column="oemId"/>
		<result property="name" column="name"/>
		<result property="description" column="description"/>
		<result property="color" column="color"/>
		<result property="imageURL" column="imageURL"/>
		<result property="price" column="price"/>
		<result property="dimension.length" column="length"/>
		<result property="dimension.width" column="width"/>
		<result property="dimension.height" column="height"/>
		<result property="weight" column="weight"/>
		<result property="stockQty" column="stockQty"/>
    </resultMap>

 
    <select id="allItems" resultMap="itemResultMap" >
        select item_id as itemId,
        oem_id as oemId,
        item_x as name,
        description as description,
        color as color,
        image_url as imageURL,
        price as price,
        length as length,
        width as width,
        height as height,
        weight as weight,
        stock_qty as stockQty
        from item;
    </select>
    <select id="getItemInfo" parameterClass="int" resultMap="itemResultMap" >
        select item_id as itemId,
        oem_id as oemId,
        item_x as name,
        description as description,
        color as color,
        image_url as imageURL,
        price as price,
        length as length,
        width as width,
        height as height,
        weight as weight,
        stock_qty as stockQty
        from item where item_id = #id#;
    </select>
 
    <insert id="itemInsert" parameterClass="Item">
        insert into item (oem_id,
            item_x,
            description,
            color,
            image_url,
            price,
            length,
            width,
            height,
            weight,
            stock_qty)
        values ( #oemId#, #name#, #description#, #color#,
            #imageURL#, #price#, #dimension.length#, #dimension.width#,
            #dimension.height#, #weight#, #stockQty# );
        <selectKey resultClass="int" >
            <!-- HSQLDB -->
            CALL IDENTITY();
            <!-- MySQL -->
            <!-- SELECT LAST_INSERT_ID(); -->
        </selectKey>
    </insert>
    <update id="updateItem" parameterClass="Item">
        update item set oem_id = #oemId#, item_x = #name#, description=#description#,
            color = #color#, image_url = #imageURL#, price = #price#, length = #dimension.length#,
            width = #dimension.width#, height = #dimension.height#, weight = #weight#, stock_qty = #stockQty#
        where item_id = #itemId#;
    </update>
    <delete id="deleteItem" parameterClass="java.lang.Integer">
        delete from item where item_id = #itemId#;
    </delete>
</sqlMap>

Change the Grid

MyApp={};
MyApp.getContext = function() {
	var base = document.getElementsByTagName('base')[0];
	if (base && base.href && (base.href.length > 0)) {
		base = base.href;
	} else {
		base = document.URL;
	}
	return base.substr(0, base.indexOf("/", base.indexOf("/", base.indexOf("//") + 2) + 1));
};

var App = new Ext.App({});
 
var proxy = new Ext.data.HttpProxy({
	url: MyApp.getContext() + '/appdata/itemresource'
});

var reader = new Ext.data.JsonReader({
    totalProperty: 'total',
    successProperty: 'success',
    idProperty: 'itemId',
    root: 'data',
    record: 'item',
    messageProperty: 'message',  // <-- New "messageProperty" meta-data
    fields : [
              {name: 'itemId'},
              {name: 'name', allowBlank: false},
              {name: 'description', allowBlank: true},
              {name: 'stockQty', allowBlank: true},
              {name: 'color', allowBlank: true},
// The 'mapping' field allows binding to complex nested JSON objects              
              {name: 'length', allowBlank: true, mapping: 'dimension.length'},              
              {name: 'width', allowBlank: true, mapping: 'dimension.width'},              
              {name: 'height', allowBlank: true, mapping: 'dimension.height'}              
    ]
});

// The new DataWriter component.
var writer = new Ext.data.XmlWriter({
    encode : false,
    writeAllFields : true,
    xmlEncoding : "UTF-8"
});

// Typical Store collecting the Proxy, Reader and Writer together.
var store = new Ext.data.Store({
    id: 'item',
    restful: true,     // <-- This Store is RESTful
    proxy: proxy,
    reader: reader,
    writer: writer    // <-- plug a DataWriter into the store
});

// load the store
store.load();

// The following is necessary to map the Record Fields to the grid columns.
var userColumns =  [
    {header: "Item Id", width: 40, sortable: true, dataIndex: 'itemId'},
    {header: "Item Name", width: 100, sortable: true, dataIndex: 'name', editor: new Ext.form.TextField({})},
    {header: "Description", width: 100, sortable: true, dataIndex: 'description', editor: new Ext.form.TextField({})},
    {header: "Quantity", width: 100, sortable: true, dataIndex: 'stockQty', editor: new Ext.form.TextField({})},
    {header: "Color", width: 100, sortable: true, dataIndex: 'color', editor: new Ext.form.TextField({})},
    {header: "Length", width: 100, sortable: true, 
    	dataIndex: 'length', editor: new Ext.form.TextField({})},
    {header: "Width", width: 100, sortable: true, 
    	dataIndex: 'width', editor: new Ext.form.TextField({})},
    {header: "Height", width: 100, sortable: true, 
    	dataIndex: 'height', editor: new Ext.form.TextField({})}
];

Ext.onReady(function() {
    Ext.QuickTips.init();

    // use RowEditor for editing
    var editor = new Ext.ux.grid.RowEditor({
        saveText: 'Save'
    });

    // Create a typical GridPanel with RowEditor plugin
    var userGrid = new Ext.grid.GridPanel({
        renderTo: 'user-grid',
        iconCls: 'icon-grid',
        frame: true,
        title: 'Fun with RESTful CRUD',
        height: 300,
        store: store,
        plugins: [editor],
        columns : userColumns,
    	enableColumnHide: false,
        tbar: [{
        	id: 'addBtn',
            text: 'Add',
            iconCls: 'silk-add',
            handler: onAdd
        }, '-', {
        	id: 'deleteBtn',
            text: 'Delete',
            iconCls: 'silk-delete',
            handler: onDelete
        }, '-'],
        viewConfig: {
            forceFit: true
        }
    });

    function onAdd(btn, ev) {
        var u = new userGrid.store.recordType({
            first : '',
            last: '',
            email : ''
        });
        editor.stopEditing();
        userGrid.store.insert(0, u);
        editor.startEditing(0);
    }

    // Disable the buttons when editing.
    editor.addListener('beforeedit', function(roweditor, rowIndex){
        Ext.getCmp('addBtn').disable();
        Ext.getCmp('deleteBtn').disable();
    });
    editor.addListener('afteredit', function(roweditor, rowIndex){
        Ext.getCmp('addBtn').enable();
        Ext.getCmp('deleteBtn').enable();
    });
    editor.addListener('canceledit', function(roweditor, rowIndex){
    	// get fresh records from the database.
        userGrid.store.reload({});
        Ext.getCmp('addBtn').enable();
        Ext.getCmp('deleteBtn').enable();
    });     

    function onDelete() {
        var rec = userGrid.getSelectionModel().getSelected();
        if (!rec) {
            return false;
        }
        userGrid.store.remove(rec);
    }
    activateGridValidation(userGrid);
});

Test the application

In the project top folder type in the following command to compile and run the code in jetty servlet engine.

mvn clean compile jetty:run

Navigate to the following URL:
http://localhost:8080/crud/

Modify the application presented in Part II

In this section we will modify the application presented in part II of my posting in this series.

Regular form panel can submit nested bean information. The submission looks like this:

name=4&description=444444&stockQty=4&color=blue&dimension.length=4&dimension.width=4&dimension.height=4

Modify the form.html page to look like this…

src/main/webapp/form.html

<html>
<head>
 
<link rel="stylesheet" type="text/css"
    href="http://dev.sencha.com/deploy/ext-3.3.1/resources/css/ext-all.css"/>
 
<script type="text/javascript"
    src="http://dev.sencha.com/deploy/ext-3.3.1/adapter/ext/ext-base-debug.js">
</script>
<script type="text/javascript"
    src="http://dev.sencha.com/deploy/ext-3.3.1/ext-all-debug.js">
</script>
 
<script type="text/javascript">
 
MyApp={};
MyApp.getContext = function() {
    var base = document.getElementsByTagName('base')[0];
    if (base && base.href && (base.href.length > 0)) {
        base = base.href;
    } else {
        base = document.URL;
    }
    return base.substr(0, base.indexOf("/", base.indexOf("/", base.indexOf("//") + 2) + 1));
};
 
function buildWindow() {
    Ext.QuickTips.init();
    Ext.form.Field.prototype.msgTarget = 'side';
    var bd = Ext.getBody();
 
    var fs = new Ext.FormPanel({
        labelWidth: 75,
        frame: true,
        title:'Form with crazy amount of validation',
        bodyStyle:'padding:5px 5px 0',
        defaults: {width: 230},
        waitMsgTarget: true,
        defaultType: 'textfield',    
 
        items: [{
                fieldLabel: 'Item Name',
                name: 'name'
            },{
                fieldLabel: 'Description',
                name: 'description'
            },{
                fieldLabel: 'Quantity',
                name: 'stockQty'
            }, {
                fieldLabel: 'Color',
                name: 'color'
            }, {
                fieldLabel: 'Length',
                name: 'dimension.length'
		    }, {
		        fieldLabel: 'Width',
		        name: 'dimension.width'
			}, {
			    fieldLabel: 'Height',
			    name: 'dimension.height'
			}
        ],    
 
    });
 
    var onSuccessOrFail = function(form, action) {
        var result = action.result;
 
        if(result.success) {
            Ext.MessageBox.alert('Success', 'Success!');
            userGrid.store.reload({});
        } else { // put code here to handle form validation failure.
        }
 
    }
 
    var submit = fs.addButton({
        text: 'Submit',
        disabled:false,
        handler: function(){
            fs.getForm().submit({
                url:MyApp.getContext() +'/app/itemresource',
                waitMsg:'Submitting Data...',
                submitEmptyText: false,
                success : onSuccessOrFail,
                failure : onSuccessOrFail
            });
        }
    });
 
    fs.render('form-first');
 
    // the following is necessary to display the grid.
 
// The following is necessary to map the Record Fields to the grid columns.
var userColumns =  [
    {header: "Item Id", width: 40, sortable: true, dataIndex: 'itemId'},
    {header: "Item Name", width: 100, sortable: true, dataIndex: 'name', editor: new Ext.form.TextField({})},
    {header: "Description", width: 100, sortable: true, dataIndex: 'description', editor: new Ext.form.TextField({})},
    {header: "Quantity", width: 100, sortable: true, dataIndex: 'stockQty', editor: new Ext.form.TextField({})},
    {header: "Color", width: 100, sortable: true, dataIndex: 'color', editor: new Ext.form.TextField({})},
    {header: "Length", width: 100, sortable: true, 
    	dataIndex: 'length', editor: new Ext.form.TextField({})},
    {header: "Width", width: 100, sortable: true, 
    	dataIndex: 'width', editor: new Ext.form.TextField({})},
    {header: "Height", width: 100, sortable: true, 
    	dataIndex: 'height', editor: new Ext.form.TextField({})}
];
 
    var proxy = new Ext.data.HttpProxy({
        url: MyApp.getContext() + '/app/itemresource'
    });
 
    var reader = new Ext.data.JsonReader({
        totalProperty: 'total',
        successProperty: 'success',
        idProperty: 'itemId',
        root: 'data',
        record: 'item',
        messageProperty: 'message',  // <-- New "messageProperty" meta-data
        fields : [
                  {name: 'itemId'},
                  {name: 'name', allowBlank: false},
                  {name: 'description', allowBlank: true},
                  {name: 'stockQty', allowBlank: true},
              {name: 'color', allowBlank: true},
// The 'mapping' field allows binding to complex nested JSON objects              
              {name: 'length', allowBlank: true, mapping: 'dimension.length'},              
              {name: 'width', allowBlank: true, mapping: 'dimension.width'},              
              {name: 'height', allowBlank: true, mapping: 'dimension.height'}              
        ]
    });
 
    // Typical Store collecting the Proxy, Reader and Writer together.
    var store = new Ext.data.Store({
        id: 'item',
        restful: true,     // <-- This Store is RESTful
        proxy: proxy,
        reader: reader
    });
 // load the store
    store.load();
 
    // Create a typical GridPanel with RowEditor plugin
    var userGrid = new Ext.grid.GridPanel({
        renderTo: 'user-grid',
        iconCls: 'icon-grid',
        frame: true,
        title: 'Records in HyperSQL Database',
        height: 300,
        store: store,
        columns : userColumns,
        enableColumnHide: false,
        viewConfig: {
            forceFit: true
        }
    });
 
}
Ext.onReady(buildWindow);
</script>
</head>
<body>
 
<div class="container" style="width:500px">
    <div id="form-first"></div>
    <div id="user-grid"></div>
</div>
 
</body>
</html>
23
Apr
11

CRUD with Extjs

The content of this page has moved/combined with another page: https://numberformat.wordpress.com/2011/04/07/restful-crud-using-extjs/




Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 75 other followers

May 2017
S M T W T F S
« Mar    
 123456
78910111213
14151617181920
21222324252627
28293031  

Blog Stats

  • 813,721 hits