Spring Data provides many ways to define a query that we can execute.

One of these is the @Query annotation.

In this tutorial, we’ll demonstrate how to use the @Query annotation in
Spring Data JPA to execute both JPQL and native SQL queries.

Spring Data JPA @Query We’ll also show how to build a dynamic query when the @Query
annotation is not enough.

Further reading:
Derived Query Methods in Spring Data JPA
Repositories (/?post_type=post&p=53886)
Explore the query derivation mechanism in Spring Data JPA.

Read more (/?post_type=post&p=53886) →

Last updated: April 12, 2024 Spring Data JPA @Modifying Annotation (/?
post_type=post&p=50307)
Create DML and DDL queries in Spring Data JPA by combining the
Written by: baeldung (https://www.baeldung.com/author/baeldung) @Query and @Modifying annotations

Read more (/?post_type=post&p=50307) →

Reviewed by: Kevin Gilmore (https://www.baeldung.com/editor/kevin-

author)
2. Select Query
Spring Data (https://www.baeldung.com/category/persistence/
spring-persistence/spring-data)
In order to define SQL to execute for a Spring Data repository method, we
JPQL (https://www.baeldung.com/tag/jpql) can annotate the method with the @Query annotation — its value
attribute contains the JPQL or SQL to execute.
Spring Data JPA (https://www.baeldung.com/tag/spring-data-jpa)
The @Query annotation takes precedence over named queries, which are
SQL (https://www.baeldung.com/tag/sql) annotated with @NamedQuery or defined in an orm.xml file.
ADVERTISING

1. Overview
 We can pass an additional parameter of type Sort to a Spring Data
method declaration that has the @Query annotation. It’ll be translated into
the ORDER BY clause that gets passed to the database.
ADVERTISING

It’s a good approach to place a query definition just above the method
inside the repository rather than inside our domain model as named
queries. The repository is responsible for persistence, so it’s a better place
to store these definitions.

2.1. JPQL

By default, the query definition uses JPQL.

Let’s look at a simple repository method that returns active User entities
from the database:

@Query("SELECT u FROM User u WHERE u.status = 1")

Collection<User> findAllActiveUsers();

2.2. Native

We can use also native SQL to define our query. All we have to do is set (https://ads.freestar.com/?
the value of the nativeQuery attribute to true and define the native SQL utm_campaign=branding&utm_medium=banner&utm_source=baeldung.com&utm_co
query in the value attribute of the annotation: 3.1. Sorting for JPA Provided and Derived Methods
@Query(
For the methods we get out of the box such as findAll(Sort) or the ones
value = "SELECT * FROM USERS u WHERE u.status = 1",
nativeQuery = true)
that are generated by parsing method signatures, we can only use object
Collection<User> findAllActiveUsersNative(); properties to define our sort:

userRepository.findAll(Sort.by(Sort.Direction.ASC, "name"));

3. Define Order in a Query Now imagine that we want to sort by the length of a name property:
 It’s crucial that we use JpaSort.unsafe() to create a Sort object instance.
userRepository.findAll(Sort.by("LENGTH(name)"));
When we use:
When we execute the above code, we’ll receive an exception:
Sort.by("LENGTH(name)");

org.springframework.data.mapping.PropertyReferenceException: No
property LENGTH(name) found for type User! then we’ll receive the same exception as we saw above for the findAll()
method.
When Spring Data discovers the unsafe Sort order for a method that uses
3.2. JPQL 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.
When we use JPQL for a query definition, then Spring Data can handle
sorting without any problem — all we have to do is add a method
parameter of type Sort:
3.3. Native
@Query(value = "SELECT u FROM User u")
List<User> findAllUsers(Sort sort); When the @Query annotation uses native SQL, then it’s not possible to
define a Sort.
We can call this method and pass a Sort parameter, which will order the If we do, we’ll receive an exception:
result by the name property of the User object:
org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodEx
ception: 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.

(https://ads.freestar.com/?
n=branding&utm_medium=lazyLoad&utm_source=baeldung.com&utm_content=baeldung_leaderboard_mid_3
userRepository.findAllUsers(Sort.by("name"));

And because we used the @Query annotation, we can use the same
method to get the sorted list of Users by the length of their names:
(https://ads.freestar.com/?
userRepository.findAllUsers(JpaSort.unsafe("LENGTH(name)")); However, there is a workaround that enables pagination, and we’ll cover it
utm_campaign=branding&utm_medium=banner&utm_source=baeldung.com&utm_content=baeldung_inc
in the next section.
class DTO {
private Long customer_id;
private Long order_id;
private Long product_id;
4. Joining Tables in a Query
public DTO(Long customer_id, Long order_id, Long product_id) {
this.customer_id = customer_id;
When we use join clauses in queries in the @Query annotation, there are this.order_id = order_id;
this.product_id = product_id;
multiple ways to define the select clause when handling columns from
}
multiple tables. }

@Entity

4.1. JPQL @IdClass(DTO.class)

public class ResultDTO {
@Id
When using JPQL queries, we need to create projections/DTOs to return private Long customer_id;
the required fields from the joining tables. For example: @Id
private Long order_id;
@Id
@Query(value = "SELECT new ResultDTO(c.id, o.id, p.id, c.name, private Long product_id;
c.email, o.orderDate, p.productName, p.price) " private String customerName;
+ " from Customer c, CustomerOrder o ,Product p " private String customerEmail;
+ " where c.id=o.customer.id " private LocalDate orderDate;
+ " and o.id=p.customerOrder.id " private String productName;
+ " and c.id=?1 ") private Double productPrice;
List<ResultDTO> findResultDTOByCustomer(Long id);
// getters, setters, constructors etc
}
The DTO and ResultDTO classes are defined as follows:

As can be seen, the ResultDTO class has a composite primary key which
is defined in a separate class DTO and referenced via @IdClass annotation.
This is only required if the repository method returns a DTO with Id as
composite keys.

4.2. Native

In the case when we want to use native queries to join tables and fetch
results using Spring Data JPA API, we cannot specify a subset of columns
to be selected for the objects in the select clause, rather, we have to
create repository methods like below:
 @Query(value = "SELECT c.*, o.*, p.* "
5.1. JPQL
+ " from Customer c, CustomerOrder o ,Product p "
+ " where c.id=o.customer_id " Using pagination in the JPQL query definition is straightforward:
+ " and o.id=p.customerOrder_id "
+ " and c.id=?1 "
, nativeQuery = true) @Query(value = "SELECT u FROM User u ORDER BY id")
List<Map<String, Object>> findByCustomer(Long id); Page<User> findAllUsersWithPagination(Pageable pageable);

We can see that the return type of the method findByCustomer is a list of We can pass a PageRequest parameter to get a page of data.
maps where the keys in the map correspond to the column names in the
Pagination is also supported for native queries but requires a little bit of
nativeQuery. The list itself corresponds to the list of objects returned by
additional work.
the method.
In the nativeQuery approach, we do not need to create any DTO classes,
but at the same time, nativeQuery fetches all columns from all the tables 5.2. Native
being joined in the @Query.
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<User> findAllUsersWithPagination(Pageable pageable);

(https://ads.freestar.com/?
paign=branding&utm_medium=lazyLoad&utm_source=baeldung.com&utm_content=baeldung_incontent_2 5.3. Spring Data JPA Versions Prior to 2.0.4

5. Pagination The above solution for native queries works fine for Spring Data JPA
versions 2.0.4 and later.

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.
 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) (https://ads.freestar.com/? (https://ads.freestar.com/?
paign=branding&utm_medium=lazyLoad&utm_source=baeldung.com&utm_content=baeldung_incontent_3
Page<User> findAllUser sWithP agination (Pageable pageable); utm_campaign=branding&utm_medium=lazyLoad&utm_source=baeldung.com&utm_content=baeldung_in
@Query("SELECT u FROM User u WHERE u.status = ?1")
User findUserByStatus(Integer status);

In the above example, we add @Query("SELECT u FROM User u WHERE u.status = ?1 and u.name = ?2")
User findUserByStatusAndName(Integer status, String name);
\n-- #pageable\n

For the above queries, the status method parameter will be assigned to
as the placeholder for the pagination parameter. This tells Spring Data JPA the query parameter with index 1, and the name method parameter will
how to parse the query and inject the pageable parameter. This solution be assigned to the query parameter with index 2.
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. 6.2. Native

Indexed parameters for the native queries work exactly in the same way
as for JPQL:
6. Indexed Query Parameters
@Query(
value = "SELECT * FROM Users u WHERE u.status = ?1",
There are two possible ways that we can pass method parameters to our
nativeQuery = true)
query: indexed and named parameters. User findUserByStatusNative(Integer status);
In this section, we’ll cover indexed parameters.
In the next section, we’ll show a different approach: passing parameters
via name.
6.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
7. Named Parameters
declaration:
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.

7.1. JPQL @Query(value = "SELECT * FROM Users u WHERE u.status = :status and
u.name = :name",
nativeQuery = true)
As mentioned above, we use the @Param annotation in the method
User findUserByStatusA(https:/
ndNameN/ads.freestar.com/?
amedParamsNative(
declaration to match parameters defined by name in JPQL with @Param("status") Integer status, @Param("name") String name);
utm_campaign=branding&utm_medium=lazyLoad&utm_source=baeldung.com&utm_content=baeldung_in
parameters from the method declaration:

@Query("SELECT u FROM User u WHERE u.status = :status and u.name =

:name")
User findUserByStatusAndNameNamedParams( 8. Collection Parameter
@Param("status") Integer status,
@Param("name") String name);
Let’s consider the case when the where clause of our JPQL or SQL query
contains the IN (or NOT IN) keyword:
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
SELECT u FROM User u WHERE u.name IN :names
value strings are the same:

@Query("SELECT u FROM User u WHERE u.status = :status and u.name = In this case, we can define a query method that takes Collection as a
:name") parameter:
User findUserByUserStatusAndUserName(@Param("status") Integer
userStatus,
@Query(value = "SELECT u FROM User u WHERE u.name IN :names")
@Param("name") String userName);
List<User> findUserByNameList(@Param("names") Collection<String>
names);

7.2. Native 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.
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:
9. 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.
 9.1. JPQL 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 (/jpa-
insert):
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(
value =
@Modifying "insert into Users (name, age, email, status) values (:name,
@Query("update User u set u.status = :status where u.name = :name") :age, :email, :status)",
int updateUserSetStatusForName(@Param("status") Integer status, nativeQuery = true)
@Param("name") String name); void insertUser(@Param("name") String name, @Param("age") Integer
age,
@Param("status") Integer status, @Param("email") String email);
The return value defines how many rows are updated by the execution of
the query. Both indexed and named parameters can be used inside
update queries.

10. Dynamic Query

9.2. Native
Often, we’ll encounter the need for building SQL statements based on
We can modify the state of the database also with a native query. We just conditions or data sets whose values are only known at runtime. And in
need to add the @Modifying annotation: those cases, we can’t just use a static query.

10.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%'
(https://ads.freestar.com/?
paign=branding&utm_medium=lazyLoad&utm_source=baeldung.com&utm_content=baeldung_incontent_6 Since the set is dynamically constructed, we can’t know at compile-time
@Modifying
how many LIKE clauses to add.
@Query(value = "update Users u set u.status = ? where u.name = ?",
nativeQuery = true) In this case, we can’t just use the @Query annotation since we can’t
int updateUserSetStatusForNameNative(Integer status, String name); 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
9.3. Inserts building a dynamic query. Let’s take a look at how to do this.
 10.2. Custom Repositories and the JPA Criteria API public class UserRepositoryCustomImpl implements UserRepositoryCustom
{
Luckily for us, Spring provides a way for extending the base repository
@PersistenceContext
through the use of custom fragment interfaces. We can then link them
private EntityManager entityManager;
together to create a composite repository (/spring-data-composable-
repositories). @Override
public List<User> findUserByEmails(Set<String> emails) {
We’ll start by creating a custom fragment interface: CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<User> query = cb.createQuery(User.class);
Root<User> user = query.from(User.class);

Path<String> emailPath = user.get("email");

List<Predicate> predicates = new ArrayList<>();

for (String email : emails) {
predicates.add(cb.like(emailPath, email));
}
query.select(user)
.where(cb.or(predicates.toArray(n
new
Predicate[predicates.size()])));

return entityManager.createQuery(query)
.getResultList();
(https://ads.freestar.com/? }
paign=branding&utm_medium=lazyLoad&utm_source=baeldung.com&utm_content=baeldung_incontent_7 }
public interface UserRepositoryCustom {
List<User> findUserByEmails(Set<String> emails);
} As shown above, we leveraged the JPA Criteria API (/hibernate-
criteria-queries) to build our dynamic query.
And then we’ll implement it: 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.

10.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<User, Integer>,
UserRepositoryCustom {
// query methods from section 2 - section 7
}

10.4. Using the Repository

And finally, we can call our dynamic query method:

Set<String> 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.

11. Conclusion

In this article, we covered several ways of defining queries in Spring Data

JPA repository methods using the @Query annotation.