Recursividad en MYSQL con Java

Hola Estimd@s,

he sacado algunas cosas del baúl y las quiero compartir, en este caso se trata de una clase Java que permite ejecutar consultas recursivas en MySQL, la idea surgió a partir de una necesidad en primera instancia y luego me base en la forma en que DB2 la lleva a cabo. Tengo la idea (cuando me haga de algún tiempo) de escribir algún articulo relacionado con recursividad en DB2 ya que es muy útil y por demás interesante. Por ahora solo esto.
Pueden descargar la clases desde aquí:

• SQLRecursivo
• Test

Copio el readme.txt que escribí para poder usar esta clase.

Esta clase está basada en la idea de recursividad de DB2.
La clase ar.com.magm.jdbc.SQLRecursivo permite implementar recursividad en MySQL. 
Aún no está bien testeada la indexación que es fundamental cuando se trabaja con muchos datos

La tabla con la que funciona la demo (clase Test) es:

CREATE TABLE `practico`.`arbol` ( 
    `idPadre` integer NOT NULL,
    `idHijo` integer NOT NULL, 
    `cantidad` integer NOT NULL, 
    PRIMARY KEY (`idPadre`, `idHijo`) 
)

Agunos datos:

INSERT INTO `practico`.`arbol` VALUES  
 (3,8,1),
 (5,6,3),
 (2,5,2),
 (1,4,2),
 (1,3,2),
 (1,2,1);
 
 Forma el siguiente árbol:
 
 1
 +--2
 |  +--5
 |     +--6
 |
 +--3
 |  +--8
 |
 +--4

El uso es muy simple, solo hay que crear una instancia de la clase SQLRecursivo, el constructor pide
una conexión JDBC, ejemplo;

SQLRecursivo sqlRec = new SQLRecursivo(cn);

Luego llamar al método recursivo(consultaInicial, aliasTablaPadre, consultaRecursiva, indexKey)

Este método retornará el resultado en forma de ResultSet.

Los parámetros son:

@param consultaInicial 
  consulta que produce la población inicial de datos.
@param aliasTablaPadre 
  alias que se utilizará en la consulta recursiva para la tabla padre
@param consultaRecursiva 
  consulta que obtiene el resto de los datos en forma recursiva. 
         Esta consulta contiene la lógica de corte de cotrol.
  Se hace referencia a la tabla padre con: //TablaPadre//
@param indexKey
         representa la clave del índice/indices que se crearán sobre la
  tabla padre, la forma es: 
  (opciones indice1)\t(clave indice1)\n(opciones indice2)\t(clave indice2), 
  en otras palabras el \n determina la cantidad de índices a crear, 
  el \t separa las opciones de la clave. 
  Para no crear ningún índice enviar "" o null. 
  Ejemplo: 
    UNIQUE CLUSTERED\tidHijo,idFiltroArbol,idFiltroGeneral\nNONCLUSTERED\tcc 
  Se crearán: 
    CREATE UNIQUE CLUSTERED INDEX IX0_##TablaPadre ON ##TablaPadre (idHijo,idFiltroArbol,idFiltroGeneral) 
    y 
    CREATE NONCLUSTERED INDEX IX1_TablaPadre ON ##TablaPadre (cc)
  

Se recomienda probar en el test las siguientes consultas:

String consultaInicial = "SELECT idPadre,idHijo FROM arbol where idPadre=1"; //Obtiene el árbol completo

para obtener el árbol completo o:
String consultaInicial = "SELECT idPadre,idHijo FROM arbol where idPadre=2"; //Obtiene el subarbol del nodo 2

En la consulta recursiva se puede (y en general se debe) hacer referencia a la tabla padre (consulta inicial),
esto se hace con la expresión: //TablaPadre//, esto se puede ver en el ejemplo. La tabla tiene un alias, 
que este ejemplo es 'padre' y es el segundo argumento del método recursivo.  Por ello en la consulta se ve:  
... a.idPadre=padre.idHijo


String consultaRecursiva = "SELECT a.idPadre,a.idHijo FROM arbol a,//TablaPadre// WHERE a.idPadre=padre.idHijo";

Enjoy 

Mariano

Valores de filas afectadas y claves identidad en DB2

Estimad@s,

expongo aquí algunas formas de trabajar con los datos recién insertados en tablas DB2.
Muchas veces es necesario conocer el último id insertado en una columna identity (auto-numérica) o también la última o últimas filas insertadas en una tabla.
Muchos desarrolladores tratan esto con algunas prácticas que no son muy buenas, algunos ejemplos pueden ser ejecutar una consulta del estilo SELECT MAX(id) FROM tabla luego de insertar, SELECT * FROM tabla WHERE descripcion='algún dato unique que se tenía antes de insertar' o almacenar últimos valores de clave en una tabla, esta última la peor de las prácticas.
Existen otros casos, pero casi todos tienen en común que no dan soporte a la concurrencia y que en general son muy ineficientes.
DB2 posee una serie de características que permiten lidiar con estos problemas y darles una solución sencilla y elegante, además se tendrá en cuenta la concurrencia y la eficiencia en la ejecución.

