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:
- Go to the HSQL home page.
- Click on the link for the latest version. (At the time of this blog post, that was 2.5.0)
- Click through the many screens until it downloads.
- 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; "
+ "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.
