SQL – Retrieve ID of Record Just Inserted into Java DB (Derby) Database

javadbjdbcsql

I am connecting to a Java DB database with JDBC and want to retrieve the id (which is on auto increment) of the last record inserted.

I see this is a common question, but I see solutions using for example MS SQL Server, what is the equivalent for Java DB?

Best Answer

No need to use a DBMS specific SQL for that.

That's what getGeneratedKeys() is for.

When preparing your statement you pass the name(s) of the auto-generated columns which you can then retrieve using getGeneratedKeys()

PreparedStatement pstmt = connection.prepareStatement(
     "insert into some_table (col1, col2, ..) values (....)", 
      new String[] { "ID_COLUMN"} ); 

pstmt.executeUpdate();

ResultSet rs = pstmt.getGeneratedKeys(); // will return the ID in ID_COLUMN

Note that column names are case sensitive in this case (in Derby and many other DBMS).
new String[] { "ID_COLUMN"} is something different than new String[] { "id_column"}

Alternatively you can also use:

connection.prepareStatement("INSERT ...", PreparedStatement.RETURN_GENERATED_KEYS);

Related Solutions

JDBC – How to Get Insert ID

If it is an auto generated key, then you can use Statement#getGeneratedKeys() for this. You need to call it on the same Statement as the one being used for the INSERT. You first need to create the statement using Statement.RETURN_GENERATED_KEYS to notify the JDBC driver to return the keys.

Here's a basic example:

public void create(User user) throws SQLException {
    try (
        Connection connection = dataSource.getConnection();
        PreparedStatement statement = connection.prepareStatement(SQL_INSERT,
                                      Statement.RETURN_GENERATED_KEYS);
    ) {
        statement.setString(1, user.getName());
        statement.setString(2, user.getPassword());
        statement.setString(3, user.getEmail());
        // ...

        int affectedRows = statement.executeUpdate();

        if (affectedRows == 0) {
            throw new SQLException("Creating user failed, no rows affected.");
        }

        try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
            if (generatedKeys.next()) {
                user.setId(generatedKeys.getLong(1));
            }
            else {
                throw new SQLException("Creating user failed, no ID obtained.");
            }
        }
    }
}

Note that you're dependent on the JDBC driver as to whether it works. Currently, most of the last versions will work, but if I am correct, Oracle JDBC driver is still somewhat troublesome with this. MySQL and DB2 already supported it for ages. PostgreSQL started to support it not long ago. I can't comment about MSSQL as I've never used it.

For Oracle, you can invoke a CallableStatement with a RETURNING clause or a SELECT CURRVAL(sequencename) (or whatever DB-specific syntax to do so) directly after the INSERT in the same transaction to obtain the last generated key. See also this answer.

Oracle – Getting ID After Insert Within a Transaction

The Oracle JDBC Driver does not support getGeneratedKeys() - you are manually generating the keys in your trigger, presumably from a SEQUENCE.

You can use Oracle's returning clause:

String query = "BEGIN INSERT INTO team (name) values (?) returning id into ?; END;";
CallableStatement cs = conn.prepareCall(query);
cs.setString(1, "cougars");
cs.registerOutParameter(2, OracleTypes.NUMBER);
cs.execute();
System.out.println(cs.getInt(2));

Or grab the last sequence number with a second SQL query:

SELECT mysequence.CURRVAL FROM dual

Best Answer

Related Solutions

JDBC – How to Get Insert ID

Oracle – Getting ID After Insert Within a Transaction

Related Question