Frameworks de persistencia

5. Consultas

Hasta ahora hemos explicado cómo se realiza la definición de entidades y relaciones en JPA. En esta sesión vamos a explicar el lenguaje de consultas JPQL, el API que utiliza JPA para realizar consultas sobre esas tablas definidas. Explicaremos las sentencias principales de este API, sin entrar en demasiados detalles sobre la estructura de clases en las que se van a guardar estas consultas. Lo haremos en la próxima sesión, donde explicaremos cómo definir las clases DAO y Service que trabajan con entidades y proporcionan la capa de negocio de la aplicación.

5.1. Definición de clases

Vamos a definir las consultas sobre las clases Empleado, Proyecto y CuentaCorreo, definidas como sigue (todas con identificador autogenerado de tipo Long):

Empleado:

nombre: String
sueldo: Float
direccion: Direccion, clase embebida
departamento: Departamento, relación muchos-a-uno (departamento de un empleado)
proyectos: Set<Proyecto>, relación muchos-a-muchos (proyectos de un empleado)
facturas: Set<FacturaGasto>, relación uno-a-muchos (gastos de un empleado)

Departamento:

nombre: String
empleados: Set<Empleado>, relación a-muchos (empleados de un departamento)

Proyecto:

nombre: String
empleados: Set<Empleado>, relación a-muchos (empleados que trabajan en el proyecto)

FacturaGasto:

concepto: String
cantidad: Double
fecha: Date
empleado: Empleado, relación a-uno (empleado asociado a la cuenta de correo)

Direccion (Clase embebida):

campus: String
edificio: String

Código JPA de estas clases:

@Entity
public class Empleado {
    @Id
    private Long id;
    private String nombre;
    private Double sueldo;
    @ManyToOne
    private Departamento departamento;
    @ManyToMany
    private Set<Proyecto> proyectos;
    @OneToMany(mappedBy = "empleado")
    private Set<FacturaGasto> gastos;

    ...
}

@Entity
public class Departamento {
    @Id
    private Long id;
    private String nombre;
    @Embedded
    private Direccion direccion;
    @OneToMany(mappedBy = "departamento")
    private Set<Empleado> empleados;

    ...
}

@Entity
public class Proyecto {
    @Id
    private Long id;
    private String nombre;
    @ManyToMany(mappedBy = "proyectos")
    private Set<Empleado> empleados;

    ...
}

@Entity
public class FacturaGasto {
    @Id
    private Long id;
    @ManyToOne
    private Empleado empleado;
    private String concepto;
    private Double cantidad;
    private Date fecha;

    ...
}


@Embeddable
public class Direccion {
    private String campus;
    private String edificio;

    ...
}

El diagrama de clases que genera IntelliJ con estas entidades es el siguiente:

5.2. Definición de consultas JPQL

El API JPA proporciona la interfaz Query para configurar y ejecutar consultas. Podemos obtener una instancia que implemente esa interfaz mediante un dos de métodos del entity manager: createQuery() y createNamedQuery(). El primer método se utilizar para crear una consulta dinámica y el segundo una consulta con nombre.

Una vez obtenida la consulta podemos pasarle los parámetros con setParameter() y ejecutarla. Se definen dos métodos para ejecutar consultas: el método getSingleResult() que devuelve un Object que es la única instancia resultante de la consulta y el método getResultList() que devuelve una lista de instancias resultantes de la consulta.

5.2.1. Parámetros de las consultas

JPQL soporta dos tipos de sintaxis para la ligadura (binding) de parámetros: posicional y por nombre. Vemos un ejemplo del primer caso:

QUERY = "SELECT e
   FROM Empleado e
   WHERE e.departamento = ?1 AND
        e.salario > ?2"

La segunda forma de definir parámetros es por nombre:

QUERY = "SELECT  e
   FROM Empleado e
   WHERE e.departamento = :dept AND
         e.salario > :sal"

Veremos en el siguiente apartado ejemplos en los que se liga parámetros determinados a esas posiciones o nombres.

5.2.2. Consultas dinámicas

