Invocar a procedimientos almacenados de Oracle usando Spring JDBC

Invocar a procedimientos almacenados de Oracle usando Spring JDBC

Introducción

Practicamente cualquier aplicación que nos planteemos hacer va a necesitar de una base de datos (o cualquier otro modo de persistencia, por ejemplo un fichero) para almacenar la información, leerla, etc. Suponiendo que estamos usando una base de datos relacional, basada en SQL, la forma habitual de comunicarnos con la base de datos es mediante consultas sencillas (select, update, delete, insert). Sin embargo, podemos encontrarnos en la necesidad de interactuar con la base de datos mediante llamadas a procedimientos almacenados En este turorial vamos a utilizar la clase StoredProcedure de Spring para invocar a procedimientos en PL/SQL de Oracle

Comenzando por el principio

Lo primero es tener una base de datos Oracle, y en esa base de datos tener unos procedimientos almacenados… Si no, poco vamos a hacer en este tutorial 😉 La base de datos que vamos a emplear es muy sencilla, con una tabla de discos y una tabla de canciones de dicho disco La tabla de discos Estructura de la tabla de discos La tabla de canciones Estructura de la tabla de canciones La instalación y configuración de Oracle lo dejo para vosotros. Lo mismo digo de la configuración de un DataSource en vuestro servidor que permita conectar contra el Oracle que hemos instalado y que se pueda acceder por JNDI. En cuanto a los procedimientos almacenados, os dejo aquí el código de los mismos

Primeros pasos en nuestra aplicación Spring

Para desarrollar este tutorial nos vamos a basar en el proyecto de Spring MVC que creamos para el tutorial anterior. Sobre dicho proyecto, hacemos un primer cambio en el fichero de configuración “spring-business context.xml”, donde definiremos un DataSource que usaremos para invocar a nuestros procedimientos A continuación, crearemos nuestro primer procedimiento almacenado en Java usando Spring. Vamos a comenzar con el que devuelve el titulo de un disco a partir de su identificador (OBTIENE_TITULO_DISCO) Para ello, comenzamos creando una clase que extienda de StoredProcedure Sobre esta clase hay dos cosas importantes que decir: Por un lado, los parámetros deben definirse siempre en el mismo orden en que aparezcan en la especificación del procedimiento. En el caso de ser una función (en lugar de un procedimiento), como es este caso, el primer parámetro siempre será un parámetro de salida, que será el valor de retorno de la función. Además, en este último caso, es necesario indicar que se trata de una función mediante la llamada “setFunction(true)”. Esto se debe a que, al final, Spring acaba empleando JDBC por debajo, y la forma de generar un “CallableStatement” es distinta en el caso de las funciones (“{? = call miFuncion(?)}”) que en el de los procedimientos (“{call miProcedimiento(?)}”) También es importante tener en cuenta que, cuando ejecutemos el procedimiento, tenemos que proporcionar valores a todos los parámetros de entrada (o de entrada / salida) que declaremos. Esto lo haremos creando un mapa en el que especificaremos como claves los nombres que hemos usado al declarar los parámetros. Los resultados de la ejecución los obtendremos a su vez en otro mapa, en el que se emplearán como claves los nombres dados a los parámetros de salida al declararlos De acuerdo… Con esto ya tenemos la clase que acabará invocando al procedimiento almacenado. Ahora vamos a crear toda la estructura de clases para que la petición de un usuario desencadene la llamada a la base de datos Primero creamos un interfaz que ofrezca todas las operaciones posibles en nuestra aplicacion Y luego una clase que implemente este interfaz, y que será la que acabe llamando a la clase que extiende el procedimiento almacenado Después, creamos un controlador que llame a los métodos del interfaz Y finalizamos con la JSP que pinta los resultados Si ahora hacemos una invocación a nuestro controlador… Nos devuelve el resultado :)

Procedimientos que devuelven cursores

