Obtener una Colección mediante JDBC

Para mapear objetos colección mediante JDBC, vamos a mostrar el ejemplo utilizado para solucionar al error ORA-01000 en /*Consulta de Notas de Selectividad del 2015*/.

Lo primero que tenemos que definir es el tipo que va a contener los datos (representa la fila de una tabla), en este caso se utiliza un Object como base, y se declara fuera del paquete PL/SQL. Recordad que tenemos que darle el permiso EXECUTE al usuario JV_ asociado con la aplicación WEB.

CREATE OR REPLACE TYPE ACCESO.R_NOTAS_PARTE_GENERAL AS Object(
    dni             VARCHAR2(8),
    letra           VARCHAR2(1),
    año             NUMBER,
    conv            VARCHAR2(1),
    cod_tipo_acceso VARCHAR2(1),
    cod_materia     VARCHAR2(10),
    num_correccion  NUMBER,
    nota            NUMBER,
    nulo1           VARCHAR2(1),
    nulo2           VARCHAR2(1),
    des_materia     VARCHAR2(40)
);
 
GRANT EXECUTE ON ACCESO.R_NOTAS_PARTE_GENERAL TO JV_ACCESO;

Ahora creamos el tipo que va a representar la tabla (sería la colección de objetos) basado en el tipo anterior, fuera del paquete PL/SQL y darle el permiso EXECUTE al usuario JV_ asociado con la aplicación WEB.

CREATE OR REPLACE TYPE ACCESO.T_NOTAS_PARTE_GENERAL IS TABLE OF ACCESO.R_NOTAS_PARTE_GENERAL;
 
 
GRANT EXECUTE ON ACCESO.T_NOTAS_PARTE_GENERAL TO JV_ACCESO;

A continuación, tenemos el código de la función.

• En la declaración de la función, primero creamos el objeto t_notas del tipo ACCESO.T_NOTAS_PARTE_GENERAL y obtenemos el cursor c_notas_general.
• En el cuerpo, tenemos un bucle, donde en cada iteración, creamos un objeto ACCESO.R_NOTAS_PARTE_GENERAL y lo asigna a la posición indicada por el indice i de t_notas. En cada iteración, se llama a t_notas.Extend para extender en una fila a t_notas, antes de la asignación del nuevo objeto ACCESO.R_NOTAS_PARTE_GENERAL creado.

FUNCTION f_notas_parte_general(p_anyo IN NUMBER, p_conv IN VARCHAR2,p_tipo_acceso IN VARCHAR2,p_dni IN VARCHAR2, p_letra IN VARCHAR2) RETURN ACCESO.T_NOTAS_PARTE_GENERAL IS
 
     i NUMBER(2) := 0;    
     t_notas ACCESO.T_NOTAS_PARTE_GENERAL := ACCESO.T_NOTAS_PARTE_GENERAL();
 
     Cursor c_notas_general IS
         SELECT P.dni, P.letra, P.año, P.conv, P.cod_tipo_acceso, P.cod_materia, P.num_correccion, P.nota, mp.des_materia
           FROM TABLA1 P, TABLA2 mp
          WHERE cond1 = ? AND cond2 = ? ;
 
     BEGIN
         FOR c IN c_notas_general Loop
                i := i + 1;
                dbms_output.put_line(i);              
                t_notas.Extend;
                t_notas(i) := ACCESO.R_NOTAS_PARTE_GENERAL( c.dni, c.letra, c.año, c.conv, c.cod_tipo_acceso, c.cod_materia,  c.num_correccion, c.nota, NULL, NULL,  c.des_materia);    
         END Loop;       
        RETURN t_notas;
END f_notas_parte_general;

Los pasos serian los siguientes:

