Los procedimientos almacenados (stored procedures) suelen ser ignorados por los programadores no especialistas en bases de datos. Pero son una herramienta con un ámbito de aplicación más que interesante y que podemos usar fácilmente con JPA sin tener que recurrir a la API JDBC. Por ello, los vemos en este pequeño capítulo que complementa el estudio del empleo de SQL con JPA e Hibernate.
Procedimientos almacenados
Un procedimiento es una «función» compilada en la propia base de datos que realiza una tarea basada en la ejecución de un conjunto de sentencias SQL que pueden colaborar entre sí gracias al uso de variables. También pueden aparecer bucles, expresiones condicionales, etcétera, como en cualquier lenguaje de programación. Cuando sea necesario, los procedimientos reciben parámetros de entrada y devuelven datos. Las posibilidades son enormes.
Obsérvese que he entrecomillado la palabra función. Con el ánimo de ser rigurosos, es importante aclarar que no es lo mismo un procedimiento almacenado que las funciones que hemos visto hasta ahora. Del mismo modo que escribimos procedimientos en la base de datos, podemos crear funciones de forma más o menos parecida. Pero estas siempre devuelven un único valor y están pensadas para formar parte de expresiones en consultas SQL. Por el contrario, los procedimientos no tienen retorno -sí parámetros de salida- y no forman parte de las consultas. En SQL estándar se invocan con la orden CALL.
Ciertamente, es posible desarrollar los procesos realizados por los procedimientos con código Java. No obstante, cuando hay que realizar el tratamiento de un gran volumen de registros suele ser más eficiente que todo quede en la base de datos y evitar la ida y venida de los datos. Otro beneficio del empleo de procedimientos es que son utilizables por aplicaciones distintas y sin importar su lenguaje; la lógica de negocio queda centralizada.
Antes que nada, vamos a crear un procedimiento en MySQL. Téngase en cuenta que la sintaxis y funcionalidades son muy dependiente de cada base de datos.
DELIMITER //
CREATE PROCEDURE sp_count_expenses(IN concept VARCHAR(50), OUT total INT)
BEGIN
SELECT COUNT(e.id) INTO total FROM expenses e WHERE e.concept like CONCAT('%', concept, '%');
END //
DELIMITER ;
El código anterior se encuentra en el fichero /src/test/resources/docker/mysql/init.sql del proyecto de ejemplo jpa-query, un script definido en el Dockerfile que se ejecuta cada vez que se crea el contenedor de MySQL de las pruebas. Este tipo de configuraciones la expliqué aquí. Si no lanzamos las pruebas con Docker (el perfil de Maven arq-docker ), tendremos que ejecutarlo manualmente en la base de datos configurada en WildFly.
sp_count_expenses recibe como parámetro de entrada (IN) la cadena concept y tiene un parámetro de salida (OUT) llamado total. Su cuerpo ejecuta una consulta que cuenta los gastos cuyo concepto contiene la cadena recibida como argumento. El resultado se guarda en la variable total. Se trata, por tanto, de un ejemplo puramente didáctico; es evidente que para esto no necesitamos un procedimiento.
Ejecución con JPA
A continuación describo la praxis habitual a la hora de definir y ejecutar procedimientos almacenados.
- Los procedimientos se modelan en JPA con el subtipo de Query llamado StoredProcedureQuery. Sus objetos se crean con uno de los métodos createStoredProcedure del gestor de entidades a partir del nombre del procedimiento.
StoredProcedureQuery sp = em.createStoredProcedureQuery("sp_count_expenses")
- Una vez creado, hay que añadirle todos los parámetros con registerStoredProcedureParameter, indicando el nombre o un número con la posición (empezando en uno), el tipo Java asociado y el modo de uso. Este último se define con uno de cuatro tipos del enumerado ParameterMode: modo de entrada (IN), salida (OUT) y entrada \ salida (IN \ OUT). Hay un cuarto llamado REF_CURSOR usado cuando el parámetro es un cursor abierto que será devuelto por el procedimiento. Lo veremos más adelante porque tiene «truco».
sp.registerStoredProcedureParameter("concept", String.class, ParameterMode.IN);
sp.registerStoredProcedureParameter("total", Long.class, ParameterMode.OUT);
o bien
sp.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
sp.registerStoredProcedureParameter(2, Long.class, ParameterMode.OUT);
- Si tenemos parámetros de entrada y entrada \ salida, les asignamos sus valores con los métodos setParameter. La manera de identificar cada parámetro (nombre o posición) debe coincidir con la usada en su declaración en el paso anterior.
sp.setParameter("concept", concept);
- Ejecutamos el objeto StoredProcedureQuery.
sp.execute();
- Los resultados, si existen, están contenidos en los parámetros de salida y entrada \ salida y se obtienen llamando a getOutputParameter. En breve veremos que también pueden ser cursores recuperables con getResultList y getSingleResult.
(Long) sp.getOutputParameterValue("total")
Este es el código completo.
@Override
public long countExpensesWithProcedure(String concept) {
StoredProcedureQuery sp = em.createStoredProcedureQuery("sp_count_expenses")
.registerStoredProcedureParameter("concept", String.class, ParameterMode.IN)
.registerStoredProcedureParameter("total", Long.class, ParameterMode.OUT)
.setParameter("concept", concept);
sp.execute();
return (Long) sp.getOutputParameterValue("total");
}
Un detalle importante: la llamada a execute está implícita cuando se solicita por primera vez la lectura de un resultado, por lo que puede omitirse. He decidido realizarla en los ejemplos en pos de una mayor claridad, pero podemos escribir todo el cuerpo de countExpensesWithProcedure tal que así.
return (long) em.createStoredProcedureQuery("sp_count_expenses")
.registerStoredProcedureParameter("concept", String.class, ParameterMode.IN)
.registerStoredProcedureParameter("total", Long.class, ParameterMode.OUT)
.setParameter("concept", concept)
.getOutputParameterValue("total");
Esta prueba para el método anterior no parece que tenga mucho interés.
@Test
void testCountExpensesWithProcedure() {
long count = expenseSqlDAO.countExpensesWithProcedure("menu");
assertThat(count).isEqualTo(1L);
}
Pero si somos malvados y probamos a usar null como valor del parámetro de entrada concept…
java.lang.IllegalArgumentException: The parameter named [concept] was null. You need to call ParameterRegistration#enablePassingNulls(true) in order to pass null parameters.
El mensaje es claro: no se permite el uso de parámetros nulos. Por fortuna, Hibernate los admite si configuramos la siguiente propiedad en el persistence.xml.
<property name="hibernate.proc.param_null_passing" value="true"/>
Esta configuración es global, así que se aplica a todos los procedimientos que se ejecuten. Puede hacerse programáticamente para parámetros específicos accediendo a las clases de Hibernate.
StoredProcedureQuery sp = em.createStoredProcedureQuery("sp_count_expenses")
.registerStoredProcedureParameter("concept", String.class, ParameterMode.IN)
.registerStoredProcedureParameter("total", Long.class, ParameterMode.OUT);
((org.hibernate.procedure.ParameterRegistration) sp.getParameter("concept")).enablePassingNulls(true);
sp.setParameter("concept", concept);
En el proyecto he dejado las dos opciones comentadas.
Obtención de resultados
Más allá de los parámetros de entrada, en el único ejemplo que hemos visto hasta ahora el resultado es un escalar de tipo Long que leemos de un parámetro de salida (el código no cambia si fuera de entrada \ salida). Pero lo común será que los procedimientos devuelvan una o varias tuplas de datos que, internamente, JDBC recoge en un objeto de tipo ResultSet. Es el caso siguiente.
DELIMITER //
CREATE PROCEDURE sp_cheap_expenses(IN maxAmount FLOAT)
BEGIN
SELECT * FROM expenses WHERE amount < maxAmount ORDER BY date DESC;
END //
DELIMITER ;
sp_cheap_expenses obtiene los gastos inferiores a la cuantía maxAmount. Su resultado será una lista de entidades Expense (obsérvese que no se almacena en un parámetro de salida). Por ello hay que usar la versión de createStoredProcedureQuery que recibe como segundo parámetro la entidad para que Hibernate transforme los resultados de forma automática.
@Override
public List<Expense> findCheapExpensesWithProcedure(BigDecimal maxAmount) {
StoredProcedureQuery sp = em.createStoredProcedureQuery("sp_cheap_expenses", Expense.class)
.registerStoredProcedureParameter("maxAmount", BigDecimal.class, ParameterMode.IN)
.setParameter("maxAmount", maxAmount);
sp.execute();
return (List<Expense>) sp.getResultList();
}
Ahora solicitamos (línea 7) los registros retornados por el procedimiento con los métodos getResultList, getSingleResult o getResultStream de la interfaz Query que ya hemos usado tantas veces.
Si el resultado no fuera una entidad, no se declara su clase en createStoredProcedureQuery y recibiremos el array genérico Object[], de nuevo algo que hemos visto en varias ocasiones a lo largo del curso. Sirva de ejemplo esta variación de findCheapExpensesWithProcedure.
StoredProcedureQuery sp = em.createStoredProcedureQuery("sp_cheap_expenses")
.registerStoredProcedureParameter("maxAmount", BigDecimal.class, ParameterMode.IN)
.setParameter("maxAmount", maxAmount);
List<Object[]> resultList = sp.getResultList();
Otra posibilidad, examinada en el capítulo anterior, es recurrir a un ResultSetMapping que indicaremos con esta sobrecarga de createStoredProcedureQuery. Con esta estrategia, recordemos, es posible procesar automáticamente una proyección consistente en más de una entidad o bien en un conjunto de columnas \ valores que podemos volcar en una clase si contamos con el constructor adecuado.
Si los procedimientos devuelven más de un ResultSet, invocaremos a getResultList (o equivalente) sucesivamente para obtenerlos en el orden establecido dentro del procedimiento. Este es el motivo por el que a createStoredProcedureQuery podemos pasar una lista de entidades o de resultSetMappings: indicar la conversión, en orden, aplicable a cada ResultSet obtenido
StoredProcedureQuery createStoredProcedureQuery(String procedureName, Class... resultClasses) StoredProcedureQuery createStoredProcedureQuery(String procedureName, String... resultSetMappings)
De todas formas, el ejemplo que estamos usando es específico de MySQL porque en esta base de datos los procedimientos devuelven directamente los resultados de las consultas. En otras, como PostgreSQL y Oracle, están contenidos en parámetros de tipo REF_CURSOR. Por consiguiente, necesitamos declarar esos parámetros.
query.registerStoredProcedureParameter("expenses", void.class, ParameterMode.REF_CURSOR);
Luego, el parámetro expenses se obtiene como cualquier otro.
List<Expense> expenses = (List<Expense>) q.getOutputParameterValue("expenses");
En MySQL, los parámetros REF_CURSOR no se admiten
org.hibernate.QueryException: Dialect [org.hibernate.dialect.MySQL8Dialect] not known to support REF_CURSOR parameters
Definición estática
Sabemos que las consultas JPQL y SQL pueden declararse de forma estática (consultas nombradas). Los procedimientos no son menos, y esto puede parecer extraño porque están codificados en la base de datos y solicitamos su ejecución con sus nombres. Sin embargo, es imprescindible configurar sus parámetros de entrada y salida, y es justo esta configuración la que declararemos con @NamedStoredProcedureQuery en una entidad.
@NamedStoredProcedureQuery(
name = "ExpenseSpCountExpenses",
procedureName = "sp_count_expenses",
parameters = {
@StoredProcedureParameter(name = "concept", type = String.class,
mode = ParameterMode.IN),
@StoredProcedureParameter(name = "total", type = Long.class,
mode = ParameterMode.OUT)
})
@Entity
@Table(name = "expenses")
@Getter
@Setter
public class Expense {
Las propiedades name (el nombre identificativo del procedimiento en la unidad de persistencia) y procedureName (el nombre en la base de datos) son obligatorias.
La misma configuración en el fichero orm.xml.
<named-stored-procedure-query name="ExpenseSpCountExpenses" procedure-name="sp_count_expenses">
<parameter name="concept" class="java.lang.String" mode="IN"/>
<parameter name="total" class="java.lang.Long" mode="OUT"/>
</named-stored-procedure-query>
En ambos casos, este es un método que usa el procedimiento «nombrado» a partir de su name.
@Override
public long countExpensesWithNamedProcedure(String concept) {
StoredProcedureQuery sp = em.createNamedStoredProcedureQuery("ExpenseSpCountExpenses")
.setParameter("concept", concept);
sp.execute();
return (Long) sp.getOutputParameterValue("total");
}
También se pueden definir los argumentos admitidos por los métodos createStoredProcedureQuery: los tipos de las entidades devueltas (resultClasses), o bien la lista de conversores (resultSetMappings). Es el caso, recordemos, de sp_cheap_expenses que devuelve una lista de Expense. Su declaración como procedimiento nombrado es la siguiente.
@NamedStoredProcedureQuery(
name = "ExpenseSpCheapExpenses",
procedureName = "sp_cheap_expenses",
resultClasses = Expense.class,
parameters = {
@StoredProcedureParameter(name = "maxAmount", type = BigDecimal.class,
mode = ParameterMode.IN)
})
Código de ejemplo
El código de ejemplo se encuentra en GitHub (todos los proyectos son independientes, pero están en un único repositorio). Para más información sobre cómo utilizar GitHub, consultar este artículo.
danielme.com 