Java, JDBC and the HSQLDB database tutorial

What is HSQLDB?

The easiest way for a Java developer to learn Java Database Connectivity (JDBC) is to experiment with the HyperSQL Database, aka HSQLDB.

HSQLDB is a simple, lightweight database that requires no installation or complex configuration.

If you have an IDE such as Eclipse or IntelliJ at your disposal, and you are willing to work with a Java build tool like Maven or Gradle, you can get your first JDBC and HSQLDB application up and running in just minutes.

HSQLDB JDBC tutorial prerequisites

For this tutorial we will use Eclipse and Maven, but the process is the same for other build tools and IDEs.

The steps performed in this JDBC with HSQLDB tutorial are as follows:

1. Create a new Maven project in Eclipse.
2. Create an HSQLDB database with the DatabaseManagerSwing app.
3. Create a database table named Player.
4. Create a Java class with a main method that throws the generic Exception.
5. Add an import for the required JDBC SQL classes.
6. Code a database connection.
7. Programmatically issue an insert query against the HyperSQL database.
8. Validate that the JDBC insert succeeded.

HSQLDB’s Maven dependency

To get started, create a new Maven project in Eclipse, add the HSQLDB dependency to the Project Object Model (POM) file, and update the project.

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

That’s all you have to do to install and configure HSQLDB. The process really couldn’t be easier!

How do you create a database in HSQLDB?

Once you’ve added the HSQLDB POM dependency, right-click on your Eclipse project and select Run As and search for the DatabaseManagerSwing class located in the org.hsqldb.util package.

In the connection window that appears, enter in the following settings:

• Setting Name: JDBC HSQLDB Tutorial Database
• Type: HSQL Database Engine Standalone
• Driver: org.hsqldb.jdbc.JDBCDriver
• URL: jdbc:hsqldb:file:target/myDB
• User: SA
• Password: password

Use the built-in HSQLDB configuration tool to create a JDBC database for you app.

How do you create an HSQLDB table?

With the database created, the next step is to use the COMMAND –> CREATE TABLE option in the DatabaseManagerSwing app to quickly create a database table named PLAYER.

To do this, paste the following command into the query window and select ExecuteQuery:

CREATE MEMORY TABLE PLAYER (ID BIGINT IDENTITY NOT NULL PRIMARY KEY, loginname VARCHAR(255), password VARCHAR(255))

Then right-click on PUBLIC.PLAYER folder the left-hand pane, choose SELECT * FROM “PUBLIC”.”PLAYER”, and hit the Execute SQL button.

You will see an empty table with three columns: ID, LOGINNAME and PASSWORD.

The HSQL database manager makes it easy to create tables for JDBC connectivity.

How Java, JDBC and HSQLDB works

With the HSQLDB JDBC setup and configuration complete, it’s time to write a Java class that:

• imports java.sql.*
• has a main method that throws the generic Exception
• connects to HSQLDB with JDBC and adds a record

The basic structure of the Java class looks like this:

package com.mcnz.jdbc.hsql;
import java.sql.*;

public class JdbcExample {

  public static void main(String[] args) throws Exception {
    // JDBC HSQL tutorial code goes here
  }
}

How do you create an HSQLDB JDBC connection?

To connect to the database, you must provide the JDBC DriverManager with the name of the database and credentials for a user with rights to access it. Declare this information as String properties at the start of the main method, and then feed them to the getConnection() method of the DriverManager.

String db = "jdbc:hsqldb:file:db";
String user = "root";
String password = "password";
Connection connection = DriverManager.getConnection(db, user, password);

Execute an SQL insert query

With the HSQL JDBC connection created, you can obtain JDBC statement objects which enable you to issue SQL commands against the database.

The following lines of code use JDBC to add a new record to the Player table of the HSQLDB database:

String insertQuery = "INSERT INTO PLAYER VALUES (1,'McKenzie','password')";
Statement stmt = conn.createStatement();
stmt.execute(insertQuery);

Save your changes, close the DatabaseManagerSwing app if it is still running, and then run the code. This will add a new record to the Player table of the database.

Validate the SQL insert

To validate the successful JDBC insert into HSQLDB, run the DatabaseManagerSwing class again and query the Player table. You will see that your JDBC code has added a new record to the database, which proves that your HSQLDB JDBC example ran successfully.

The JDBC example successfully inserted a record into the HSQL database.

Close your Closeable JDBC resources

Both the JDBC Statement and Connection objects implement the Closeable interface, which means we should invoke the close() method on them when they are no longer in use.

This is done automatically if the objects are initialized in a try-with-resources block. In this case, however, we must call the close() method manually.

With these finishing touches, the complete JDBC HSQL example looks as follows:

package com.mcnz.hsql.example;
import java.sql.*;

public class JdbcExample {

  public static void main(String[] args) throws Exception {

    String db = "jdbc:hsqldb:file:db";
    String user = "root";
    String password = "password";

    Connection conn = DriverManager.getConnection(db, user, password);
    String insertQuery = "INSERT INTO PLAYER VALUES (1,'McKenzie','password')";
    Statement stmt = conn.createStatement();
    stmt.execute(insertQuery);

    stmt.close();
    conn.close();

  }
}

And that’s how easy it is to connect to HSQLDB with JDBC in a Java program.

Learn Apache Maven fast!