Invocar a procedimientos almacenados de Oracle usando Spring JDBC

4
53064

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

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

<!-- 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

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

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

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

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

 <%@ 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

<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

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

@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

<%@ 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 🙂

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

	@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:

 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

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…

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

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

@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!!

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

--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:

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.

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

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

4 COMENTARIOS

  1. 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?

DEJA UNA RESPUESTA

Por favor ingrese su comentario!

He leído y acepto la política de privacidad

Por favor ingrese su nombre aquí

Información básica acerca de la protección de datos

  • Responsable:
  • Finalidad:
  • Legitimación:
  • Destinatarios:
  • Derechos:
  • Más información: Puedes ampliar información acerca de la protección de datos en el siguiente enlace:política de privacidad