JDBC HSQLDB Tutorial

In this tutorial we will review HSQLDB basics. Then we will write a simple Java application to demonstrate how to access and interact with an HSQLDB database using JDBC.

1. JDBC HSQLDB – Introduction

HSQLDB stands for Hyper Structured Query Language Database. It’s a relational database management system written in Java. It supports JDBC interface for database access. It offers a fast and small database engine that offers both in-memory and disk based tables. It can be embedded within an application or an application server or can be run as a separate server process. It’s the only open-source RDBMS with a high performance dedicated lob storage system, which makes it suitable for applications that process large sets of data.

1.2 HSQLDB Features

The following are some of the features of HSQLDB:

• Standards compliance – It supports all the core features and many optional features of SQL:2008
• Only open-source RDBMS with a high performance dedicated lob storage system
• Only relational database that can create and access large comma delimited files as SQL tables
• Supports three live switchable transaction control models, including fully multi-threaded MVCC
• It has a wide range of enterprise deployment options, such as XA transactions, connection pooling data sources and remote authentication

1.3 HSQLDB Components

The main components of HSQLDB is packaged in a Jar file called hsqldb.jar. You can find this Jar file in the lib folder.

• HyperSQL RDBMS Engine
• HyperSQL JDBC Driver
• Database Manager

The core functionality of HSQLDB is provided by the RDBMS engine and the JDBC driver whereas the Database Manager is a GUI database access tool. sqltool.jar found in lib folder is a general-purpose command line database access tool. Both database manager and sqltool can be used with other database engines as well.

1.3.1 Types of Database

An HSQLDB database is called catalog. There are three types of catalog depending on how the data is stored.

• mem – Stored entirely in RAM. Usually used for test data or cache for applications. It’s life cycle depends JVM process’s life
• file – Stored in file system
• res – Stored in a Java resource such as Jar or Zip files. This is used for small and read-only database that is distributed with Java applications

1.3.2 Components of a File Catalog

A file catalog consists 2 – 6 files, all named same but with different extensions. For example, a database named “sample” will contain the following files:

• sample.properties – Contains settings about the database
• sample.script – Contains definitions for tables and other objects plus data for non-cached tables
• sample.log – This file is used to record the recent changes made to the data. It is removed when database is shutdown normally. In case of abnormal shutdown, this is used to redo the changes in the next startup
• sample.data – Contains data for cached tables
• sample.backup – It’s a backup of last known consistent state of data file

A sample.lck file is used to lock the database

You will be able to see the files when the database is open as shown in picture below.

When the database is shutdown normally the following files will be available.

1.4 How to access HSQLDB Database

JDBC is used to access the database. The connection string used to establish a database connection is as follows:

Connection c = DriverManager.getConnection("jdbc:hsqldb:<protocol identifier>:<path to database>", "username", "password");

• Protocol identifier – Protocol identifier can be mem: file: res: hsql: http: hsqls: https:
• Path to database – Path to database can be specified using forward slashes in Windows and Linux. If relative path is used, the path will be taken relative to the directory in which the shell command to start JVM is executed
• username – Default user name is SA
• password – There are no default passwords. If you don’t want to set any then leave it blank

1.4.1 Connection Examples

• DriverManager.getConnection(“jdbc:hsqldb:file:sampledb”, “SA”, “”) – In this example, we are connecting to a file type database, whose files are located in the same folder as where the command to run the application is executed. The username is SA and there is no password. Only one Java process at a time can make an in-process connection to a given file type database unless the database is read-only.
• DriverManager.getConnection(“jdbc:hsqldb:file:/temp/db/sampledb”, “SA”, “”) – In this example, we are connecting to a file type database with files names sampledb.* reside in the folder temp/db. Do note that the relative path is relative to the directory from which the command to start JVM is executed.
• DriverManager.getConnection(“jdbc:hsqldb:mem:sampledb”, “SA”, “”) – In this example, we are connecting to a memory database thus only the name. Several memory database can exist at the same time distinguished by their name
• DriverManager.getConnection(“jdbc:hsqldb:res:org.jcg.sampledb”, “SA”, “”) – In this example, we are connecting to a resource type database. As it’s a Java resource, the path is a URL similar to path to a class. In this example, sampledb is the root name of the database files, which reside in “org/jcg” folder within the classpath (probably in a Jar). A Java resource is stored in a compressed format and decompressed in memory when used thus it should not contain large amounts of data. It’s always read-only.

1.5 Server Modes

Even though in-process access is faster as the data is not converted and sent over network, the issue is you will not be able to connect to the database outside of your application. For example, a Database Manager tool or another application.

In Server mode you can connect to the database from multiple sources. Those connections are translated into in-process connections. It can serve unlimited number of databases at the time of running the server or as the connection is received.

1.5.1 Types of Server Modes

There are 3 server modes based on the protocol used for communication between client and server.

• HyperSQL HSQL Server – This is the preferred mode and is the fastest. It uses proprietary communication protocol. In the following command we are starting the server for database named “sampledb” with public name “sdb”. Public name is used to hide the real name from users.

  java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:sampledb --dbname.0 sdb

• HyperSQL HTTP Server – This method is preferred if the machine hosting the database is restricted to only HTTP protocol. In this mode, it acts as a simple web server that allows clients to connect via HTTP. It can also serve static web pages.
• HyperSQL HTTP Servlet – In this mode, the servlet class available in the HSQLDB.jar file needs to be installed on the server to provide the connection. The database file path is specified using an application server property. Multiple databases can be served in this mode.