Secuencias:
Una manera puede ser utilizar secuencias. Las secuencias son objetos de la base de datos que permiten generar números en secuencia (valga aquí la redundancia) a pedido, además permite obtener el último número generado en la secuencia. Veamos un pequeño ejemplo de como crear y utilizar una secuencia.

Creamos la tabla que utilizaremos como ejemplo:

CREATE TABLE EJ_SECUENCIA  (
    id BIGINT NOT NULL, 
    descripcion VARCHAR(50) NOT NULL,
    CONSTRAINT PK_EJEMPLO PRIMARY KEY (id) 
); 

Luego la secuencia seq1 que inicia en 1 e incrementa de 1.

CREATE SEQUENCE seq1 AS INTEGER START WITH 1 INCREMENT BY 1

La sintaxis de las secuencias en DB2 es muy amplia y permite definir muchas opciones como: ciclos, secuencias decrementales, etc.

Las secuencias tienen dos métodos asociados, PREVVAL que permite obtener el último número generado (no puede utilizarse luego de crear la secuencia, debe generarse al menos un valor antes de ejecutar este método) y NEXTVAL que permite generar un nuevo valor de la secuencia. Veamos con un ejemplo como insertar valores en la tabla EJ_SECUENCIA con esta secuencia.

INSERT INTO EJ_SECUENCIA (id, descripcion) VALUES  (NEXTVAL FOR seq1, 'a');

VALUES (PREVVAL FOR seq1);

La salida será:

1
-----------
          1

  1 registro(s) seleccionado(s).

Si ejecutamos ahora:

INSERT INTO EJ_SECUENCIA (id, descripcion) VALUES 
    (NEXTVAL FOR seq1, 'b'),
    (NEXTVAL FOR seq1, 'c'),
    (NEXTVAL FOR seq1, 'd');

Obtendremos:

SELECT * FROM DB2ADMIN.EJ_SECUENCIA

ID                   DESCRIPCION
-------------------- -------------
                   1 a
                   2 b
                   3 c
                   4 d

  4 registro(s) seleccionado(s).

Como se puede apreciar el uso de las secuencia es muy sencillo y flexible.

Autoincrementales y filas afectadas:

Creamos la tabla de ejemplo:

CREATE TABLE EJ_IDS  (
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY, 
    descripcion VARCHAR(10) NOT NULL,
    CONSTRAINT PK_IDS PRIMARY KEY (id)
); 

Insertamos algunos valores:

INSERT INTO EJ_IDS (descripcion) VALUES ('a'),('b'),('c');

Noten que solo es necesario dar valores a la columna descripcion, DB2 se encargará dar valores a la columna id.

Luego de consultar la tabla:

SELECT * FROM EJ_IDS

Obtendremos:

ID                   DESCRIPCION
-------------------- ---------------
                   1 a
                   2 b
                   3 c

  3 registro(s) seleccionado(s).

Luego ejecutando :

VALUES (IDENTITY_VAL_LOCAL())

La salida será:

1

-----------

          3

  1 registro(s) seleccionado(s).

Para finalizar una herramienta excelente para obtener las filas afectadas, denominamos filas afectadas a las filas insertadas o que cumplen un predicado y por ello son modificadas o eliminadas.

SELECT * FROM FINAL TABLE (INSERT INTO EJ_IDS (descripcion) VALUES ('e'),('f'),('g'))

La sentencia anterior cumplirá dos funciones, por un lado se insertarán tres nuevas filas y por otro se obtendrán de la consulta a la tabla 'FINAL TABLE'

ID                   DESCRIPCION
-------------------- -----------
                   4 e
                   5 f
                   6 g

  3 registro(s) seleccionado(s).

También podemos utilizar 'FINAL TABLE'  en modificaciones:

SELECT * FROM FINAL TABLE (UPDATE EJ_IDS SET descripcion = descripcion || '-nuevo' WHERE MOD(ID,2)=0)

Obtendremos:


ID                   DESCRIPCION
-------------------- -----------
                   2 b-nuevo
                   4 e-nuevo
                   6 g-nuevo

  3 registro(s) seleccionado(s).

Además de 'FINAL TABLE' podemos utilizar 'NEW TABLE' la diferencia es que con 'NEW TABLE' obtendremos los valores de la tabla antes que se ejecuten las restricciones referenciales (claves foráneas)  los triggers definidos como after:

Para obtener las filas borradas:


SELECT * FROM OLD TABLE (DELETE FROM EJ_IDS WHERE MOD(ID,3)=0)

ID                   DESCRIPCION
-------------------- -----------
                   3 c
                   6 c-nuevo

  2 registro(s) seleccionado(s).


Espero que les sea útil.

Saludos

Mariano