Home > Hsqldb, Java, Testing > Testing a stored procedure using hsqldb

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

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.

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.

Categories: Hsqldb, Java, Testing
  1. 8 January 2008 at 1:52 pm

    very interesting.
    i’m adding in RSS Reader

  2. 7 May 2008 at 8:56 pm

    Thanks 🙂

  3. 22 May 2008 at 8:21 am

    Is there a way to return multiple values from stored procedure?

  4. 22 May 2008 at 8:40 am

    It can return multiple values which you should read by using a ResultSet. If you want to simulate multiple values from a stored procedure you should make your test method return a ResultSet (or its implementation RowSet) which you will have filled up with the values needed.

  5. Larry
    29 October 2008 at 9:06 am

    Good article.

    Can you describe more on how to fetch the return values, since normally I need to use “registerOutParameter()” function to do it.

    While from HSQLDB source codes ( , it is stated it is not supported.

  6. 29 October 2008 at 9:14 am

    Are you sure it’s not supported? This:


    states that it is supported. It’s HSQLDB 1.7.2 that does not support this feature.

  7. Larry
    30 October 2008 at 12:47 am

    I checked the lastest source codes inside
    That’s the generic statement for the function, and probably they forget to update the statement for as well

    public void registerOutParameter(int parameterIndex,
    int sqlType) throws SQLException {
    throw Util.notSupported();

    I will post the question in help forum to get the result as well.

  8. Larry
    4 November 2008 at 12:19 am

    It is stated it will be supported in 1.9.0.x release.

    This blog save me a lot of time, thx.

  9. Larry Cai
    13 July 2009 at 12:50 am

    Do you have time to update the codes for 1.9.x release and post new blog ? seldom people use this feature, so your experience is very useful.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: