Share this page 

Retrieve the generated keys (JDBC Oracle)Tag(s): JDBC


Oracle can generated keys by creating a sequence. This sequence is then incremented with each INSERT to provides a unique numeric key.

While it's possible to do the INSERT and then a SELECT to RETRIEVE the last sequence, JDBC 3.0 provides a way to tell to the INSERT to return the specified generated keys. 

PreparedStatement pstmt = null;
ResultSet result = null;
String sql = "INSERT INTO ORDERS (DESC_ORDER) VALUES(?)";
//
//  we have a trigger (on INSERT) to populate a field (ID_ORDER)
//  an Oracle sequence is used to generated the value.
//
pstmt = this.dbConnection.prepareStatement(sql,new String [] {"ID_ORDER"});
pstmt.setString(1,orderDto.getDesc());
if(pstmt.executeUpdate() != 1){
   throw new OrderException("Error INSERT Order!!!");
}
result = pstmt.getGeneratedKeys();
result.next();
idOrder = result.getLong(1);  // 1 --> ID_ORDER
  • Works only with INSERT
  • The JDBC driver must be JDBC 3.0
  • Oracle 10g Release 2 is required (others DBMS (ingres, DB2, etc.) support the same feature)

    You can check if this feature is supported 

    DatabaseMetaData metaData =  this.dbConnection.getMetaData();
log("SupportsGetGeneratedKeys?="+metaData.supportsGetGeneratedKeys());
log("ProductDatabaseName="+metaData.getDatabaseProductName());
log("ProductDatabaseVersion="+metaData.getDatabaseProductVersion());
log("ProductDatabaseMajorVersion="+metaData.getDatabaseMajorVersion());
log("ProductDatabaseMinorVersion="+metaData.getDatabaseMinorVersion());
log("ProductDriverName="+metaData.getDriverName());
log("ProductDriverVersion="+metaData.getDriverVersion());
log("ProductDriverMajor="+metaData.getDriverMajorVersion());
log("ProductDriverMinor="+metaData.getDriverMinorVersion());
log("JDBCMajorVersion="+metaData.getJDBCMajorVersion());
log("JDBCMinorVersion="+metaData.getJDBCMajorVersion());

    Nice article on the subject : Oracle JDBC: Automatic key generation and retrieval

    Thanks to F.Joyal for his help