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

Posted on by

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.

6 thoughts on “OCP 11 Book Bonus: Creating a HSQL Database/Stored Procedure in Java 11”

  1. Hi Jeanne,
    I’m preparing the upgrade of java (1Z0-817).
    I bought both books for Java 8 and I passed 1Z0-808 and 1Z0-809.
    Now I bought the first book about Java 11, but I want to ask you if I can buy the second book before the publication date shown on Amazon (April 28th).
    I’d like to buy the Oracle voucher for the Java 11 exam but I will have to take the exam within six months of the purchase, so if I buy it today, by June 28 I have to take the exam otherwise the voucher will no longer be valid. I don’t know if I will do that, if the second book will be published on April 28th.
    Are there any chances of the second book being published first?
    I believe that in the second book only the private methods of the interfaces and a further deepening of modularity (as a novelty compared to Java 8) are left. Have you written any books on modularity? Maybe I could avoid buying the second one and studying another of your books on modularity.

    Thanks a lot!

    I have great respect for you!!!

    Adriana

  2. Oh, Jeanne, I forgot last question: I read your information about AWS certifications. I’d like to take the first AWS certification (cloud practitioner).
    Will you write books about this certification or other AWS certifications?
    I bought some books about this certification, even the official guide of Amazon.

    Your resume is all i would like to do and become.

    Thanks!

    Adriana

  3. Late April sounds like an accurate estimate for publication. There’s a lot of new topics on the 816 exam: modularity, security, advanced modules. Plus topics that are in the OCP 8 book but are covered in a different scope like JDBC. It’s a lot of work to study for an exam without a study guide!

  5. Hey Rock Stars (Jeanne and Scott),

    I have just passed 1Z0-819 OCP Java SE 11 Developer with 87%. I have provided feedback on the coderanch.

    https://coderanch.com/t/738093/certification/Feedback#3432170

    First, Thank you for your great effort in the form of OCP java 11 study guides and exam questions practice test books.

    Second, there was a question on CallableStatement in JDBC. We all are getting a single JDBC question in CallableStatement. Can you please edit your blog?

    Third, your service diagram on page 329 in chapter 6 Modular Application of OCP Java 11 Programmer II is wrong. The arrows are bit confusing. I have seen you use arrows for “requires” throughout your Book 1 and 2.

    (i) As you mentioned “No one requires service provider” but there is an arrow from service locator to service provider. I think it should be deleted

    (ii) As you found and made correction in errata page to remove arrow from service provider interface to service provider. I think, we still need an arrow but in opposite direction because service provider requires service provider interface.

    I matched your diagram with yours and other exam questions. I made a correct picture. There is no way to attach the picture here. If you could send me your email address, I could send you the correct picture

    Regards,

  6. Re CallableStatement, we confirmed with Oracle that the topic is not on the exam. Remember that there are sometimes unscored questions on the exam. Maybe Oracle is doing an experiment with that question.

Leave a Reply

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