Como trabajar con parámetros tipo Array entre Java y Oracle.

Imaginemos el caso en que para crear un objeto Java necesitamos leer varios registros de una tabla de una base de datos Oracle y queremos hacerlo con una sola llamada a un procedimiento almacenado PL/SQL. Necesitamos poder trabajar con parámetros tipo Array entre Java y Oracle.

Por ejemplo, una clase usuario que aparte de atributos normales incluye dos atributos múltiples de tipo lista de objetos de otra clase servicio:

ArrayList <servicio> servicios = new ArrayList<servicio>();
ArrayList <servicio> intereses = new ArrayList<servicio>();

Tendremos que leer de la base de datos la tabla usuarios y las tablas necesarias para obtener los servicios e intereses de dicho usuario.

Creación de objetos y procedimiento almacenado en Oracle

Necesitamos un objeto tipo que tenga la misma estructura que el objeto servicio:

create or replace TYPE TYP_SERVICIO AS
    OBJECT (SRV_ID NUMBER, SRV_NOMBRE VARCHAR2(40))

Ahora necesitamos un objeto tipo lista que sea una lista del anterior y que utilizaremos como parámetro de salida del procedimiento almacenado:

create or replace TYPE TYP_LST_SERVICIOS_2 AS TABLE OF TYP_SERVICIO;

Los objetos anteriores los utilizamos tanto para los servicios de usuario como para sus intereses porque tienen la misma estructura y, en realidad, los datos vienen de la misma tabla, pero eso no viene al caso. Si no fuese así tendríamos que haber creado otro objeto tipo y lista adicional.

Creamos el procedimiento almacenado en el que realizamos las tres consultas que necesitamos y que requiere de un solo parámetro de entrada, el ID del usuario:

PROCEDURE PRC_USR_GET_DATA(
    P_USR_ID IN USUARIOS.USR_ID%TYPE,
    P_USR_NICK OUT USUARIOS.USR_NICK%TYPE,
    P_USR_NOMBRE OUT USUARIOS.USR_NOMBRE%TYPE,
    P_USR_APELLIDOS OUT USUARIOS.USR_APELLIDOS%TYPE,
    P_USR_EMAIL OUT USUARIOS.USR_EMAIL%TYPE,
    P_USR_LOC_LAT OUT USUARIOS.USR_LOC_LAT%TYPE,
    P_USR_LOC_LONG OUT USUARIOS.USR_LOC_LONG%TYPE,
    P_USR_SALDO OUT USUARIOS.USR_SALDO%TYPE,
    P_USR_VALORACION OUT USUARIOS.USR_VALORACION%TYPE,
    P_USR_FECALTA OUT VARCHAR2,
    P_USR_LASTCONEX OUT VARCHAR2,
    P_SRV_LIST OUT TYP_LST_SERVICIOS_2,
    P_INT_LIST OUT TYP_LST_SERVICIOS_2,
    P_ERROR OUT VARCHAR2
) AS
V_PRC_NOMBRE BT_LOG.LOG_PROCESO%TYPE := 'PKG_USUARIOS.PRC_USR_GET_DATA';
BEGIN
    SELECT USR_NICK, USR_NOMBRE, USR_APELLIDOS, USR_EMAIL,
        USR_LOC_LAT, USR_LOC_LONG, USR_SALDO, USR_VALORACION,
        TO_CHAR(USR_FEC_ALTA, 'DD Month YYYY'),
        TO_CHAR(USR_LASTCONEX, 'DD Month YYYY HH24:MI')
    INTO P_USR_NICK, P_USR_NOMBRE, P_USR_APELLIDOS, P_USR_EMAIL,    
        P_USR_LOC_LAT, P_USR_LOC_LONG, P_USR_SALDO,
        P_USR_VALORACION, P_USR_FECALTA, P_USR_LASTCONEX
    FROM USUARIOS WHERE USR_ID=P_USR_ID;
    SELECT TYP_SERVICIO(S.SRV_ID, initcap(S.SRV_NOMBRE)) bulk collect
        INTO P_INT_LIST
        FROM USR_INT UI LEFT JOIN SERVICIOS S ON S.SRV_ID=UI.SRV_ID
        WHERE UI.USR_ID=P_USR_ID;
    SELECT TYP_SERVICIO(S.SRV_ID, initcap(S.SRV_NOMBRE)) bulk collect
        INTO P_SRV_LIST
        FROM USR_SRV US LEFT JOIN SERVICIOS S ON S.SRV_ID=US.SRV_ID
        WHERE US.USR_ID=P_USR_ID;
    RETURN;
    EXCEPTION WHEN OTHERS THEN
        PKG_LOG.PRC_LOG_WRITE(P_USR_ID, V_PRC_NOMBRE, SQLCODE,
            SUBSTR(SQLERRM,0,200), P_ERROR);
    RETURN;
END PRC_USR_GET_DATA;

Hay que destacar los dos puntos; primero los parámetros de salida que son del mismo tipo lista que creamos anteriormente:

    P_SRV_LIST OUT TYP_LST_SERVICIOS_2,
    P_INT_LIST OUT TYP_LST_SERVICIOS_2,

Y segundo, las consultas para rellenar dichos parámetros de salida:

    SELECT TYP_SERVICIO(S.SRV_ID, initcap(S.SRV_NOMBRE)) bulk collect
        INTO P_SRV_LIST...

Creación de objetos Java

La clase servicio es muy simple, dos atributos y sus métodos getter y setter:

