Mantenimiento de una tabla en Oracle con Java y JSP – 13/15 – Crear CallableStatement con un cursor como parametro de salida.

Capa BD
01. Crear una BD en Oracle.
02. Crear una Tabla con clave primaria.
03. Crear un secuenciador.
04. Implementar sentencias para mantenimiento de la tabla.
05. Crear un package en la BD.
06. Crear un SP con un cursor como parametro de salida.
07. Crear un SP para devolver nombres similares de un campo.

Applicacion Web
08. Crear Un proyecto Web en Java.

Capa modelo
09. Crear Una clase Entidad (una clase simple).
10. Obtener una Conexión hacia la BD.
11. Crear un Statement simple.
12. Crear un PreparedStatement con parametros.
13. Crear un CallableStatement con un cursor como parametro de salida.

Capa Controlador
14. Crear un Servlet Controlador.

Capa Vista
15. Crear una pagina JSP con un formulario.

Anexos
01. Cadena de conexión.
02. Interface connection.
03. Clase OracleDataSource.
04. SQLException.
05. try – catch.
06. RequestDispatcher.

Crear un CallableStatement con un cursor como parametro de salida

Esta forma de sentencia de JDBC se corresponde con las ejecuciones de SPs en los scripts SQL de la BD.
Esto permite hacer uso de la versatilidad de busqueda de la BD y la logica de codigo en lenguaje PL/SQL.

Preparación:


Este codigo se hara en el archivo Linea.java
Se importara la clase OracleTypes del paquete de Oracle.

Como se hace

import oracle.jdbc.OracleTypes;

Obtener una fila buscada por id


public String[][] selectLineaSP(int id) {

         //array en donde se copiara el resultado
         // de la llamada al Stored procedure
        String[][] rsArray = new String[10][];
        //indice del array
        int i = 0;
        Connection conn;
        CallableStatement cstmt;
        ResultSet rset;
        try {

            // Crea un OracleDataSource y coloca el URL
            OracleDataSource ods = new OracleDataSource();
            ods.setUser("SYSTEM");
            ods.setPassword("12345");
            ods.setURL("jdbc:oracle:thin:@//masterpc:1521/Quadro");//OK

            System.out.print("Conectando a la database...");
            System.out.println("Conectando...");
            conn = ods.getConnection();
            System.out.println("conectado.");

            //Crear un callable statement
            //Los parametros son indicados por ?
            cstmt = conn.prepareCall("{call missps.selectLinea(?,?)}");

            //Colocar el String dentro de la sentencia callableStatement
            // se coloca un valor al parametros con:
            //set<tipoDato>(indice de parametro,Valor)
            cstmt.setInt(1, id);

            //Registrar el parametro de salida
            //El tipo de dato CURSOR referenciara al resultado
            //obtenido de la llamada al Stored Procedure
            cstmt.registerOutParameter(2,OracleTypes.CURSOR);

            //Se llama al metodo execute porque un Stored
            //procedure puede devolver mas de un set de resultados
            //El metodo executeQuery solo podra retornar un
            //solo set de resultados
            cstmt.execute();

            // Con getObject Obtenemos un valor generico al que luego
            // se le hara cast para convertirlo en el tipo adecuado en este caso ResultSet
            rset = (ResultSet)cstmt.getObject(2);

            while (rset.next()) {
                System.out.print(rset.getInt(1) + "|");
                System.out.print(rset.getString(2) + "|");
                System.out.println(rset.getInt(3));

                rsArray[i] = new String[3];

                rsArray[i][0] = String.valueOf(rset.getInt(1));
                rsArray[i][1] = rset.getString(2);
                rsArray[i][2] = String.valueOf(rset.getInt(3));
                i++;
            }

            // cerrar el resultSet
            rset.close();
            // cerrar el el callablestatement
            cstmt.close();
            // Cerrar la conexión
            conn.close();

            return rsArray;

        } catch (SQLException e) {
            // Check for and return connection error or SQL error.
            if (e.getSQLState() == null) {
                System.out.println(
                        new SQLException("Oracle Thin Client Net8 Connection Error.",
                        "ORA-" + e.getErrorCode()
                        + ": Incorrect Net8 thin client arguments:nn", e.getErrorCode()).getSQLState());
                return rsArray;
            } else {
                System.out.println(e.getMessage());
                return rsArray;
            }
        }

        finally {
            //block finally usado para cerrar recursos
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }//fin finally try

    }    //fin de metodo

Obtener nombres parecidos en la columna descripcion

