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.
- Download the HyperSQL Database Engine from SourceForge. Unzip to your favorite location after downloading the zip file.
- 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.
|
1 2 3 |
$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.
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.
|
1 2 3 |
$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.
HSQLDB Insert Test Data
We can use HSQL Database Manager to populate test data, as shown in the below image.
This will create few tables and insert some values into it, as shown in the below image.
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.
Once “SHUTDOWN” command is executed, it will produce the following screen.
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.
|
1 2 3 4 5 6 7 |
<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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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:
|
1 2 3 4 5 6 7 8 9 |
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.
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