Completemos la revisión de JPQL examinando sus funciones y cómo podemos sacar partido a las nativas que ofrecen las bases de datos. No está de más subrayar que, por cuestiones de rendimiento, es preferible delegar en la base de datos todo el procesamiento y cálculo que pueda realizar, en lugar de implementarlo nosotros en el código.
Funciones de agregación
Las consultas de agregación aplican sobre todo el conjunto de datos, ignorando los nulos, un cálculo según una función de agregación. Estas funciones son las archiconocidas de SQL, y además ya han aparecido en los ejemplos de capítulos previos.
COUNT. Devuelve un Long con el número de elementos total del grupo. A diferencia de las demás funciones de agregación, si no hay resultados devuelve 0 en vez de null.
SELECT COUNT(e)
FROM Expense e
Hemos recuperado el número total de gastos. Además de una variable de entidad, el argumento de la función puede ser una expresión de navegación, acompañada opcionalmente del operador DISTINCT para no incluir en el conteo elementos duplicados. En la siguiente consulta, añadimos a la anterior la cantidad de categorías distintas con algún gasto.
SELECT COUNT(e), COUNT(DISTINCT e.category.id)
FROM Expense e
AVG. Devuelve un Double con la media aritmética (average) de un atributo de tipo numérico.
Este es el gasto medio.
SELECT AVG(e.amount)
FROM Expense e
SUM, MIN, MAX. Calculan la suma, el valor máximo y el mínimo de un atributo de tipo numérico. El tipo devuelto coincide con el de ese atributo.
SELECT SUM(e.amount), MIN(e.amount), MAX(e.amount)
FROM Expense e
Aunque usemos este tipo de funciones, declararemos las uniones y condiciones que necesitemos como hemos hecho hasta ahora. Este es el conteo de todos los gastos y el importe total pertenecientes a la categoría 1.
SELECT COUNT(e), SUM(e.amount)
FROM Expense e
WHERE e.category.id=1
La cláusula GROUP BY opera del mismo modo que en SQL: establece cómo se agregan o agrupan los valores de los resultados según un conjunto de atributos. Cada grupo será uno de los resultados retornado por la consulta. Las funciones se calculan para cada grupo de forma independiente.
Vamos a obtener un listado con un resumen estadístico acerca de las categorías y sus gastos. En concreto, queremos conocer «para cada categoría» su nombre, la cantidad de gastos y el importe total de los mismos. Ese «para cada» es una pista que nos avisa que debemos declarar una cláusula GROUP BY que agrupe por categoría todos los gastos. Otra consideración a tener en cuenta es que a la hora de reunir categorías y gastos queremos que en los resultados aparezcan todas las categorías, tengan o no gastos. Nos encontramos ante un caso claro del uso de la reunión de tipo LEFT JOIN tratada en el capítulo anterior.
SELECT c.id AS id, c.name AS name, SUM (e.amount) AS total, COUNT(e) AS expenses
FROM Category c LEFT JOIN c.expenses e
GROUP BY c.id
Esta consulta se encuentra en el proyecto jpa-query declarada como una consulta nombrada y fue la base del ejemplo de tratamiento de la proyección de la cláusula SELECT cuando incluye varios valores presentada en el capítulo 33. Su funcionamiento se ve más claro si echamos un vistazo al juego de datos de las pruebas automáticas del proyecto.
Este es el resultado.
Cuando usamos funciones agregadas, si no definimos correctamente la cláusula GROUP BY la excepción que Hibernate eleva incluye el mensaje de error de la base de datos el cual suele ser muy descriptivo.
Caused by: java.sql.SQLSyntaxErrorException: In aggregated query without GROUP BY
Tal y como sucede en SQL, GROUP BY se puede acompañar de la cláusula HAVING para imponer condiciones de selección DESPUÉS de que los datos se hayan agrupado. Es decir, se aplican dentro de cada grupo, mientras que las condiciones que aparecen en el WHERE actúan sobre el conjunto completo de datos ANTES de que sean agrupados.
HAVING se suele emplear para filtrar los grupos según funciones agregadas, pues estas se pueden aplicar directamente, de ahí que sea una poderosa funcionalidad de JPQL \ SQL. También admite el empleo de subconsultas. Veámoslo con una consulta parecida a la anterior. En esta ocasión los resultados solo incluirán aquellas categorías cuyo conjunto de gastos tienen una cuantía media superior a 50 unidades monetarias. La reunión externa ya no es necesaria porque las categorías sin gastos nunca verificarán la condición del HAVING.
SELECT c.id AS id, c.name AS name, SUM (e.amount) AS total, COUNT(e) AS expenses
FROM Category c INNER JOIN c.expenses e
GROUP BY c.id
HAVING AVG(e.amount) > 50
ORDER BY c.name
Del resultado desaparecen la categoría sin gastos y la número tres porque la media de sus gastos se queda en 38.
Funciones en JPQL
A continuación desgloso las funciones exigidas por el estándar JPA a sus implementaciones. Pueden aparecer en la cláusula SELECT y en el WHERE.
Cadenas
CONCAT. Une las cadenas que recibe como argumento.
Este es el listado con el nombre de cada gasto y el de su categoría
SELECT CONCAT('Expense: ', e.concept, ', Category: ', e.category.name)
FROM Expense e
ORDER BY e.concept
En HQL, hacemos lo mismo con el operador ||.
SUBSTRING. Igual que el método de Java: extrae de una cadena el fragmento que hay entre una posición de inicio (obligatoria, empieza en 1) y la final (opcional).
LOWER, UPPER. Convierte a minúsculas \ mayúsculas. Un uso típico es ignorar la capitalización en una comparación.
SELECT e
FROM Expense e
WHERE UPPER(e.concept) LIKE UPPER(:concept)
TRIM. Elimina los espacios al principio y al final de la cadena. Se puede indicar con un primer argumento opcional si solo se quiere los del principio (LEADING) o los del final (TRAILING), y con un segundo, también opcional, el carácter a eliminar. Si hay algún argumento, la cadena a usar viene precedida de FROM.
Dado el nombre de un gasto, borrar los espacios en sus extremos.
SELECT TRIM(e.concept) FROM Expense e
La letra m al principio y al final.
SELECT TRIM('m' FROM e.concept) FROM Expense e
La letra m solo al principio
SELECT TRIM(LEADING 'm' FROM e.concept) FROM Expense e
LOCATE. Encuentra la posición dentro de una cadena en la que comienza otra. Opcionalmente, se indicará la posición a partir de la que empezar la búsqueda. La primera es 1.
SELECT LOCATE('movie', LOWER(e.concept))
FROM Expense e
LENGTH. El número de caracteres de la cadena.
Funciones aritméticas
Nota: se incluirán nuevas funciones aritméticas en JPA 3.1 (Jakarta EE 10).
ABS. El valor absoluto.
SQRT. Double con la raíz cuadrada.
MOD. El entero con el resto de dos enteros.
Colecciones
SIZE. El número de elementos de una colección.
SELECT SIZE(b.categories)
FROM Budget b WHERE b.id = 1
En SQL, Hibernate hace una reunión para aplicar el COUNT a las categorías del presupuesto.
SELECT count(categories1_.budget_id) as col_0_0_
FROM budgets budget0_ CROSS JOIN budgets_categories categories1_
WHERE budget0_.id = categories1_.budget_id AND budget0_.id = 1
Asimismo, si aparece en el WHERE
SELECT b
FROM Budget b
WHERE SIZE(b.categories) > 1
implica una subconsulta.
SELECT ...
FROM
budgets budget0_
WHERE (
SELECT COUNT(categories1_.budget_id)
FROM budgets_categories categories1_
WHERE budget0_.id = categories1_.budget_id) > 1 )
INDEX. Busca la posición de una entidad en la colección de una relación múltiple. Es imprescindible que la relación tenga una columna en la tabla que defina su orden, algo que se configura con la anotación @OrderColumn.
public class Budget {
@ManyToMany
@JoinTable(name = "budgets_categories",
joinColumns = @JoinColumn(name = "budget_id"),
inverseJoinColumns = @JoinColumn(name = "category_id") )
@OrderColumn
private List<Category> categories;
Así se recuperan los presupuestos en los que la categoría 1 aparezca en primera posición de su lista de categorías.
SELECT c
FROM Budget b JOIN b.categories c
WHERE c.id = 1 AND INDEX(c) = 1
Fecha y hora
Aquí encontramos la gran decepción del catálogo de funciones de JPQL: no proporciona las suficientes opciones para trabajar con fechas y tiempos. Estamos limitados a obtener la fecha, la hora o la fecha y hora actual establecida en la base de datos con CURRENT_DATE, CURRENT_TIME y CURRENT_TIMESTAMP. Las tres, por cierto, disponibles en SQL. Así pues, en este aspecto tendremos que recurrir a funciones nativas de la base de datos o las que ofrezca nuestro proveedor de JPA. Veremos ambas opciones en breve.
Esta consulta obtiene los gastos realizados en el día de hoy. Recordemos que el atributo Expense#date no contiene la hora por ser un LocalDate.
SELECT e
FROM Expense e
WHERE e.date = CURRENT_DATE
TYPE
Esta función retorna el nombre de una entidad -el nombre de la clase o bien lo que hayamos puesto dentro de la anotación @Entity-. A priori, su utilidad no parece clara, a menos que pensemos en las relaciones de herencia como la mostrada por el siguiente diagrama UML.
Estos son los dispositivos que pesen más de una unidad.
SELECT d
FROM Device d
WHERE d.weight > 1
Cuando recojamos los resultados en objetos Java, todos serán de la clase Device, y haremos un casting a la subclase de cada de uno si fuera necesario. Pero si queremos distinguir por entidad en la propia consulta, contamos con TYPE.
SELECT d
FROM Device d
WHERE d.weight > 1 AND TYPE(d) <> Camera
Los resultados ahora excluyen las cámaras.
Funciones HQL
Abandonamos el estándar para examinar funciones propias de Hibernate.
BIT_LENGTH. Obtiene la longitud de un dato binario.
CAST. Convierte el tipo de una proyección a uno de los predefinidos en Java.
SELECT CAST(e.date as java.lang.String)
FROM Expense e
WHERE e.id = 1
La consulta anterior, en lugar de devolver la fecha como un LocalDate, nos la da en una cadena con el formato yyyy-MM-dd.
El casting a String se puede hacer con la función específica STR.
SELECT STR(e.date)
FROM Expense e
WHERE e.id = 1
Con EXTRACT se amplían las opciones de manipulación de fechas y tiempos. Extrae uno de los valores de una fecha\tiempo: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND. Este es el formato.
EXTRACT [campo] FROM [propiedad]
Recuperemos los gastos de cierto año de forma paginada porque pueden ser numerosos.
@Override
public Page<Expense> findByYear(int year, int first, int max) {
Long count = em.createQuery("SELECT count(e) FROM Expense e WHERE EXTRACT(YEAR FROM e.date) = :year", Long.class)
.setParameter("year", year)
.getSingleResult();
List<Expense> expenses = em.createQuery("SELECT e FROM Expense e WHERE EXTRACT(YEAR FROM e.date) = :year ORDER BY e.date",
Expense.class)
.setParameter("year", year)
.setFirstResult(first)
.setMaxResults(max)
.getResultList();
return new Page<>(expenses, count, first, max);
}
EXTRACT se incluirá en JPA 3.1 (Jakarta EE 10).
Funciones de SQL
En esta sección nos olvidamos de toda abstracción de la base de datos para entrar de lleno en las funciones exclusivas del SQL de cada base de datos. La buena noticia es que podemos usarlas tanto en JPQL como en Criteria API, así que cuando sean de utilidad no nos veremos forzados a escribir las consultas en SQL. Todo lo que veamos es aplicable también a las funciones definidas por nosotros mismos en la base de datos.
Dialectos de Hibernate
Hibernate registra en sus dialectos las funciones SQL reconocidas por HQL. Algunas de ellas estarán disponibles en varios dialectos, otras no, así que corremos el riesgo perder portabilidad entre bases de datos, algo que ya comenté que en la mayoría de proyectos no supone ningún problema.
Ahora, las posibilidades en la construcción de consultas HQL crecen exponencialmente. Por ejemplo.
SELECT WEEKOFYEAR(e.date)
FROM Expense e WHERE e.id = 1
La función de MySQL WEEKOFYEAR obtiene el número de la semana que corresponde a una fecha, una información que no podemos conseguir con JPQL estándar, al menos en la versión 3.0 (Jakarta EE 9).
Para consultar las funciones disponibles, lo mejor es revisar el código fuente de Hibernate en GitHub. En nuestro caso, debemos mirar MySQLDialect y sus subclases. A continuación se muestra un fragmento para la versión 5.6.
Las funciones genéricas de HQL para todos los dialectos están en la superclase Dialect.
Registro de funciones en Hibernate
¿Qué sucede si la función que queremos no está registrada en Hibernate? Podemos registrarla implementando un dialecto que herede del correspondiente a la base de datos que usemos.
Vamos a añadir una función para MySQL llamada WEEK que invoque a WEEKOFDAY. Es un simple ejemplo, pues WEEKOFDAY ya está registrada con ese nombre.
package com.danielme.jakartaee.jpa;
import org.hibernate.dialect.MySQL8Dialect;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;
public class MySQLCustomDialect extends MySQL8Dialect {
public MySQLCustomDialect() {
super();
registerFunction(
"WEEK",
new StandardSQLFunction("weekofyear", StandardBasicTypes.INTEGER));
}
}
La función SQL se modela con una implementación de la interfaz SQLFunction. Hibernate incluye bastantes implementaciones; normalmente optaremos por StandardSQLFunction. Construimos un objeto de esa clase proporcionando el nombre de la función SQL (recordemos que la capitalización es irrelevante) y el tipo retornado como una implementación de Type. Los tipos básicos los encontramos en la clase StandardBasicTypes.
Ahora tenemos que indicar nuestro dialecto personalizado en el fichero persistence.xml.
<property name="hibernate.dialect"
value="com.danielme.jakartaee.jpa.MySQLCustomDialect"/>
Y ya podemos usar WEEK en HQL como cualquier otra función.
@Override
public Optional<Integer> weekOfYearExpense(Long expenseId) {
return em.createQuery("SELECT WEEK(e.date) FROM Expense e WHERE e.id = :expenseId", Integer.class)
.setParameter("expenseId", expenseId)
.getResultStream()
.findFirst();
}
Con todo, esta solución es muy intrusiva y rígida. Si cambiamos la versión del dialecto, debemos actualizar el nuestro. No obstante, puede ser útil para definir una función genérica en HQL que llame a una nativa distinta según el dialecto cuando estemos desarrollando un sistema que deba ser compatible con varias bases de datos. Por ejemplo, en PostgreSQL no existe WEEKOFYEAR, pero es posible crear una versión de WEEK basada en DATE_PART en un nuevo dialecto. De este modo, la función WEEK estará disponible cuando usemos MySQL y PostgreSQL gracias a nuestros dialectos personalizados.
package com.danielme.jakartaee.jpa;
import org.hibernate.dialect.PostgreSQL95Dialect;
import org.hibernate.dialect.function.SQLFunctionTemplate;
import org.hibernate.type.StandardBasicTypes;
public class PostgreCustomDialect extends PostgreSQL95Dialect {
public PostgreCustomDialect() {
super();
registerFunction("WEEK",
new SQLFunctionTemplate(StandardBasicTypes.STRING,
"DATE_PART('week', ?1)"));
}
}
<property name="hibernate.dialect"
value="com.danielme.jakartaee.jpa.PostgreCustomDialect"/>
Otra forma de registrar funciones más «limpia» y recomendable consiste en implementar un MetadataBuilderContributor que añada todas las que necesitemos. Esta es la implementación de WEEK para MySQL. El código es muy parecido al de MySQLCustomDialect, ya que seguimos creando la misma StandardSQLFunction.
package com.danielme.jakartaee.jpa;
import org.hibernate.boot.MetadataBuilder;
import org.hibernate.boot.spi.MetadataBuilderContributor;
import org.hibernate.dialect.function.StandardSQLFunction;
import org.hibernate.type.StandardBasicTypes;
public class CustomFunctionsMySQLMetadataBuilderContributor implements MetadataBuilderContributor {
@Override
public void contribute(MetadataBuilder metadataBuilder) {
metadataBuilder.applySqlFunction(
"WEEK",
new StandardSQLFunction("WEEKOFYEAR", StandardBasicTypes.INTEGER));
}
}
Hibernate incorporará las funciones definidas en la clase anterior si así lo indicamos en el persistence.xml.
<property name="hibernate.metadata_builder_contributor"
value="com.danielme.jakartaee.jpa.CustomFunctionsMySQLMetadataBuilderContributor"/>
Con esta estrategia no perdemos la posibilidad que vimos con los dialectos de declarar funciones HQL cuya implementación en SQL dependa de la base de datos. Tan sencillo como crear los MetadataBuilderContributor que sean necesarios y elegir el adecuado a la base de datos con la que trabajemos en cada momento.
FUNCTION
JPQL estandariza las llamadas a funciones nativas con la función, valga la redundancia, FUNCTION. Recibe el nombre de la función y todos sus parámetros y devuelve un valor escalar (numérico, lógico, cadena, fecha, tiempo).
Las consultas anteriores pueden reescribirse así.
SELECT FUNCTION('WEEKOFYEAR', e.date)
FROM Expense e
WHERE e.id = 1
Si bien funciona, es un ejemplo tramposo. FUNCTION solo puede aplicarse directamente en la cláusula WHERE. Para usarla en la SELECT, la función que llamamos debe estar registrada porque Hibernate necesita conocer el tipo que retorna. Y WEEKOFYEAR ya lo está, al igual que la mayoría de funciones de MySQL.
No es el caso de RPAD (añade caracteres a una cadena de forma peculiar). La siguiente consulta fallará si no aplicamos una de las dos estrategias de registro de funciones (dialecto o MetaBuilder).
SELECT FUNCTION('RPAD', e.concept, 10, '!')
FROM Expense e
WHERE e.id = :id
org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode
\-[METHOD_CALL] MethodNode: 'FUNCTION (RPAD)'
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 