Se puede crear una consulta dinámica pasándole al entity manager la cadena con la consulta al método createQuery. JPA transforma en ese momento la consulta en el código SQL que se ejecutará en la base de datos.

Veamos un ejemplo de consulta, que utiliza el paso de parámetros por nombre visto anteriormente:

public double salarioPorDeptoNombre(String nombreDepto, String nombreEmpleado) {
    final String SALARIO_POR_DEPTO_NOMBRE = "SELECT e.sueldo " +
            "FROM Empleado e " +
            "WHERE e.departamento.nombre = :deptNombre AND " +
            "      e.nombre = :empNombre";
    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();

    TypedQuery<Double> query = em.createQuery(SALARIO_POR_DEPTO_NOMBRE, Double.class)
            .setParameter("deptNombre", nombreDepto)
            .setParameter("empNombre", nombreEmpleado);
    double sueldo = query.getSingleResult();
    System.out.println(sueldo);
    em.getTransaction().commit();
    em.close();
    return sueldo;
}

La clase TypedQuery se introdujo en el API JPA 2.0 y es recomendble usarlo. Se construye la query pasando al método createQuery un segundo parámetro con la clase del tipo devuelto por la query. De esta forma se evita tener que hacer un casting de lo que devuelve query.getSingleResult().

Un inconveniente de las consultas dinámicas es que se procesan en tiempo de ejecución de la aplicación, con la consiguiente pérdida de rendimiento. Su ventaja principal es que se pueden definir con mucha comodidad.

5.2.3. Consultas con nombre

Las consultas con nombre se definen junto con alguna entidad, utilizando la anotación @NamedQuery. Mejoran el rendimiento con respecto a las consultas dinámicas, ya que son procesadas una única vez. El siguiente código muestra un ejemplo:

@Entity
@NamedQuery(name="Empleado.salarioPorNombreDepartamento",
            query="SELECT e.sueldo " +
                  "FROM Empleado e " +
                  "WHERE e.departamento.nombre = : deptNombre AND " +
                  "      e.nombre = : empNombre")
public class Empleado implements Serializable {
   ...
}

El nombre de la consulta es una cadena única. Utilizamos el convenio de colocar el nombre de la clase al comienzo de la cadena, separado por un punto del resto. Las consultas pueden definirse en cualquier clase de entidad y este convenio permite que sea la definición de la consulta sea fácil de localizar.

Si se necesita más de una consulta para una entidad, deben incluirse en la anotación @NamedQueries, que acepta una array de una o más anotaciones @NamedQuery:

@Entity
@NamedQueries({
   @NamedQuery(name="Empleado.findAll",
               query="SELECT e FROM Empleado e"),
   @NamedQuery(name="Empleado.findById",
               query="SELECT e FROM Empleado e WHERE e.id = :id"),
   @NamedQuery(name="Empleado.findByNombre",
               query="SELECT e FROM Empleado e WHERE e.nombre = :nombre")
})
public class Empleado implements Serializable {
   ...
}

Para ejecutar la consulta hay que llamar al método createNamedQuery pasándole como parámetro el nombre de la consulta. El siguiente código muestra dos ejemplos:

public Empleado findEmpleadoPorNombre(String nombreEmpleado) {
    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();
    TypedQuery<Empleado> queryEmpleado =
            em.createNamedQuery("Empleado.findByNombre", Empleado.class)
                    .setParameter("nombre", nombreEmpleado);
    Empleado empleado = queryEmpleado.getSingleResult();
    em.getTransaction().commit();
    em.close();
    return empleado;
}


public List<Empleado> findAllEmpleados() {
    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();
    TypedQuery<Empleado> queryListaEmpleados =
            em.createNamedQuery("Empleado.findAll", Empleado.class);
    List<Empleado> empleados = queryListaEmpleados.getResultList();
    em.getTransaction().commit();
    em.close();
    return empleados;
}

5.2.4. Ejecución de consultas

Veamos por último los métodos del interfaz Query para ejecutar las consultas definidas. Son los métodos getSingleResult() y getResultList().

