Posts Tagged ‘hsqldb

09
Feb
12

ALTERing Database Schema Using DDLUtils

This page describes how to use DDLUtils to only prints out alter statements instead of making the changes automatically.

Requirements

Background

My previous article described how to use DDLUtils to automatically make changes to a database. Since we were using an in-memory/temporary database, it wouldn’t be a big deal if DDLUtils made a mistake.

Shortcut: If you have not implemented the “In memory database” in the requirements section and you have your own database. Then you can skip to the short cut section below. the return here.

DDLUtils has functionality to fetch the current DDL and dump it into an XML file. Once we modify the xml we can have DDLUtils compare the xml against the current database and only print “ALTER” statements to the screen. We can take those alter statements and manually run them against the database.

Create the Java Code

The following class is designed to be run from the command line. You can wrap it into a shell script. Take some time to review the java source below. It is pretty much self explanatory. Please be sure to name your xml file different from the prior project. In the example below its called “ddl-draft.xml”.

vi src/main/java/com/test/DDLUtils.java

package com.test;

import java.io.InputStreamReader;
import java.io.StringWriter;

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.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 * <p>
 * Dumps or generates schema information for the batch project. This class is
 * only designed to output information. Although the framework is capabile, It
 * should not make any alterations to the database since automated alterations
 * could be risky.
 * </p>
 * 
 * <p>
 * The Dump command takes the existing database schema and creates an xml file.
 * You can choose to redirect this output to a file so you can modify the
 * schema.
 * </p>
 * 
 * <p>
 * The alter command takes the xml located in the /ddl.xml file and generates
 * alter statements to standard output. You can redirect this to a file and use
 * it to alter the database manually.
 * </p>
 * 
 * <p>
 * mvn -q exec:java -Dexec.mainClass=com.test.DDLUtils -Dexec.args="dump"
 * </p> language
 * <p>
 * mvn -q exec:java -Dexec.mainClass=com.test.DDLUtils -Dexec.args="alter"
 * </p>
 * 
 */
public class DDLUtils {
	private DataSource dataSource;

	public DDLUtils() {
		super();
		ApplicationContext context = new 
		ClassPathXmlApplicationContext("com/test/app-config.xml");
		dataSource = (DataSource) context.getBean("dataSource");		
	}
	
	public static void main(String args[]) {
		(new DDLUtils()).contextInitialized(args);
	}
	
    public void contextInitialized(String args[]) {
        Platform platform = PlatformFactory
                .createNewPlatformInstance(dataSource);
 
        Database database = new DatabaseIO().read(new InputStreamReader(
                getClass().getResourceAsStream("/ddl-draft.xml")));
        if(args.length < 1) {
        	usage();
        	return;
        }
        if("dump".equals(args[0])) {
    		StringWriter writer = new StringWriter();
    		new DatabaseIO().write(platform.readModelFromDatabase("batch"), writer);
    		System.out.println(writer.toString());        	
        } else if("alter".equals(args[0])) {
        	System.out.println(platform.getAlterTablesSql(database));
        } else {
        	usage();
        	return;
        }
    }

	private void usage() {
    	System.out.println("usage: ");
    	System.out.println("\t\t"+getClass().getName()+" (dump|alter)\n\n");    
	}
    
}

Testing the Application

The following command takes the current schema and creates an xml file to standard output.

mvn -q exec:java -Dexec.mainClass=com.test.DDLUtils -Dexec.args="dump"

The following prints to standard out the DDL that will get the current database to resemble what is documented in the xml file.

mvn -q exec:java -Dexec.mainClass=com.test.DDLUtils -Dexec.args="alter"

Shortcut

Read this section if you already have a database and simply want to use DDL utils to query the schema or help modify it.

Step 1: Create a Maven Project with all the necessary dependencies listed below + the JDBC driver specific to your database.
mkdir ddlutil
cd ddlutil
vi 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>ddlutil</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>ddlutil 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.apache.ddlutils</groupId>
		<artifactId>ddlutils</artifactId>
		<version>1.0</version>    
    </dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring</artifactId>
			<version>2.5.6</version>
		</dependency>                
  </dependencies>
  <build>
    <finalName>ddlutil</finalName>
    
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.0.2</version>
				<configuration>
					<source>1.5</source>
					<target>1.5</target>
				</configuration>
			</plugin>
		</plugins>    
  </build>
</project>

Step 2: Create the spring configuration file with the information necessary to connect to your datasource.

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="xxxreplace with your database driverxxx"/>
        <property name="url" value="xxxreplace with your database urlxxx"/>
        <property name="username" value="xxx_username_xxx"/>
        <property name="password" value="xxx"/>
</bean>
</beans>