Ya hemos dado un primer paso, aunque bastante básico… Hemos visto como invocar a métodos que devuelven un único resultado (aunque es muy simple extrapolarlo a procedimientos con N parámetros de salida simples, es decir, números o cadenas de texto)). Sin embargo, puede darse en caso de que el procedimiento almacenado devuelva un conjunto de registros (el resultado de una query, por ejemplo). Esto, en PL/SQL se hace mediante el uso de “Cursores”. El manejo de un cursor como parámetro de salida no es tan sencillo como en los tipos básicos, pero tampoco es particularmente complejo En este ejemplo, yo tengo un procedimiento que recibe el nombre de un intérprete y devuelve en un cursor todos los discos de dicho artista Antes de empezar a codificar nuestro llamada al procedimiento desde Java, hay que tener en cuenta que los cursores no se encuentran entre los tipos básicos de SQL, por lo que necesitaremos incluir en nuestro pom.xml la dependencia del jar de Oracle Esta dependencia la he marcado como “provided” porque he colocado el jar en el classpath del Tomcat, para que lo compartan todas las aplicaciones del servidor El siguiente paso es crear la clase Java que representará al procedimiento almacenado Como podéis ver, no es ni mucho menos complejo tratar los cursores que devuelve el procedimiento. Simplemente, al declarar el parámetro de salida, hay que indicar que se trata de un cursor (fijaos en que hay que usar una constante de la clase OracleTypes, por eso hemos tenido que añadir el jar de “ojdbc” a nuestro proyecto) y proporcionarle un RowMapper, es decir, una clase que permita a Spring saber cómo sacar los datos del ResultSet en que convierte el cursor. En este caso, al ser un ejemplo muy sencillo y para que se vea más claro, he hecho una implementación “inline” del interfaz, pero vosotros podéis implementarlo en una clase externa y simplemente pasar una instancia del mapeador. En la implementación del DiscosRepository (DiscosRepositoryImpl) simplemente hay que añadir un objeto de la nueva clase e implementar el método correspondiente… o sea que voy a saltarme ese paso e ir directamente con la parte de la vista (Controller + JSP) En la clase DiscosController añadimos un nuevo método, que invocará a nuestro nuevo procedimiento almacenado Y añadimos una nueva JSP, que pintará la lista de discos recuperados Y si invocamos ahora a nuestra nueva página… Otra cosa conseguida :) Listado de discos por intérprete

Manejando Blobs

Ya sabemos como llamar a funciones, a procedimientos y cómo manejar un cursor… Nuestro siguiente paso va a ser el manejo de Blobs, tanto de entrada como de salida

Pasando Blobs a un procedimiento almacenado

Bueno… vamos a dar el siguiente paso. Y para ello, vamos a comenzar al revés de como lo hemos hecho hasta ahora y vamos a empezar creando una página con un formulario… Formulario de alta de un disco Y para que esta página funcione, hemos añadido dos nuevos métodos a nuestro DiscosController Como podéis ver, hemos creado un nueva clase, DiscoFormBean, para manejar los datos enviados por nuestro formulario. Lo hemos hecho porque hemos usado el sistema para subir el fichero con la carátula del disco que explicamos en el tutorial anterior. Por tanto, necesitamos una variable de tipo “CommonsMultipartFile”, por lo que el bean que ya teníamos no nos vale :-( . Este es nuestro nuevo bean: El siguiente paso es implementar nuestro clase de llamada al procedimiento almancenado En el código he resatado tres líneas, que son la clave para que funcione correctamente la llamada con el Blob En la primera, creamos un manejador de blobs propio de Oracle. Esto es imprescindible, puesto que si no lo hacemos Spring y el driver no son capaces de tratar correctamente el dato y la manejan como si fuera una cadena de caracteres. Esto quiere decir que no admitirá más de 4000 bytes aproximadamente La segunda de estas líneas resaltadas es la que permite que las conexiones con las que trabaje Spring sean propias del driver de Oracle. Sin esto, el manejador de LOB’s que hemos declarado arriba produce un error porque no puede asociar un manejador de Oracle a una conexión que no sea de dicha base de datos Por último, en la tercera línea asociamos al parámetro de entrada un tipo de datos específico para Lob’s, pasándole como parámetro el manejador de lobs que hemos declarado antes. Si ahora, usando el formulario que hemos creado anteriormente, insertamos un nuevo disco con su carátula… Tabla de discos con un blob insertado Podemos ver que el último registro tiene el campo de la carátula relleno. Otra cosa conseguida :)

