Spring Data JPA @Query | Code Factory

1. Overview

Spring Data provides many ways to define a query that we can execute. One of these is the @Query annotation.

2. Select Query

In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute.

2.1. JPQL

By default the query definition uses JPQL.

@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 to set the value of the nativeQuery attribute to true and define the native SQL query in the value attribute of the annotation:

@Query(
value = "SELECT * FROM USERS u WHERE u.status = 1",
nativeQuery = true)
Collection<User> findAllActiveUsersNative();

3. Define Order in a Query

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.

3.1. Sorting for JPA Provided and Derived Methods

For the methods we get out-of-the-box like findAll(Sort) or the ones that are generated by parsing method signatures, we can only use object properties to define our sort:

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

3.2. JPQL

When we use JPQL for a query definition, then Spring Data can handle sorting without any problem — all we have to do is to add a method parameter of type Sort:

@Query(value = "SELECT u FROM User u")
List<User> findAllUsers(Sort sort);
userRepository.findAllUsers(new Sort("name"));
userRepository.findAllUsers(JpaSort.unsafe("LENGTH(name)"));
new Sort("LENGTH(name)");

3.3. Native

When the @Query annotation uses native SQL, then it’s not possible to define a Sort.

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.

4.1. JPQL

Using pagination in the JPQL query definition is straightforward:

@Query(value = "SELECT u FROM User u ORDER BY id")
Page<User> findAllUsersWithPagination(Pageable pageable);

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<User> 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 version 2.0.4 and later.

@Query(
value = "SELECT * FROM Users ORDER BY id \n-- #pageable\n",
countQuery = "SELECT count(*) FROM Users",
nativeQuery = true)
Page<User> findAllUsersWithPagination(Pageable pageable);

5. Indexed Query Parameters

There are two possible ways that we can pass method parameters to our query. 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);

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);

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.

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);
@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 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 :names
@Query(value = "SELECT u FROM User u WHERE u.name IN :names")
List<User> findUserByNameList(@Param("names") Collection<String> names);

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 difference 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);

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

@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 times, 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%'

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.

public interface UserRepositoryCustom {
List<User> findUserByEmails(Set<String> emails);
}
public class UserRepositoryCustomImpl implements UserRepositoryCustom {

@PersistenceContext
private EntityManager entityManager;

@Override
public List<User> findUserByEmails(Set<String> emails) {
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(new Predicate[predicates.size()])));

return entityManager.createQuery(query)
.getResultList();
}
}

9.3. Extending the Existing Repository

Notice that all the query methods from section 2 — 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
}

9.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);

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store