MyBatis Generator: generate mappers with autoincrement fields that work both with Mysql and HSQLDB

Question

I'm using MyBatis Generator to generate mappers for my Mysql database. I have some tables with autoincrement fields and so I have declared them as 'generatedKey' and they work as intended.

Now I want to use HSQLDB to create an in-memory database for my JUnit tests. I have enabled the MySQL syntax of HSQLDB but my mappers don't work because the statement generated for getting the last value of the autoincrement fields is not compatibile.

This is the configuration file of MyBatis Generator:

<generatorConfiguration>
<classPathEntry location="D:\mysql-connector-java-5.1.29.jar" />
<context id="Mysql" targetRuntime="MyBatis3">
    <plugin type="org.mybatis.generator.plugins.SerializablePlugin">
        <property name="suppressJavaInterface" value="false"/>
    </plugin>
    <jdbcConnection driverClass="com.mysql.jdbc.Driver"
        connectionURL="xxxxxxxxxxxxxxxxxxxxxx"
        userId="xxxxx"
        password="xxxxx">
    </jdbcConnection>
    <javaModelGenerator targetPackage="it.test.dbTablesDTO" targetProject="src\main\java">
        <property name="enableSubPackages" value="true" />
        <property name="trimStrings" value="true" />
    </javaModelGenerator>
    <sqlMapGenerator targetPackage="it.test.dbTables" targetProject="src\main\resources">
        <property name="enableSubPackages" value="true" />
    </sqlMapGenerator>
    <javaClientGenerator targetPackage="it.test.dbTables" type="XMLMAPPER" targetProject="src\main\java">
        <property name="enableSubPackages" value="true" />
    </javaClientGenerator>
    <table tableName="history" schema="hist">
        <generatedKey column="id_history" sqlStatement="MySql"/>
    </table>
</context>

I have also tried to change the value of sqlStatement in "JDBC" as written in the manual but with this value it doesn't work neither with MySQL nor with HSQLDB.

This is the mapper generated with sqlStatement="MySql" (I have deleted the rows that are not interesting):

<insert id="insert" parameterType="it.xxxx.History">
<!--
  WARNING - @mbggenerated
  This element is automatically generated by MyBatis Generator, do not modify.
  This element was generated on Wed Jun 25 10:44:25 CEST 2014.
-->
<selectKey keyProperty="idHistory" resultType="java.lang.Long">
  SELECT LAST_INSERT_ID()
</selectKey>
insert into history(
  )
values (
  )

As you can see the "SELECT LAST_INSERT_ID()" statement obviously is not compatible with Hypersonic.

Any idea?

Answer 1

Try creating your own function in HSQLDB.

CREATE FUNCTION LAST_INSERT_ID() RETURNS BIGINT
   RETURN IDENTITY();