Real'sHowTo AddThis Feed Button
Custom Search

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


    blog comments powered by Disqus


    If you find this article useful, consider making a small donation
    to show your support for this Web site and its content.

    Written and compiled by Réal Gagnon ©1998-2014
    [ home ]