Return sequence number on insert

Upon inserting order into a HSQL database through my OrderDAO object, I want to be able to retrieve the sequence number that was assigned to order on insert.

I have this for my PreparedStatement:

public long saveOrder(Order order) {
    long orderId = 0;
    try (Connection conn = MyDataSource.getDataSource().getConnection();
         PreparedStatement ps = conn.prepareStatement("INSERT INTO orders(id, order_number) VALUES (NEXT VALUE FOR seq1, ?)",
                                                            PreparedStatement.RETURN_GENERATED_KEYS)) {

        ps.setString(1, order.getOrderNumber());
        ps.execute();

        ResultSet rs = ps.getResultSet();
        if (rs.next()) {
            orderId = rs.getLong(1);
        }

    } catch (Exception e) {
        throw new RuntimeException(e);
    }
    return orderId;
}

I would assume, that after executing the query and asking for its resultset, the first column in the resultset would be the sequence number. But it seems that i doesn't work that way.

Where am I going wrong with this?

Solution

There are two problems with your code:

You are using the wrong method to retrieve the generated keys result set. The generated keys result set can only be retrieved using getGeneratedKeys() (or at least, that is what the JDBC specification requires).

You need to change your code to use ps.getGeneratedKeys() instead of ps.getResultSet()
The other problem is your code assumes non-standard generated keys behavior: your insert is not actually using a generated key in the sense the JDBC specification intends, as you are generating the identifier in the insert statement yourself (using NEXT VALUE FOR seq1), instead of the key being generated as a side-effect of the insert statement (eg by a identity column or a trigger).

HSQLDB does not return a generated key in this situation as it does not consider id as a generated column. Instead, you need to either define the column as an identity column (and don't explicitly specify it in your insert), or alternatively, explicitly specify the column to return.

For creating an identity column, refer to the HSQLDB documentation. To explicitly specify the column to return replace
```
conn.prepareStatement("<query>", PreparedStatement.RETURN_GENERATED_KEYS)
```
with either an index specification of the columns to return (that is 1 is the first column):
```
conn.prepareStatement("<query>", new int[] { 1 })
```
or a column name specification of the columns to return
```
conn.prepareStatement("<query>", new String[] { "id" })
```

Your final code should be something like:

try (PreparedStatement ps = conn.prepareStatement(
         "INSERT INTO orders(id, order_number) VALUES (NEXT VALUE FOR seq1, ?)",
         new String[] { "id" })) {
    ps.setString(1, order.getOrderNumber());
    ps.execute();

    try (ResultSet rs = stmt.getGeneratedKeys()) {
        if (rs.next()) {
            return rs.getLong(1);
        }
    }
}