Extrayendo un Blob de un procedimiento almacenado

Una vez que sabemos como insertar un Blob en la base de datos, ahora toca aprender a recuperarlo Vamos a ver cómo implementar una clase que llame a un procedimiento almacenado que recupere la carátula de nuestro disco Este caso es todavía más sencillo que el anterior… lo único reseñable es que el índice a la hora de recuperar el contenido del blob en forma de array de bytes, empieza en 1. Esto es una constante en JDBC, que podemos ver también en los ResultSet (la primera columna devuelta está en la posición 1) o los PreparedStatements (en que el primer parámetro es el 1, no el 0 como es lo habitual en Java) El siguiente paso es añadir un nuevo método a nuestro clase controladora Y si invocamos a este nuevo controlador… Ya tenemos nuestra carátula!! Carátula de un disco

Pasando Arrays como parámetro a un procedimiento almacenado

Ya casi hemos terminado con este “breve” repaso a las llamadas a procedimientos almacenados PL/SQL desde Java usando Spring. Sólo nos queda un paso, que es llamar a un procedimiento que recibe un array (VARRAY en este caso). Los VARRAYS son un tipo de colección de Oracle que tiene la particularidad de tener un tamaño prefijado. es decir, sabemos de antemano el número máximo de elementos que va a contener Si váis al principio del tutorial y revisáis el código de los procedimientos almacenados, veréis que hay una línea comentada Esta es la declaración del tipo “LISTA” como un array que puede contener hasta 150 cadenas de caracteres. Este tipo de dato es el que se emplea al pasar la lista de canciones al procedimiento que da de alta un disco en la base de datos. Pero, entonces ¿por qué está comentado? Y ¿por qué sigue funcionando el procedimiento si la declaración está comentada? La respuesta es “simple”… Existe una limitación en el uso que Java / JDBC puede hacer de los tipos declarados en Oracle. En particular, no puede usar tipos declarados y definidios dentro de un paquete de procedimientos (El caso del cursor es diferente. En este caso, Java si que es capaz de “traducirlo” a un ResultSet). Por tanto, la declaración del tipo “LISTA” la he sacado del paquete y he creado dicho tipo como un tipo de datos general para toda la instancia de Oracle: Una vez que tenemos esto, ahora hay que hacer que nuestra clase “CreaNuevoDisco” pase los datos correctamente al Oracle. En las líneas de la 14 a la 30 del código anterior, se observa el proceso para poder pasar el array como parámetro al procedimiento almacenado. Tengo que decir que esta es la parte que más quebraderos de cabeza me ha dado, no por su dificultad, sino porque, tras horas de hacer pruebas y obtener errores, parece que hay algún problema con el paso de arrays a versiones de Oracle Express de 64 bits (adivinad cuál estaba usando 😉 . En cuanto lo he probado con una base de datos en un sistema de 32 bits, ha funcionado todo a la primera Modificamos el formulario para solicitar los títulos de las canciones, y lo rellenamos con nuestros datos Formulario con lista de canciones Enviamos el formulario y… Nuevo disco en base de datos Canciones del nuevo disco Como podemos comprobar, todo se ha grabado correctamente…

Conclusiones

Cada vez es más habitual el uso de API’s y frameworks de persistencia como Hibernate u otros similares. Sin embargo, nunca está de más saber crear clases que accedan directamente a la base de datos, ya sea mediante consultas o mediante invocación de procedimientos almacenados. Spring nos proporciona clases que nos facilitan el trabajo con estos últimos, pero aún así a veces es necesario conocer ciertos mecanismos para poder emplear las características específicas de lenguajes como el PL/SQL de Oracle. En este tutorial os hemos enseñado cómo utilizar los mecanismos que proporciona Spring para emplear dichas características. Esperamos que os sea útil