Stored procedures in HSQLDB

Today, I had the fun of configuring HSQLDB to run stored procs. The entire process was not that bad, but there didn’t seem to be a simple tutorial for how to accomplish this task. Here is my attempt at that tutorial.

A Little Background:
My current app uses a stack of Wicket, Spring, and Hibernate with HSQLDB as the database for development and Oracle for test/prod deployments. Up to this point, we have used Hibernate to generate all of the SQL for acessing and modifying data.

One of our features is the ability to generate a report which cacluates YTD totals and averages. The generation processes several hundred thousand records so we chose not to implement it in Java/Hibernate. Instead we used stored procedures in Oracle. The generation of the report is initiated from a page within Wicket which means that my web interface must call into Spring and then into Hibernate to call the stored procedure.

For this scenario, I didn’t need to write full stored procedures in HSQLDB since HSQLDB is only used for my dev and unit testing. However, I did need HSQLDB to have stub procs to call so that I could pass all of my unit tests.

The Solution:
First, HSQLDB has to be configured to load the stored procs. Since I use Hibernate, I have an import.sql file that preps my database with a bunch of inserts.

From the hibernate website:
Also note that since Hibernate 3.1 you can include a file called “import.sql” in the runtime classpath of Hibernate. At the time of schema export it will execute the SQL statements contained in that file after the schema has been exported.

To the import.sql file, I can add my stored proc definition. Since HSQLDB is written in Java, it uses Java classes for its stored procs. Any Java method can be configured as a stored procedure such as Math.sqrt as shown in the HSQLDB FAQ.

Given this info, I created a new class named HSQLDummyStoredProcs. Inside of this class I have the following methods defined:

1. public class HSQLDummyStoredProcs {

2. 	public static void proc_Int(Integer i1) {

3. 	}

5. 	public static void proc_IntInt(Integer i1, Integer i2) {

6. 	}

8. 	public static void proc_IntDate(Integer i1, Date d1) {

9. 	}

11. 	public static void proc_LongDate(Long l1, Date d1) {

12. 	}

13. }

This class simply defines blank methods with various combinations of parameter types. Inside of my import.sql file, I can alias these methods to a stored procedure name such as :


CREATE ALIAS load_ReportItem FOR "com.mycompany.dao.sp.HSQLDummyStoredProcs.proc_IntDate";

This aliases the load_ReportItem stored procedure with the proc_IntDate method in my dummy proc class.

Now, anytime my code calls the load_ReportItem stored procedure, HSQLDB will execute my proc_IntDate method with the given parameters. I don’t have to worry about the functionality of the stored procs since I’m only trying to verify the stored procedure is called during my unit testing. I can use the same Spring/Hibernate code for both HSQLDB and Oracle. The testing and verification of the stored procedures are done by the Oracle developer.

Note:
If you are using HSQLDB as my primary database, you probably don’t have to go through the ALIAS step since you could call the stored procedure by the fully qualified Java method name. However, I still think the ALIAS is a good idea because it gives you one level of indirection between the DB names and the Java names.

Note 2:
HSQLDB does not support OUT parameters in a stored procedures. I was able to work around this issue by simply not setting any OUT parameters if HSQLDB was my database. The details of this workaround will be in the next post, but the basic idea is to let Spring inject an HSQLDB class that doesn’t do OUT parameters when I’m in dev, and inject an Oracle class that does set OUT parameters for other environments

In the next post, I’ll detail how I plugged the stored procedure code into my generic DAO framework and issues I discovered with having generic code work for HSQLDB and Oracle.