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?
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.