1. Información general
Spring Data proporciona muchas formas de definir una consulta que podemos ejecutar. Uno de ellos es la anotación @Query .
En este tutorial, demostraremos cómo usar la anotación @Query en Spring Data JPA para ejecutar consultas tanto JPQL como SQL nativas.
También mostraremos cómo crear una consulta dinámica cuando la anotación @Query no sea suficiente.
2. Seleccione Consulta
Para definir SQL para ejecutar para un método de repositorio de Spring Data, podemos anotar el método con la anotación @Query - su atributo de valor contiene el JPQL o SQL para ejecutar.
La anotación @Query tiene prioridad sobre las consultas con nombre, que se anotan con @NamedQuery o se definen en un archivo orm.xml .
Es un buen enfoque colocar una definición de consulta justo encima del método dentro del repositorio en lugar de dentro de nuestro modelo de dominio como consultas con nombre. El repositorio es responsable de la persistencia, por lo que es un mejor lugar para almacenar estas definiciones.
2.1. JPQL
De forma predeterminada, la definición de la consulta utiliza JPQL.
Veamos un método de repositorio simple que devuelve entidades de usuario activas de la base de datos:
@Query("SELECT u FROM User u WHERE u.status = 1") Collection findAllActiveUsers(); 2.2. Nativo
También podemos usar SQL nativo para definir nuestra consulta. Todo lo que tenemos que hacer es establecer el valor del atributo nativeQuery en verdadero y definir la consulta SQL nativa en el atributo de valor de la anotación:
@Query( value = "SELECT * FROM USERS u WHERE u.status = 1", nativeQuery = true) Collection findAllActiveUsersNative(); 3. Definir orden en una consulta
Podemos pasar un parámetro adicional de tipo Sort a una declaración de método Spring Data que tenga la anotación @Query . Se traducirá en la cláusula ORDER BY que se pasa a la base de datos.
3.1. Clasificación por métodos proporcionados y derivados de JPA
Para los métodos que obtenemos de la caja, como findAll (Sort) o los que se generan al analizar las firmas de métodos, solo podemos usar las propiedades del objeto para definir nuestra clasificación :
userRepository.findAll(new Sort(Sort.Direction.ASC, "name")); Ahora imagine que queremos ordenar por la longitud de una propiedad de nombre:
userRepository.findAll(new Sort("LENGTH(name)")); Cuando ejecutamos el código anterior, recibiremos una excepción:
org.springframework.data.mapping.PropertyReferenceException: ¡No se encontró la propiedad LENGTH (nombre) para el tipo User!
3.2. JPQL
Cuando usamos JPQL para una definición de consulta, Spring Data puede manejar la clasificación sin ningún problema ; todo lo que tenemos que hacer es agregar un parámetro de método de tipo Ordenar :
@Query(value = "SELECT u FROM User u") List findAllUsers(Sort sort); Podemos llamar a este método y pasar un parámetro Sort , que ordenará el resultado por la propiedad de nombre del objeto Usuario :
userRepository.findAllUsers(new Sort("name"));Y debido a que usamos la anotación @Query , podemos usar el mismo método para obtener la lista ordenada de Usuarios por la longitud de sus nombres:
userRepository.findAllUsers(JpaSort.unsafe("LENGTH(name)")); Es crucial que usemos JpaSort.unsafe () para crear una instancia de objeto Sort .
Cuando usamos:
new Sort("LENGTH(name)"); entonces recibiremos exactamente la misma excepción que vimos arriba para el método findAll () .
When Spring Data discovers the unsafe Sort order for a method that uses the @Query annotation, then it just appends the sort clause to the query — it skips checking whether the property to sort by belongs to the domain model.
3.3. Native
When the @Query annotation uses native SQL, then it's not possible to define a Sort.
If we do, we'll receive an exception:
org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting and/or pagination
As the exception says, the sort isn't supported for native queries. The error message gives us a hint that pagination will cause an exception too.
However, there is a workaround that enables pagination, and we'll cover it in the next section.
4. Pagination
Pagination allows us to return just a subset of a whole result in a Page. This is useful, for example, when navigating through several pages of data on a web page.
Another advantage of pagination is that the amount of data sent from server to client is minimized. By sending smaller pieces of data, we can generally see an improvement in performance.
4.1. JPQL
Using pagination in the JPQL query definition is straightforward:
@Query(value = "SELECT u FROM User u ORDER BY id") Page findAllUsersWithPagination(Pageable pageable); We can pass a PageRequest parameter to get a page of data.
Pagination is also supported for native queries but requires a little bit of additional work.
4.2. Native
We can enable pagination for native queries by declaring an additional attribute countQuery.
This defines the SQL to execute to count the number of rows in the whole result:
@Query( value = "SELECT * FROM Users ORDER BY id", countQuery = "SELECT count(*) FROM Users", nativeQuery = true) Page findAllUsersWithPagination(Pageable pageable);4.3. Spring Data JPA Versions Prior to 2.0.4
The above solution for native queries works fine for Spring Data JPA versions 2.0.4 and later.
Prior to that version, when we try to execute such a query, we'll receive the same exception we described in the previous section on sorting.
We can overcome this by adding an additional parameter for pagination inside our query:
@Query( value = "SELECT * FROM Users ORDER BY id \n-- #pageable\n", countQuery = "SELECT count(*) FROM Users", nativeQuery = true) Page findAllUsersWithPagination(Pageable pageable);In the above example, we add “\n– #pageable\n” as the placeholder for the pagination parameter. This tells Spring Data JPA how to parse the query and inject the pageable parameter. This solution works for the H2 database.
We've covered how to create simple select queries via JPQL and native SQL. Next, we'll show how to define additional parameters.
5. Indexed Query Parameters
There are two possible ways that we can pass method parameters to our query: indexed and named parameters.
In this section, we'll cover indexed parameters.
5.1. JPQL
For indexed parameters in JPQL, Spring Data will pass method parameters to the query in the same order they appear in the method declaration:
@Query("SELECT u FROM User u WHERE u.status = ?1") User findUserByStatus(Integer status); @Query("SELECT u FROM User u WHERE u.status = ?1 and u.name = ?2") User findUserByStatusAndName(Integer status, String name); For the above queries, the status method parameter will be assigned to the query parameter with index 1, and the name method parameter will be assigned to the query parameter with index 2.
5.2. Native
Indexed parameters for the native queries work exactly in the same way as for JPQL:
@Query( value = "SELECT * FROM Users u WHERE u.status = ?1", nativeQuery = true) User findUserByStatusNative(Integer status);In the next section, we'll show a different approach: passing parameters via name.
6. Named Parameters
We can also pass method parameters to the query using named parameters. We define these using the @Param annotation inside our repository method declaration.
Each parameter annotated with @Param must have a value string matching the corresponding JPQL or SQL query parameter name. A query with named parameters is easier to read and is less error-prone in case the query needs to be refactored.
6.1. JPQL
As mentioned above, we use the @Param annotation in the method declaration to match parameters defined by name in JPQL with parameters from the method declaration:
@Query("SELECT u FROM User u WHERE u.status = :status and u.name = :name") User findUserByStatusAndNameNamedParams( @Param("status") Integer status, @Param("name") String name); Note that in the above example, we defined our SQL query and method parameters to have the same names, but it's not required as long as the value strings are the same:
@Query("SELECT u FROM User u WHERE u.status = :status and u.name = :name") User findUserByUserStatusAndUserName(@Param("status") Integer userStatus, @Param("name") String userName); 6.2. Native
For the native query definition, there is no difference in how we pass a parameter via the name to the query in comparison to JPQL — we use the @Param annotation:
@Query(value = "SELECT * FROM Users u WHERE u.status = :status and u.name = :name", nativeQuery = true) User findUserByStatusAndNameNamedParamsNative( @Param("status") Integer status, @Param("name") String name);7. Collection Parameter
Let's consider the case when the where clause of our JPQL or SQL query contains the IN (or NOT IN) keyword:
SELECT u FROM User u WHERE u.name IN :namesIn this case, we can define a query method that takes Collection as a parameter:
@Query(value = "SELECT u FROM User u WHERE u.name IN :names") List findUserByNameList(@Param("names") Collection names);As the parameter is a Collection, it can be used with List, HashSet, etc.
Next, we'll show how to modify data with the @Modifying annotation.
8. Update Queries With @Modifying
We can use the @Query annotation to modify the state of the database by also adding the @Modifying annotation to the repository method.
8.1. JPQL
The repository method that modifies the data has two differences in comparison to the select query — it has the @Modifying annotation and, of course, the JPQL query uses update instead of select:
@Modifying @Query("update User u set u.status = :status where u.name = :name") int updateUserSetStatusForName(@Param("status") Integer status, @Param("name") String name); The return value defines how many rows the execution of the query updated. Both indexed and named parameters can be used inside update queries.
8.2. Native
We can modify the state of the database also with a native query. We just need to add the @Modifying annotation:
@Modifying @Query(value = "update Users u set u.status = ? where u.name = ?", nativeQuery = true) int updateUserSetStatusForNameNative(Integer status, String name);8.3. Inserts
To perform an insert operation, we have to both apply @Modifying and use a native query since INSERT is not a part of the JPA interface:
@Modifying @Query( value = "insert into Users (name, age, email, status) values (:name, :age, :email, :status)", nativeQuery = true) void insertUser(@Param("name") String name, @Param("age") Integer age, @Param("status") Integer status, @Param("email") String email);9. Dynamic Query
Often, we'll encounter the need for building SQL statements based on conditions or data sets whose values are only known at runtime. And in those cases, we can't just use a static query.
9.1. Example of a Dynamic Query
For example, let's imagine a situation where we need to select all the users whose email is LIKE one from a set defined at runtime — email1, email2, …, emailn:
SELECT u FROM User u WHERE u.email LIKE '%email1%' or u.email LIKE '%email2%' ... or u.email LIKE '%emailn%'Since the set is dynamically constructed, we can't know at compile-time how many LIKE clauses to add.
In this case, we can't just use the @Query annotation since we can't provide a static SQL statement.
Instead, by implementing a custom composite repository, we can extend the base JpaRepository functionality and provide our own logic for building a dynamic query. Let's take a look at how to do this.
9.2. Custom Repositories and the JPA Criteria API
Luckily for us, Spring provides a way for extending the base repository through the use of custom fragment interfaces. We can then link them together to create a composite repository.
We'll start by creating a custom fragment interface:
public interface UserRepositoryCustom { List findUserByEmails(Set emails); }And then we'll implement it:
public class UserRepositoryCustomImpl implements UserRepositoryCustom { @PersistenceContext private EntityManager entityManager; @Override public List findUserByEmails(Set emails) { CriteriaBuilder cb = entityManager.getCriteriaBuilder(); CriteriaQuery query = cb.createQuery(User.class); Root user = query.from(User.class); Path emailPath = user.get("email"); List predicates = new ArrayList(); for (String email : emails) { predicates.add(cb.like(emailPath, email)); } query.select(user) .where(cb.or(predicates.toArray(new Predicate[predicates.size()]))); return entityManager.createQuery(query) .getResultList(); } }As shown above, we leveraged the JPA Criteria API to build our dynamic query.
Also, we need to make sure to include the Impl postfix in the class name. Spring will search the UserRepositoryCustom implementation as UserRepositoryCustomImpl. Since fragments are not repositories by themselves, Spring relies on this mechanism to find the fragment implementation.
9.3. Extending the Existing Repository
Notice that all the query methods from section 2 through section 7 are in the UserRepository.
So, now we'll integrate our fragment by extending the new interface in the UserRepository:
public interface UserRepository extends JpaRepository, UserRepositoryCustom { // query methods from section 2 - section 7 }9.4. Using the Repository
And finally, we can call our dynamic query method:
Set emails = new HashSet(); // filling the set with any number of items userRepository.findUserByEmails(emails); We've successfully created a composite repository and called our custom method.
10. Conclusion
En este artículo, cubrimos varias formas de definir consultas en los métodos del repositorio Spring Data JPA utilizando la anotación @Query .
También aprendimos cómo implementar un repositorio personalizado y crear una consulta dinámica.
Como siempre, los ejemplos de código completos utilizados en este artículo están disponibles en GitHub.