See the following URL to setup data connectivity using spring and help fill in the values above.

Step 3: Create sample DDL and return the the Background section on this page.

src/main/resources/ddl.xml

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="testdb">
  <table name="author">
    <column name="author_id"
            type="INTEGER"
            primaryKey="true"
            required="true"/>
    <column name="name"
            type="VARCHAR"
            size="50"
            required="true"/>
    <column name="organization"
            type="VARCHAR"
            size="50"
            required="false"/>
  </table>

  <table name="book">
    <column name="book_id"
            type="INTEGER"
            required="true"
            primaryKey="true"
            autoIncrement="true"/>
    <column name="isbn"
            type="VARCHAR"
            size="15"
            required="true"/>
    <column name="author_id"
            type="INTEGER"
            required="true"/>
    <column name="title"
            type="VARCHAR"
            size="255"
            required="true"/>

    <foreign-key foreignTable="author">
      <reference local="author_id" foreign="author_id"/>
    </foreign-key>  

    <index name="book_isbn">
      <index-column name="isbn"/>
    </index>
  </table>
</database>

References

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/

31
Mar
11

Schema Maintenance Using DDLUtils

This page describes the process of creating in memory database and maintaining the schema using Apache DDLUtils within your web application. Since we will be using a pure java database it allows developers to rapidly develop and unit test database driven applications in isolation.

There is no need to maintain an external mysql or Oracle database. The database is self contained within the application. Once you are finished with development you would just remove the HSQLDB dependency from the pom file and change the connect string to connect with a live database.

The primary motivation for writing this was so that I could use this as a base for writing CRUD type web applications.

This page is part of a series of Rapid Development and prototyping tutorials.

Background

HyperSQL DataBase (HSQLDB) is a Java based in-memory database that allows you to develop and unit test your web application in isolation. It supports stored procedures (callable statements) as well.

Schema maintenance is a common problem for in-memory or sandbox databases. Changes to the data model need to be applied to your test database using DDL statements often during application development.

This page describes the process of using the Apache DDLUtils framework to incrementally apply the changes kept in an XML file each time the application restarts. Since the changes are incremental (ALTER TABLE etc..) and every effort is made to preserve the existing data.

The application below will create 2 tables when the application initializes. The DDL will be generated and executed from an xml file located in the classpath. The application initialization and shut-down are handled by writing ContextLoaderListeners.

Requirements

  • Maven
  • Basic understanding of the Spring Framework
  • Basic understanding of JDBC and SQL

Procedure

Start a new project

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

groupId: com.test
artifactId: ddlutil

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

cd to the project base folder.

cd ddlutil

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

Create missing folder
mkdir -p src/main/java/com/test

Modify the pom.xml file to look like this.

<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>ddlutil</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>ddlutil 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.apache.ddlutils</groupId>
		<artifactId>ddlutils</artifactId>
		<version>1.0</version>    
    </dependency>
		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>1.8.0.10</version>
		</dependency>
		    
		<dependency>
            <groupId>org.apache.geronimo.specs</groupId>
            <artifactId>geronimo-servlet_2.5_spec</artifactId>
            <version>1.2</version>
            <scope>provided</scope>
        </dependency>

		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring</artifactId>
			<version>2.5.6</version>
		</dependency>
                
  </dependencies>
  <build>
    <finalName>ddlutil</finalName>
    
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>2.0.2</version>
				<configuration>
					<source>1.5</source>
					<target>1.5</target>
				</configuration>
			</plugin>
			<plugin>
				<groupId>org.mortbay.jetty</groupId>
				<artifactId>jetty-maven-plugin</artifactId>
				<version>7.0.0.v20091005</version>
				<configuration>
					<scanIntervalSeconds>2</scanIntervalSeconds>
				</configuration>
			</plugin>
		</plugins>    
  </build>
</project>

Spring configuration

The following contains a single bean that represents a un-pooled data source.

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/resources/db/testdb"/>
        <property name="username" value="sa"/>
        <property name="password" value=""/>
</bean>
</beans>

Servlet Context Listener

The following class listens to application life-cycle events like start-up and shut-down. It executes code to initialize and shut-down the database based on these events. Using listeners over Servlets is that the contextListeners will execute before any Servlets get a chance to run.

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

	}

}

Database Schema (DDL)

The following file contains the schema that will be generated in the HSQLDB database. Any changes made to this file will result in “ALTER” table statements when the server restarts.

