getting the id of the inserted row

Question

I am inserting records into a database table through plain old JDBC(as this is what the set up is in the project I am working on.. not yet migrated to Hibernate).

here is my table structure

employee (empid int indentity not null, empName);

when I try to insert record through JDBC code

insert into employee(empName) values('something')

it works and a record gets inserted. However I need to get the id against the inserted row. I can try executing a separate select query to get the inserted row but might not get the correct inserted record id if 2 employee names are same.

How do I get the inserted record id using JDBC. I am using preparedStatment.executeUpdate which returns 0 or 1 based on whether the record is inserted or not.

What database are you using? – Sotirios Delimanolis Jan 10 '14 at 20:05 — Sotirios Delimanolis, Jan 10 '14 at 20:05
Database is SQL Server.. checking the answers – user3137375 Jan 10 '14 at 20:20 — user3137375, Jan 10 '14 at 20:20

Elliott Frisch · Accepted Answer · 2014-01-10T20:19:06.680

2

Per the the Statement JavaDoc, you can use "RETURN_GENERATED_KEYS" like so -

stmt.execute("insert into employee(empName) values('something')",
    Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
if (rs.next()) {
  return rs.getLong(1); // for example.
}

edited Jan 10 '14 at 20:19

answered Jan 10 '14 at 20:09

Elliott Frisch

198,278
20
158
249

score 1 · Answer 2 · answered Jan 10 '14 at 20:08

1

You want to read the documentation for JDBC's Statement#getGeneratedKeys(), which you must call after doing the insert. This assumes your database supports retrieving generated keys, which not all databases do.

answered Jan 10 '14 at 20:08

Jim Garrison

85,615
20
155
190

score 0 · Answer 3 · answered Jan 10 '14 at 20:13

It depends from the db:

Oracle & PostgreSQL: when you use a sequence, get the value returned

Mysql: mysql_insert_id()

SQL Server: @@IDENTITY

Custom way: select max(id) from table

Pure java (JDBC Driver supported): see Statement.getGeneratedKeys() Retrieves any auto-generated keys created as a result of executing this Statement object.

getting the id of the inserted row

3 Answers3