How to get the last insert id in Oracle using Java

unfortunately it isn’t as easy to retrieve the last inserted id in Oracle as it was in MySQL. Browsing the web you mostly find solutions that suggest you simply select the latest column value of the auto incremented field. But that might throw issues if several concurrent threads are writing into an unlocked table.

Oracle uses sequence generators to create an increment value for a field. Those sequences are triggered to update BEFORE the row gets INSERTed. Here’s a small snippet demonstrating how to get the latest insert id in oracle with a simple Java JDBC connection (used Oracle 10g Express Edition):

protected static Long testWriteToDB() {
String sql = " INSERT INTO \"ABOOKTABLE\" (ARTICLETITLE) VALUES (?) ";
Connection con = OracleSEResearch.getConnection();
Long insertId = null;
try {
PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
ps.setString(1, "JAVA inserted article");
ps.execute();

Statement statement = con.createStatement();
ResultSet generatedKeys = statement.executeQuery("SELECT ABOOKTABLE_SEQ.CURRVAL FROM DUAL");
if (generatedKeys.next()) {
insertId = new Long(generatedKeys.getLong(1));
}

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return insertId;
}

The solution is the following standalone statement

SELECT ABOOKTABLE_SEQ.CURRVAL FROM DUAL

that yields the current sequence value. The “Dual” Table is one of Oracles specialities and contains only one row. Actually I haven’t understood yet what it does exactly…

in my opinion that code should be “safe” to return the actual inserted id since both calls are executed within one session and the sequence should be accessed in the same transaction scope as the payload table. If someone’s reading this and has another opinion I’d be keen on your comments! Found a reference that seems to confirm my assumption here: http://forums.oracle.com/forums/thread.jspa?threadID=354998

The only real alternative (that should definately be lock-safe) is a stored procedure. And as I found know there also is a possibility to return values from every query using a RETURN-statement. Here’s a small example from the thread mentioned above:

insert into onecol values(myseq.nextval) returning col1 into :p1

you can extract the return values using common ResultSet functions in Java…

This is the trigger definition that SQL Developer has created to realize an autoincrement column on ABOOKTABLE using the sequence ABOOKTABLE_SEQ.

create or replace
TRIGGER ABOOKTABLE_TRG BEFORE INSERT ON ABOOKTABLE
FOR EACH ROW
BEGIN
<>
BEGIN
IF :NEW.ARTICLEID IS NULL THEN
SELECT ABOOKTABLE_SEQ.NEXTVAL INTO :NEW.ARTICLEID FROM DUAL;
END IF;
END COLUMN_SEQUENCES;
END;

References
http://dbaforums.org/oracle/index.php?showtopic=956
http://www.adp-gmbh.ch/ora/misc/dual.html
http://www.sitepoint.com/forums/showthread.php?t=125765



					
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s