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
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
create or replace PACKAGE PCK_DISCOS IS TYPE RESULTADOS IS REF CURSOR; --TYPE LISTA IS VARRAY(150) OF VARCHAR2(255); FUNCTION OBTIENE_TITULO_DISCO ( PID_DISCO IN NUMBER ) RETURN VARCHAR2; PROCEDURE OBTIENE_DISCOS_INTERPRETE ( PINTERPRETE IN VARCHAR2, PDISCOS OUT RESULTADOS, PMENS_ERROR OUT VARCHAR2); PROCEDURE OBTIENE_CARATULA_DISCO ( PID_DISCO IN NUMBER, PCARATULA OUT BLOB); PROCEDURE CREAR_DISCO ( PTITULO IN VARCHAR2, PINTERPRETE IN VARCHAR2, PCARATULA IN BLOB, PCANCIONES IN LISTA, PID_DISCO OUT NUMBER); END PCK_DISCOS; / create or replace PACKAGE BODY PCK_DISCOS IS FUNCTION OBTIENE_TITULO_DISCO ( PID_DISCO IN NUMBER ) RETURN VARCHAR2 IS VTITULO VARCHAR2(255); BEGIN SELECT TITULO INTO VTITULO FROM DISCOS WHERE ID_DISCO = PID_DISCO; RETURN VTITULO; END; PROCEDURE OBTIENE_DISCOS_INTERPRETE ( PINTERPRETE IN VARCHAR2, PDISCOS OUT RESULTADOS, PMENS_ERROR OUT VARCHAR2) IS BEGIN OPEN PDISCOS FOR SELECT ID_DISCO, TITULO, INTERPRETE FROM DISCOS WHERE INTERPRETE = PINTERPRETE; Exception when others then PMENS_ERROR := SQLERRM; END; PROCEDURE OBTIENE_CARATULA_DISCO ( PID_DISCO IN NUMBER, PCARATULA OUT BLOB) IS BEGIN SELECT CARATULA INTO PCARATULA FROM DISCOS WHERE ID_DISCO = PID_DISCO; END; PROCEDURE CREAR_DISCO ( PTITULO IN VARCHAR2, PINTERPRETE IN VARCHAR2, PCARATULA IN BLOB, PCANCIONES IN LISTA, PID_DISCO OUT NUMBER) IS VID_DISCO NUMBER; BEGIN INSERT INTO DISCOS (TITULO, INTERPRETE, CARATULA) VALUES (PTITULO, PINTERPRETE, PCARATULA) RETURNING ID_DISCO INTO VID_DISCO; IF VID_DISCO > 0 THEN if pcanciones is not null then FOR i IN 1..PCANCIONES.COUNT LOOP INSERT INTO CANCIONES (ID_DISCO, TITULO) VALUES (VID_DISCO, PCANCIONES(i)); END LOOP; end if; END IF; PID_DISCO := VID_DISCO; END; END PCK_DISCOS; / |
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
|
1 2 3 4 5 6 |
<!-- Look up the database in JNDI --> <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean"> <property name="jndiName" value="java:comp/env/jdbc/TUTORIAL" /> </bean> |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
package com.autentia.tutoriales.file.upload.domain.procedures; import java.sql.Types; import java.util.HashMap; import java.util.Map; import javax.sql.DataSource; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.SqlOutParameter; import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.object.StoredProcedure; import org.springframework.stereotype.Service; @Service public class ObtieneTituloDisco extends StoredProcedure { public static final String PROC_OBTENER_TIT_DISCO = "PCK_DISCOS.OBTIENE_TITULO_DISCO"; @Autowired public ObtieneTituloDisco(DataSource ds) { super(ds, PROC_OBTENER_TIT_DISCO); //Declaramos los parametros del procedimiento declareParameter(new SqlOutParameter("tituloDisco", Types.VARCHAR)); declareParameter(new SqlParameter("idDisco", Types.INTEGER)); setFunction(true); compile(); } public String obtenerTituloDiscoPorId(int idDisco) { String titulo=""; Map<String, Object> result; Map<String, Object> paramsEntrada = new HashMap<String, Object>(); paramsEntrada.put("idDisco", idDisco); result = execute(paramsEntrada); titulo = (String) result.get("tituloDisco"); return titulo; } } |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
package com.autentia.tutoriales.file.upload.domain.repository; import java.util.List; import com.autentia.tutoriales.file.upload.domain.beans.Disco; public interface DiscosRepository { public String obtieneTituloDiscoPorId(int idDisco); public int creaDisco(Disco disco); public List<Disco> recuperaDiscosPorInterprete(String interprete); public byte[] recuperaCaratulaDisco(int idDisco); } |
Y luego una clase que implemente este interfaz, y que será la que acabe llamando a la clase que extiende el procedimiento almacenado
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
package com.autentia.tutoriales.file.upload.domain.repository; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.List; import com.autentia.tutoriales.file.upload.domain.beans.Disco; import com.autentia.tutoriales.file.upload.domain.procedures.ObtieneTituloDisco; @Service public class DiscosRepositoryImpl implements DiscosRepository { @Autowired ObtieneTituloDisco obtenerTitulo; @Override public String obtieneTituloDiscoPorId(int idDisco) { return obtenerTitulo.obtenerTituloDiscoPorId(idDisco); } @Override public int creaDisco(Disco disco) { // TODO Auto-generated method stub return 0; } @Override public List<Disco> recuperaDiscosPorInterprete(String interprete) { // TODO Auto-generated method stub return null; } @Override public byte[] recuperaCaratulaDisco(int idDisco) { // TODO Auto-generated method stub return null; } } |
Después, creamos un controlador que llame a los métodos del interfaz
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
package com.autentia.tutoriales.file.upload.web; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.ModelAttribute; import org.springframework.web.bind.annotation.RequestMapping; import com.autentia.tutoriales.file.upload.domain.beans.Disco; import com.autentia.tutoriales.file.upload.domain.repository.DiscosRepository; @Controller @RequestMapping("discos") public class DiscosController { private final DiscosRepository discosRepository; @Autowired public DiscosController(DiscosRepository discosRepository) { super(); this.discosRepository = discosRepository; } @RequestMapping(value="obtieneTitulo", params="idDisco") public @ModelAttribute("tituloDisco") String obtieneTituloDisco(@ModelAttribute Disco disco) { String tituloDisco=discosRepository.obtieneTituloDiscoPorId(disco.getIdDisco()); return tituloDisco; } } |
Y finalizamos con la JSP que pinta los resultados
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Insert title here</title> </head> <body> <c:choose> <c:when test="${not empty tituloDisco }"><p>El titulo del disco es <c:out value="${tituloDisco }" /> </c:when> <c:otherwise><p style="color: red;">no se ha encontrado el disco solicitado</p></c:otherwise> </c:choose> </body> </html> |
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
|
1 2 3 4 5 6 |
<dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc14</artifactId> <version>10.2.0.4.0</version> <scope>provided</scope> </dependency> |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
package com.autentia.tutoriales.file.upload.domain.procedures; import ... import oracle.jdbc.driver.OracleTypes; import com.autentia.tutoriales.file.upload.domain.beans.Disco; @Service public class ObtieneDiscosPorInterprete extends StoredProcedure { public static final String PROC_OBTENER_DISCOS_INTERPRETE = "PCK_DISCOS.OBTIENE_DISCOS_INTERPRETE"; @Autowired public ObtieneDiscosPorInterprete(DataSource ds) { super(ds, PROC_OBTENER_DISCOS_INTERPRETE); declareParameter(new SqlParameter("interprete", Types.VARCHAR)); declareParameter(new SqlOutParameter("discos", OracleTypes.CURSOR, new RowMapper<Disco>() { @Override public Disco mapRow(ResultSet rs, int rowNumber) throws SQLException { Disco disco = new Disco(); disco.setIdDisco(rs.getInt("id_disco")); disco.setTitulo(rs.getString("titulo")); disco.setInterprete(rs.getString("interprete")); return disco; } })); declareParameter(new SqlOutParameter("mensajeError", Types.VARCHAR)); compile(); } public List<Disco> obtieneDiscos(String interprete) { List<Disco> discos = new ArrayList<Disco>(); Map<String, Object> result; Map<String, Object> params = new HashMap<String, Object>(); params.put("interprete", interprete); result = execute(params); String mensError = (String) result.get("mensajeError"); if (!StringUtils.hasText(mensError)) { discos = (List<Disco>) result.get("discos"); } return discos; } } |
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
|
1 2 3 4 |
@RequestMapping(value="obtenerDiscosPorInterprete", params="interprete") public @ModelAttribute("discos") List<Disco> obtieneDiscosPorInterprete(@ModelAttribute Disco disco) { return discosRepository.recuperaDiscosPorInterprete(disco.getInterprete()); } |
Y añadimos una nueva JSP, que pintará la lista de discos recuperados
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"> <title>Obtener discos por interprete: {disco.interprete}</title> </head> <body> <p>Discos del interprete: ${disco.interprete}</p> <c:if test="${not empty discos}"> <ul> <c:forEach items="${discos}" var="myDisco"> <li>Título del disco: <c:out value="${myDisco.titulo }" /> - Interprete: <c:out value="${myDisco.interprete }" /></li> </c:forEach> </ul> </c:if> </body> </html> |
Y si invocamos ahora a nuestra nueva página… Otra cosa conseguida 🙂
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…
Y para que esta página funcione, hemos añadido dos nuevos métodos a nuestro DiscosController
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
@RequestMapping(value="altaDisco", method=RequestMethod.GET) public @ModelAttribute("nuevoDisco") DiscoFormBean muestraFormNuevoDisco() { return new DiscoFormBean(); } @RequestMapping(value="altaDisco", method=RequestMethod.POST) public @ModelAttribute("mensaje") String salvaNuevoDisco(@ModelAttribute DiscoFormBean nuevoDisco, Model model) { String mensaje = ""; Disco disco = new Disco(); disco.setTitulo(nuevoDisco.getTitulo()); disco.setInterprete(nuevoDisco.getInterprete()); disco.setCaratula(nuevoDisco.getCaratula() != null ? nuevoDisco.getCaratula().getBytes() : null); int idDisco = discosRepository.creaDisco(disco); if (idDisco > 0) { mensaje = "Disco guardado correctamente con id = "+idDisco; } else { mensaje = "Se ha producido un error al guardar el disco"; } model.addAttribute("nuevoDisco", new DiscoFormBean()); return mensaje; } |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
package com.autentia.tutoriales.file.upload.web.formbean; import org.springframework.web.multipart.commons.CommonsMultipartFile; public class DiscoFormBean { String titulo = ""; String interprete =""; CommonsMultipartFile caratula = null; public String getTitulo() { return titulo; } public void setTitulo(String titulo) { this.titulo = titulo; } public String getInterprete() { return interprete; } public void setInterprete(String interprete) { this.interprete = interprete; } public CommonsMultipartFile getCaratula() { return caratula; } public void setCaratula(CommonsMultipartFile caratula) { this.caratula = caratula; } } |
El siguiente paso es implementar nuestro clase de llamada al procedimiento almancenado
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
package com.autentia.tutoriales.file.upload.domain.procedures; import java.sql.Types; import java.util.HashMap; import java.util.Map; import javax.sql.DataSource; import oracle.jdbc.driver.OracleTypes; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.SqlOutParameter; import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.core.support.SqlLobValue; import org.springframework.jdbc.object.StoredProcedure; import org.springframework.jdbc.support.lob.LobHandler; import org.springframework.jdbc.support.lob.OracleLobHandler; import org.springframework.jdbc.support.nativejdbc.CommonsDbcpNativeJdbcExtractor; import org.springframework.stereotype.Service; import com.autentia.tutoriales.file.upload.domain.beans.Disco; @Service public class CreaNuevoDisco extends StoredProcedure { private static final String PROC_CREAR_NUEVO_DISCO = "PCK_DISCOS.CREAR_DISCO"; @Autowired public CreaNuevoDisco(DataSource ds) { super(ds, PROC_CREAR_NUEVO_DISCO); declareParameter(new SqlParameter("titulo", Types.VARCHAR)); declareParameter(new SqlParameter("interprete", Types.VARCHAR)); declareParameter(new SqlParameter("caratula", OracleTypes.BLOB)); declareParameter(new SqlParameter("canciones", Types.ARRAY, "TUTORIAL.LISTA")); declareParameter(new SqlOutParameter("idDisco", Types.INTEGER)); } public int guardar(Disco disco) { int idDisco = -1; Map<String, Object> result; Map%lt;String, Object> params = new HashMap<String, Object>(); LobHandler lobHandler = new OracleLobHandler(); getJdbcTemplate().setNativeJdbcExtractor(new CommonsDbcpNativeJdbcExtractor()); params.put("titulo", disco.getTitulo()); params.put("interprete", disco.getInterprete()); params.put("canciones", null); params.put("caratula", new SqlLobValue(disco.getCaratula(), lobHandler)); result = execute(params); idDisco = (Integer)result.get("idDisco"); return idDisco; } } |
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…
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
package com.autentia.tutoriales.file.upload.domain.procedures; import java.sql.Blob; import java.sql.SQLException; import java.sql.Types; import java.util.HashMap; import java.util.Map; import javax.sql.DataSource; import oracle.jdbc.driver.OracleTypes; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.SqlOutParameter; import org.springframework.jdbc.core.SqlParameter; import org.springframework.jdbc.object.StoredProcedure; import org.springframework.stereotype.Service; @Service public class RecuperaCaratula extends StoredProcedure { private static final String PROC_CREAR_NUEVO_DISCO = "PCK_DISCOS.OBTIENE_CARATULA_DISCO"; @Autowired public RecuperaCaratula(DataSource ds) { super(ds, PROC_CREAR_NUEVO_DISCO); declareParameter(new SqlParameter("idDisco", Types.INTEGER)); declareParameter(new SqlOutParameter("caratula", OracleTypes.BLOB)); } public byte[] extraeCaratula(int idDisco) { byte[] caratula = null; Map<String, Object> params = new HashMap<String, Object>(); Map<String, Object> result = null; params.put("idDisco",idDisco); result = execute(params); Blob salida = (Blob) result.get("caratula"); if (salida != null) { try { caratula = salida.getBytes(1, (int)salida.length()); } catch (SQLException e) { caratula = null; } } return caratula; } } |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
@RequestMapping(value="caratula", params="idDisco") public void recuperaCaratulaDisco(@RequestParam int idDisco, HttpServletResponse response) throws IOException { byte[] caratula = discosRepository.recuperaCaratulaDisco(idDisco); if (caratula != null && caratula.length > 0) { response.setContentType("image/jpeg"); response.setContentLength(caratula.length); OutputStream out = response.getOutputStream(); out.write(caratula, 0, caratula.length); } else { response.sendError(HttpServletResponse.SC_NOT_FOUND); } } |
Y si invocamos a este nuevo controlador… Ya tenemos nuestra carátula!!
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
|
1 |
--TYPE LISTA IS VARRAY(150) OF VARCHAR2(255); |
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:
|
1 |
create or replace type LISTA as VARRAY(150) of VARCHAR2(255); |
Una vez que tenemos esto, ahora hay que hacer que nuestra clase «CreaNuevoDisco» pase los datos correctamente al Oracle.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
public int guardar(final Disco disco) { int idDisco = -1; Map<String, Object> result; Map<String, Object> params = new HashMap<String, Object>(); LobHandler lobHandler = new OracleLobHandler(); getJdbcTemplate().setNativeJdbcExtractor(new CommonsDbcpNativeJdbcExtractor()); params.put("titulo", disco.getTitulo()); params.put("interprete", disco.getInterprete()); params.put("canciones", disco.getCanciones() == null || disco.getCanciones().size() <= 0 ? null : new AbstractSqlTypeValue() { @Override protected Object createTypeValue(Connection con, int sqlType, String typeName) throws SQLException { if (con instanceof org.apache.tomcat.dbcp.dbcp.PoolableConnection) { con = ((org.apache.tomcat.dbcp.dbcp.PoolableConnection) con).getInnermostDelegate(); } final oracle.sql.ArrayDescriptor arrayDescriptor = new oracle.sql.ArrayDescriptor(typeName, con); String[] canciones = (String[]) disco.getCanciones().toArray(); oracle.sql.ARRAY miArray = new oracle.sql.ARRAY(arrayDescriptor, con, canciones); return miArray; } }); params.put("caratula", disco.getCaratula() != null && disco.getCaratula().length > 0 ? new SqlLobValue(disco.getCaratula(), lobHandler) : null); result = execute(params); idDisco = (Integer)result.get("idDisco"); return idDisco; } |
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
Enviamos el formulario y…
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
El codigo esta un poco liado y a veces incompleto tienes algo más sencillo para novatos en Spring?? Por ejemplo la clase Disco donde esta definida?
Muchas Gracias Daniel me ayudo bastante.
[…] A modo de introducción previa al tutorial os dejo otra forma de hacerlo con Spring JDBC como nos indicaba Daniel en su tutorial. […]
No empiecen este tutorial no esta completo y esta bastante desorganizado, ni como referencia, un dia perdido