Return sequence number on insert

Question

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?

Show us the code you have tried, and also please tell us exactly which database and driver you are using. Not all DBs may support generated keys, and not all support it in the same way. — Tim Biegeleisen, Oct 20 '18 at 13:36
Execute a first query to get the next value from the sequence, then use the result as a parameter for the second query. — JB Nizet, Oct 20 '18 at 13:43

Mark Rotteveel · Accepted Answer · 2018-10-21T09:09:49.480

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

Tim Biegeleisen · Answer 2 · 2018-10-21T08:42:53.917

1

Basing off of this SO question and answer, your syntax should be something like this:

Connection conn = MyDataSource.getDataSource().getConnection();
long orderId = 0;
String sql = "INSERT INTO orders (id, order_number) VALUES (NEXT VALUE FOR seq1, ?)";
PreparedStatement ps = conn.prepareStatement(sql, RETURN_GENERATED_KEYS);
ps.setString(1, order.getOrderNumber());
int numAffected = ps.executeUpdate();
ResultSet rs = ps.getGeneratedKeys();
if (rs.next()) {
    orderId = rs.getLong(1);
}

edited Oct 21 '18 at 08:42

answered Oct 20 '18 at 13:45

Tim Biegeleisen

502,043
27
286
360

The check for the update count is entirely unnecessary – Mark Rotteveel Oct 21 '18 at 08:36
@MarkRotteveel `entirely unnecessary` ... so you are saying there is no conceivable scenario where the DML update count is not one? – Tim Biegeleisen Oct 21 '18 at 08:38
It is an insert, either that succeeds and inserted a row or it failed with an exception. Also consider that update counts are tricky and in some databases could return a value higher than 1 even if only 1 row was inserted (eg if a trigger inserted an audit record, this could be included in the update count in some database systems). – Mark Rotteveel Oct 21 '18 at 08:41

Return sequence number on insert

2 Answers2