src/main/resources/ddl.xml

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="testdb">
  <table name="author">
    <column name="author_id"
            type="INTEGER"
            primaryKey="true"
            required="true"/>
    <column name="name"
            type="VARCHAR"
            size="50"
            required="true"/>
    <column name="organization"
            type="VARCHAR"
            size="50"
            required="false"/>
  </table>

  <table name="book">
    <column name="book_id"
            type="INTEGER"
            required="true"
            primaryKey="true"
            autoIncrement="true"/>
    <column name="isbn"
            type="VARCHAR"
            size="15"
            required="true"/>
    <column name="author_id"
            type="INTEGER"
            required="true"/>
    <column name="title"
            type="VARCHAR"
            size="255"
            required="true"/>

    <foreign-key foreignTable="author">
      <reference local="author_id" foreign="author_id"/>
    </foreign-key>  

    <index name="book_isbn">
      <index-column name="isbn"/>
    </index>
  </table>
</database>

Web Application Configuration

The following loads both the spring and the DBLifecycle ContextLoaderListeners. The ordering is critical as you want the spring application context to initialize before anything else.

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>
</web-app>

Run the application

Return to the project main folder and run the following on the command line to start the jetty servlet engine.

mvn clean compile jetty:run

You should see the following at the console

Database [name=testdb; 2 tables]
Mar 31, 2011 10:12:38 PM org.apache.ddlutils.alteration.ModelComparator compare
INFO: Table author needs to be added
Mar 31, 2011 10:12:38 PM org.apache.ddlutils.alteration.ModelComparator compare
INFO: Table book needs to be added
Mar 31, 2011 10:12:38 PM org.apache.ddlutils.platform.SqlBuilder getForeignKeyName
WARNING: Encountered a foreign key in table book that has no name. DdlUtils will use the auto-generated and shortened name book_FK_author_id_author instead.
Mar 31, 2011 10:12:38 PM org.apache.ddlutils.platform.PlatformImplBase evaluateBatch
INFO: Executed 4 SQL command(s) with 0 error(s)
HSQLDB is ready: org.apache.ddlutils.platform.hsqldb.HsqlDbPlatform@10e9c592
2011-03-31 22:12:38.392:INFO::Started SelectChannelConnector@0.0.0.0:8080
[INFO] Started Jetty Server
[INFO] Starting scanner at interval of 2 seconds.

There is no need to open a browser to view the web application since all the work that was needed has already been done when the application initialized.

Shutdown the server by typing CTRL-C.

View the results

The server will initiate a graceful shut-down when you type CTRL-C at the console. The HSQLDB will write all data from the memory to a file located in the src/main/resources/db/ folder.

During runtime the data is kept in memory. Saving the data to a file-system allows the server to resume where it left of without loosing data when the server starts up again. We can use this to our advantage to peek into the file to see what is the state of the database.

After the first run the following file should look like this. Note the length of the AUTHOR.NAME field.

src/main/resources/db/testdb.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE BOOK(BOOK_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,ISBN VARCHAR(15) NOT NULL,AUTHOR_ID INTEGER NOT NULL,TITLE VARCHAR(255) NOT NULL)
CREATE INDEX BOOK_ISBN ON BOOK(ISBN)
CREATE MEMORY TABLE AUTHOR(AUTHOR_ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(50) NOT NULL,ORGANISATION VARCHAR(50))
ALTER TABLE BOOK ADD CONSTRAINT BOOK_FK_AUTHOR_ID_AUTHOR FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID)
ALTER TABLE BOOK ALTER COLUMN BOOK_ID RESTART WITH 0
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10

Altering the schema

Open up ddl.xml and increase the length of the name field of the “AUTHOR” table to VARCHAR(100) line 11 (highlighted).

1. Restart the jetty
2. shut it back down by typing CTRL-C.

Re-open the file below. You will notice that the auther.name field has been expanded to 100 characters.

src/main/resources/db/testdb.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE BOOK(BOOK_ID INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,ISBN VARCHAR(15) NOT NULL,AUTHOR_ID INTEGER NOT NULL,TITLE VARCHAR(255) NOT NULL)
CREATE INDEX BOOK_ISBN ON BOOK(ISBN)
CREATE MEMORY TABLE AUTHOR(AUTHOR_ID INTEGER NOT NULL PRIMARY KEY,NAME VARCHAR(100) NOT NULL,ORGANISATION VARCHAR(50))
ALTER TABLE BOOK ADD CONSTRAINT BOOK_FK_AUTHOR_ID_AUTHOR FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHOR(AUTHOR_ID)
ALTER TABLE BOOK ALTER COLUMN BOOK_ID RESTART WITH 0
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10

That’s all for now!

28
Mar
11

Using Spring to perform CRUD on an HSQLDB using Ibatis

The following page describes how to create a simple web application that performs CRUD operations against the database using Ibatis SQL Mapper and the Spring Framework and an in-memory Java database called HyperSQL. Since we are using HSQLDB there is no need to set-up an external database. This page contains all the code you need to have a working database driven application.

