OCP 17 Book Bonus: Creating a HyperSQL Database in Java 17

Chapter 15 in our OCP Oracle Certified Professional Java SE 17 Study Guide: Exam 1Z0-829 uses the database setup described in this post. We are using HyperSQL because it supports SQL based stored procedures which are the most common form.

You don’t need to know anything about writing SQL or creating stored procedures for the exam. You do have to know how to call them from Java. This blog post is for readers who have purchased our book, OCP Oracle Certified Professional Java SE 17 Study Guide: Exam 1Z0-829 and want to follow along. It also includes the database installation instructions and setup code so you can simply copy/paste it. The actual book covers what you need to know for the exam.

Download HSQL

Option 1: Download the latest version of hsqldb.jar from Maven Central. The jar is about 1.5 MB.

Option 2: HSQL is an open source database. It is really easy to install and use. To install:

  1. Go to the HSQL home page.
  2. Click on the link for the latest version. (At the time of this blog post, that was 2.6.0)
  3. Click through the many screens until it downloads.
  4. Download the zip file and unzip it. This will create a folder named something like hsqldb-2.6.0. Drill down to hsqldb and then lib to get the hsqldb.jar file.

Copy this hsqldb.jar to someplace convenient on your machine.

Creating your initial database

To start out, copy this code into a file named SetupDatabase.java.

import java.sql.*;

public class SetupDatabase {

    public static void main(String[] args) throws Exception {
        String url = "jdbc:hsqldb:file:zoo";
        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt = conn.createStatement()) {

            dropExisting(conn);
            createTables(conn);
            createStoredProcedures(conn);

            printCount(conn, "SELECT count(*) FROM names");
        }
    }

    private static void dropExisting(Connection conn) throws SQLException {
        run(conn, "DROP PROCEDURE read_e_names IF EXISTS");
        run(conn, "DROP PROCEDURE read_names_by_letter IF EXISTS");
        run(conn, "DROP PROCEDURE magic_number IF EXISTS");
        run(conn, "DROP PROCEDURE double_number IF EXISTS");
        run(conn, "DROP TABLE names IF EXISTS");
        run(conn, "DROP TABLE exhibits IF EXISTS");
    }

    private static void createTables(Connection conn) throws SQLException {
        run(conn, """
                CREATE TABLE exhibits (
                  id INTEGER PRIMARY KEY,
                  name VARCHAR(255),
                  num_acres DECIMAL(4,1))""");

        run(conn, """
                CREATE TABLE names (
                   id INTEGER PRIMARY KEY,
                   species_id integer REFERENCES exhibits (id),
                   name VARCHAR(255))""");

        run(conn, "INSERT INTO exhibits VALUES (1, 'African Elephant', 7.5)");
        run(conn, "INSERT INTO exhibits VALUES (2, 'Zebra', 1.2)");

        run(conn, "INSERT INTO names VALUES (1, 1, 'Elsa')");
        run(conn, "INSERT INTO names VALUES (2, 2, 'Zelda')");
        run(conn, "INSERT INTO names VALUES (3, 1, 'Ester')");
        run(conn, "INSERT INTO names VALUES (4, 1, 'Eddie')");
        run(conn, "INSERT INTO names VALUES (5, 2, 'Zoe')");
    }

    private static void createStoredProcedures(Connection conn) throws SQLException {
        String noParams = """
                CREATE PROCEDURE read_e_names()
                READS SQL DATA DYNAMIC RESULT SETS 1
                BEGIN ATOMIC
                DECLARE result CURSOR WITH RETURN FOR SELECT * FROM names WHERE LOWER(name) LIKE 'e%';
                OPEN result;
                END""";

        String inParam = """
                CREATE PROCEDURE read_names_by_letter(IN prefix VARCHAR(10))
                READS SQL DATA DYNAMIC RESULT SETS 1
                BEGIN ATOMIC
                DECLARE result CURSOR WITH RETURN FOR
                SELECT * FROM names WHERE LOWER(name) LIKE CONCAT(LOWER(prefix), '%');
                OPEN result;
                END""";

        String inOutParam = """
                CREATE PROCEDURE double_number(INOUT num INT) READS SQL DATA
                  DYNAMIC RESULT SETS 1 
                  BEGIN ATOMIC 
                  SET num = num * 2; 
                  END""";

        String outParam = """
                CREATE PROCEDURE magic_number(OUT num INT) READS SQL DATA
                      BEGIN ATOMIC
                     SET num = 42;
                      END""";

        run(conn, noParams);
        run(conn, inParam);
        run(conn, outParam);
        run(conn, inOutParam);
    }

    private static void run(Connection conn, String sql) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.executeUpdate();
        }
    }

    private static void printCount(Connection conn, String sql) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery()) {
            rs.next();
            System.out.println(rs.getInt(1));
        }
    }
}


To run the program, you need to include the hsqldb jar file in your classpath. If you don’t know how to find it or encounter problems see the below frequently asked questions in this blog post.