2. JDBC HSQLDB in Action

We have reviewed the basics of HSQLDB database. Let’s put together what we have learnt so far into a real example. We will write a simple Java application to connect to the sample database that is bundled with HSQLDB download, run a query to retrieve a list of customers from customer table.

2.1 Technologies used

For this example, we will use the following tools in a Windows 64-bit platform:

• NetBeans – 8.2
• Java – 1.8.0_161
• HSQLDB Database – 2.4.1

2.2 Pre-requisite

Since HSQLDB is implemented in Java, we have to make sure Java JDK is available before installing HSQLDB. In order to verify whether JDK is installed or not, open up a command prompt console and execute the following command.

java -version

If JDK is installed on the machine then you will get the following output:

Now that we have confirmed JDK installation let’s proceed to this link and download the HSQLDB database. Version 2.4.1 is the latest as of this writing. It comes in the form of a zip file named hsqldb-2.4.1.zip. Extract the entire hsqldb folder keeping the folder structure intact.

2.3 Setting up the database

Let’s setup our database and make sure the tables exist before writing the Java application. We can use the Database Manager that is bundled with HSQLDB download for this step. I have extracted the zip file and this is how the folder structure looks like:

Go to bin folder then double click runManagerSwing.bat file. It should open the Database Manager. Do note the current folder is changed to data folder as highlighted in the picture below. All it means is the database that we are going to create is going to be created in data folder.

You should have the Connect window open already. Enter the value as shown in the picture below then press ok.

Want to be a JDBC Master ?

Subscribe to our newsletter and download the JDBC Ultimate Guide right now!

In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

Download NOW!

Thank you!

We will contact you soon.

In the Setting Name field we provided a name to save our settings so that we can just select it from the dropdown next time. We have selected the stand alone database engine as we are going to create a file type catalog. Last part of the URL represents the database name. Here it means create sampledb files inside sampledb folder in data folder. The user name is SA and there is no password. Our database is now created. It should show up in the database manager as shown in the picture below.

Click on the “Options” option on the menu bar then select “Insert test data”.

You should see the customer list on the right hand pane. Our database is ready for the Java application. We can just shutdown for now. In order to do that, click on Command option from menubar and click SHUTDOWN.

In the command pane you should see the SHUTDOWN. Press “Execute SQL” button as shown in the picture below.

You can close the database manager for now as we don’t need it.

2.4 Writing the Java Application

Let’s write the Java application to demonstrate how to access HSQLDB using JDBC. I will use NetBeans as my IDE of choice. We will use the new project wizard to create a Java application. To do so, click on New Project button on the main toolbar or press Control + Shift + N on Windows (Cmd + Shift + N on Mac). Select Java from Categories list and Java Application from Projects list. Press Next.

Enter a Project Name, select a location on your hard disk. Project Folder will be filled automatically based on name and location. Press Finish.

We will have to add the HSQLDB JDBC driver jar file to our project so that JVM can load it automatically. In order to do that, right click on the project name and select properties.
Click on Libraries and press Add Jar/Folder button. Select the hsqldb.jar file in the lib folder.

Here is the listing of our JDBCHSQLDBExample.java file.

JDBCHSQLDBExample.java

package com.jcg.jdbcexamples;

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

/**
 *
 * @author Satya Choudhury
 */
public class JDBCHSQLDBExample {

    public static void getCustomers() {
        Connection conn = null;
        String db = "jdbc:hsqldb:hsql://localhost/sampledb;ifexists=true";
        String user = "SA";
        String password = "";
        
        try {
            // Create database connection
            conn = DriverManager.getConnection(db, user, password);
            
            // Create and execute statement
            Statement stmt = conn.createStatement();
            ResultSet rs =  stmt.executeQuery("select FIRSTNAME, LASTNAME from CUSTOMER");
            
            // Loop through the data and print all artist names
            while(rs.next()) {
                System.out.println("Customer Name: " + rs.getString("FIRSTNAME") + " " + rs.getString("LASTNAME"));
            }
            
            // Clean up
            rs.close();
            stmt.close();
        }
        catch (SQLException e) {
            System.err.println(e.getMessage());
        }
        finally {
            try {
                // Close connection
                if (conn != null) 
                    conn.close();
            }
            catch (SQLException e) {
                System.err.println(e.getMessage());
            }
        }
    }
    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // Retrieve all customers
        getCustomers();
    }
    
}

• Line 17 – It’s our database connection string. We are using HSQL server mode and saying that our sampledb database is available on the same machine i.e. localhost. Ifexists=true option will prevent creating a database if it doesn’t exist. It doesn’t matter in our case as we have already created sampledb database
• Line 27 – We are executing a query to retrieve the first and last name of all customers from customer table
• Line 30-32 – We are looping through the resultset and printing customer’s first and last name on console
• Line 35-36 – Closing the resultset and sql statement.
• Line 45 – Closing the connection to database
• Line 57 – Calling the getCustomers method

Before we run our application, we have to make sure that the database server is running in HSQL server mode and serving our sampledb. In order to do that, open up a console window then move to the data folder of your HSQLDB database. Execute the following command.

java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:sampledb/sampledb --dbname.0 sampledb

The database server should start successfully like shown in picture below.

Now, go back to NetBeans and press F6 to run the Java application. You should see the customer names on the console like shown in picture below.

3. Summary

In this tutorial we went through the HSQLDB basics then we wrote a simple Java application to demonstrate how to interact with an HSQLDB using JDBC.

4. Download the NetBeans Project

This was an example of how to interact with JDBC HSQLDB.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.