title: Unit test MyBatis with HSQL instead of Oracle
body: <p>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:</p>
  
  <pre><code>&lt;insert id="insertBasis" parameterType="com.foo.Basis" useGeneratedKeys="true" keyProperty="id"&gt;
          &lt;selectKey resultType="long" keyProperty="id" order="BEFORE"&gt;
              SELECT basis_seq.NEXTVAL FROM DUAL
          &lt;/selectKey&gt;
          insert into basis
          (id, name)
          values
          (#{id}, #{name})
  &lt;/insert&gt;
  </code></pre>
  
  <p>This works when I run the application but the unit test gets an error:</p>
  
  <blockquote>
    <p>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</p>
  </blockquote>
  
  <p>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 <code>&lt;selectKey&gt;</code>-tag the unit test work (since HSQL can autogenerate ids for columns marked <code>identity</code>) but not the real application. One workaround would be to create separate MyBatis mapper XML files for the unit tests without the <code>&lt;selectKey&gt;</code>-tag but this is undesired since I want to test the real configuration.</p>
  
  <p>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?</p>
  
  <hr>
  
  <p>I use:</p>
  
  <ul>
  <li>Spring 3.0.5 </li>
  <li>HSQL 2.2.4 </li>
  <li>MyBatis 3.0.5</li>
  </ul>
  
  <hr>
  
  <h1>UPDATE:</h1>
  
  <p>After getting the answer from <strong>fredt</strong>, here is how I edited my Spring configuration:</p>
  
  <p>Before I defined my data source with:</p>
  
  <pre><code>&lt;jdbc:embedded-database id="dataSource"&gt;
      &lt;jdbc:script location="classpath:test-data/schema.sql" /&gt;
      &lt;jdbc:script location="classpath:test-data/data.sql" /&gt;
  &lt;/jdbc:embedded-database&gt;
  </code></pre>
  
  <p>Now I do this:</p>
  
  <pre><code>&lt;bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
      destroy-method="close"&gt;
      &lt;property name="driverClassName" value="org.hsqldb.jdbcDriver" /&gt;
      &lt;property name="url" value="jdbc:hsqldb:mem:test;sql.syntax_ora=true" /&gt;
      &lt;property name="username" value="sa" /&gt;
      &lt;property name="password" value="" /&gt;
  &lt;/bean&gt;
  
  &lt;jdbc:initialize-database data-source="dataSource"&gt;
      &lt;jdbc:script location="classpath:test-data/schema.sql" /&gt;
      &lt;jdbc:script location="classpath:test-data/data.sql" /&gt;
  &lt;/jdbc:initialize-database&gt;
  </code></pre>
  
  <p>Also, in schema.sql I need to create the sequences:</p>
  
  <pre><code>CREATE SEQUENCE BASIS_SEQ START WITH 1000 INCREMENT BY 1;
  CREATE SEQUENCE OTHER_SEQ START WITH 1000 INCREMENT BY 1;
  </code></pre>
  
  <p>(if you run this script many times during unit testing, remember to add <code>drop sequence BASIS_SEQ if exists;</code> to top of schema.sql)</p>