Posts Tagged ‘prototyping

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 74 other followers

March 2017
S M T W T F S
« Mar    
 1234
567891011
12131415161718
19202122232425
262728293031  

Blog Stats

  • 800,977 hits