HSQLDB Java API tutorial

HSQLDB stands for HyperSQL Database, it is in memory database, used to develop your applications very quickly. If you are curious to know about in memory database, you can go through the following article.

https://self-learning-java-tutorial.blogspot.com/2015/03/in-memory-database.html

By using HSQL, you can develop, test and deploy the applications. I am using following maven dependency for this demo.

                 <dependency>

                          <groupId>hsqldb</groupId>

                          <artifactId>hsqldb</artifactId>

                          <version>1.8.0.10</version>

                 </dependency>

HSQL Database

A Database in HSQL is called as a catalog. Based on the way they store information, Catalogs are divided into 3 categories.

Catalog	Description
mem	Stored entirely in RAM - without any persistence beyond the JVM process's life
file	Stored in filesystem files
res	Stored in a Java resource, such as a Jar and always read-only

File Catalog

It stores the data in file system. If you specify the file name as 'customerDb', then it creates 2 to 6 files all named the same but with different extensions, located in the same directory. Following table summarizes the files created.

File	Description
customerDb.properties	It contains settings about the database.
customerDb.script	It contains the definition of tables and other database objects, plus the data for non-cached tables.
customerDb.log	Contains recent changes to the database.
customerDb.data	Contains the data for cached tables
customerDb.backup	Contains compressed backup of the last known consistent state of the data file.
customerDb.lobs	Store large objects

If you see any file like 'customerDb.lck', it means that the database is open. This is deleted at a normal SHUTDOWN.

I am using file catalog for the demo purpose.

Create a database connection

By using DriverManager class, you can create a connection.

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:customerDb", "SA", "");

Notify above snippet, file: database name is "customerDb" and its files are located in the same directory as where the command to run your application was issued.

If you want to specify the full path of the database file, you can specify like below.

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:/opt/db/customerDb", "SA", "");   //Linux

Connection c = DriverManager.getConnection("jdbc:hsqldb:file:C:\\Users\\customerDb", "SA", ""); //Windows

How to create in memory databae (mem:)?

Inmemory database is created by using 'mem:' protocol.

Connection c = DriverManager.getConnection("jdbc:hsqldb:mem:customerDb", "SA", "");

Let’s try to create a table ‘customer’ in the database ‘customerDb’.

Database.java

package com.sample;

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

import org.hsqldb.jdbc.jdbcDataSource;

public class Database {
 private Connection connection;
 private String databaseName;

 public Database(String databaseName) throws SQLException {
  this.databaseName = databaseName;
  jdbcDataSource dataSource = new jdbcDataSource();
  dataSource.setDatabase("jdbc:hsqldb:file:" + databaseName);
  connection = dataSource.getConnection("sa", "");
 }

 public Connection getConnection() {
  return connection;
 }

 public String getDatabaseName() {
  return databaseName;
 }

 public void shutdown() throws SQLException {
  if(connection == null){
   return;
  }
  
  Statement st = connection.createStatement();

  /*
   * db writes out to files and performs clean shuts down otherwise there
   * will be an unclean shutdown when program ends
   */
  st.execute("SHUTDOWN");
  
  connection.close();
 }

}

Customer.java

package com.sample;

public class Customer {
 private int id;
 private String firstName;
 private String lastName;
 
 public Customer(){
  
 }

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getFirstName() {
  return firstName;
 }

 public void setFirstName(String firstName) {
  this.firstName = firstName;
 }

 public String getLastName() {
  return lastName;
 }

 public void setLastName(String lastName) {
  this.lastName = lastName;
 }

}

Customerutil.java

package com.sample;

import java.sql.SQLException;
import java.sql.Statement;

public class CustomerUtil {
 private Database database;

 public Database getDatabase() {
  return database;
 }

 public CustomerUtil(String databaseName) throws SQLException {
  database = new Database(databaseName);
 }

 public void createTable() throws SQLException {
  String query = "CREATE TABLE customer (id int, firstName varchar(30), lastName varchar(30), PRIMARY KEY(id))";
  Statement stmt = database.getConnection().createStatement();
  stmt.execute(query);
 }

 public void closeConnection() throws SQLException{
  database.shutdown();
 }
 
 public void insertCustomer(Customer customer) throws SQLException{
  int id = customer.getId();
  String firstName = customer.getFirstName();
  String lastName = customer.getLastName();
  Statement stmt = database.getConnection().createStatement();
  
   /* Insert data to employee table */
        String query = "INSERT INTO customer values(" + id + "," + firstName + "," + lastName + ")";
        System.out.println("Executing the query " + query);
        stmt.execute(query);
 }
}

CustomerUtilTest.java

package com.sample;

import java.sql.SQLException;

