Unit test MyBatis with HSQL instead of Oracle

Question

I would like to unit test my MyBatis persistence layer using an HSQL in-memory database. The real application uses an Oracle database. This worked fine unitl we started adding auto incremented numbers for the id columns. Oracle requires the use of a sequence to get the incremented number so a sequence called basis_seq was created in the Oracle database. In my MyBatis mapper XML file I have this:

<insert id="insertBasis" parameterType="com.foo.Basis" useGeneratedKeys="true" keyProperty="id">
        <selectKey resultType="long" keyProperty="id" order="BEFORE">
            SELECT basis_seq.NEXTVAL FROM DUAL
        </selectKey>
        insert into basis
        (id, name)
        values
        (#{id}, #{name})
</insert>

This works when I run the application but the unit test gets an error:

org.springframework.jdbc.BadSqlGrammarException: Error selecting key or setting result to parameter object. Cause: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: DUAL ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: DUAL

As I understand 'DUAL' is some kind of virtual table in Oracle that stores the sequences and I don't have this in my test database. If I remove the <selectKey>-tag the unit test work (since HSQL can autogenerate ids for columns marked identity) but not the real application. One workaround would be to create separate MyBatis mapper XML files for the unit tests without the <selectKey>-tag but this is undesired since I want to test the real configuration.

Is there a way to create and use a sequence in HSQL as well or maybe some MyBatis workaround for this? Or should I use another database for my unit test like H2?

---

I use:

• Spring 3.0.5
• HSQL 2.2.4
• MyBatis 3.0.5

---

UPDATE:

After getting the answer from fredt, here is how I edited my Spring configuration:

Before I defined my data source with:

<jdbc:embedded-database id="dataSource">
    <jdbc:script location="classpath:test-data/schema.sql" />
    <jdbc:script location="classpath:test-data/data.sql" />
</jdbc:embedded-database>

Now I do this:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="org.hsqldb.jdbcDriver" />
    <property name="url" value="jdbc:hsqldb:mem:test;sql.syntax_ora=true" />
    <property name="username" value="sa" />
    <property name="password" value="" />
</bean>

<jdbc:initialize-database data-source="dataSource">
    <jdbc:script location="classpath:test-data/schema.sql" />
    <jdbc:script location="classpath:test-data/data.sql" />
</jdbc:initialize-database>

Also, in schema.sql I need to create the sequences:

CREATE SEQUENCE BASIS_SEQ START WITH 1000 INCREMENT BY 1;
CREATE SEQUENCE OTHER_SEQ START WITH 1000 INCREMENT BY 1;

(if you run this script many times during unit testing, remember to add drop sequence BASIS_SEQ if exists; to top of schema.sql)

Solution 1

Latest HSQLDB provides extensive Oracle syntax compatibility. All you need is add sql.syntax_ora=true to your database URL. For example:

jdbc:hsqldb:mem:test;sql.syntax_ora=true

See the Guide

http://hsqldb.org/doc/2.0/guide/deployment-chapt.html

http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html

SQL syntax compatibility is constantly extended in new versions of HSQLDB, so it's best to use the latest available version.

Solution 2

You can still use your original 4 line configuration using <jdbc:embedded-database ...>. Just add following line at the beginning of your test-data/schema.sql file:

SET DATABASE SQL SYNTAX ORA TRUE;

This is effectivelly same as appending sql.syntax_ora=true to your JDBC URL.

Comments

[Luwil]

Thank you @fredt, works like a charm. For others using Spring, I will add text to my question detailing how I changed my configuration (hard to get the formatting right in a comment).