1- Configuramos la cadena de la llamada PL/SQL.
2- Obtenemos la meta información del tipo PL/SQL que contiene los datos.
3- Indicamos que el resultado es de tipo ARRAY.
4- Obtenemos el ARRAY JDBC.
5- Obtenemos el array de Objects Java.
6- Los objetos del ARRAY JDBC son STRUCT.
7- Obtenemos los atributos del STRUCT.
8- Iteramos sobre los atributos del STRUCT, para crear el objeto que utilizamos en la aplicación.
9- Devolvemos la lista de objetos, con la representación de la aplicación

   /**
     * Devuelve las notas de la parte general para un año, convocatoria, tipo de acceso y usuario
     * SE OBTIENEN CON UN ARRAY DE OBJETOS ORACLE
     * FUNCIONA COMO ESTÁ
     * @author javierbl
     * @return 1 si la clave es correcta, 0 en caso contrario
     */
    public List<NotaMateria> funNotasParteGeneral(short anyo, String conv, String tipo_acceso, String dni, String letra)
    {
        Connection con = null;
        CallableStatement cs = null;
        ResultSet rs = null;
        List<NotaMateria> al = new ArrayList<NotaMateria>();
 
        try
        {        
            Map<String, String> logParams = new LinkedHashMap<String, String>(); 
            String consulta = "MI_PAQUETE.f_mi_funcion";
            logParams.put("p_año", anyo+"");
            logParams.put("p_conv", conv);
            logParams.put("p_tipo_acceso", tipo_acceso);
            logParams.put("p_dni", dni);
            logParams.put("p_letra", letra);       
            this.getLog().info("Ejecutamos el método funNotasParteGeneral, consulta [%0] - paramtreos [%1]", consulta, logParams) ;  
 
            // 1- Configuramos la cadena de la llamada PL/SQL.
            String llamada = new String("{? = call ACCESO.MI_PAQUETE.f_mi_funcion(?,?,?,?,?)}");
            con = this.dataSource.getConnection();
 
            // 2- Obtenemos la meta información del tipo PL/SQL que contiene los datos
            StructDescriptor stDesc = StructDescriptor.createDescriptor("ACCESO.R_NOTAS_PARTE_GENERAL",con);
            final ResultSetMetaData metaData = stDesc.getMetaData();
 
            cs = con.prepareCall(llamada);
            // 3- Indicamos que el resultado es de tipo ARRAY
            cs.registerOutParameter(1, OracleTypes.ARRAY, "ACCESO.T_NOTAS_PARTE_GENERAL");
            // Establecemos el Tipo del Parametro de entrada (IN)
            cs.setShort(2, anyo);
            cs.setString(3, conv);
            cs.setString(4, tipo_acceso);
            cs.setString(5, dni);
            cs.setString(6, letra);      
            cs.executeQuery();         
 
 
            Array lista = null;
 
            // 4- Obtenemos el ARRAY JDBC
            if (cs.getArray( 1 ).getClass().getCanonicalName().startsWith( "weblogic.jdbc.wrapper.Array" )) {
                // Con este código obtenemos el objeto OJDBC en lugar del Wrapper de Weblogic
                try {
                    Object weblogicArray = cs.getArray( 1 );
                    Method vendorObjectmethod = Reflections.getMethod( cs.getArray( 1 ).getClass(), "getVendorObj" );
                    lista = (Array) vendorObjectmethod.invoke( weblogicArray );
                }
                catch ( Exception e ) {
                   log.error("Error al obtener el Array de Oracle SQL", e);
                }
            }
            else {
                lista = cs.getArray( 1 );
            }
 
            // 5- Obtenemos el array de Objects Java
            Object[] objectos = (Object[]) lista.getArray();              
            int i = 1;
            // Creamos los datos en la representacion de nuestra aplicacion --> NotaMateria
            for (Object objeto : objectos) {
                // 6- Los objetos del ARRAY JDBC son STRUCT
                STRUCT struct = (STRUCT) objeto;
                NotaMateria nm = new NotaMateria();
 
                // 7- Obtenemos los atributos del STRUCT
                Object[] atributos = struct.getAttributes();
 
                // 8- Iteramos sobre los atributos del STRUCT, para crear el objeto que utilizamos en la aplicacion
                for (Object atributo : atributos) {
                    if (atributo != null) {
                        if("DNI".equals(metaData.getColumnName( i ))) {
                            nm.setDni(atributo.toString());
                        }
                        else if("LETRA".equals(metaData.getColumnName( i ))) {
                            nm.setLetra(atributo.toString());
                        }
                        else if("AÑO".equals(metaData.getColumnName( i ))) {
                            nm.setAño(Short.valueOf( atributo.toString()));
                        }
                        else if("CONV".equals(metaData.getColumnName( i ))) {
                            nm.setConv(atributo.toString());
                        }
                        else if("COD_TIPO_ACCESO".equals(metaData.getColumnName( i ))) {
                            nm.setCodTipoAcceso(atributo.toString());
                        }
                        else if("COD_MATERIA".equals(metaData.getColumnName( i ))) {
                            nm.setCodMateria(atributo.toString());
                        }
                        else if("NUM_CORRECCION".equals(metaData.getColumnName( i ))) {
                            nm.setNumCorreccion(Short.valueOf(atributo.toString()));
                        }
                        else if("NOTA".equals(metaData.getColumnName( i ))) {
                            nm.setNota( Float.valueOf( atributo.toString()));
                            nm.setTextoNota(formatNota(nm.getNota()));
                        }
                        else if("DES_MATERIA".equals(metaData.getColumnName( i ))) {
                            nm.setDesMateria(atributo.toString());
                        }  
                    }
                    i++;                   
                }
                i = 1;
                al.add(nm);  
            }
            this.getLog().info("Salimos del metodo funNotasParteGeneral, resultado [%0]", al.size());          
        } catch(Exception e) {
            log.error("[ACCESO]::[ERROR] :: funNotasParteGeneral :: Error al ejecutar al ejecutar la funcion", e);
        }
        finally {
            close(con, cs, rs);
        }
        // 9- Devolvemos la lista de objetos, con la representacion de la aplicacion
        return al;
    }

— JUAN MIGUEL BERNAL GONZALEZ 2016/07/08 12:43