How to get the insert ID in JDBC?

Question

I want to INSERT a record in a database (which is Microsoft SQL Server in my case) using JDBC in Java. At the same time, I want to obtain the insert ID. How can I achieve this using JDBC API?

Answer 1

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 is been 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 {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet generatedKeys = null;

    try {
        connection = database.getConnection();
        preparedStatement = connection.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
        preparedStatement.setValue(1, user.getName());
        preparedStatement.setValue(2, user.getPassword());
        preparedStatement.setValue(3, user.getEmail());
        // ...

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

        generatedKeys = preparedStatement.getGeneratedKeys();
        if (generatedKeys.next()) {
            user.setId(generatedKeys.getLong(1));
        } else {
            throw new SQLException("Creating user failed, no generated key obtained.");
        }
    } finally {
        close(connection, preparedStatement, generatedKeys);
    }
}

Note that you're dependent on the JDBC driver whether it works. Currently, most of the last versions will do, 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 short ago. No wording about MSSQL as I've never used it.

You can alternatively also invoke a SELECT CURRVAL(sequencename) (or whatever DB-specific syntax to do so) on the same Statement directly after the INSERT to obtain the last generated key.