Spring Data JPA @Query | Code Factory

Reference Link : Link

Donate : Link

1. Overview

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

Also, we’ll show how to build a dynamic query when the @Query annotation is not enough.

2. Select Query

The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm.xml file.

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

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

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

3. Define Order in a Query

3.1. Sorting for JPA Provided and Derived Methods

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

Now imagine that we want to sort by the length of a name property:

userRepository.findAll(new Sort("LENGTH(name)"));

When we execute the above code we’ll receive an exception:

org.springframework.data.mapping.PropertyReferenceException: No property lENGTH(name) found for type User!

3.2. JPQL

@Query(value = "SELECT u FROM User u")
List<User> findAllUsers(Sort sort);

We can call this method and pass a Sort parameter, which will order the result by the name property of the User object:

userRepository.findAllUsers(new Sort("name"));

And because we used @Query annotation, we can use the same method to get the sorted list of Users by the length of their names:

userRepository.findAllUsers(JpaSort.unsafe("LENGTH(name)"));

It’s crucial that we use JpaSort.unsafe() to create a Sort object instance.

When we use:

new Sort("LENGTH(name)");

then we’ll receive exactly the same exception as we saw above for the findAll() method.

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

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.

4. Pagination

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

@Query(value = "SELECT u FROM User u ORDER BY id")
Page<User> 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

@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

Prior to that version, when we try to execute such a query we’ll receive an exception — the same one 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<User> 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.

5. Indexed Query Parameters

5.1. JPQL

@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

@Query(
value = "SELECT * FROM Users u WHERE u.status = ?1",
nativeQuery = true)
User findUserByStatusNative(Integer status);

6. Named Parameters

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

@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

@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

SELECT u FROM User u WHERE u.name IN :names

In this case we can define a query method which takes Collection as a parameter:

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

As the parameter is a Collection it can be used with List, HashSet, etc.

8. Update Queries with @Modifying

8.1. JPQL

@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

@Modifying
@Query(value = "update Users u set u.status = ? where u.name = ?",
nativeQuery = true)
int updateUserSetStatusForNameNative(Integer status, String name);

8.3. Inserts

@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

9.1. Example of a Dynamic Query

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.

9.2. Custom Repositories and the JPA Criteria API

We’ll start by creating a custom fragment interface:

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

And then, we’ll implement it:

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

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

public interface UserRepository extends JpaRepository<User, Integer>, UserRepositoryCustom {
// query methods from section 2 - section 7
}

9.4. Using the Repository

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.