"; */ ?>

Use HSQLDB Functions with Hibernate Mapping Files

Let’s say you need to do a “local integration” test, where you code does not depend on external systems (queues/external servers/DB/etc..). Creating unit tests, mocking/stubbing everything out is all good, but sometimes you need to be able to run tests that are as close as possible to the “real world” deal, while you are in “local mode” – e.g. plane, subway, basically somewhere without access to the real external systems.

One of ways you can approach it with databases is to load schemas you need in memory, and work (test) against those schemas. This is relatively easy to do with HSQLDB, and there are many “googlable” guides on how to do it. However in this little howto, I want to show you how you can define your own HSQLDB functions, and use them as either “stubs” (or even “real deal”).

Here is an example on why you may need it. Consider this Hibernate mapping file that is used in your application:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
	<class name="CustomerCreditCardSearchResultDto" table="CR_CREDIT_CARD">
		<id name="id" column="CR_CREDIT_CARD_ID">
			<generator class="native">
                                 <param name="sequence">SQ_CR_CREDIT_CARD</param>
                <property name="accountNumber" formula="some_pkg.decode( ACCOUNT_NUMBER )" />
                <property name="secretNumber" formula="some_pkg.decode( SECRET_NUMBER )" />
                <property name="expirationDate" column="EXPIRATION_DATE" type="date" />
                <property name="zipCode" column="ZIP_CODE" />

Let’s say you created a CR_CREDIT_CARD table in in-memory DB, populated it, started your test. Now when you try to read a “CustomerCreditCardSearchResultDto” object somewhere in your test, Hibernate will construct an SQL query from the mapping file above, and execute it on the in-memory DB.

However there is a problem – it is going to fail with a similar Exception:

Caused by: java.sql.SQLException: Unexpected token: DECODE in statement [select <query here> .... ]
	at org.hsqldb.jdbc.Util.throwError(Unknown Source)
	at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source)
	at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source)
	at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:248)
	at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302)
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:442)
	at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
	at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:105)
	at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
	at org.hibernate.loader.Loader.doQuery(Loader.java:661)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
	at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
	... 60 more

If there is an Exception – there is a Reason for it :) Notice these two mappings in the Hibernate mapping file above:

<property name="accountNumber" formula="some_pkg.decode( ACCOUNT_NUMBER )" />
<property name="secretNumber" formula="some_pkg.decode( SECRET_NUMBER )" />

That says Hibernate to construct SQL that uses this “some_pkg.decode()” custom function/store procedure after (in case of SELECT) reading these values from a database. However, in you local testing environment, you do not have this function defined. It is probably defined in the DB (like Oracle/DB2/etc) itself.

But for most, if not for all, technical problems there is a solution, so don’t worry – you can define this function(s) yourself. Since it is a test, the most logical thing, since you want to abstract the code out from the external systems (and their functions) would be to define stubs to these functions.

Let’s create a utility “HsqlFunctions” Java class with static methods to be used by HSQLDB (it can only use static Java methods in its SQL, btw):

package org.project.test.util;
 * HSQL functions to be aliased.
 *    DDL example:
 *    CREATE ALIAS DECODE FOR "org.project.test.util.HsqlFunctions.decodeString"
 * @author blog.dotkam.com
public final class HsqlFunctions
   private HsqlFunctions()
     // static utility class - does not need to be constructed.
     * Stub for the decode function.
     * Usually used to "please" Hibernate Mapping Files.
     * @param value - String value for the column
     * @return - returns the same String value
    public static String decodeString( String value )
        return value;

Now, when creating a test schema, you can ALIAS this static method as HSQLDB function like this:

     CREATE ALIAS DECODE FOR "org.project.test.util.HsqlFunctions.decodeString"

This will tell HSQLDB to call “decodeString” static Java method on the column value, every time it sees “decode( COLUMN )” in SQL.

One thing to notice, though – make sure the type that the Java methods take are exactly the same as defined by DDL (Database Schema). For example, if you pass in column value as an Object:

    public static String decodeString( Object value )
        return String.valueOf (value );

And the column is defined as VARCHAR in schema, HSQLDB will try to pack that String into an Object, and it will fail with a similar exception:

Caused by: java.sql.SQLException: Wrong data type: hexadecimal string with odd number of characters in statement [select ... <query here>]
	at org.hsqldb.jdbc.Util.throwError(Unknown Source)
	at org.hsqldb.jdbc.jdbcPreparedStatement.executeQuery(Unknown Source)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
	at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
	at org.hibernate.loader.Loader.doQuery(Loader.java:662)
	at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
	at org.hibernate.loader.Loader.loadEntity(Loader.java:1785)
	... 60 more

Below is just an excerpt on how you would set up the schema to use it with HSQLDB in your test:

    private static final String CREATE_ALIASES =
        "FOR \"org.project.test.util.HsqlFunctions.decodeString\"";
    private static final String CREATE_SEQUENCES =
    private static final String CREATE_TABLES =
        "DROP TABLE cr_credit_card IF EXISTS;" +
        "CREATE TABLE cr_credit_card (" +
            "cr_credit_id NUMERIC(15), " +
            "account_number VARCHAR(48)," +
        //  Creating the schema
        m_jdbcTemplate.execute( CREATE_ALIASES );
        m_jdbcTemplate.execute( CREATE_SEQUENCES );
        m_jdbcTemplate.execute( CREATE_TABLES );
        //  HSQLDB is ready to be populated with data at this point.

You can use DBUnit to create the schema above and populate it with the data. The above is just a straight forward hardcoded example.
Notice how it also creates a sequence “SQ_CR_CREDIT_CARD” to please the Hibernate, and others who might use it in the application.

Happy “local integration” testing!

Feel free to post questions/comments/suggestions, I’ll try to respond when have a free second or two :)