En muchas bases de datos puedes crear rutinas SQL reutilizables. En este capítulo descubrirás su utilidad y cómo ejecutarlas con Spring Data JPA. La clave de todo, la anotación @Procedure.
Contenido
- En defensa de las rutinas SQL
- El primer procedimiento
- Declaraciones alternativas
- Retorno de múltiples resultados
- Funciones
- Resumen
- Código de ejemplo
En defensa de las rutinas SQL
El cometido de las rutinas SQL es el mismo que el de las funciones de cualquier lenguaje de programación: encapsular el código que realiza una operación. De hecho, las escribimos con un lenguaje procedural que cuenta con variables, bucles y estructuras de selección, y que puede ejecutar cualquier sentencia SQL. Si bien este lenguaje es específico de cada base de datos, su sintaxis general es parecida.
¿Por qué estas rutinas deberían interesarte? A fin de cuentas, puedes obtener la información que quieras de una base de datos y manipularla a tu antojo con Java, Kotlin, Python…
Las rutinas tienen a su favor la baza del rendimiento. Imagina que necesitas procesar una gran cantidad de datos, tarea que precisa de varias consultas. Con una aplicación, tienes que traerlos de la base de datos, manipularlos y luego enviar los cambios. Si todo queda dentro de la base de datos, te ahorras ese trasiego de datos por la red y su conversión en objetos. Cuando hablamos de volúmenes grandes de información, la mejora del rendimiento resulta notable.
Otra ventaja es la reutilización. Las rutinas están a disposición de cualquier cliente software que use la base de datos, sin importar el lenguaje de programación. Nada de servicios REST, buses de datos o sistemas parecidos que den acceso a esas funcionalidades. Quien pueda interactuar con la base de datos ya las tiene disponibles.
Por los motivos anteriores, y según mi experiencia profesional, el empleo de rutinas SQL resulta habitual en grandes empresas y administraciones públicas. Es decir, en entidades que manejan un volumen gigantesco de datos. Además, estas organizaciones cuentan con numerosos proveedores de software que necesitan compartir procesos entre múltiples y variados sistemas.
Sin embargo, el uso de rutinas SQL suscita controversias. Por ejemplo, se suele cuestionar la distribución de la lógica de negocio entre las aplicaciones y la base de datos.
Pero el principal argumento en contra es la falta de portabilidad. Como ya señalé, las rutinas se escriben de forma específica para una base de datos. Por tanto, llevarlas a otra implica su reescritura. Si las usas con profusión, quizás te ates demasiado a un proveedor, problema denominado vendor lock-in. En pocas palabras, significa que cambiar de proveedor resulta más caro que permanecer con el actual.
Admito que son preocupaciones razonables. Con todo, repito lo que afirmé en el capítulo previo con respecto a SQL: cambiar de base de datos o ser compatible a la vez con varias es un requerimiento que aparece en pocos proyectos. No renuncies a SQL por un problema que no tienes y que casi seguro nunca tendrás.
Exprime tu base de datos; es mucho más que un almacén. Programa rutinas cuando sean la mejor solución.
Dedicaremos el capítulo a ejecutar rutinas con Spring Data JPA. Existen dos tipos: procedimientos almacenados y funciones almacenadas. Comenzaremos con los procedimientos, pues su uso presenta mayor dificultad.
El primer procedimiento
Nuestro primer procedimiento de prueba para HyperSQL (HSQLDB), la base de datos del proyecto del curso:
CREATE PROCEDURE count_countries_by_confederation_id (IN param_conf_id int, OUT countries_count int)
READS SQL DATA
BEGIN ATOMIC
SELECT COUNT(*) INTO countries_count FROM countries WHERE confederation_id = param_conf_id;
END^;
Aquí tienes la documentación.
Pondré los procedimientos en el fichero /src/test/resources/procedures.sql. Más adelante explicaré cómo ejecutar su contenido desde los tests. Adelanto que tiene «truco».
El procedimiento count_countries_by_confederation_id cuenta los países asociados a una confederación. ¿Cuál? Aquella cuyo identificador recibe como un parámetro de entrada (IN) de tipo entero. El conteo queda guardado en un parámetro de salida (OUT), también de tipo entero. Un procedimiento tiene los parámetros de entrada, salida y entrada\salida (INOUT) que necesites.
¿Cómo invocas al procedimiento? Primero, decláralo de forma parecida a una consulta nombrada. Define su signatura en una clase de tipo entidad con la anotación de JPA @NamedStoredProcedure:
@NamedStoredProcedureQuery(name = "Country.countCountriesByConfederationId",
procedureName = "count_countries_by_confederation_id",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "param_conf_id", type = Long.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "countries_count", type = Integer.class) })
@Entity
public class Country {
Diseccionemos el código:
- La propiedad
namecontiene el nombre único del procedimiento en la unidad de persistencia de JPA. procedureNameestablece el nombre del procedimiento en la base de datos. No confundasnameconprocedureName, aunque podrían coincidir si quieres.- Especificas cada parámetro con
@StoredProcedureParameter. Esta anotación recoge el tipo según el enumeradoParameterMode, el nombre, y la clase de Java equivalente al tipo del parámetro en SQL.
Como alternativa, llévate la configuración al fichero /src/META-INF/orm.xml:
<named-stored-procedure-query name="Country.countCountriesByConfederationId"
procedure-name="count_countries_by_confederation_id">
<parameter class="java.lang.Long" mode="IN" name="param_conf_id"/>
<parameter class="java.lang.Long" mode="OUT" name="countries_count"/>
</named-stored-procedure-query>
Ahora que JPA conoce el aspecto del procedimiento, crea un método que lo ejecute:
package com.danielme.springdatajpa.repository.procedure;
import com.danielme.springdatajpa.model.entity.Country;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.Repository;
import org.springframework.transaction.annotation.Transactional;
@Transactional(readOnly = true)
public interface CountryStoredProceduresRepository extends Repository<Country, Long> {
@Procedure
Integer countCountriesByConfederationId(Long param_conf_id);
}
¿Cómo vincula Spring el método con el procedimiento? Con la convención que ya vimos en el capítulo 6. El nombre del procedimiento en JPA (@NamedStoredProcedureQuery#name) se compone del nombre de la entidad del repositorio, un punto y el nombre que quieras darle al método. Como alternativa a esta convención, pon el nombre del procedimiento nombrado en la propiedad name de @Procedure:
@Procedure(name = "Country.countCountriesByConfederationId")
Integer elNombreQueQuieras(Long param_conf_id);
Otros detalles relevantes del código:
- Cada parámetro del método equivale a un parámetro de entrada del procedimiento. Ambos deben tener el mismo nombre. Evitas esta restricción con la anotación
@Param:
@Procedure
Integer countCountriesByConfederationId(@Param("param_conf_id") Long confederacionId);
Otra alternativa: respeta el orden de los parámetros del procedimiento. Decláralos con ese orden tanto en @NamedStoredProcedureQuery como en el método del repositorio, y no indiques el nombre en @StoredProcedureParameter. El ejemplo queda así:
CREATE PROCEDURE count_countries_by_confederation_id (IN param_conf_id int, OUT countries_count int)
@NamedStoredProcedureQuery(name = "Country.countCountriesByConfederationId",
procedureName = "count_countries_by_confederation_id",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, type = Long.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, type = Integer.class) })
@Procedure
List<Country> findCountriesByConfederationIdAsNamed(Long cualquier_nombre_que_quieras);
- Si el método devuelve algo, asegura que se ejecuta como parte de un bloque transaccional. De lo contrario, Spring lanzará una excepción:
org.springframework.dao.InvalidDataAccessApiUsageException: You're trying to execute a @Procedure method without a surrounding transaction that keeps the connection open so that the ResultSet can actually be consumed; Make sure the consumer code uses @Transactional or any other way of declaring a (read-only) transaction
Si tienes alguna duda con respecto a las transacciones, consulta el capítulo 4.
- Y hablando de retorno, el método devuelve el parámetro de salida del procedimiento (
countries_count).
Probemos el repositorio con este test:
package com.danielme.springdatajpa.repository.procedure;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.jdbc.SqlConfig;
import static com.danielme.springdatajpa.DatasetConstants.UEFA_ID;
import static org.assertj.core.api.Assertions.assertThat;
@SpringBootTest
@Sql(value = "/procedures.sql", config = @SqlConfig(separator = "^;"))
class CountryStoredProceduresRepositoryTest {
@Autowired
private CountryStoredProceduresRepository countryRepository;
@Test
void testCountCountriesByConfIdNamedProc() {
Integer uefaCountries = countryRepository.countCountriesByConfederationId(UEFA_ID);
assertThat(uefaCountries).isEqualTo(3);
}
}
Lo único reseñable está en la línea 13. @Sql ejecuta el script contenido en el fichero /src/test/resources/procedures.sql a fin de crear el procedimiento en la base de datos. Aunque ya hemos usado esta anotación en varias pruebas del curso, advierte que ahora configuré el separador de sentencias SQL que debe aplicar Spring. El separador predeterminado es «;», y lo he cambiado al valor «^;».
¿A qué viene esta configuración, en apariencia caprichosa? El punto y coma es el separador más común de los lenguajes de programación…
El motivo reside en el cuerpo del procedimiento: contiene un punto y coma que no señala la separación entre sentencias SQL. Mira la línea 6:
CREATE PROCEDURE count_countries_by_confederation_id (IN param_conf_id int, OUT countries_count int)
READS SQL DATA
BEGIN ATOMIC
SELECT COUNT(*) INTO countries_count
FROM countries
WHERE confederation_id = param_conf_id;
END^;
Por consiguiente, Spring debe distinguir esos puntos y comas que aparecen dentro de los procedimientos de aquellos que marcan el final de una sentencia SQL. De ahí que para el segundo caso haya escogido un separador distinto del punto y coma, por ejemplo, «^;», e informado a Spring de este hecho con @Sql.
Sin la configuración del separador, HyperSQL recibirá un código SQL erróneo que causa una excepción:
org.hsqldb.HsqlException: unexpected end of statement : required: ;
Declaraciones alternativas
Con @Procedure
Hemos declarado el procedimiento según manda la especificación JPA: como un procedimiento nombrado. No obstante, podemos configurarlo en un método del repositorio. Presta atención:
@Procedure
Integer count_countries_by_confederation_id(Long param_conf_id);
Tan fácil como dar al método el nombre del procedimiento. ¿Contraviene ese nombre las convenciones de tu código? Sírvete de la propiedad procedureName:
@Procedure(procedureName = "count_countries_by_confederation_id")
Integer countCountries(Long param_conf_id);
Ahora bien, si un procedimiento tiene varios parámetros de salida, como pronto veremos, debes configurar un procedimiento nombrado.
Con SQL
En SQL llamas a los procedimientos con una sentencia CALL:
@Query(value = "call count_countries_by_confederation_id(:confId)",
nativeQuery = true)
Integer countCountriesCallWithSql(Long confId);
Pero este ejemplo en concreto falla porque el procedimiento tiene parámetros de salida. Con esta manera de ejecutar procedimientos no puedes configurar esos parámetros. De todas formas, resulta más cómodo y flexible aprovechar los mecanismos que Spring Data JPA provee.
Retorno de múltiples resultados
Varios párametros de salida
El método countCountriesByConfederationId devuelve el único parámetro de salida del procedimiento. ¿Y si tuviera varios? Es el caso de este ejemplo:
CREATE PROCEDURE count_countries_and_population_by_confederation_id (IN param_conf_id int,
OUT countries_count int, OUT countries_population int)
READS SQL DATA
BEGIN ATOMIC
SELECT COUNT(*), SUM(population) INTO countries_count, countries_population FROM countries WHERE confederation_id = param_conf_id;
END^;
Me disculpo por mi escasa inventiva. Es igual que el procedimiento anterior, pero con un segundo parámetro de salida que contendrá la suma de la población de los países pertenecientes a la confederación.
Cuando tengas varios parámetro de salida, configura un procedimiento nombrado, como ya te he mostrado:
@NamedStoredProcedureQuery(name = "Country.countCountriesAndPopulationByConfId",
procedureName = "count_countries_and_population_by_confederation_id",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "param_conf_id", type = Long.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "countries_count", type = Integer.class),
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "population", type = Integer.class) })
Spring Data JPA almacena los parámetros de salida en un diccionario de tipo Map<String, Object>, así que este es el método que ejecuta el procedimiento anterior:
@Procedure(procedureName = "count_countries_and_population_by_confederation_id")
Map<String, Object> countCountriesAndPopulationByConfId(Long param_conf_id);
Dado que la clave del diccionario es el nombre del parámetro, tienes fácil conocer los resultados:
@Test
void testCountCountriesAndPopulation() {
Map<String, Object> uefaStats = countryRepository.countCountriesAndPopulationByConfId(UEFA_ID);
assertThat(uefaStats)
.containsEntry("countries_count", 3)
.containsKey("population");
}
Los dos parámetros de salida del ejemplo son de tipo entero. Cuando todos los parámetros sean del mismo tipo, indícalo en la declaración del diccionario:
@Procedure
Map<String, Integer> countCountriesAndPopulationByConfId(Long param_conf_id);
Si solo quieres un parámetro, olvídate del diccionario. Pídelo con la propiedad outputParameterName:
@Procedure(outputParameterName = "population")
Integer countCountriesAndPopulationByConfId(Long param_conf_id);
Cursores
Por lo común, los procedimientos devuelven un conjunto de registros accesibles con un CURSOR. Y esto resulta problemático porque estamos condicionados por la base de datos: lo que funciona para una, puede que no funcione para otra.
Ejemplo con MySQL
Si bien los procedimientos de HyperSQL pueden devolver un cursor, existe algún tipo de incompatibilidad entre Hibernate y el controlador JDBC de esta base de datos. Esto causa que Hibernate sea incapaz de determinar que el procedimiento devuelve un cursor. Por ello, recurriré como ejemplo al siguiente procedimiento para MySQL que encuentra los países asociados a una confederación:
CREATE PROCEDURE countries_by_confederation_id(IN param_conf_id INT)
BEGIN
SELECT * FROM countries WHERE confederation_id = param_conf_id ORDER BY name;
END^;
Los ejemplos basados en MySQL los encontrarás en el proyecto de este tutorial, que resume las principales características de Spring Data JPA con el mismo ejemplo de los países.
Fíjate en countries_by_confederation_id. ¿Dónde se declara el cursor o se devuelve el resultado? En este sentido, MySQL es peculiar. Retorna un cursor por cada SELECT que aparezca en el procedimiento cuyo resultado no se recoja en una variable. En otras bases de datos, como Oracle, el cursor es un parámetro de salida.
La declaración del procedimiento nombrado:
@NamedStoredProcedureQuery(name = "Country.findCountriesByConfederationIdAsNamed",
procedureName = "countries_by_confederation_id",
resultClasses = Country.class,
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN, name = "param_conf_id", type = Long.class)
})
resultClasses establece que los resultados son entidades Country.
El método del repositorio:
@Procedure
List<Country> findCountriesByConfederationIdAsNamed(@Param("param_conf_id") Long confId);
Puesto que el procedimiento carece de parámetros de salida, puedes declararlo en el método y así ahorrarte la creación del procedimiento nombrado:
@Procedure(procedureName = "countries_by_confederation_id")
List<Country> findCountriesByConfederationFromAnnotation(@Param("param_conf_id") Long confId);
Conversión de resultados
Los método anteriores retornan entidades porque la consulta del procedimiento proyecta exactamente las columnas configuradas en la clase Country. ¿Qué otras opciones tienes? Las ya referidas en el capítulo precedente para las consultas SQL, salvo Tuple. Las resumo:
- Recogida de los resultados en crudo con un array de
Object:
@Procedure(procedureName = "countries_by_confederation_id")
List<Object[]> findCountriesByConfederationFromAsObject(@Param("param_conf_id") Long confId);
El array de Object es la opción predeterminada. Por tanto, si declaras el procedimiento como uno nombrado, no des valor a resultClasses.
- Proyección en interfaz.
La interfaz de ejemplo:
public interface IdNameProjection {
Long getId();
String getName();
}
La proyección de la consulta y los getters de la interfaz han de ser coherentes, por lo que necesitamos otro procedimiento que proyecte las columnas id (getId()) y name (getName()).
CREATE PROCEDURE countries_id_name_by_confederation_id(IN param_conf_id INT)
BEGIN
SELECT id, name FROM countries WHERE confederation_id = param_conf_id ORDER BY name;
END^;
La llamada desde un repositorio:
@Procedure(procedureName = "countries_id_name_by_confederation_id")
List<IdNameProjection> findCountriesByConfederationAsInterface(@Param("param_conf_id") Long confId);
- Proyección en constructor. Requiere asociar la configuración del constructor (anotación
@SqlResultSetMapping) a un procedimiento nombrado mediante la propiedadresultSetMappings:
@SqlResultSetMapping(
name = "idNameConstructor",
classes = @ConstructorResult(targetClass = IdNameDTO.class,
columns = {
@ColumnResult(name = "id", type = Long.class),
@ColumnResult(name = "name", type = String.class)}))
@NamedStoredProcedureQuery(name = "Country.findCountriesIdNameByConfederationAsDTO",
procedureName = "countries_by_confederation_id",
resultSetMappings = "idNameConstructor",
parameters = {
@StoredProcedureParameter(mode = ParameterMode.IN,
name = "param_conf_id", type = Long.class)
})
El método:
@Procedure
List<IdNameDTO> findCountriesIdNameByConfederationAsDTO(@Param("param_conf_id") Long confId);
Funciones
Además de procedimientos, en las bases de datos puedes crear funciones almacenadas, el otro tipo de rutinas SQL. La principal diferencia entre ambos es que las funciones están diseñadas para ser invocadas dentro de consultas en vez de con CALL. Piensa en las típicas funciones de manipulación de cadenas o fechas ya incluidas en las bases de datos.
Por su naturaleza, las funciones pueden tener parámetros de entrada, pero no de salida. Asimismo, devuelven un resultado de manera explícita con una instrucción RETURN. De todas formas, encontrarás peculiaridades en cada base de datos. Por ejemplo, en HyperSQL una función no modifica datos y admite llamadas con CALL, dos características poco habituales.
JPA no distingue entre funciones y procedimientos. En consecuencia, todo lo que hemos visto es aplicable a las funciones.
Esta función equivale al procedimiento que cuenta los países:
CREATE FUNCTION COUNT_COUNTRIES(param_conf_id int) RETURNS INT
READS SQL DATA
RETURN (SELECT COUNT(*) FROM countries WHERE confederation_id = param_conf_id)
^;
La ejecutas como cualquiera de las incluídas en la base de datos:
@Query(value = "SELECT COUNT_COUNTRIES(:confId) FROM (VALUES(0))" nativeQuery = true)
Integer countCountriesWithFunction(Long confId);
¿Y esa tabla tan extraña? HyperSQL no procesa consultas sin FROM, así que la «engañamos» con FROM (VALUES(0)).
Cuando dije que una función devuelve un único resultado, me refería a que tiene un único RETURN. En HyperSQL el resultado puede ser un conjunto de registros:
CREATE FUNCTION COUNTRIES_BY_CONFEDERATION(param_conf_id int)
RETURNS TABLE(id BIGINT, name VARCHAR(50))
READS SQL DATA
RETURN TABLE (SELECT id, name FROM countries WHERE confederation_id = param_conf_id)
^;
Puesto que la función retorna una estructura de datos de tipo TABLE, la ejecución con SELECT falla. Ningún problema; usa CALL:
@Query(value = "CALL COUNTRIES_BY_CONFEDERATION(:confId)", nativeQuery = true)
List<IdName> findCountriesWithFunction(Long confId);
El resultado del método es cortesía del mecanismo de proyección en interfaz de Spring Data. También cuentas con los otros tipos de resultados disponibles para SQL.
Resumen
Los puntos básicos del capítulo:
- Procedimientos y funciones te permiten programar con el máximo rendimiento procesos que exploten intensamente la base de datos.
- En JPA los procedimientos se declaran como procedimientos nombrados en clases de entidad (anotaciones
@NamedStoredProcedurey@StoredProcedureParameter) o en el ficheroorm.xml. - Ejecutas esos procedimientos en métodos de repositorios anotados con
@Procedure. En algunos casos sencillos, te ahorras, si quieres, definir el procedimiento nombrado. - Puedes retornar cursores. Esta capacidad depende de cada base de datos.
- Las funciones están diseñadas para ser llamadas desde consultas y devolver siempre un valor. JPA no las diferencia de los procedimientos.
Código de ejemplo
El proyecto, explicado en el capítulo dos, se encuentra en GitHub. Si necesitas ayuda para obtener y configurar proyectos alojados en GitHub, consulta este tutorial.
Otros tutoriales relacionados con Spring
Introducción a Spring Boot: Aplicación Web con servicios REST y Spring Data JPA
Spring Boot: Gestión de errores en aplicaciones web y REST
Testing en Spring Boot con JUnit 45. Mockito, MockMvc, REST Assured, bases de datos embebidas
danielme.com 