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?
JDBC – How to Get Insert ID
insert-idjavajdbcsql
insert-idjavajdbcsql
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?
Best Answer
If it is an auto generated key, then you can use
Statement#getGeneratedKeys()
for this. You need to call it on the sameStatement
as the one being used for theINSERT
. You first need to create the statement usingStatement.RETURN_GENERATED_KEYS
to notify the JDBC driver to return the keys.Here's a basic example:
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 aRETURNING
clause or aSELECT CURRVAL(sequencename)
(or whatever DB-specific syntax to do so) directly after theINSERT
in the same transaction to obtain the last generated key. See also this answer.