How to retrieve last IDENTITY using JDBC

Background:

I was working on an existing module which was using JDBC for all the database operations. There was a scenario where front-end UI has to update a set of tables, meaning data will be inserted into parent table first, than in child tables using parent's ID to establish the referential integrity. Existing code was using database MAX function to fetch the ID which I felt not a good idea and might have concurrency issues when multiple users will be using the application. My investigation came up with few alternates which I have listed below.

Right now the application is pointing to the MS SQL Server database and all the solutions are for that database only. Plan is to do similar investigation in future to incorporate other databases like Oracle.

Solutions for MS SQL Server:

Solution-01: Using JDBC built-in functionality

Assumptions:

1. Table where the insertion is happening has a auto-generated key

Steps: (see code-01)

1. Use Statement.RETURN_GENERATED_KEYS during PreparedStatement creation
2. Execute insert statement
3. Get list of generated keys as ResultSet
4. Get the first value from ResultSet

Solution-02: Using MS SQL server OUTPUT clause feature in the query

Assumptions:

1. The query should use OUTPUT clause feature of SQL Server (I have another post, which explains how to use OUTPUT clause)

Steps: (see code-02)

1. Create a query with OUTPUT clause
2. Execute the query using ' executeQuery' method of Statement
3. Get list of generated keys as ResultSet
4. Get the first value from ResultSet

Resources:

1. How to use OUTPUT clause, this is my another post on this topic - http://peerprogrammer.blogspot.com/2012/04/how-to-retrieve-identity-of-table_18.html

Sample Codes:

--------------------

--- Code-01 - use JDBC feature to get IDENTITY

--------------------

public int getLastIdentity(String sql) {  

PreparedStatement pStmt;  

int lastIdentity = 0;  

try {  

pStmt = conn.prepareStatement( sql, Statement.RETURN_GENERATED_KEYS);  

   

pStmt.executeUpdate();  

ResultSet identities =  pStmt.getGeneratedKeys();  

  

identities.next();  

lastIdentity = identities.getInt(1);  

identities.close();  

pStmt.close();  

conn.close();  -- connection is created outside of this method

} catch (Exception e) { e.printStackTrace(); }  

return  lastIdentity;  

}

--------------------

--- Code-02 - use SQL Server 2005 feature OUTPUT clause

--------------------

Input SQL should look like below and assuming the table's auto increment column name is 'id':

INSERT INTO 'tablename' OUTPUT inserted.id VALUES('First Column', 'Second Column');

public int getLastIdentity(String sql) {  

PreparedStatement pStmt;  

int lastIdentity = 0;  

try {  

pStmt = conn.prepareStatement(sql);  

ResultSet identities =   pStmt.executeQuery();  

identities.next();  

lastIdentity = identities.getInt(1);  

identities.close();  

pStmt.close();  

conn.close();  -- connection is created outside of this method

} catch (Exception e) { e.printStackTrace(); }  

return  lastIdentity;  

}