java -cp "<path to jar>/hsqldb.jar" SetupDatabase.java

If all goes well, the program will output the number 5.

Note that we used single file source code execution here. This simplified the classpath since we only needed to specify the derby jar. Had we compiled our class we would have needed to include it as well (along with an operating system delimiter)

Alternatively, you could have added HSQL to your CLASSPATH environment variable and just run the program as

java SetupDatabase.java

What does this program actually do?

The main method starts out by obtaining a connection to the HSQL database. It then calls a number of run methods to actually run the SQL. The run methods use a PreparedStatement with all the data hardcoded.

First the code drops anything already in the database. This allows you to run the program over and over receiving the same results. Then, the code runs two SQL statements to create tables in the zoo database. The commands each include:

  • the table name – exhibits and names
  • the fields in each table along with their type. Integer is like a Java int. Decimal is like a Java double. Varchar stands for variable character and is like a String. The variable length part means that the database doesn’t need to allocate space for all 255 characters and should only use the space for the actual length of the string. (This matters when you frequently update the field with values of different lengths)
  • the primary key for each table – this tells the database how to you uniquely identify each row

Next, the code runs seven SQL statements to insert rows into these tables. The order of the data columns matches the order the fields were defined in the create statements. After that, it creates the stored procedures used in the book.

Finally, the code runs a query to check the rows were added to the database. The count(*) function in SQL always returns a number. For an empty table, this number is zero. Therefore, we can call rs.next() outside of a conditional or loop. We know there is always a number being returned.

HSQL will create some “zoo” files in your current directory for the database and logs.

Frequently Encountered Problems

If you have an error that isn’t here or have trouble with these instructions, feel free to ask a question in the CodeRanch forums

What does “user lacks privilege or object not found in statement” mean?

In HSQL, it means “something went wrong.” It could be a runtime error in your stored proc. It could be that you aren’t calling it right. Confusing message, I know.

What does “no suitable driver” found mean?

Exact message:

Exception in thread "main" java.sql.SQLException: No suitable driver found for jdbc:hsqldb:file:zoo
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:706)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:252)
	at SetupDatabase.main(SetupDatabase.java:7)

This means you forgot to include the hsqldb.jar in your classpath or are pointing to an invalid location for it.

Making sample JDBC code easier

I’m writing a lot of small JDBC examples for our upcoming practice tests book. I got tired of writing the same code over and over so I wrote this helper class. It can:

  • Drop the table if it already there (because I run the same examples many times)
  • Run a varargs of SQL statements (to create tables and insert data)
  • Spit out the contents of a table (to confirm what happened)


import java.sql.*;
public class DerbyUtil {

    public static void dropTablesIfExist(String url, String... tables) throws SQLException {
        try (var conn = DriverManager.getConnection(url)) {
            for (String table : tables) {
                try (PreparedStatement ps = conn.prepareStatement("DROP TABLE " + table)) {
                    ps.executeUpdate();
                } catch (SQLException e) {
                    // ignore exceptions on drop since table might not exist
                }
            }
        }
    }

    public static void run(String url, String... sqlStatements) throws SQLException {
        try (var conn = DriverManager.getConnection(url)) {
            for (String stmt : sqlStatements) {
                try (PreparedStatement ps = conn.prepareStatement(stmt)) {
                    ps.executeUpdate();
                }
            }
        }
    }

    public static void outputTable(String url, String tableName) throws SQLException {
        String sql = "SELECT * FROM " + tableName;
        try (var conn = DriverManager.getConnection(url);
             var ps = conn.prepareStatement(sql);
             var rs = ps.executeQuery()) {

            var metadata = rs.getMetaData();
            int numColumns = metadata.getColumnCount();

            while (rs.next()) {
                outputRow(rs, numColumns);
            }
        }
    }

    private static void outputRow(ResultSet rs, int numColumns) throws SQLException {
        for (int i = 1; i <= numColumns; i++) {
            System.out.print(rs.getString(i));
            System.out.print('\t');
        }
        System.out.println();
    }
}

OCP 11 Book Bonus: Creating a HSQL Database/Stored Procedure in Java 11

Update (11/05/2020): Read The 1Z0-819 Exam page to learn how you can easily our Java 11 Study Guides to prepare for Oracle’s 1Z0-819 Exam, as well as the 1Z0-817 Upgrade Exam.

For the PreparedStatement section of chapter 10, we used Derby. We recommend following the creating Derby instructions first.

For the CallableStatement section we used HSQL (Hyper Structured Query Database). We switched because Derby doesn’t use SQL based stored procedures which are the most common form.

You don’t need to know anything about creating stored procedures for the exam. You do have to know how to call them. This blog post is for readers who have purchased our book, OCP: Oracle Certified Professional Java SE 11 Programmer II Study Guide: Exam 1Z0-816 and want to follow along. It also includes the database installation instructions and setup code so you can simply copy/paste it. The actual book covers what you need to know for the exam.


