Auto-incrementation with HSQLDB (2.2.8) + DDLUtils

Question

I want to use HSQLDB as an embedded database but am having trouble getting it to auto-increment.

As far as I understand, [CALL] IDENTITY() can be used to get the last primary key value. However, experiments through both iBatis and HSQLDB's DatabaseManagerSwing continually return a 0 value.

How can I get auto-incrementation to work with HSQLDB?

Edit:

I didn't mention that I'm using DDLUtils to autogenerate tables. The following does not suit HSQLDB:

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">

<database name="testdb">

    <table name="users">
        <!-- using autoincrement attribute below causes
        "primary key already exists" exception -->
        <column name="id" type="INTEGER" primaryKey="true" />
        <column name="username" type="VARCHAR" size="30" />
        <column name="password" type="VARCHAR" size="100" />
    </table>

</database>

Also, here is the iBatis SQL map used for the domain class:

<insert id="insertUser" parameterClass="user">
    <selectKey keyProperty="id" resultClass="int">
        CALL IDENTITY()
    </selectKey>
INSERT INTO USERS
( USERNAME, PASSWORD ) 
VALUES
( #username#, #password#)       
</insert>

This might be the source of the problem: http://stackoverflow.com/questions/4857730/how-to-fix-hsql-datasource-txm-where-identity-always-return-0 — James P., Feb 19 '12 at 18:29
Another related question: http://stackoverflow.com/questions/5456742/hsqldb-ignores-first-insert-operation-on-table-at-server-server-needs-to-be-wa — James P., Feb 19 '12 at 18:46
@EmmanuelBourg Do you have ties with Apache? The issue simply had to do with how the table was being generated through DDLUtils. — James P., Feb 19 '12 at 19:00

gsteff · Accepted Answer · 2012-02-19T01:16:57.487

Here's an example that prints out

0
1
2

on my machine:

import java.io.File;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Date;

public class Test {

  public static void main(String[] args) throws Exception {

    File dbDir = new File("/tmp/identity_test"); 
    String connectionTemplate = "jdbc:hsqldb:file:%s/test";
    String connStr = String.format(connectionTemplate, dbDir);
    Connection connection = DriverManager.getConnection(connStr, "", "");
    Statement s = connection.createStatement();
    s.execute("CREATE TABLE test (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, s VARCHAR(10))");
    PreparedStatement psInsert = connection.prepareStatement("INSERT INTO test (s) VALUES (?)");
    for (int i = 0; i < 3; i++) {
      psInsert.setString(1, "hello");
      psInsert.executeUpdate();
      PreparedStatement psIdentity = connection.prepareStatement("CALL IDENTITY()");
      ResultSet result = psIdentity.executeQuery();
      result.next();
      int identity = result.getInt(1);
      result.close();
      System.out.println(identity);
    }
    connection.close();
  }
}

The SQL you used to create the table provided the solution: `GENERATED ALWAYS AS IDENTITY`. I didn't mention I was using Apache DDLUnit to generate the tables automatically using XML. I will seek another framework for this purpose. — James P., Feb 19 '12 at 18:49

score 1 · Answer 2 · answered Feb 19 '12 at 15:30

If you use an ORM, they will perform the identity column work for you. sormula makes it easy with an annotation. See org.sormula.tests.identity package within the project for examples.

Row class defined:

public class IdentityTest
{
    @Column(identity=true)
    int id;
    ...

From org.sormula.identity.tests.InsertTest:

 IdentityTest row = new IdentityTest(-1, "Insert one");
 assert getTable().insert(row) == 1 : "insert one failed";
 assert row.getId() > 0 : "indentity column was not generated";

HSQLDB is included in the tests.

Auto-incrementation with HSQLDB (2.2.8) + DDLUtils

2 Answers2

Linked