Note: This page was originally written on Nov 6 2009.

Full downloadable source for this page is available here. Corrections and enhancements are welcome, fork, change and push back to GitHub.

Background

CRUD = Create Read Update Delete.

The application described here presents the user with a simple html form that allows them to create read update and delete items in the database. The Spring JDBC template and Ibatis is used to execute SQL statements.

Requirements

  1. Maven 2
  2. Java 6
  3. Servlet 2.5
  4. Ibatis 2
  5. Spring 2.5
  6. Basic understanding of Spring and Ibatis

Procedure

Outline

  1. Create a new Project
  2. Database Setup and Configuration
  3. Spring Application
  4. Integration with Ibatis
  5. Tying it all Together with Spring MVC
  6. Web Application Configuration
  7. Test the application

Create a new project

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

groupId: com.test
artifactId: crud

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

cd to the project base folder.

cd crud

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.

vi 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>crud</artifactId>
    <packaging>war</packaging>
    <version>1.0-SNAPSHOT</version>
    <name>crud Maven Webapp</name>
    <url>http://maven.apache.org</url>
    <pluginRepositories>
      <pluginRepository>
        <id>numberformat-releases</id>
        <url>https://raw.github.com/numberformat/20130213/master/repo</url>
      </pluginRepository>
    </pluginRepositories>

    <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>crud</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>/crud</contextPath>
                    </webAppConfig>
                    <stopKey>s</stopKey>
                </configuration>
            </plugin>
            <plugin>
                <groupId>github.numberformat</groupId>
                <artifactId>blog-plugin</artifactId>
                <version>1.0-SNAPSHOT</version>
                <configuration>
                <gitUrl>https://github.com/numberformat/20110328</gitUrl>
                </configuration>
            <executions>
              <execution>
                <id>1</id>
                <phase>site</phase>
                <goals>
                  <goal>generate</goal>
                </goals>
              </execution>
            </executions>
            </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.

vi src/main/resources/ddl.xml

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

<database name="testdb">

    <table name="item">
        <column name="item_id" type="INTEGER" required="true"
            primaryKey="true" autoIncrement="true" />
        <column name="category_id" type="INTEGER" />
        <column name="oem_id" type="VARCHAR" size="50"/>
        <column name="item_x" type="VARCHAR" size="100" />
        <column name="special_f" type="CHAR" size="1" />
        <column name="description" type="VARCHAR" size="1000" />
        <column name="color" type="VARCHAR" size="20" />
        <column name="image_url" type="VARCHAR" size="100" />
        <column name="price" type="DECIMAL" size="15" />
        <column name="length" type="DECIMAL" size="10" />
        <column name="width" type="DECIMAL" size="10" />
        <column name="height" type="DECIMAL" size="10" />
        <column name="weight" type="DECIMAL" size="10" />
        <column name="stock_qty" type="DECIMAL" size="10" />
        <index name="item_item_x">
            <index-column name="item_x" />
        </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.

vi 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

  • Item – POJO that represents the individual item
  • ItemModel – Service Interface
  • ItemModelImpl – Implementation of the Service
  • ItemDataManager – DataManager Interface
  • ItemDataManagerImpl – 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“.

Item Bean

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

package com.test;

import java.math.BigDecimal;

/**
 * Represents an item in inventory or in a shopping cart.
 */
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 BigDecimal length;
    private BigDecimal width;
    private BigDecimal height;

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

}

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.

vi src/main/java/com/test/ItemModel.java

package com.test;

import java.util.List;

public interface ItemModel {
    /**
     * This is a pass-thru method that just calls the DataManager.
     */
    public Item getItemInfo(Integer itemId);

    public Item createItem(Item item);

    public void updateItem(Item item);

    public void deleteItem(Integer itemId);

    /**
     * This is a pass-thru method that just calls the DataManager.
     */
    public List<Item> getAllItems(int skipResults, int maxItems);

    /**
     * This is a pass-thru method that just calls the DataManager.
     */
    public int getItemCount();

    public void deleteItemImage(Integer itemId);

}

The following is the implementation class.

vi src/main/java/com/test/ItemModelImpl.java

package com.test;

import java.util.List;

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

@Service
public class ItemModelImpl implements ItemModel {

    @Autowired
    @Qualifier("simpleItemDataManager")
    private ItemDataManager itemDataManager;   

    @Override
    public Item createItem(Item item) {
        return itemDataManager.createItem(item);
    }

    @Override
    public void deleteItem(Integer itemId) {
        itemDataManager.deleteItem(itemId);
    }

    @Override
    public void deleteItemImage(Integer itemId) {
        itemDataManager.deleteItemImage(itemId);
    }

