If you ever wanted to have a small, portable database that you could launch directly from the Java code, now you can do it – with HSQLDB! HSQLDB is written entirely in Java. To try it out, you will need just one file – hsqldb.jar which you will find in lib folder of the archive downloaded from here. Nothing else, just put the file on your class path and you are ready to roll!

The code belows shows a very simple use of HSQLDB API:

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
91:
92:
93:
94:
95:
96:
97:
98:
99:
90:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:

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

import org.hsqldb.Server;

public class HSQLDBTest {

    public static void main(String[] args) throws
        ClassNotFoundException, SQLException {

        // 'Server' is a class of HSQLDB representing
        // the database server
        Server hsqlServer = null;
        try {
            hsqlServer = new Server();

            // HSQLDB prints out a lot of informations when
            // starting and closing, which we don't need now.
            // Normally you should point the setLogWriter
            // to some Writer object that could store the logs.
            hsqlServer.setLogWriter(null);
            hsqlServer.setSilent(true);

            // The actual database will be named 'xdb' and its
            // settings and data will be stored in files
            // testdb.properties and testdb.script
            hsqlServer.setDatabaseName(0, "xdb");
            hsqlServer.setDatabasePath(0, "file:testdb");

            // Start the database!
            hsqlServer.start();

            Connection connection = null;
            // We have here two 'try' blocks and two 'finally'
            // blocks because we have two things to close
            // after all - HSQLDB server and connection
            try {
                // Getting a connection to the newly started database
                Class.forName("org.hsqldb.jdbcDriver");
                // Default user of the HSQLDB is 'sa'
                // with an empty password
                connection = DriverManager.getConnection(
                    "jdbc:hsqldb:hsql://localhost/xdb", "sa", "");

                // Here we run a few SQL statements to see if
                // everything is working.
                // We first drop an existing 'testtable' (supposing
                // it was there from the previous run), create it
                // once again, insert some data and then read it
                // with SELECT query.
                connection.prepareStatement("drop table testtable;")
                    .execute();
                connection.prepareStatement(
                    "create table testtable ( id INTEGER, "+
                    "name VARCHAR);")
                    .execute();
                connection.prepareStatement(
                    "insert into testtable(id, name) "+
                    "values (1, 'testvalue');")
                    .execute();
                ResultSet rs = connection.prepareStatement(
                    "select * from testtable;").executeQuery();

                // Checking if the data is correct
                rs.next();
                System.out.println("Id: " + rs.getInt(1) + " Name: "
                    + rs.getString(2));
            } finally {
                // Closing the connection
                if (connection != null) {
                    connection.close();
                }

            }
        } finally {
            // Closing the server
            if (hsqlServer != null) {
                hsqlServer.stop();
            }
        }
    }
}

When you run this code, you should receive following output:

Id: 1 Name: testvalue

As you can see in the code, we first start the HSQLDB with the method Server.start(). Since that time, HSQLDB server runs as a separate thread. In this example, tables and data that we put in the database will be all stored in file ‘testdb.script’. Let’s take a look at the file created after the run of our code:

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE TESTTABLE(ID INTEGER,NAME VARCHAR)
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO TESTTABLE VALUES(1,'testvalue')

As you see it is just a bunch of SQL statements creating the schema, tables, setting up user accounts and putting the data into tables. You can manually modify this file and every change you make to it will visible in the actual database after the next run of the server.

There is also ‘testdb.properties’ file which stores all the settings of the database. In our example it looks like that:

#HSQL Database Engine 1.8.0.10
#Sun Sep 06 18:11:59 JST 2009
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=false
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=true
hsqldb.cache_scale=14
version=1.8.0
hsqldb.default_table_type=memory
hsqldb.cache_file_scale=1
hsqldb.log_size=200
modified=yes
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0

Besides using HSQLDB in your normal programs, it is also a perfect database for integration tests of your software. You can configure your testing suite to start the HSQLDB every time before a test with exactly specified tables and data. No more relying on external databases and no more fear that somebody could have changed your test data without a notice (which, by the way, you could achieve also with DbUnit).