Testing a stored procedure using hsqldb

Today I needed to test a data access object that will be calling a stored procedure in the database. Since we are using HSQLDB and mock objects to do all the database interactions while we develop, I needed to test it without having access to the actual stored procedure. At first I wasn’t sure how to do it. Then I started looking at the HSQLDB documentation and I realised that I could create an alias to a static method and treat it exactly like I could treat the stored procedure.

Lets say that the stored procedure returns a random number from 1 to 100. In my test class I define a static method that returns this number

public static int random()
{
return 1 + (int)(Math.random() * 100);
}


Then I create an alias to this method call


// Define the alias to the stored procedure as a
// static final variable
private static final String SP =
"CREATE ALIAS randomStoredProcedure
FOR\"my.class.DaoTester.random\"";

Note that I needed to define the full class name of my test class, including the package. I also used double quotes around the class declaration since HSQLDB automatically converts all lower case letters to upper case.

Once the stored procedure is loaded up in the in-memory database using

// Load the alias.
jdbc.update(SP);


it can be called from the actual DAO implementation class like you would call any other stored procedure


// Call it to get the random number.
int random = jdbc.queryForInt("{call randomStoredProcedure()}");


The jdbc variable is of type SimpleJdbcOperations from the Spring framework. The implementation class can be used without changes with the real stored procedure. Neat.