How to Return Last Inserted (Auto Incremented) Row Id in Hsql

How to retrieve previously auto-generated PK ID value using JDBC and HSQLDB

Not much action here, so I'll go ahead and answer to bring closure to this question. After playing around with the different options, and after see this question, I was able to get my option 3 to work. Like I mentioned in the edit to my question, I'm going to use option 3. Option 4 also worked fine, but since the accepted answer to the linked question is given by a reputable source, I am sticking with that. I wish I'd have seen that question/answer before starting this one, I'd have saved some time!

How to get the generated ID of an auto increment column after insertion of a record in HSQLDB within same connection?

You can use the identity() method in the next INSERT statement for the child table. In this example the child table has a ref_id column for the foreign key plus a data_col for its data. The child table has its own primary key column and a foreign key column that references the log_id column of the test table.

create table child(child_id integer identity primary_key, ref_id integer, data_col varchar);
alter table child add constraint child_fk foreign key (ref_id) references test(log_id);
insert into child(ref_id, data_col) values (identity(), ?);

Knowing the value of autoincremented fields

Use the getGeneratedKeys():

ps = con.prepareStatement(
      "insert into infousuarios (nombre, apellidos, email) " +
      "values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS); 

ps.setString(1, name);
ps.setString(2, surnames);
ps.setString(3, login+"@micorreo.com");

rows = ps.executeUpdate();

ResultSet keys = ps.getGeneratedKeys();
long id = -1;

if (keys.next()) {
  id = rs.getLong(1);
}

Note the call to prepareStatement passing PreparedStatement.RETURN_GENERATED_KEYS and the call to ps.getGeneratedKeys()

Auto incrementing ID value

According to the HSQL Documentation:

Since 1.7.2, the SQL standard syntax
is used by default, which allows the
initial value to be specified. The
supported form is( INTEGER
GENERATED BY DEFAULT AS IDENTITY(START
WITH n, [INCREMENT BY m])PRIMARY KEY,
...). Support has also been added for
BIGINT identity columns. As a result,
an IDENTITY column is simply an
INTEGER or BIGINT column with its
default value generated by a sequence
generator.

...

The next IDENTITY value to be used can
be set with the

ALTER TABLE <table name> ALTER COLUMN <column name> RESTART WITH <new value>;

Auto-incrementation with HSQLDB (2.2.8) + DDLUtils

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

Return int with auto incremented ID after insert statement

if(keys.next()) {
            keys.next(); //This is being called 2nd time.
            result = keys.getInt(1);
            System.out.println(keys.getInt(1));
        }

The issue is in your 2nd line keys.next();

You are calling it two times, even in the if condition, it traverses to the next in line.

Replace your code with this and I assume it would work correctly

if(keys.next()) {
                result = keys.getInt(1);
                System.out.println(keys.getInt(1));
            }

Reference : https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html#next()

Link a sequence with to an identity in hsqldb

In version 2.0, there is no direct feature for this. You can define a BEFORE INSERT trigger on the table to do this:

CREATE TABLE company ( id bigint PRIMARY KEY, name varchar(128) NOT NULL CHECK (name <> '') );

CREATE TRIGGER trigg BEFORE INSERT
ON company REFERENCING NEW ROW AS newrow 
FOR EACH ROW
SET newrow.id = NEXT VALUE FOR seq_company_id;

and insert without using any vlue for id

INSERT INTO company VALUES null, 'test'

Update for HSQLDB 2.1 and later: A feature has been added to support this.

CREATE SEQUENCE SEQU
CREATE TABLE company ( id bigint GENERATED BY DEFAULT AS SEQUENCE SEQU PRIMARY KEY, name varchar(128) NOT NULL CHECK (name <> '') );

See the Guide under CREATE TABLE http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_table_creation

In addition, 2.1 and later has a PostgreSQL compatibility mode in which it accepts the PostgreSQL CREATE TABLE statement that references the sequence in the DEFAULT clause and translates it to HSQLDB syntax.

---

Related Topics