public class CustomerUtilTest {
 public static void main(String args[]) throws SQLException {
  String dbName = "C:\\Users\\Public\\databases\\customerDb";

  CustomerUtil util = new CustomerUtil(dbName);
  util.createTable();

  util.closeConnection();

 }
}

Run ‘CustomerUtilTest.java’, and go to the directory ‘C:\\Users\\Public\\databases’, you can able to see two files.

a.   customerDb.properties

b.   customerDb.script

customerDb.properties

#HSQL Database Engine 1.8.0.10
#Wed Nov 09 15:22:51 IST 2016
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=no
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0

customerDb.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE CUSTOMER(ID INTEGER NOT NULL PRIMARY KEY,FIRSTNAME VARCHAR(30),LASTNAME VARCHAR(30))
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10

Notify customerDb.script file, it has the statement to create the table 'Customer'.

Let’s try to insert new record into customer table.

Update 'CustomerUtilTest.java' like below.

CustomerUtilTest.java

package com.sample;

import java.sql.SQLException;

public class CustomerUtilTest {
 public static void main(String args[]) throws SQLException {
  String dbName = "C:\\Users\\Public\\databases\\customerDb";

  CustomerUtil util = new CustomerUtil(dbName);

  Customer customer = new Customer();
  
  customer.setId(1);
  customer.setFirstName("Hari Krishna");
  customer.setLastName("Gurram");

  util.insertCustomer(customer);

  util.closeConnection();

 }
}

Re run the above application. After successful execution of the above application, open ‘customerDb.script’ file. It contain following contents.

customerDb.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE CUSTOMER(ID INTEGER NOT NULL PRIMARY KEY,FIRSTNAME VARCHAR(30),LASTNAME VARCHAR(30))
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO CUSTOMER VALUES(1,'Hari Krishna','Gurram')

Try to insert one more record and make sure you don’t lose the old data.

Update CustomerUtil.java by adding ‘printAllCustomers’ method.

CustomerUtil.java

package com.sample;

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

public class CustomerUtil {
 private Database database;

 public Database getDatabase() {
  return database;
 }

 public CustomerUtil(String databaseName) throws SQLException {
  database = new Database(databaseName);
 }

 public void createTable() throws SQLException {
  String query = "CREATE TABLE customer (id int, firstName varchar(30), lastName varchar(30), PRIMARY KEY(id))";
  Statement stmt = database.getConnection().createStatement();
  stmt.execute(query);
 }

 public void closeConnection() throws SQLException {
  database.shutdown();
 }

 public void insertCustomer(Customer customer) throws SQLException {
  int id = customer.getId();
  String firstName = customer.getFirstName();
  String lastName = customer.getLastName();
  Statement stmt = database.getConnection().createStatement();

  /* Insert data to employee table */
  String query = "INSERT INTO customer values(" + id + ",\'" + firstName + "\',\'" + lastName + "\')";

  System.out.println("Executing the query " + query);

  stmt.execute(query);
 }

 public void printAllCustomers() throws SQLException {
  /* Insert data to employee table */
  String query = "SELECT * FROM customer";

  Statement stmt = database.getConnection().createStatement();

  ResultSet rs = stmt.executeQuery(query);

  while (rs.next()) {
   int id = rs.getInt("id");
   String firstName = rs.getString("firstName");
   String lastName = rs.getString("lastName");
   System.out.println(id + " " + firstName + " " + lastName);
  }
  rs.close();

 }
}

CustomerUtilTest.java

package com.sample;

import java.sql.SQLException;

public class CustomerUtilTest {
 public static void main(String args[]) throws SQLException {
  String dbName = "C:\\Users\\Public\\databases\\customerDb";

  CustomerUtil util = new CustomerUtil(dbName);

  Customer customer = new Customer();
  
  customer.setId(2);
  customer.setFirstName("Rachit");
  customer.setLastName("Kumar");

  util.insertCustomer(customer);
  
  util.printAllCustomers();

  util.closeConnection();

 }
}

Output

Executing the query INSERT INTO customer values(2,'Rachit','Kumar')
1 Hari Krishna Gurram
2 Rachit Kumar

customerDb.script

CREATE SCHEMA PUBLIC AUTHORIZATION DBA
CREATE MEMORY TABLE CUSTOMER(ID INTEGER NOT NULL PRIMARY KEY,FIRSTNAME VARCHAR(30),LASTNAME VARCHAR(30))
CREATE USER SA PASSWORD ""
GRANT DBA TO SA
SET WRITE_DELAY 10
SET SCHEMA PUBLIC
INSERT INTO CUSTOMER VALUES(1,'Hari Krishna','Gurram')
INSERT INTO CUSTOMER VALUES(2,'Rachit','Kumar')