HSQLDB Tutorial

Filed Under: Java

HSQLDB (HyperSQL DataBase) is a relational database software written in Java. HSQLDB is very easy to use and requires very less memory. HSQLDB provides multithreading support and it’s a great choice to easily test your application against a database.

HSQLDB Installation

Let’s see how we can install HSQLDB easily.

  1. Download the HyperSQL Database Engine from SourceForge. Unzip to your favorite location after downloading the zip file.
  2. HyperSQL Database latest version is 2.4.1 and it supports Java 8, Java 9 and Java 10. If you have required java version installed, then you are ready to start the database.

Starting HSQLDB Database

Below command will start the HSQLDB database instance.


$java -cp lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:data/mydb --dbname.0 Test

Above command will run HSQLDB in server mode, we are providing the location where database script file will be created. dbname.0 specifies the public name of the database and it’s used by client applications to connect to this database instance.

Below image shows the console output produced by the command, notice that terminal will remain open after database server is started. If you want HSQLDB to work in the background, then add & to the above command.

HSQLDB Server Start

HSQL Database Manager

HSQL Database manager provides a GUI to connect to HSQL database instances. It allows us to run some useful commands, such as populating test data, shutting down the server etc. We can use below command to launch the HSQL Database Manager Swing application.


$java -cp lib/hsqldb.jar org.hsqldb.util.DatabaseManagerSwing

When the application launches, a popup window opens to enter the database configuration details to connect. Provide the required information as shown in below image and connect to the database instance.

HSQL Database Manager

HSQLDB Insert Test Data

We can use HSQL Database Manager to populate test data, as shown in the below image.

HSQLDB insert test data

This will create few tables and insert some values into it, as shown in the below image.

hsqldb test data

HSQLDB Shutdown Server

We can quit the terminal to abruptly shut down the HSQLDB server. When we will start the server next time, it will pick where it has been terminated. However, we can use HSQL Database Manager to shut down the server gracefully.

hsqldb shutdown server

Once “SHUTDOWN” command is executed, it will produce the following screen.
HSQLDB shutdown complete

HSQLDB Script File

HSQLDB stores database table and its data in the form of SQL script file. For every database instance, HSQLDB creates a script file. If you open the script file in a text editor, it will look like a SQL script file. You can use this script file to export data from HSQLDB to other databases such as MySQL, Oracle etc.

HSQLDB Example

Let’s look at a simple example where we will connect to our HSQLDB database instance and retrieve some data from the test data we have inserted.

Create a simple maven project and add HSQLDB driver dependency.


<dependency>
	<groupId>org.hsqldb</groupId>
	<artifactId>hsqldb</artifactId>
	<version>2.4.1</version>
</dependency>

We will use org.hsqldb.jdbc.JDBCDriver driver class for creating connection to our HSLQDB database server. Below is the utility class showing how to create SQL Connection to HSQLDB database.


package com.journaldev.hsqldb;

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

public class HSQLDBConnection {

	public static Connection getConnection() {
		Connection con = null;

		try {
			Class.forName("org.hsqldb.jdbc.JDBCDriver");
			System.out.println("HSQLDB JDBCDriver Loaded");
			con = DriverManager.getConnection(
					"jdbc:hsqldb:hsql://localhost/Test", "SA", "");
			System.out.println("HSQLDB Connection Created");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
}

Here is the test class where we are getting the HSQLDB connection and running some database queries.


package com.journaldev.hsqldb;

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

public class HSQLDBExample {

	public static void main(String[] args) {
		Connection con = HSQLDBConnection.getConnection();
		System.out.println("Connection Obtained");

		try {
			PreparedStatement ps = con.prepareStatement(
					"select id, firstName, lastName from customer");
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				System.out.println("ID = " + rs.getInt("id") + 
						", Name = " + rs.getString("firstName") + " "
						+ rs.getString("lastName"));
			}
			rs.close();
			con.close();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
	}
}

Notice that except the Driver class, everything is using generic SQL classes. You can run other CRUD operation commands too.

Output produced by the main program:


HSQLDB JDBCDriver Loaded
HSQLDB Connection Created
Connection Obtained
ID = 0, Name = Laura Steel
ID = 1, Name = Robert King
...
ID = 49, Name = Robert Steel

Below image shows the final structure of our very simple HSQLDB example project.

HSQLDB Eclipse Java Example Tutorial

You can download the example code from our GitHub Repository.

Summary

HSQLDB is a very good utility to have a local database for development and running test systems. It has very low memory requirements and provides most of the basic features for the application. I think it lags into security area because the data is stored in simple text files. You can use it for basic development purposes and switch to better relational databases such as MySQL at later point of time.

Reference: Official Website

Comments

  1. Aakash Shinde says:

    Works Perfectly thanks!!

  2. asd says:

    gooooooooooooooooooooooooooooood thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages