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

Oracle: Query para restar minutos a la fecha del sistema

La problemática de hoy viene dada por la necesidad de recuperar en una base de datos Oracle los usuarios que han tenido interacción con la aplicación en los últimos 15 minutos.

En mi caso tengo un campo en mi tabla de usuarios que registra cuando ha sido la última actualización o acceso de este usuario a la aplicación.

La query a realizar se la siguiente:

SELECT * FROM users WHERE LAST_UPDATED_DATE > SYSTIMESTAMP - (?/1440)

Donde ? tenemos que sustituirlo por el número de minutos que queremos restar a la fecha del sistema.

SYSTIMESTAMP nos devuelve la fecha del sistema incluyendo hora minutos y segundos con el TimeZone de la base de datos.

El valor de 1440 es porque un día tiene 1440 minutos 24 x 60. Y usamos minutos en este formato ya que lo que se resta normalmente a SYSTIMESTAMP son días.

Por lo tanto mi query final es la siguiente:

PreparedStatement pstmt = null;       
ResultSet rSet = null;
Connection conn = null;        
int minutes = 15;
........
........
String strSQL="select from users where LAST_UPDATED_DATE > SYSTIMESTAMP-(?/1440)";

pstmt = conn.prepareStatement(strSQL);
pstmt.setInt(1, minutes);                     
rSet = pstmt.executeQuery();

Referencias:

Time Aritmetic with Oracle

SYSTIMESTAMP