    @Override
    public List<Item> getAllItems(int skipResults, int maxItems) {
        return itemDataManager.getAllItems(skipResults, maxItems);
    }

    @Override
    public int getItemCount() {
        return itemDataManager.getItemCount();
    }

    @Override
    public Item getItemInfo(Integer itemId) {
        return itemDataManager.getItemInfo(itemId);
    }

    @Override
    public void updateItem(Item item) {
        itemDataManager.updateItem(item);
    }

    public void setItemDataManager(ItemDataManager itemDataManager) {
        this.itemDataManager = itemDataManager;
    }

    public ItemDataManager getItemDataManager() {
        return itemDataManager;
    }

}

Spring Data Managers

Data Managers isolate the Model from the implementation of the data store.

vi src/main/java/com/test/ItemDataManager.java

package com.test;

import java.util.List;

public interface ItemDataManager {

    public Item getItemInfo(Integer itemId);

    public Item createItem(Item item);

    public void updateItem(Item item);

    public void deleteItem(Integer itemId);

    List<Item> getAllItems(int skipResults, int maxItems);

    int getItemCount();

    public void deleteItemImage(Integer itemId);
}

The following is the data manager implementation class.

vi src/main/java/com/test/ItemDataManagerImpl.java

package com.test;

import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
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;

/**
 * This is the non-ibatis implementation.
 */
@Repository("itemDataManager")
public class ItemDataManagerImpl implements ItemDataManager {
    private final Log logger = LogFactory.getLog(ItemDataManagerImpl.class);
    private SqlMapClientTemplate sqlMapClientTemplate;

    public SqlMapClientTemplate getSqlMapClientTemplate() {
        return sqlMapClientTemplate;
    }

    @Autowired
    @Required
    public void setSqlMapClientTemplate(SqlMapClientTemplate sqlMapClientTemplate) {
        this.sqlMapClientTemplate = sqlMapClientTemplate;
    }

    @Override
    public Item getItemInfo(Integer itemId) {
        return (Item)getSqlMapClientTemplate().queryForObject("getItemInfo", itemId);
    }

    @Override
    public Item createItem(Item item) {
        Integer id = (Integer) getSqlMapClientTemplate().insert("itemInsert", item);
        item.setItemId(id);
        return item;
    }

    @Override
    public void deleteItem(Integer itemId) {
        if(itemId != null) {
            getSqlMapClientTemplate().delete("deleteItem", itemId);
        }
    }
    @Override
    public void deleteItemImage(Integer itemId) {
        if(itemId != null) {
            getSqlMapClientTemplate().delete("deleteItemImage", itemId);
        }
    }

    @Override
    public void updateItem(Item item) {
        getSqlMapClientTemplate().update("updateItem", item);
    }

    @Override
    public int getItemCount() {
        return 0;
    }

    @Override
    public List<Item> getAllItems(int skipResults, int maxItems) {
        List<Item> list = null;

        list = getSqlMapClientTemplate().queryForList("allItems", null, skipResults, maxItems);

        return list;
    }

}

Spring Configuration

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

vi 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>

Configure simple logging to the console.

vi src/main/resources/log4j.properties

# Set root logger level to DEBUG and its only appender to A1.
log4j.rootLogger=INFO, A1

log4j.appender.A1=org.apache.log4j.ConsoleAppender
log4j.appender.A1.layout=org.apache.log4j.PatternLayout
log4j.appender.A1.layout.ConversionPattern=%d %-5p %c - %m%n

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

vi 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/ItemDataManager.xml"/>
</sqlMapConfig>

vi 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" />

    <select id="allItems" resultClass="Item">
        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" resultClass="Item">
        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#, #length#, #width#,
            #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 = #length#,
            width = #width#, height = #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>

Tying it all Together with Spring MVC

The following is the controller class that handles the http form submissions.

vi src/main/java/com/test/ItemController.java

package com.test;

import java.util.Date;
import java.util.List;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Required;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

@Controller
@RequestMapping("/item")
public class ItemController {

    private ItemModel itemModel;

    @RequestMapping(method = RequestMethod.GET)
    public String itemInfo(HttpServletRequest request) {
        List <Item> items = itemModel.getAllItems(0, 100);
        request.setAttribute("items", items);
        return "index";
    }

