Funciones en consultas JPQL

Introducción

Las funciones en JPQL son herramientas potentes que permiten realizar operaciones más complejas directamente en las consultas, tales como manipulaciones de cadenas de texto, operaciones matemáticas y manejo de fechas, entre otros. 

Utilizando JPQL, los desarrolladores pueden escribir consultas que son independientes de la base de datos, proporcionando así una capa de abstracción que facilita la portabilidad y mantenimiento del código. 

En el contexto de Hibernate, las funciones JPQL se integran de manera fluida para realizar consultas sobre entidades gestionadas.

Funciones de cadena

Las funciones de cadena permiten realizar operaciones sobre campos de texto de las entidades. Utilizar estas funciones en la consulta puede reducir la necesidad de manipulaciones adicionales en el código Java, lo que optimiza el rendimiento al procesar grandes volúmenes de datos.

CONCAT

La función CONCAT combina dos o más cadenas. Es útil para formar una sola cadena a partir de varios campos o valores literales.

// Ejemplo de CONCAT
// Concatena el nombre y apellido de un empleado con un espacio en medio

EntityManager em = ...; // Obtener el EntityManager
TypedQuery<String> query = em.createQuery(
    "SELECT CONCAT(e.nombre, ' ', e.apellido) FROM Empleado e", 
    String.class);
List<String> nombresCompletos = query.getResultList();

LENGTH

LENGTH devuelve el número de caracteres en una cadena. Es especialmente útil para validar la longitud de los datos de texto o para ordenar registros por la longitud de un campo específico.

// Ejemplo de LENGTH
// Obtiene la longitud del nombre de cada empleado

TypedQuery<Integer> queryLength = em.createQuery(
    "SELECT LENGTH(e.nombre) FROM Empleado e", 
    Integer.class);
List<Integer> longitudesNombres = queryLength.getResultList();

LOWER y UPPER

LOWER convierte todos los caracteres de una cadena a minúsculas, y UPPER los convierte a mayúsculas. Estas funciones son útiles para realizar comparaciones de texto insensibles a mayúsculas y minúsculas.

// Ejemplo de LOWER y UPPER
// Selecciona empleados cuyo nombre en mayúsculas es 'ALICIA'

TypedQuery<Empleado> queryUpper = em.createQuery(
    "SELECT e FROM Empleado e WHERE UPPER(e.nombre) = 'ALICIA'", 
    Empleado.class);
List<Empleado> empleadosNombreAlicia = queryUpper.getResultList();

SUBSTRING

SUBSTRING extrae una subcadena de una cadena principal. El segundo argumento es la posición inicial desde donde empezar a extraer la subcadena, notando que el índice de las cadenas comienza en 1. El tercer argumento (opcional) es la longitud de la subcadena a extraer, y si se omite, SUBSTRING extraerá todos los caracteres desde la posición inicial hasta el final de la cadena.

// Ejemplo de SUBSTRING
// Extrae los primeros 5 caracteres del nombre de cada empleado

TypedQuery<String> querySubstring = em.createQuery(
    "SELECT SUBSTRING(e.nombre, 1, 5) FROM Empleado e", 
    String.class);
List<String> primerosCincoCaracteres = querySubstring.getResultList();

Funciones de Agregación

Las funciones de agregación permiten obtener información resumida como conteos, sumas, promedios, valores máximos y mínimos, que son críticos para el análisis de datos y la toma de decisiones dentro de las aplicaciones. Utilizar funciones de agregación directamente en la consulta puede mejorar el rendimiento al procesar grandes cantidades de datos y evitar la sobrecarga de recuperar y manipular estos datos en el lado del cliente.

COUNT

COUNT es una función que cuenta el número de elementos en un grupo o en total, lo que es útil para determinar la cantidad de registros que cumplen ciertos criterios.

// Ejemplo de COUNT
// Cuenta el número total de empleados en la empresa

EntityManager em = ...; // Obtener el EntityManager
TypedQuery<Long> queryCount = em.createQuery(
    "SELECT COUNT(e) FROM Empleado e", 
    Long.class);
Long numeroEmpleados = queryCount.getSingleResult();

SUM

SUM calcula la suma total de un conjunto de valores numéricos. Es ideal para calcular totales en campos numéricos, como el total de ventas, salarios, etc.

// Ejemplo de SUM
// Calcula el total de salarios pagados a los empleados

TypedQuery<Double> querySum = em.createQuery(
    "SELECT SUM(e.salario) FROM Empleado e", 
    Double.class);
Double totalSalarios = querySum.getSingleResult();

AVG

AVG calcula el promedio de un conjunto de valores numéricos. Esta función es muy útil para análisis estadísticos, como encontrar el salario promedio, el costo promedio de los productos, etc.

// Ejemplo de AVG
// Calcula el salario promedio de los empleados

TypedQuery<Double> queryAvg = em.createQuery(
    "SELECT AVG(e.salario) FROM Empleado e", 
    Double.class);
Double salarioPromedio = queryAvg.getSingleResult();

MAX y MIN

MAX y MIN encuentran el valor máximo y mínimo de un conjunto de valores, respectivamente. Estas funciones son cruciales para determinar extremos en los datos, como los salarios más altos y más bajos, las fechas más recientes y más antiguas de transacciones, etc.

// Ejemplo de MAX
// Encuentra el salario más alto entre los empleados

TypedQuery<Double> queryMax = em.createQuery(
    "SELECT MAX(e.salario) FROM Empleado e", 
    Double.class);
Double salarioMaximo = queryMax.getSingleResult();

// Ejemplo de MIN
// Encuentra el salario más bajo entre los empleados