Ambos métodos se deben lanzar sobre una Query ya construida y en la que se han introducido los parámetros. El método getSingleResult() se utiliza con consultas que devuelven un único resultado. Devuelve un Object que contiene el resultado de la consulta. Después de llamarlo conviene hacer un casting al tipo (entidad o tipo básico) que esperamos. Puede suceder que la consulta ejecutada no devuelva ningún resultado o devuelva más de uno. En el primer caso se genera la excepción NoResultException y en el segundo NonUniqueResultException.

Si no tenemos seguridad de una consulta vaya a devolver un único valor deberíamos llamar a getResultList(). Este método devuelve una List de Object. La utilización de la interfaz List en lugar de Collection es para soportar la devolución de colecciones ordenadas. En el caso en que la consulta no obtenga resultados, se devuelve una lista vacía. El siguiente código muestra un ejemplo de utilización de una consulta:

public void imprimeEmpleadosProyecto() {
    List<Empleado> empleados = findEmpleadosProyecto("Reconocimiento de caras");
    for (Empleado empleado : empleados) {
        System.out.println(empleado.getNombre());
    }
}

private List<Empleado> findEmpleadosProyecto(String nombreProyecto) {
    EntityManager em = emf.createEntityManager();
    em.getTransaction().begin();
    TypedQuery<Empleado> queryEmpleadosProyecto =
            em.createQuery(
                    "SELECT e " +
                            "FROM Proyecto p JOIN p.empleados e " +
                            "WHERE p.nombre = ?1", Empleado.class);

    List<Empleado> empleados = queryEmpleadosProyecto
            .setParameter(1, nombreProyecto)
            .getResultList();
    em.getTransaction().commit();
    em.close();
    return empleados;
}

5.3. Java Persistence Query Language

JPQL es el lenguaje en el que se construyen las queries en JPA. Aunque en apariencia es muy similar a SQL, en la realidad operan en mundos totalmente distintos. En JPQL las preguntas se construyen sobre clases y entidades, mientras que SQL opera sobre tablas, columnas y filas en la base de datos.

Una consulta JPQL puede contener los siguientes elementos

SELECT c
FROM Category c
WHERE c.categoryName LIKE :categoryName
ORDER BY c.categoryId

Una cláusula SELECT que especifica el tipo de entidades o valores que se recuperan
Una cláusula FROM que especifica una declaración de entidad que es usada por otras cláusulas
Una cláusula opcional WHERE para filtrar los resultados devueltos por la query
Una cláusula opcional ORDER BY para ordenar los resultados devueltos por la query
Una cláusula opcional GROUP BY para realizar agregación
Una cláusula opcional HAVING para realizar un filtrado en conjunción con la agregación

5.3.1. Consultas básicas

La consulta más sencilla en JPQL es la que selecciona todas las instancias de un único tipo de entidad:

SELECT e FROM Empleado e

La sintaxis de JPQL es similar a la de SQL. De esta forma los desarrolladores con experiencia en SQL pueden comenzar a utilizarlo rápidamente. La diferencia principal es que en SQL se seleccionan filas de una tabla mientras que en JPQL se seleccionan instancias de un tipo de entidad del modelo del dominio. La cláusula SELECT es ligeramente distinta a la de SQL listando sólo el alias e del Empleado. Este tipo indica el tipo de datos que va a devolver la consulta, una lista de cero o más instancias Empleado.

Código que ejecuta la consulta:

final String FIND_ALL_EMPLEADOS = "SELECT e FROM Empleado e ";

TypedQuery<Empleado> query = em.createQuery(FIND_ALL_EMPLEADOS, Empleado.class);

List<Empleado> empleados = query.getResultList();
if (empleados != null) {
    for (Empleado empleado : empleados) {
        System.out.println(empleado.getNombre() + " - " +
                empleado.getDespacho().getCodigoDespacho());
    }
}

La consulta devuelve una colección de entidades, junto con sus grafos de objetos relacionados cargados en memoria (de aquellas relaciones que sean EAGER). Por ejemplo cada empleado contiene también su despacho y todas las entidades con las que está relacionado.

Podemos restringir el tipo de dato devuelto. Por ejemplo, si sólo queremos los sueldos de los empleados podemos definir la siguiente consulta:

SELECT e.sueldo FROM Empleado e

Código que ejecuta la consulta:

final String FIND_ALL_SUELDOS_EMPLEADOS = "SELECT e.sueldo FROM Empleado e ";

TypedQuery<Double> query = em.createQuery(FIND_ALL_SUELDOS_EMPLEADOS, Double.class);

List<Double> sueldosEmpleados = query.getResultList();

if (sueldosEmpleados != null) {
    for (double sueldoEmpleado : sueldosEmpleados) {
        System.out.println(sueldoEmpleado);
    }
}

Podemos también seleccionar una entidad cuyo tipo no listamos en la cláusula SELECT, utilizando los atributos relación. Por ejemplo:

SELECT e.departamento FROM Empleado e

EL código que ejecuta la consulta:

final String FIND_ALL_DEPARTAMENTOS = "SELECT e.departamento FROM Empleado e ";

TypedQuery<Departamento> query = em.createQuery(FIND_ALL_DEPARTAMENTOS, Departamento.class);

List<Departamento> departamentos = query.getResultList();

if (departamentos != null) {
    for (Departamento departamento : departamentos) {
         System.out.println(departamento.getNombre());
    }
}

Debido a la relación muchos-a-uno entre Empleado y Departamento la consulta devolverá una lista de instancias de tipo Departamento. La consulta devuelve una lista con tantos elementos como empleados hayan definidos, con los departamentos repetidos.

5.3.2. Filtrando los resultados

Igual que SQL, JPQL contiene una cláusula WHERE para especificar condiciones que deben cumplir los datos que se devuelven. La mayoría de los operadores disponibles en SQL están en JPQL, incluyendo las operaciones básicas de comparación: IN, LIKE y BETWEEN, funciones como SUBSTRING o LENGTH y subqueries. Igual que antes, la diferencia fundamental es que se utilizan expresiones sobre entidades y no sobre columnas. El siguiente código muestra un ejemplo:

SELECT e
   FROM Empleado e
      WHERE e.departamento.nombre LIKE '%IA' AND
      e.sueldo BETWEEN 2000 AND 2500

El código Java es el siguiente:

final String FIND_ALL_EMPLEADOS_WHERE = "SELECT e FROM Empleado e " +
        "WHERE e.departamento.nombre LIKE '%IA' AND" +
        " e.sueldo BETWEEN 2000 AND 2500";

TypedQuery<Empleado> query = em.createQuery(FIND_ALL_EMPLEADOS_WHERE, Empleado.class);

List<Empleado> empleados = query.getResultList();

if (empleados != null) {
    for (Empleado empleado : empleados) {
        System.out.println(empleado.getNombre() + " - " +
                    empleado.getSueldo();
        }
    }
}

5.3.3. Proyectando los resultados

Es posible recuperar un conjunto de atributos de las instancias, sin que formen una entidad. Esto es útil cuando tenemos una gran cantidad de atributos (columnas) y sólo vamos a necesitar listar algunos. Por ejemplo, la siguiente consulta selecciona sólo el nombre y el salario de los empleados:

SELECT e.nombre, e.sueldo
   FROM Empleado e

El resultado será una colección de cero o más instancias de arrays de tipo Object. Cada array contiene dos elementos, el primero un String y el segundo un Double:

final String FIND_NOMBRE_SUELDO = "SELECT e.nombre, e.sueldo FROM Empleado e";

TypedQuery<Object[]> query = em.createQuery(FIND_NOMBRE_SUELDO, Object[].class);

List<Object[]> tuplas = query.getResultList();

if (tuplas != null) {
    for (Object[] tupla : tuplas) {
        String nombre = (String) tupla[0];
        Double sueldo = (Double) tupla[1];
        System.out.println(nombre + " - " + sueldo);
    }
}

5.3.4. Joins entre entidades

Al igual que en SQL es posible definir consultas que realicen una selección en el resultado de unir (join) entidades entre las que se ha establecido una relación a-muchos. Es posible hacerlo sin necesidad de escribir explícitamente la sentencia JOIN. Por ejemplo, el siguiente código busca los gastos de todos los empleados del departamento DCCIA y devuelve el String concepto:

SELECT f.concepto
   FROM Empleado e, FacturaGasto f
   WHERE e = f.empleado AND
         e.departamento.nombre='DCCIA'

Código Java:

final String FIND_GASTOS_DCCIA = "SELECT f.concepto" +
         "   FROM Empleado e, FacturaGasto f" +
         "   WHERE e = f.empleado AND" +
         "         e.departamento.nombre='DCCIA'";

TypedQuery<String> query = em.createQuery(FIND_GASTOS_DCCIA, String.class);

List<String> conceptos = query.getResultList();

if (conceptos != null) {
    for (String concepto : conceptos) {
        System.out.println(concepto);
    }
}

También es posible especificar joins en la cláusula FROM utilizando el operador JOIN. Una ventaja de este operador es que el join puede especificarse en términos de la propia asociación y que el motor de consultas proporcionará automáticamente el criterio de join necesario cuando genere el SQL. El siguiente código muestra la misma consulta reescrita para utilizar este operador:

SELECT f.concepto
   FROM Empleado e JOIN e.gastos f
   WHERE e.departamento.nombre='DCCIA'

Para especificar el JOIN hay que indicar la entidad que tiene la relación a-muchos, el atributo que define la relación a-muchos y el alias que instancia los registros de la otra entidad.

JPQL soporta múltiples tipos de joins, incluyendo inner y outer joins, left joins y una técnica denominada fetch joins para cargar datos asociados a las entidades que no se devuelven directamente. No tenemos tiempo de detallar todos ellos. Vamos a ver algunos ejemplos más, para tener una idea de la potencia de la sintaxis.

Selecciona todos los departamentos distintos asociados a empleados (sin el operador JOIN):

SELECT DISTINCT e.departamento FROM Empleado e

Otra forma de hacer la misma consulta utilizando el operador JOIN:

SELECT DISTINCT d FROM Empleado e JOIN e.departamento d

Selecciona los proyectos distintos que pertenecen a empleados del departamento DLSI:

SELECT DISTINCT p
   FROM Departamento d JOIN d.empleados e JOIN e.proyectos p
   WHERE d.nombre='DLSI'

Otro ejemplo algo más complicado. Selecciona los departamentos en el campus UA en donde trabajan empleados que participan en el proyecto Reconocimiento de caras:

SELECT DISTINCT d
   FROM Empleado e JOIN e.departamento d JOIN e.proyectos p
   WHERE d.direccion.campus='UA' AND
   	 p.nombre='Reconocimiento de caras'

Si cambiáramos en Empleado el tipo de recuperación de departamento a LAZY, la siguiente query recuperaría forzaría la recuperación:

SELECT e FROM Empleado e JOIN FETCH e.departamento

Podríamos comprobar el funcionamiento de este último ejemplo con el siguiente código.

En la clase Empleado:

@Entity
public class Empleado {
   ...
   @ManyToOne(fetch = FetchType.LAZY)
   private Departamento departamento;
   ...
}

El código que ejecuta la consulta:

private static final String JOIN_FETCH =
                "SELECT e FROM Empleado e JOIN FETCH e.departamento";

query = em.createQuery(JOIN_FETCH);

List<Empleado> empleados = query.getResultList();

em.close(); (1)

if (empleados != null) {
   for (Empleado empleado : empleados) {
      System.out.println(empleado.getDepartamento().getDireccion().getEdificio());
   }
}

1	Se cierra el entity manager para comprobar que se han cargado la relación `LAZY` definida con `Departamento` y que se recupera en `empleado.getDepartamento()`

5.3.5. Paginación de resultados

Es posible realizar un paginado de los resultados, definiendo un número máximo de instancias a devolver en la consulta y un número de instancia a partir del que se construye la lista:

Query q = em.createQuery("SELECT e FROM Empleado e");
q.setFirstResult(20);
q.setMaxResults(10);
List empleados = q.getResultList();

5.3.6. Subqueries

Es posible anidar múltiples queries. Podemos utilizar el operador IN para obtener las instancias que se encuentran en el resultado de la subquery. Por ejemplo, la siguiente expresión devuelve los empleados que participan en proyectos de tipo 'A'.

SELECT e FROM Empleado e
WHERE e.proyecto IN (SELECT p
   FROM Proyecto p
   WHERE p.tipo = 'A')

5.3.7. Valores nulos y colecciones vacías

Es posible utilizar los operadores IS NULL o IS NOT NULL para chequear si un atributo es o no nulo:

WHERE e.departamento IS NOT NULL

En el caso de colecciones hay que utilizar los operadores IS EMPTY o IS NOT EMPTY:

WHERE e.proyectos IS EMPTY

5.3.8. Funciones

Es posible utilizar llamadas a funciones predefinidas en JPQL. Veamos unos cuantos ejemplos:

CONCAT concatena dos cadenas

CONCAT(string1, string2)

Por ejemplo:

WHERE CONCAT(e.nombre, e.apellido) LIKE 'Ju%cia'

SUBSTRING extra una subcadena
```
SUBSTRING(string, position, length)
```
LOWER devuelve los caracteres de una cadena convertidos en minúsculas
```
LOWER(string)
```
UPPER devuelve los caracteres de una cadena convertidos en mayúsculas
```
UPPER(string)
```
LENGTH devuelve la longitud de una cadena
```
LENGTH(string)
```
SIZE devuelve el tamaño de una colección

WHERE SIZE (e.proyectos) > 4

CURRENT_TIME, CURRENT_DATE, CURRENT_TIMESTAMP devuelven el tiempo del momento actual

5.3.9. Pattern matching

Veamos el conjunto de funciones y operadores que permiten introducir _pattern matching_ sobre campos de texto en las búsquedas.

LIKE

El operador LIKE permite buscar un patrón en un texto y comprueba si una cadena especificada cumple un patrón especificado. Si se precede con el operador NOT devuelve aquellos valores que no cumplen el patrón. El patrón puede incluir cualquier carácter y los siguientes caracteres libres:

El carácter tanto por ciento (%) empareja con 0 o más caracteres cualesquiera
El carácter subrayado (_) empareja un único carácter cualquiera

El operando izquierdo es siempre la cadena que se comprueba y el derecho el patrón. Ejemplos:

c.name LIKE '_r%' es TRUE para 'Brasil' and FALSE for 'Dinamarca'
c.name LIKE '%' es siempre TRUE
c.name NOT LIKE '%' es siempre FALSE

Para emparejar un carácter subrayado o tanto por ciento tiene que ir precedido por un carácter de escape. Por ejemplo:

'100%' LIKE '%\%' ESCAPE '\' se evalua a TRUE.
'100' LIKE '%\%' ESCAPE '\' se evalua a FALSE.

En las expresiones de arriba sólo el primer carácter tanto por ciento (%) representa un carácter libre. El segundo (que aparece tras el carácter de escape) representa un carácter % real %.

LOCATE

El operador LOCATE(str, substr [, start]) busca una subcadena y devuelve su posición (comenzando a contar por 1). El tercer argumento, si está presente, especifica la posición a partir de la que comienza la búsqueda. Se devuelve 0 si no se encuentra la subcadena. Por ejemplo:

LOCATE('India', 'a') devuelve 5
LOCATE('Alicante', 'a', 3) devuelve 5
LOCATE('Mejico', 'a') devuelve 0

TRIM

El operador TRIM([[LEADING|TRAILING|BOTH] [char] FROM] str) devuelve una cadena después de haber eliminado los caracteres del comienzo (LEADING), del final (TRAILING) o ambos (BOTH). Si no se especifica el carácter, se considera el espacio en blanco. Ejemplos:

TRIM(' UK ') devuelve 'UK'
TRIM(LEADING FROM ' UK ') devuelve 'UK '
TRIM(TRAILING FROM ' UK ') devuelve ' UK'
TRIM(BOTH FROM ' UK ') devuelve 'UK'
TRIM('A' FROM 'ARGENTINA') devuelve 'RGENTIN'

SUBSTRING

El operador SUBSTRING(str, pos [, long]) devuelve una subcadena de una cadena específica a partir de la posición pos y con el número de caracteres long (opcional). Si no se especifica el número de caracteres se devuelve la cadena completa. Las posiciones comienzan en 1, a diferencia de Java en donde el comienzo de la cadena viene indicado por el 0.

SUBSTRING('Italia', 3) devuelve 'aly'
SUBSTRING('Italia', 3, 2) devuelve 'al'

5.3.10. Agrupaciones

JPQL proporciona las siguientes funciones de agrupación: AVG,COUNT,MAX,MIN,SUM que pueden ser aplicadas a un número de valores. El tipo devuelto por las funciones es Long o Double, dependiendo de los valores implicados en la operación.

Por ejemplo, para obtener el mayor sueldo de un conjunto de empleados:

SELECT MAX(e.sueldo)
FROM Empleado e

Para obtener el número de elementos que cumplen una condición:

SELECT COUNT(e)
FROM Empleado e
WHERE ...

Podemos realizar consultas más complicadas utilizando GROUP BY y HAVING. Por ejemplo, podemos obtener los empleados y el número de proyectos en los que participan de la siguiente forma:

SELECT p.empleado, COUNT(p)
FROM Proyecto p
GROUP BY p.empleado

El código para recorrer la lista resultante sería el siguiente:

List result = em.createQuery("SELECT p.empleado, COUNT(p)" +
"FROM Proyecto p GROUP BY p.empleado").getResultList();
Iterator res = result.iterator();
while (res.hasNext()) {
  Object[] tupla = (Object[]) res.next();
  Empleado emp = (Empleado) tupla[0];
  long count = ((Long) tupla[1]).longValue();
  ...
}

La siguiente consulta combina todos los elementos. Primero se filtrarían los resultados con la cláusula WHERE, después los resultados son agrupados y por último se comprueba la cláusula HAVING. Devuelve los empleados que participan en más de 5 proyectos creados entre dos fechas dadas.

SELECT p.empleado, COUNT(p)
FROM Proyecto p
WHERE p.fechaCreacion is BETWEEN :date1 and :date2
GROUP BY p.empleado
HAVING COUNT(p) > 5

5.3.11. Order by

Es posible ordenar la lista resultante de la consulta por alguno de los campos usando la directiva ORDER BY. Un ejemplo de su uso:

public void muestraEmpleadosProyecto(String nombreProyecto) {
   List<Empleado> result = em.createQuery(
                             "SELECT e " +
                             "FROM Proyecto p JOIN p.empleados e " +
                             "WHERE p.nombre = ?1 " +
                             "ORDER BY e.name")
                             .setParameter(1, nombreProyecto)
                             .getResultList();
   for (empleado : result) {
	      System.out.println(empleado.nombre);
   }
}

5.4. API Criteria

El API Criteria de JPA se introdujo en la especificación 2.0 de JPA y permite la definición de consultas dinámicas mediante en tiempo de ejecución de objetos y métodos específicos que permiten configurar la query. Al construirse las queries paso a paso de forma programativa mediante llamadas a interfaces estrictas, es posible detectar errores en su construcción en tiempo de compilación. Esto supone una importante ventaja con respecto a JPQL, en donde la construcción de una query se hace a través del parseo de la cadena con la expresión JPQL y sólo se puede detectar el error en el momento de la ejecución.

Las queries del API criteria son dinámicas y no pueden ser compiladas por lo que no pueden beneficiarse de la pre-compilación realizada por algunos proveedores de JPA.

El API es bastante avanzado y necesitaríamos una sesión completa para introducir sus elementos básicos. Vamos a ver sólo un par de ejemplos muy sencillos para comprobar el aspecto del API.

5.4.1. Ejemplos

Consulta que devuelve todos los empleados:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Empleado> cq = cb.createQuery(Empleado.class);
Root<Empleado> e = cq.from(Empleado.class);
cq.select(e);
query = em.createQuery(cq);
List<Empleado> empleados = query.getResultList();