    @RequestMapping(method = RequestMethod.POST)
    public String createItem(HttpServletRequest request,
            HttpServletResponse response) {

        if ("C".equals(request.getParameter("operation"))) {
            System.out.println("create");
            Item item = new Item();
            item.setName("item created: " + new Date());
            itemModel.createItem(item);
            String message = "newly created item: " + item.getItemId();
            System.out.println(message);
            request.setAttribute("message", message);

        } else if ("R".equals(request.getParameter("operation"))) {
            // left for the reader to implement
            Integer id = new Integer(request.getParameter("id"));

        	System.out.println(itemModel.getItemInfo(id));
        } else if ("U".equals(request.getParameter("operation"))) {
            // left for the reader to implement
            Integer id = new Integer(request.getParameter("id"));
        	Item item = itemModel.getItemInfo(id);
        	item.setName("item updated: " + new Date());
        	itemModel.updateItem(item);
        } else if ("D".equals(request.getParameter("operation"))) {
            Integer id = new Integer(request.getParameter("id"));
            itemModel.deleteItem(id);
            String message = "item deleted: " + id;
            System.out.println(message);
            request.setAttribute("message", message);
        }

        request.setAttribute("items", itemModel.getAllItems(0, 100));
        return "index";
    }

    public ItemModel getItemModel() {
        return itemModel;
    }

    @Required
    @Autowired
    public void setItemModel(ItemModel itemModel) {
        this.itemModel = itemModel;
    }

}

Create the Test HTML Form

The following form issues a POST request to the spring MVC controller. The form is missing the “action” attribute because by default the form posts back to the originating URL. In this case the originating URL is “/apps/item”

vi src/main/webapp/WEB-INF/jsp/index.jsp

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
 "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>DB Parameters</title>
<meta http-equiv="Content-type" content="text/html; charset=iso-8859-1" />
<meta http-equiv="Content-Language" content="en-us" />

</head>
<body>
<h3><c:out  value="${message}"/></h3>

<h3>Items:</h3>
<table border="1">
<thead>
    <td>Id</td>
    <td>Name</td>
</thead>
<tbody>
<c:forEach items="${items}" var="item" >
    <tr>
    <td><c:out value="${item.itemId}"/></td>
    <td><c:out value="${item.name}"/></td>
    </tr>
</c:forEach>
</tbody>
</table>

    <form method="post">
        <label>Operation: </label>
        <select name="operation">
            <option value="C">Create</option>
            <option value="R">Read</option>
            <option value="U">Update</option>
            <option value="D">Delete</option>
        </select>
        <br/>
        <label>ID: </label><input type="text" name="id"/><br/>
        <input type="submit" value="submit"/><br/>
    </form>

This page saves data to an HyperSQL database located in the src/main/webapp/WEB-INF/db folder.
</body>
</html>

Web Application Configuration

The ordering of the listeners are important. You want to make sure that the spring application context is initialized before the DBLifecycleListener.