TypedQuery<Double> queryMin = em.createQuery(
    "SELECT MIN(e.salario) FROM Empleado e", 
    Double.class);
Double salarioMinimo = queryMin.getSingleResult();

Otras funciones de agregación

• GROUP BY: Agrupa resultados que tienen el mismo valor en columnas especificadas, utilizada comúnmente con otras funciones de agregación.
• HAVING: Se emplea para especificar condiciones de filtrado sobre grupos creados por GROUP BY, permitiendo filtrar grupos según condiciones específicas.
• DISTINCT: Usada para eliminar duplicados en los resultados de una consulta, frecuentemente usada con COUNT para contar valores únicos.
• COALESCE: Devuelve el primer valor no nulo en la lista de parámetros, útil para manejar valores nulos en cálculos de agregación.
• NULLIF: Devuelve null si los dos argumentos son iguales, de lo contrario devuelve el primer argumento, utilizado para evitar divisiones por cero.

Funciones matemáticas

Las funciones matemáticas en JPQL facilitan la ejecución de operaciones aritméticas directamente en las consultas a la base de datos. Esto es particularmente útil para realizar cálculos que de otra manera requerirían manipulaciones adicionales en el código Java, optimizando así el rendimiento al procesar y analizar grandes volúmenes de datos numéricos.

ABS

La función ABS calcula el valor absoluto de un número, lo cual es útil para garantizar que se manejan valores no negativos en los cálculos o comparaciones.

// Ejemplo de ABS
// Calcula el valor absoluto del saldo de cuentas, para aquellas que tengan un saldo negativo

EntityManager em = ...; // Obtener el EntityManager
TypedQuery<Double> queryAbs = em.createQuery(
    "SELECT ABS(c.saldo) FROM Cuenta c WHERE c.saldo < 0", 
    Double.class);
List<Double> saldosAbsolutos = queryAbs.getResultList();

Otras funciones matemáticas

• MOD(a, b): Devuelve el resto de la división de a por b. Útil para determinar la paridad de un número o para aplicar operaciones que dependen de un ciclo periódico, como asignaciones de tareas por días de la semana.
• CEIL(x) o CEILING(x): Redondea x al entero más pequeño no menor que x (redondea al alza).
• FLOOR(x): Redondea x al entero más grande no mayor que x (redondea a la baja).
• ROUND(x): Redondea x al entero más cercano.
• POWER(a, b): Eleva a a la potencia de b.
• SQRT(x): Calcula la raíz cuadrada de x.
• RANDOM(): Genera un número aleatorio entre 0 y 1.

Funciones de fecha

Las funciones de fecha permiten manipular y consultar campos de datos basados en fechas y tiempos directamente desde la base de datos.

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP

CURRENT_DATE, CURRENT_TIME, y CURRENT_TIMESTAMP son funciones que retornan la fecha, la hora, y la fecha y hora actuales del sistema, respectivamente. Son útiles para registrar eventos en tiempo real o para realizar comparaciones con fechas y horas actuales.

// Ejemplo de CURRENT_DATE
// Selecciona todos los pedidos que se han realizado hoy

EntityManager em = ...; // Obtener el EntityManager
TypedQuery<Pedido> queryCurrentDate = em.createQuery(
    "SELECT p FROM Pedido p WHERE p.fechaPedido = CURRENT_DATE", 
    Pedido.class);
List<Pedido> pedidosDeHoy = queryCurrentDate.getResultList();

EXTRACT

EXTRACT se usa para descomponer una fecha en sus componentes como año, mes, día, hora, minutos, etc. Esto es particularmente útil para agrupar o filtrar datos basados en un componente específico de la fecha.

// Ejemplo de EXTRACT
// Cuenta cuántos empleados se han unido en cada año

TypedQuery<Object[]> queryExtract = em.createQuery(
    "SELECT EXTRACT(YEAR FROM e.fechaIngreso) as Year, COUNT(e) FROM Empleado e GROUP BY EXTRACT(YEAR FROM e.fechaIngreso)",
    Object[].class);
List<Object[]> empleadosPorAño = queryExtract.getResultList();

DATE_DIFF

DATE_DIFF calcula la diferencia en días entre dos fechas. Es esencial para cálculos que requieren entender la duración entre eventos, como calcular la antigüedad de los empleados o el tiempo hasta que un producto debe ser entregado.

// Ejemplo de DATE_DIFF
// Calcula la cantidad de días que faltan para la entrega de cada pedido

TypedQuery<Long> queryDateDiff = em.createQuery(
    "SELECT DATE_DIFF(p.fechaEntrega, CURRENT_DATE) FROM Pedido p WHERE p.estado = 'Pendiente'", 
    Long.class);
List<Long> diasParaEntrega = queryDateDiff.getResultList();

ADD y SUBTRACT

ADD y SUBTRACT son funciones que permiten añadir o sustraer una cantidad específica de unidades de tiempo a las fechas, como días, meses, o años.

// Ejemplo de ADD
// Añade 5 días a la fecha actual para calcular una nueva fecha de entrega

TypedQuery<Date> queryAdd = em.createQuery(
    "SELECT ADD(CURRENT_DATE, 5) FROM Pedido p", 
    Date.class);
List<Date> nuevasFechasEntrega = queryAdd.getResultList();

// Ejemplo de SUBTRACT
// Sustrae 30 días de la fecha de contratación para calcular períodos de prueba

TypedQuery<Date> querySubtract = em.createQuery(
    "SELECT SUBTRACT(e.fechaContratacion, 30) FROM Empleado e", 
    Date.class);
List<Date> inicioPeriodoPrueba = querySubtract.getResultList();