This blog post assumes you are reading chapter 10 of our OCP 11 book for 816 exam and have gotten up to the part that references this blog post.


Download HSQL

Option 1: Download the hsqldb.jar from Maven Central

Option 2: HSQL is an open source database. It is really easy to install and use. To install:

  1. Go to the HSQL home page.
  2. Click on the link for the latest version. (At the time of this blog post, that was 2.5.0)
  3. Click through the many screens until it downloads.
  4. Download the zip file and unzip it. This will create a folder named something like hsqldb-2.5.0. Drill down to hsqldb and then lib to get the hsqldb.jar file.

Copy this hsqldb.jar to someplace convenient on your machine. The same directory you picked for the derby jar is a good choice.

Creating your initial database

To start out, copy this code into a file named SetupHsqlDatabase.java.

import java.sql.*;

public class SetupHsqlDatabase {

    public static void main(String[] args) throws Exception {
        String url = "jdbc:hsqldb:file:zoo";
        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt = conn.createStatement()) {

            run(conn, "DROP PROCEDURE read_e_names IF EXISTS");
            run(conn, "DROP PROCEDURE read_names_by_letter IF EXISTS");
            run(conn, "DROP PROCEDURE magic_number IF EXISTS");
            run(conn, "DROP PROCEDURE double_number IF EXISTS");
            run(conn, "DROP TABLE names IF EXISTS");
            run(conn, "DROP TABLE exhibits IF EXISTS");

            run(conn, "CREATE TABLE exhibits ("
                    + "id INTEGER PRIMARY KEY, "
                    + "name VARCHAR(255), "
                    + "num_acres DECIMAL(4,1))");

            run(conn, "CREATE TABLE names ("
                    + "id INTEGER PRIMARY KEY, "
                    + "species_id integer REFERENCES exhibits (id), "
                    + "name VARCHAR(255))");

            run(conn, "INSERT INTO exhibits VALUES (1, 'African Elephant', 7.5)");
            run(conn, "INSERT INTO exhibits VALUES (2, 'Zebra', 1.2)");

            run(conn, "INSERT INTO names VALUES (1, 1, 'Elsa')");
            run(conn, "INSERT INTO names VALUES (2, 2, 'Zelda')");
            run(conn, "INSERT INTO names VALUES (3, 1, 'Ester')");
            run(conn, "INSERT INTO names VALUES (4, 1, 'Eddie')");
            run(conn, "INSERT INTO names VALUES (5, 2, 'Zoe')");

            String noParams = "CREATE PROCEDURE read_e_names() "
                    + "READS SQL DATA DYNAMIC RESULT SETS 1 "
                    + "BEGIN ATOMIC "
                    + "DECLARE result CURSOR WITH RETURN FOR SELECT * FROM names WHERE name LIKE 'e%'; "
                    + "OPEN result; "
                    + "END";

            String inParam = "CREATE PROCEDURE read_names_by_letter(IN prefix VARCHAR(10)) "
                    + "READS SQL DATA DYNAMIC RESULT SETS 1 "
                    + "BEGIN ATOMIC "
                    + "DECLARE result CURSOR WITH RETURN FOR " +
                    " SELECT * FROM names WHERE name LIKE CONCAT(prefix, '%'); "
                    + "OPEN result; "
                    + "END";

            String inOutParam = "CREATE PROCEDURE double_number(INOUT num INT) READS SQL DATA\n" +
                    "  DYNAMIC RESULT SETS 1 " +
                    "  BEGIN ATOMIC " +
                    "  SET num = num * 2; " +
                    "  END";

            String outParam = "CREATE PROCEDURE magic_number(OUT num INT) READS SQL DATA\n" +
                    "  BEGIN ATOMIC " +
                    "  SET num = 42;" +
                    "  END";

            run(conn, noParams);
            run(conn, inParam);
            run(conn, outParam);
            run(conn, inOutParam);

            printCount(conn, "SELECT count(*) FROM names");
        }
    }

    private static void run(Connection conn, String sql) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.executeUpdate();
        }
    }

    private static void printCount(Connection conn, String sql) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ResultSet rs = ps.executeQuery();
            rs.next();
            System.out.println(rs.getInt(1));
        }
    }
}

To run the program, you need to include the hsqldb jar file in your classpath. See the bottom of the creating Derby instructions FAQs if you aren’t sure how to do to this.

For example:

java -cp "<pathToHsql>/hsqldb.jar" SetupHsqlDatabase.java

If all goes well, the program will output the number 5.

What does this program actually do?

This program starts by doing the same thing as the Derby one. It then creates the stored procedures used in the book.

HSQL will create some “zoo” files in your current directory for the database and logs.

What does “user lacks privilege or object not found in statement” mean?

In HSQL, it means “something went wrong.” It could be a runtime error in your stored proc. It could be that you aren’t calling it right. Confusing message, I know.