vi 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>spring</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
    </servlet>

    <servlet-mapping>
        <servlet-name>spring</servlet-name>
        <url-pattern>/app/*</url-pattern>
    </servlet-mapping>

</web-app>

vi src/main/webapp/WEB-INF/spring-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>
<?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
        class="org.springframework.web.servlet.mvc.annotation.DefaultAnnotationHandlerMapping" />
    <bean
        class="org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter" />

    <bean id="viewResolver"
        class="org.springframework.web.servlet.view.InternalResourceViewResolver">
        <property name="prefix" value="/WEB-INF/jsp/" />
        <property name="suffix" value=".jsp" />
    </bean>

</beans>

Start Jetty Servlet Engine

mvn clean compile jetty:run

Test the application

 

 

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 following location…

target/crud-1.0-SNAPSHOT.war

Next Steps

Full downloadable source for this page is available here.
06
Jun
10

Starting a Simple Java Persistence (JPA) Project Using Spring

This page describes the process of converting an existing JPA project to use the Spring Framework. We will pick up where we left off in my previous post.

Requirements

Step 1

Modify the pom.xml file and add the dependency for the spring framework.

<dependency>
    <groupId>org.springframework</groupId>
    <artifactId>spring</artifactId>
    <version>2.5.6</version>
</dependency>

Save and close and regenerate and refresh the project in eclipse

mvn eclipse:clean eclipse:eclipse
Continue reading ‘Starting a Simple Java Persistence (JPA) Project Using Spring’

04
Jun
10

Hibernate and HSQLDB

This page describes how to setup a project using Hibernate and HSQLDB. A better method would be to use JPA to configure hibernate and use the JPA API directly.

Requirements

  • Java 5
  • Maven
  • Understanding of JDBC and SQL
  • Basic understanding of Relational Databases

Start a new project

Run the following command to generate a new project using a maven archetype.

mvn archetype:generate --batch-mode \
  -DarchetypeArtifactId=maven-archetype-quickstart \
  -DgroupId=com.test \
  -DartifactId=hsqldbTest

The following maven pom.xml file defines the following dependencies

  • HSQLDB
  • Hibernate and Hibernate Annotations

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>hsqldbTest</artifactId>
  <packaging>jar</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>hsqldbTest</name>
  <url>http://maven.apache.org</url>
<build>
    <plugins>
       <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>2.0.2</version>
          <configuration>
              <source>1.5</source>
              <target>1.5</target>
          </configuration>
      </plugin>
    </plugins>
</build>
  <dependencies>

    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>1.8.0.10</version>
    <scope>test</scope>
</dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate</artifactId>
      <version>3.2.6.ga</version>
    </dependency>
    <dependency>
      <groupId>org.hibernate</groupId>
      <artifactId>hibernate-annotations</artifactId>
      <version>3.3.1.GA</version>
    </dependency>

  </dependencies>
</project>

Hibernate Configuration File

The following configuration files defines

  • Connection URL that specifies that the HSQLDB database will be file based, it will be located in the src/main/resources/db folder and the “shutdown” parameter specifies that the database will be persisted to the file when all the connections are closed.
  • Default admin username is “sa” and password is “blank”
  • The connection pooling is turned off by specifying “connection.pool_size” as 0 (hibernates built in pool implementation is really bad. It prevents HSQLDB from properly shutting down. Therefore we turn it off.

src/main/resources/hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
          "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
          "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="connection.url">jdbc:hsqldb:file:src/main/resources/db/mydbname;shutdown=true</property>
    <property name="connection.username">sa</property>
    <property name="connection.password"></property>

    <property name="connection.driver_class">org.hsqldb.jdbcDriver</property>
    <property name="dialect">org.hibernate.dialect.HSQLDialect</property>

    <property name="show_sql">true</property>

    <property name="format_sql">true</property>
    <property name="hbm2ddl.auto">create</property>
 <!--
    * 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.
-->
    <!-- JDBC connection pool (use the built-in) -->
    <property name="connection.pool_size">0</property>
    <property name="current_session_context_class">thread</property>

<mapping class="com.test.hibernate.model.Blog" />

  </session-factory>
</hibernate-configuration>

Entity Bean

src/main/java/com/test/hibernate/model/Blog.java

package com.test.hibernate.model;

import java.util.Date;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.GeneratedValue;

@Entity
public class Blog {

  private Long id;

  private String subject;

  private String body;

  private Date createdAt;

  @Id
  @GeneratedValue
  public Long getId() {
    return id;
  }

  public void setId(Long id) {
    this.id = id;
  }

  public String getSubject() {
    return subject;
  }

  public void setSubject(String subject) {
    this.subject = subject;
  }

  public String getBody() {
    return body;
  }

  public void setBody(String body) {
    this.body = body;
  }

  public Date getCreatedAt() {
    return createdAt;
  }

  public void setCreatedAt(Date createdAt) {
    this.createdAt = createdAt;
  }
}

Test case

src/test/java/com/test/AppTest.java

package com.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;
import com.test.hibernate.model.Blog;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;

public class AppTest extends TestCase {
    public AppTest(String testName) {
        super(testName);
    }

    public static Test suite() {
        return new TestSuite(AppTest.class);
    }

    public void testHibernate() throws Exception {
        SessionFactory sessionFactory = new AnnotationConfiguration().configure().buildSessionFactory();

        Session session = sessionFactory.getCurrentSession();

        Transaction tx = session.beginTransaction();

        Blog b = new Blog();
        session.save(b);

        tx.commit();
// the connection pool implementation in hibernate is bad.
// Either use 3cpo or dbcp or dont use it at all...
// in this example we set the connection pooling off.
// <property name="connection.pool_size">0</property>
    }

}

Run the test case

mvn clean compile test

Related Posts

Appendix

To shutdown the HSQLDB database manually you can issue the following.
//org.hsqldb.DatabaseManager.closeDatabases(0);

03
Jun
10

Rapid Prototyping using HSQLDB

This page describes the process of setting up a simple database for the purpose of rapid development and prototyping.

Background

HSQLDB is a database is implemented purely in Java. It has a very small footprint and can run within the same JVM as the program being tested. Developing database applications using HSQLDB allows developers to rapidly develop and integration test their front end code or business logic without having a live database connection. Since all the data is stored and persisted locally, developers are free to work from locations that might not have database connectivity. For example outside the company firewall.

Full downloadable source for this page is available here. Corrections and enhancements are welcome, fork, change and push back to GitHub.

Requirements

  • Java 5
  • Maven
  • Understanding of JDBC and SQL
  • Basic understanding of Relational Databases

Start a new project

Start the project by creating one using a maven archetype.

mvn archetype:generate --batch-mode \
  -DarchetypeArtifactId=maven-archetype-quickstart \
  -DgroupId=com.test \
  -DartifactId=hsqldbTest

Once the project is created, then “cd to the project folder” and add the following depencency section in the pom.xml file

vi 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>hsqldbTest</artifactId>
	<packaging>jar</packaging>
	<version>20100603</version>
	<name>hsqldbTest</name>
	<url>http://maven.apache.org</url>
	
	<pluginRepositories>
		<pluginRepository> <!-- Ignore this repository. Its only used for document publication. -->
			<id>numberformat-releases</id>
			<url>https://raw.githubusercontent.com/numberformat/wordpress/master/20130213/repo/</url>
		</pluginRepository>
	</pluginRepositories>
		
	<dependencies>
		<dependency>
			<groupId>junit</groupId>
			<artifactId>junit</artifactId>
			<version>3.8.1</version>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.hsqldb</groupId>
			<artifactId>hsqldb</artifactId>
			<version>2.3.3</version>
			<scope>test</scope>
		</dependency>
	</dependencies>
<build>
		<plugins>
			<plugin> <!-- Ignore this plugin. Its only used for document publication. -->
				<groupId>github.numberformat</groupId>
				<artifactId>blog-plugin</artifactId>
				<version>1.0-SNAPSHOT</version>
				<configuration>
					<gitUrl>https://github.com/numberformat/wordpress/tree/master/${project.version}/${project.artifactId}</gitUrl>
				</configuration>
				<executions>
					<execution>
						<id>1</id>
						<phase>site</phase>
						<goals>
							<goal>generate</goal>
						</goals>
					</execution>
				</executions>
			</plugin>
		</plugins>
</build>	
</project>

Regenerate the eclipse project by typing

mvn eclipse:clean eclipse:eclipse

Import the project into eclipse.

Configuration

There are 2 ways to configure the database.

  • Pure In memory database URL (InMemory): jdbc:hsqldb:mem:databaseName
  • Persistant URL (File system): jdbc:hsqldb:file:databaseFilename

The following is how it was done for this example:

  • File system database was chosen since we want the data to remain persistant after the program ends. The File system databse is s specified in the url.
  • The database files are placed in the location specified in the URL (src/main/resources/db/)
  • The database will be called “mydbname” (database and parent folders will be created if they don’t exist already)
  • The default credentials to use HSQLDB is “sa” and the password is blank. (Specified during JDBC connection)

Test the database

The following test case

  1. creates a table if one does not exit already
  2. inserts some data. Once data is inserted it
  3. selects the data and displays it to the console.

See complete output of the test case below

vi src/test/java/com/test/AppTest.java

package com.test;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import junit.framework.Test;
import junit.framework.TestCase;
import junit.framework.TestSuite;
 
public class AppTest extends TestCase {
    public AppTest(String testName) {
        super(testName);
    }
 
    public static Test suite() {
        return new TestSuite(AppTest.class);
    }
 
    public void testApp() throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        String url = "jdbc:hsqldb:file:src/main/resources/db/mydbname";
        String username = "sa";
        String password = "";
 
        Connection connection = DriverManager.getConnection(url, username,
                password);
 
        Statement statement = connection.createStatement();
        try {
            statement.executeQuery("select * from sec_edgar_filer;");
        } catch (SQLException ex) { // if no table then create one.
            statement
                    .execute("CREATE TABLE sec_edgar_filer ( cik VARCHAR(22) NOT NULL);");
        }
         
        try {
            statement.execute("insert into sec_edgar_filer values ( 'Hello World! "
                    + System.currentTimeMillis() + "' );");
            ResultSet resultset = statement
                    .executeQuery("select * from sec_edgar_filer;");
 
            int count = 0;
            while (resultset.next()) {
                System.out.println(resultset.getString(1));
                count++;
            }
            System.out.println("total rows returned: " + count);
             
        } finally {
            try {statement.execute("SHUTDOWN;");} catch (Exception ex) {}
            try {connection.close();} catch (Exception ex) {}          
        }
    }
}

Run the test case

At this point you have 2 options.

  1. Run the test case in eclipse using JUnit test runner
  2. Run the test by executing “mvn test” on the command line.

Either way you will see output similar to this…

Hello World! 1275621161302
Hello World! 1275621164539
Hello World! 1275621179581
Hello World! 1275621424099
total rows returned: 4

Be sure to Shutdown

All you need to do is to issue a SQL SHUTDOWN command to instruct HSQLDB to initiate a shutdown procedure. This is an important step to allow the data in memory to be saved to the disk.

You will notice that 2 files have been created in the src/main/resources/db directory.

  • mydbname.properties – contains the settings for the database.
  • mydbname.script – contains the data for the tables

References

Full downloadable source for this page is available here. Corrections and enhancements are welcome, fork, change and push back to GitHub.



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,774 hits