Databases
StackOverflow2013
Postsdbo
POUnit test MyBatis with HSQL instead of Oracle
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUnit test MyBatis with HSQL instead of Oracle
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USLuwil
    1. USLuwil
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUnit test MyBatis with HSQL instead of Oracle
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUnit test MyBatis with HSQL instead of Oracle
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POUnit test MyBatis with HSQL instead of Oracle
      1. This table or related slice is empty.
      1. VTUpMod
    1. COLuwil: You know you can add an answer to your own question, if you want to share the solution you applied to your question...
      singulars
      1. POUnit test MyBatis with HSQL instead of Oracle
      1. USLukas Eder
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload