Retrieve Id of Record Just Inserted into a Java Db (Derby) Database

Retrieve id of record just inserted into a Java DB (Derby) database

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

INSERT, and get the auto-incremented value

Through plain SQL:

 insert into language(name) values ('value');
 SELECT IDENTITY_VAL_LOCAL();

See the manual for details: http://db.apache.org/derby/docs/10.7/ref/rrefidentityvallocal.html

When doing this from a Java class (through JDBC) you can use getGeneratedKeys() after "requesting" them with the approriate executeUpdate() method.

How to get the insert ID in JDBC?

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.

Safest way to get last record ID from a table

Safest way will be to output or return the scope_identity() within the procedure inserting the row, and then retrieve the row based on that ID. Use of @@Identity is to be avoided since you can get the incorrect ID when triggers are in play.

Any technique of asking for the maximum value / top 1 suffers a race condition where 2 people adding at the same time, would then get the same ID back when they looked for the highest ID.

How can I get the autoincremented id when I insert a record in a table via jdbctemplate

Check this reference. You can use jdbcTemplate.update as:

EDIT
Added imports as asked

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

following is the code usage:

final String INSERT_SQL = "insert into my_test (name) values(?)";
final String name = "Rob";
KeyHolder keyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(
    new PreparedStatementCreator() {
        public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
            PreparedStatement ps =
                connection.prepareStatement(INSERT_SQL, new String[] {"id"});
            ps.setString(1, name);
            return ps;
        }
    },
    keyHolder);
// keyHolder.getKey() now contains the generated key

---

Related Topics