Embedding HSQLDB in JAVA Application Project

Introduction

Sometimes we may want to test some simple database operation, or let the database portable within our application. In these cases, we can try to use the embedding hsqldb.

Pre-request

The only required jar is hsqldb.jar, you can get the latest version from official site http://hsqldb.org/.

The Program

Test.java:

Start a hsqldb server, execute some sql and show all data in db.

package test;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;

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

import org.hsqldb.Server;

public class Test {

    private static Connection _connection = null;
    /**
     * create a writer, log hsqldb server info to a file
     * @param logFileName String, the log file name
     * @param append boolean, true: append new content; false: clear old content 
     * @param autoFlush boolean, true: auto flush; false: not auto flush
     * @return PrintWriter
     * @throws IOException
     */
    private static PrintWriter createLogWriter (String logFileName,
        boolean append, boolean autoFlush)
        throws IOException {
        File f = new File(logFileName);

        // create file if not exists
        if (!f.exists()) {
            String logFilePath = f.getAbsolutePath();

            // create parent folders
            File folder = new File(logFilePath.substring(0, logFilePath.indexOf(logFileName)));
            folder.mkdirs();

            // create file
            f.createNewFile();
        }
        FileWriter fw = new FileWriter(f, append);
        return new PrintWriter(fw, autoFlush);
    }
    /**
     * get a db connection
     * @param driverName
     * @param dbUrl
     * @param userName
     * @param password
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private static Connection getConnection (String dbUrl,
        String userName, String password)
        throws SQLException, ClassNotFoundException {
        if (_connection == null
            || _connection.isClosed()) {
            // Getting a connection to the newly started database
            Class.forName("org.hsqldb.jdbcDriver");
            // Default user of the HSQLDB is 'sa'
            // with an empty password
            return DriverManager.getConnection(dbUrl
                ,userName , password);
        } else {
            return _connection;
        }
    }
    public static void main(String[] args)
        throws ClassNotFoundException, SQLException, IOException {
        String dbName = "testName";
        String path = "testPath";

        
        PrintWriter logWriter =
            createLogWriter(dbName+"_"+path+".log",
                            true, true);
        // start the hsqldb server
        Server hsqlServer = HSQLDBClass.startServer(dbName, path, logWriter);
        Connection connection =
            getConnection("jdbc:hsqldb:hsql://localhost/"+dbName, "sa", "");
        try {

            Random rand = new Random();
            Statement stmt = connection.createStatement();
            // test several SQL operation
            // create table if not exists
            stmt.execute(
                "CREATE TABLE IF NOT EXISTS testTable ( id BIGINT NOT NULL IDENTITY,"
                + "firstName VARCHAR(32),"
                + "lastName VARCHAR(32));");

            // insert data
            stmt.executeUpdate(
                "INSERT INTO testTable (firstName, lastName) VALUES("
                + "'firstName_"+rand.nextInt()+"', 'lastName_"+rand.nextInt()+"')", Statement.RETURN_GENERATED_KEYS);

            // query data
            ResultSet rs = stmt.executeQuery(
                "select * from testTable;");

            while (rs.next()) {
                // show all data
                System.out.println(rs.getBigDecimal(1) + "\t"
                    + rs.getString(2) + "\t"
                    + rs.getString(3) + "\t");
            }
        } finally {
            System.out.println("close connection");
            // Closing the connection
            if (connection != null) {
                connection.close();
            }
            // Stop the server
            if (hsqlServer != null) {
                HSQLDBClass.stopServer(dbName);
            }
        }
    }
}

HSQLDBClass.java

Handle the hsqldb server, start, stop and get instance.

package test;

import java.io.PrintWriter;
import java.util.HashMap;
import java.util.Map;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

import org.hsqldb.Server;

/**
 * A class to handle hsqldb server start/stop and get server instance
 *
 */
public class HSQLDBClass {
    // to store the database server instance
    private static Map<String, Server> _dbMap = new HashMap<String, Server>();
    private static Lock lock = new ReentrantLock();

    /**
     * srart a hsqldb server
     * @param dbName database name
     * @param path the location to store database information
     * @param logWriter where to log hsqldb server log
     * @return Server, a hsqldb Server instance
     */
    public static Server startServer (String dbName, String path, PrintWriter logWriter) {
        lock.lock();
        // 'Server' is a class of HSQLDB representing
        // the database server
        Server hsqlServer = null;
        try {
            if (isServerExist(dbName)) {
                stopServer(dbName);
            } else {
                hsqlServer = new Server();
    
                // The database will be named [dbName]
                // the settings and data of this database
                // will be stored in files
                // [path].properties and [path].script
                hsqlServer.setLogWriter(logWriter);
                hsqlServer.setDatabaseName(0, dbName);
                hsqlServer.setDatabasePath(0, "file:"+path);
    
                // Start the database!
                hsqlServer.start();
                _dbMap.put(dbName, hsqlServer);
            }
        } finally {
            lock.unlock();
        }
        return hsqlServer;
    }
    /**
     * stop a hsqldb server
     * @param dbName String, the key of a hsqldb server
     */
    public static void stopServer (String dbName) {
        lock.lock();
        try {
            Server hsqlServer = _dbMap.get(dbName);
            if (hsqlServer != null) {
                System.out.println("stop server");
                hsqlServer.stop();
                _dbMap.remove(dbName);
            }
        } finally {
            lock.unlock();
        }
    }
    /**
     * get a hsqldb server instance
     * @param dbName String, the key of a hsqldb server
     * @return Server, the hsqldb server instance
     */
    public static Server getServer (String dbName) {
        lock.lock();
        Server hsqlServer = null;
        try {
            hsqlServer = _dbMap.get(dbName);
        } finally {
            lock.unlock();
        }
        return hsqlServer;
    }
    private static boolean isServerExist (String dbName) {
        if (_dbMap.containsKey(dbName))
            return true;
        return false;
    }
}

The Result

Reference

the official document
http://hsqldb.org/doc/2.0/guide/listeners-chapt.html#lsc_app_start


Download

Full project at github
https://github.com/benbai123/JSP_Servlet_Practice/tree/master/Practice/DBPractice/HSQLDB/EmbeddingHSQLDB