Consulta que devuelve todos los empleados con sueldo mayor de 2000:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Empleado> cq = cb.createQuery(Empleado.class);
Root<Empleado> e = cq.from(Empleado.class);
cq.select(e);
cq.where(cb.greaterThan(e.get("sueldo"), 2500.0));
Query query = em.createQuery(cq);
List<Empleado> emps = query.getResultList();

5.5. Organización de las queries en DAOs

Una forma de ordenar las queries es incluyéndolas en los DAO que gestionan los distintos tipos de entidades. En cada DAO declararemos las queries relacionadas. Por ejemplo, en el siguiente código de EmpleadoDao se incluyen algunas de las consultas vistas anteriormente.

public class EmpleadoDao extends Dao<Empleado, Long> {
    String FIND_ALL_EMPLEADOS = "SELECT e FROM Empleado e ";

    String FIND_ALL_SUELDOS_EMPLEADOS =
            "SELECT e.sueldo FROM Empleado e";

    String FIND_PROJECTOS_EMPLEADOS_DEPARTAMENTO = "SELECT DISTINCT p" +
            " FROM Departamento d JOIN d.empleados e JOIN e.proyectos p" +
            " WHERE d.nombre='DLSI'";

    public EmpleadoDao(EntityManager em) {
        super(em);
    }

    @Override
    public Empleado find(Long id) {
        EntityManager em = this.getEntityManager();
        return em.find(Empleado.class, id);
    }

    public List<Empleado> listAllEmpleados() {
        EntityManager em = this.getEntityManager();
        TypedQuery<Empleado> query = em.createQuery(FIND_ALL_EMPLEADOS, Empleado.class);
        return query.getResultList();
    }

    public List<Double> listAllSueldosEmpleados() {
        EntityManager em = this.getEntityManager();
        TypedQuery<Double> query = em.createQuery(FIND_ALL_SUELDOS_EMPLEADOS, Double.class);
        return query.getResultList();
    }

    public List<Proyecto> listAllProyectosEmpleadosDepartamento(String nombreDepto) {
        EntityManager em = this.getEntityManager();
        TypedQuery<Proyecto> query = em.createQuery(FIND_PROJECTOS_EMPLEADOS_DEPARTAMENTO, Proyecto.class);
        return query.getResultList();
    }
}

5.6. Ejercicios

Vamos a trabajar con el proyecto filmoteca. Define las siguientes consultas en PeliculaDao y ActorDao. Para cada una de ellas escribe un programa main que compruebe su funcionamiento. Cada consulta se puntúa con 0,25 puntos.

5.6.1. (0,75 puntos) Definición de consultas en PeliculaDao

Consulta 1: List<Pelicula> peliculaPorNombre(String nombre), que tiene como parámetro el nombre de una película y devuelve una lista de películas que contienen ese nombre.
Consulta 2: Double puntuacionMediaCriticas(Long idPeliculas), que tiene como parámetro el identificador de la película y devuelve la puntuación media de todas las críticas de esa película.
Consulta 3: List<NumActoresPelicula> peliculasNumActores(), sin ningún parámetro que devuelve una colección de NumActoresPelicula (un objeto que contiene un identificador de película, un nombre de película y un entero que representa el número de actores que intervienen en la película), ordenado de mayor número de actores a menor. La clase NumActoresPelicula está definida en el mismo paquete que el Dao.

5.6.2. (0,75 puntos) Definición de consultas en ActorDao

Consulta 1: List<Actor> actorPorNombre(String nombre), que tiene como parámetro el nombre de un actor y devuelve el una lista de actores que contienen ese nombre.
Consulta 2: List<Pelicula> peliculasConActor(Long idActor), que tiene como parámetro el identificador de un actor y devuelve todas las películas en las que participa.
Consulta 3: List<Critica> criticasActor(Long idActor), que tiene como parámetro el identificador de un actor y devuelve todas las críticas de las películas en las que aparece el actor.
Consulta 4: List<Actor> actoresConMasPeliculasDe(int n), que tiene como parámetro un número y devuelve una colección con todos los actores que han participado en más de n películas.

Frameworks de persistencia - JPA