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

Oracle: Query to subtract minutes from system date

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 

TOAD: Exportar e importar los datos de una tabla

Me encontraba haciendo una actualización de una tabla en TOAD y lo que más fácil me ha parecido para modificar una columna ha sido, copiar el script de creación, modificarlo en el editor de queries y lanzarlo para llevar a cabo esa modificación.

La modificación se ha hecho fácilmente y correctamente pero el problema ha sido que me he cargado todos los datos que había. Así que he aprendido la lección para la próxima y he aprendido lo que hay que hacer para salvarlos.

Lo mejor desde mi punto de vista es guardarlos en un archivo de texto, un archivo table.sql y tenerlos allí por si pasa algo.

Para ello hacemos los siguientes pasos:

1 - vamos a la pantalla de editor donde lanzamos las queries y hacemos un select sobre toda la tabla

2 - Una vez tenemos los resultados tenemos que fijarnos en el botón que hay encima de ellos que es Export dataset

3 - Elegimos las opciones de exportación, con el formato INSERT STATEMENTS, y selecionamos la ruta donde queremos que se exporten los datos.

Restauración de los datos:

Una vez que tenemos los datos para restaurarlos una manera simple es copiar y pegar en el editor ejecutar como si fuera un script pulsando en el rayo y se realizarán todos los inserts.

Y con estos pasos ya tendremos todos los datos de la tabla importados de nuevo