Oracle JDBC: Como usar una secuencia en un INSERT y recuperar el valor generado

La problemática de hoy surge cuando tengo que hacer un INSERT en mi tabla de notificaciones y necesito que la clave primaria de mi notificación se una valor único.

Lo primero que se te ocurre y en lo que puedes caer en la tentación es en coger el valor más alto de las que ya hay insertadas y sumarle 1.

String strSQL="select max(notif_id) from notifications";
pstmt = conn.prepareStatement(strSQL);
ResultSet rs = pstmt.executeQuery();            
int uniqueId = (rs!=null && rs.next())?rs.getInt(1)+1:0;

Pero inmediatamente te das cuenta de que eso puede llevar a conflictos si hay muchas inserciones a la vez y alguno puede duplicar la clave.

Así que lo mejor es crear una secuencia en Oracle y usarla en nuestro INSERT.

Con TOAD podemos crear una secuencia fácilmente llamada NOTIF_ID_SEQ con los siguientes parámetros

Y utilizar la en nuestra INSERT llamando al operador NOTIF_ID_SEQ.nextval

String strSQL="insert INTO NOTIFICATIONS " +
              "(USER_ID,PROVIDER_ID,NOTIF_ID) " +                 
              "VALUES (?,?,NOTIF_ID_SEQ.nextval)";
                                                                                     
pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, userId);               
pstmt.setInt(2, providerId); 
pstmt.executeUpdate(); 

Con esto garantizamos que el valor de nuestro identificador es único y es el dado por nuestra secuencia.

Otra problemática que nos surge inmediatamente en esta casuistica es que podemos querer recuperar el valor generado por la secuencia para usarlo en otro lugar.

Para ello utilizamos el método getGeneratedKeys() del PrepareStatement pero debemos decirle al PreparedStatement que columnas son las que se autogeneran en un Array de Strings en nuestro caso "NOTIF_ID"

Veamos un ejemplo:

String generatedColumns[] = {"NOTIF_ID"};                                               
pstmt = conn.prepareStatement(strSQL,generatedColumns);

String strSQL="insert INTO NOTIFICATIONS " +
              "(USER_ID,PROVIDER_ID,NOTIF_ID) " +                 
              "VALUES (?,?,NOTIF_ID_SEQ.nextval)";
                                                                                     
pstmt = conn.prepareStatement(strSQL);
pstmt.setString(1, userId);               
pstmt.setInt(2, providerId); 
pstmt.executeUpdate(); 

//Get the generated key
ResultSet generatedKeys = pstmt.getGeneratedKeys();
if (null != generatedKeys && generatedKeys.next()) {
  int primaryKey = generatedKeys.getInt(1);
  System.out.println(primaryKey);
}

Referencias:

how to get the inserted row primary key with out  using select statement

getGeneratedKeys()

How to use Oracle sequence in preparedstatement

Oracle Java JDBC: Get Primary Key Of Inserted Record