public List selectLineaSP3(String desc) throws SQLException {

        //Usaremos un ArrayList
        //Es dinamico
        //Es adecuado para operaciones de lectura como los
        //que devuelte un selec

       List rsArrayList = new ArrayList();
        //indice de este array
        int i = 0;
        int nroCols = 0;
        Connection conn;
        CallableStatement cstmt;
        ResultSet rset;
        try {

            // Crear una OracleDataSource y colocar URL
            OracleDataSource ods = new OracleDataSource();
            ods.setUser("SYSTEM");
            ods.setPassword("12345");
            ods.setURL("jdbc:oracle:thin:@//masterpc:1521/Quadro");//OK

            System.out.print("Conectando a la database...");

            System.out.println("Conectando...");
            conn = ods.getConnection();
            System.out.println("conectado.");

            cstmt = conn.prepareCall("{call missps.buscarxDescrip(?,?)}",

            //Asignamos un valor al parametro
            //set<tipo>(indiceParametro,Valor);
            //Colocar el String dentro de la sentencia
            //CallableStatement
            cstmt.setString(1, desc);

            cstmt.registerOutParameter(2, OracleTypes.CURSOR);

            rset = (ResultSet) cstmt.executeQuery();

            if (rset == null) {
                System.out.println("Problema con la BD");
            } else {

                String[] filaTemp = null;
                while (rset.next()) {
                    System.out.print(rset.getInt(1) + "|");
                    System.out.print(rset.getString(2) + "|");
                    System.out.println(rset.getInt(3));
                    //Mandamos el rset para obtener un filaTemp de tipo String[]
                    filaTemp = llenarArrayListRes(rset);
                    rsArrayList.add(filaTemp);
                }
            }

            // close the resultSet
            rset.close();
            // Close the statement
            cstmt.close();
            // Close the connection
            conn.close();
            return rsArrayList;

        } catch (SQLException e) {
            // Check for and return connection error or SQL error.
            if (e.getSQLState() == null) {
                System.out.println(
                        new SQLException("Oracle Thin Client Net8 Connection Error.",
                        "ORA-" + e.getErrorCode()
                        + ": Incorrect Net8 thin client arguments:nn", e.getErrorCode()).getSQLState());
                return rsArrayList;
            } else {
                System.out.println(e.getMessage());
                return rsArrayList;
            }
        }

        finally {
            //block finally usado para cerrar recursos
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }//fin finally try

    }

Funcion para crear un array que se agregara a List

private String[] llenarArrayListRes(ResultSet rset) throws SQLException {
        String[] miFila = null;
        int nroCols = 0;
        //    try {
            ResultSetMetaData rsetmd = rset.getMetaData();
        nroCols = rsetmd.getColumnCount();

        miFila = new String[nroCols];
        for (int j = 0; j < nroCols; j++) {
            //Obtenemos como Object el valor de la
            //columna j en la fila actual y lo
            //convertimos a String
            miFila[j] = String.valueOf(rset.getObject(j + 1));
        }
        //this.rsArrayList.add(miFila);
        return miFila;
        /*      } catch (SQLException e) {
        // Check for and return connection error or SQL error.
        if (e.getSQLState() == null) {
        System.out.println(
        new SQLException("Oracle Thin Client Net8 Connection Error.",
        "ORA-" + e.getErrorCode()
        + ": Incorrect Net8 thin client arguments:nn", e.getErrorCode()).getSQLState());
        return miFila;
        } else {
        System.out.println(e.getMessage());
        return miFila;
        }

        }*/
    }

Como funciona :

Obtener una fila buscada por id

Obtenemos la conexion del objeto OracleDataSource.
En la clase Linea.java se usaráoracle.jdbc.OracleTypes para indicar el tipo de retorno del Stored Procedure.
Añadiremos el método que llama al Stored procedure missps.selectLinea y convierte la respuesta en una array.

Luego con el metodo prepareCall(sentencia) del objeto Connection obtenemos un CallableStatement y lo asignamos al objeto cstmt. Donde <sentencia> es un String con un formato que usa Java para hacer llamados a Stored Procedures. Inicia y cierra con llaves y dentro se usa la palabra call <NombreSP(lista_de_parametros)>. En la lista de parametros se colocaran tantos signos de cierre de interrogacion como parametros tenga el Stored Procedure.
Este Stored Procedure que estamos usando tiene dos parametros uno de entrada de tipo int y otro de salida de tipo Varchar2.
En este ejemplo se va a usar el valor del primer parametro en el SP para obtener el registro con el id buscado.
El tipo de dato de este parametro en Oracle es int, y en JDBC es int, por lo que se usara el metodo setInt(indice de parametro,valor).
El segundo parametro es de salida y de tipo CURSOR, que referenciara al resultado obtenido de la ejecución del SP.
Para establecer la variable que contendra al parametro de salida se usa el metodo registerOutParameter(indice de parametro,valor), donde valor para este caso es OracleTypes.CURSOR que indica que el resultado es un cursor el cual referenciara al resultado.

Se llama al metodo execute() del objeto CallableStatement. Con este metodo se puede obtener mas de un set de resultados devuelto por un Stored Procedure, a diferencia que con el metodo executeQuery() de la misma clase que solo obtiene un set de resultados. Solo se usa este metodo para fines demostrativos, porque este SP solo devuelve uno.

Una vez que ya se ha ejecutado el CallableStatement, podemos acceder a sus resultados con el metodo getObject(indice_de_parametro), que tendra como indice_de_parametro el valor 2 porque es el parametro CURSOR que referencia al resultado. Este metodo devuelve un conjunto de valores generico por lo que se hace un cast hacia el tipo ResultSet para asi poder asignarlo al objeto rset del mismo tipo.
Con un while iteramos a traves del set de resultados mientras el metodo next() de ResultSet devuelva true.
En cada iteracion crearemos 3 columnas a cada elemento de indice i del array rsArray en el que colocaremos los valores de los campos correspondientes del ResultSet convertidos a String.
Luego se procede a cerrar el ResultSet, el CallableStatement y la conexion.
Se retorna el array rsArray como respuesta del metodo selectLineaSP().
Despues esta el bloque catch que capturara las excepciones SQLException en el que se tiene que poner un return para que devuelva el rsArray aunque este vacio.
Por ultimo el bloque finally que se ejecuta siempre en donde se comprueba que si la conexion sigue existiendo , entonces se cierra.

Obtener nombres parecidos en la columna descripcion

Una de las cosas que hacemos cuando buscamos datos es mejorar nuestra consulta para obtener resultados iguales o parecidos al texto buscado.
Para dar esa funcionalidad SQL tiene el operador LIKE, que toma busca resultados parecidos al texto buscado.
De esa forma si buscamos “gas”, obtendremos:
gas 10 kg
gas 45 kg
gas 90 kg

LIKE, toma como parametros una cadena de texto y uno o mas caracteres comodin.
El objetivo es usar el parametro de entrada “Descrip” del procedimiento almacenado buscarxDescrip del paquete misssps y obtener una lista de LINEAS que comiencen con el texto “gas”.

El funcionamiento es identico que el del metodo anterior selectLineaSP, solo hay que hacer estos cambios
– Poner un argumento desc en el metodo selectLineaSP3
– Usar una Lista para almacenar el resultado de la consulta.
– en la sentencia que invoca al metodo prepareCall, usar el SP missps.buscarxDescrip.
– en la sentencia que pone el primer parametro para enviar al SP cambiar el tipo de dato a String.

//...
List rsArrayList = new ArrayList();
//...
cstmt = conn.prepareCall("{call missps.buscarxDescrip(?,?)}",
//...
cstmt.setString(1, desc);

Estas diferencias estan resaltadas en los codigos Obtener una fila buscada por id y Obtener nombres parecidos en la columna descripcion.

Primero declaramos un objeto de tipo List que contendra arreglos de tipo String, este sera usado para retornar el valor del metodo. El uso de la lista elimina el hecho de estar creando un array de tamaño fijo, tambien el crear arrays por cada nuevo registro y el estar comprobando si el arreglo tiene o no valores null que indiquen que ya no contiene valores. El tipo de referencia sera de tipo ArrayList, porque es dinamico y es adecuado para operaciones de lectura como los que devuelte un selec.

Luego se llevan a cabo la conexion con la BD, la creacion del callableStatement, la asignacion de parametros y la ejecucion de la consulta.
Esta vez ejecutamos el metodo executeQuery(), y realizamos el proceso de iteracion en el blucle while.
Dentro del bucle se convierte cada registro a un array de Strings con el metodo llenarArrayListRes. Este metodo asigna su resultado al array filaTemp, luego este array es adicionado como un objeto a la lista con el metodo add.

Funcion para crear un array que se agregara a List

El metodo llenarArrayListRes tiene un argumento de tipo ResultSet y lanza una SQLException.
Primero se crea un array miFila de tipo String que almacenara los valores del ResultSet y sera devuelto por el metodo.
Para obtener el numero de columnas del resultado usaremos la clase ResultSetMetaData que provee datos acerca de la estructura del ResultSet actual, se obtiene un objeto ResultSetMetaData invocando a getMetaData() del objeto rset y lo asignamos a rsetmd , luego obtenemos el numero de columnas invocando a getColumnCount() del objeto rsetmd.
Luego creamos un array de Strings con el tamaño del numero de columnas.

Despues se ingresa a un bucle for que iterara sobre cada columna del objeto rset .
En cada iteracion se toma un objeto y se le hace un casta para asignarlo al array miFila. El indice de los objetos comienza en 1 mientras que los arrays comienzan en 0, por eso se usa miFila[j] = String.valueOf(rset.getObject(j + 1));.
Por ultimo se retorna el array miFila.

Ver tambien


PL-SQL Oracle desde Java, http://albertovilches.com/pl_sql_oracle_desde_java

http://javaes.wikispaces.com, Capítulo 7 – Genéricos y Colecciones

Anuncios

Un comentario en “Mantenimiento de una tabla en Oracle con Java y JSP – 13/15 – Crear CallableStatement con un cursor como parametro de salida.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s