package comun;
public class servicio {
    private int id;
    private String nombre;
    public void setId(int a){ id=a; }
    public int getId(){ return id; }
    public void setNombre(String a){ nombre=a; }
    public String getNombre(){ return nombre; }
}

Es el objeto en el que nos hemos basado para crear el objeto de Oracle TYP_SERVICIO.

La clase usuario tiene un constructor desde el que llama al procedimiento almacenado. Necesita el ID del usuario que queremos crear:

package comun;
import java.sql.*;
import java.util.ArrayList;
import java.sql.Types;
import oracle.sql.STRUCT;
import java.math.BigDecimal;
import comun.BBDD;
import comun.servicio;
public class usuario {
    private int id ;
    private String nick;
    private String nombre;
    private String apellidos;
    private String email;
    private int saldo;
    private double loc_lat;
    private double loc_long;
    private String fec_alta;
    private int valoracion;
    private String ultima_conexion;
    ArrayList <servicio> servicios = new ArrayList<servicio>();
    ArrayList <servicio> intereses = new ArrayList<servicio>();
    private BBDD bd = new BBDD();
    public usuario(int user_id) {
        try{
            Connection conn = bd.conectar();
            bd.conectar();
            CallableStatement cs = conn.prepareCall("{call
                PKG_USUARIOS.PRC_USR_GET_DATA(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
            cs.setInt(1, user_id);
            cs.registerOutParameter(2, Types.VARCHAR);
            cs.registerOutParameter(3, Types.VARCHAR);
            cs.registerOutParameter(4, Types.VARCHAR);
            cs.registerOutParameter(5, Types.VARCHAR);
            cs.registerOutParameter(6, Types.DOUBLE);
            cs.registerOutParameter(7, Types.DOUBLE);
            cs.registerOutParameter(8, Types.INTEGER);
            cs.registerOutParameter(9, Types.INTEGER);
            cs.registerOutParameter(10, Types.VARCHAR);
            cs.registerOutParameter(11, Types.VARCHAR);
            cs.registerOutParameter(12, Types.ARRAY, "TYP_LST_SERVICIOS_2");
            cs.registerOutParameter(13, Types.ARRAY, "TYP_LST_SERVICIOS_2");
            cs.registerOutParameter(14, Types.VARCHAR);
            cs.executeUpdate();
            if(cs.getString(14)!=null){
                System.out.println("ERROR: " + cs.getString(14));
            }
            else{
                id=user_id;
                nick=cs.getString(2);
                nombre=cs.getString(3);
                apellidos=cs.getString(4);
                email=cs.getString(5);
                loc_lat=cs.getDouble(6);
                loc_long=cs.getDouble(7);
                saldo=cs.getInt(8);
                valoracion=cs.getInt(9);
                fec_alta=cs.getString(10);
                ultima_conexion=cs.getString(11);
                Array arrS = cs.getArray(12);
                Object[] objArrS = (Object[]) arrS.getArray();
                for(int i=0; i<objArrS.length;i++){
                    STRUCT st = (STRUCT)objArrS[i];
                    servicio service = new servicio();
                    Object[] obj = st.getAttributes();
                    service.setId(((BigDecimal)obj[0]).intValueExact());
                    service.setNombre(String.valueOf(obj[1]));                  
                    servicios.add(service);
                }              
                Array arrI = cs.getArray(13);
                Object[] objArrI = (Object[]) arrI.getArray();
                for(int i=0; i<objArrI.length;i++){
                    STRUCT st = (STRUCT)objArrI[i];
                    servicio service = new servicio();
                    Object[] obj = st.getAttributes();
                    service.setId(((BigDecimal)obj[0]).intValueExact());
                    service.setNombre(String.valueOf(obj[1]));                  
                    intereses.add(service);
                }              
            }          
        }
        catch (Exception ex) {
            System.out.println("ERROR: " + ex.toString());
        }
    }
    public String getNick(){ return nick; }
    public String getNombre(){ return nombre; }
    public String getApellidos(){ return apellidos; }
    public String getEmail(){ return email; }
    public int getSaldo(){ return saldo; }
    public String getFec_alta(){ return fec_alta; }
    public int getValoracion(){ return valoracion; }
    public double getLoc_lat(){ return loc_lat; }
    public double getLoc_long(){ return loc_long; }
    public String getUltima_conexion(){ return ultima_conexion; }  
    public ArrayList <servicio> getServicios(){ return servicios; }
    public ArrayList <servicio> getIntereses(){ return intereses; }
}

Hay que destacar la definición de los parámetros de salida para los servicios e intereses, en los que hay que especificar el tipo de lista definido en Oracle:

    cs.registerOutParameter(12, Types.ARRAY, "TYP_LST_SERVICIOS_2");
    cs.registerOutParameter(13, Types.ARRAY, "TYP_LST_SERVICIOS_2");

y la conversión del Array que se obtiene como salida del procedimiento almacenado en una ArrayList:

    Array arrS = cs.getArray(12);
    Object[] objArrS = (Object[]) arrS.getArray();
    for(int i=0; i<objArrS.length;i++){
        STRUCT st = (STRUCT)objArrS[i];
        servicio service = new servicio();
        Object[] obj = st.getAttributes();
        service.setId(((BigDecimal)obj[0]).intValueExact());
        service.setNombre(String.valueOf(obj[1]));                  
        servicios.add(service);
    }  

Con esto hemos terminado, espero que le sea de utilidad a alguien.

Hasta otra.