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)

---

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.

---

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.

链接地址: http://www.djcxy.com/p/88624.html

上一篇: HQSLDB

下一篇: 用HSQL代替Oracle测试MyBatis