使用HSQLDB（2.2.8）+ DDLUtils自动增量

问题描述

我想将HSQLDB用作嵌入式数据库，但无法将其设置为自动递增。

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

据我所知， [CALL] IDENTITY（）可用于获取最后一个主键值。但是，通过iBatis和HSQLDB的 DatabaseManagerSwing 的实验不断返回0值。

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.

如何获得自动增量使用HSQLDB？

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

编辑：

我没有提到我正在使用自动生成表格。以下不适合HSQLDB：

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>

此外，这里是用于域类的iBatis SQL映射：

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>

推荐答案

这是一个打印出来的例子

Here's an example that prints out

0
1
2

在我的机器上：

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();
  }
}

这篇关于使用HSQLDB（2.2.8）+ DDLUtils自动增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！