Finding the Max Value in Spring Data JPA

1. Introduction

When using Spring Data JPA, finding specific values in our database is a common task. One such task is finding the maximum value in a particular column.

In this tutorial, we’ll explore several ways to achieve this using Spring Data JPA. We’ll check how to use repository methods, JPQL and native queries, and the Criteria API to find the maximum value in a database column.

2. Entity Example

Before we move on, we have to add a required spring-boot-starter-data-jpa dependency to our project:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

After that, let’s define a simple entity to work with:

@Entity
public class Employee {
    @Id
    @GeneratedValue
    private Integer id;
    private String name;
    private Long salary;

    // constructors, getters, setters, equals, hashcode
}

In the following examples, we’ll find the maximum value of the salary column across all employees using different approaches.

3. Using Derived Queries in a Repository

Spring Data JPA provides a powerful mechanism to define custom queries using repository methods. One of these mechanisms is derived queries, which allow us to implement SQL queries by declaring the method name.

Let’s create a repository for the Employee class:

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
    Optional<Employee> findTopByOrderBySalaryDesc();
}

We just implemented a method that uses the query derivation mechanism to generate the appropriate SQL. According to the method name, we’re sorting all employees by their salary in descending order and then returning the first one, which is the employee with the highest salary.

Notably, this approach always returns an entity with all eager properties set. However, if we just want to retrieve a single salary value, we can slightly modify our code by implementing a projection feature.

Let’s create an additional interface and modify the repository:

public interface EmployeeSalary {
    Long getSalary();
}

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
    Optional<EmployeeSalary> findTopSalaryByOrderBySalaryDesc(); 
}

This solution is useful when we need to return only specific parts of the entity.

4. Using JPQL

Another straightforward approach is to use the @Query annotation. This lets us define a custom JPQL (Java Persistence Query Language) query directly in the repository interface.

Let’s implement our JQPL query to retrieve the maximum salary value:

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
    @Query("SELECT MAX(e.salary) FROM Employee e")
    Optional<Long> findTopSalaryJQPL();
}

As before, the method returns the highest salary value among all employees. Moreover, we can easily retrieve a single column of an entity without any additional projections.

5. Using a Native Query

We’ve just introduced the @Query annotation in our repository. This approach also allows us to write raw SQL directly using native queries.

To achieve the same result, we can implement:

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
    @Query(value = "SELECT MAX(salary) FROM Employee", nativeQuery = true)
    Optional<Long> findTopSalaryNative();
}

The solution is similar to JPQL. Using native queries can be useful to leverage specific SQL features or optimizations.

6. Implementing a Default Repository Method

We can also use custom Java code to find the maximum value. Let’s implement another solution without adding additional queries:

public interface EmployeeRepository extends JpaRepository<Employee, Integer> {
    default Optional<Long> findTopSalaryCustomMethod() {
        return findAll().stream()
          .map(Employee::getSalary)
          .max(Comparator.naturalOrder());
    }
}

We extended our repository by adding a new default method with our custom logic. We retrieve all Employee entities using a built-in findAll() method, then stream them and find the maximum salary. Unlike previous approaches, all filtering logic occurs at the application layer, not the database.

7. Using Pagination

Spring Data JPA provides support for pagination and sorting features. We can still use them to find the maximum salary of our employees.

We can reach our goal even without implementing any dedicated query or extending the repository:

public Optional<Long> findTopSalary() {
    return findAll(PageRequest.of(0, 1, Sort.by(Sort.Direction.DESC, "salary")))
      .stream()
      .map(Employee::getSalary)
      .findFirst();
}

As we know, a PagingAndSortingRepository interface provides additional support for Pageable and Sort types. Therefore, our built-in findAll() method in the JpaRepository can also accept these parameters. We just implemented a different approach without adding additional methods in the repository.

8. Using Criteria API

Spring Data JPA also provides the Criteria API – a more programmatic approach to construct queries. It’s a more dynamic and type-safe way to build complex queries without using raw SQL.

First, let’s inject the EntityManager bean into our service and then create a method to find the maximum salary using the Criteria API:

@Service
public class EmployeeMaxValueService {
    @Autowired
    private EntityManager entityManager;
    
    public Optional<Long> findMaxSalaryCriteriaAPI() {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Long> query = cb.createQuery(Long.class);

        Root<Employee> root = query.from(Employee.class);
        query.select(cb.max(root.get("salary")));

        TypedQuery<Long> typedQuery = entityManager.createQuery(query);
        return Optional.ofNullable(typedQuery.getSingleResult());
    }
}

In this method, we first obtain a CriteriaBuilder instance from the injected EntityManager bean. We then create a CriteriaBuilder to specify the result type, and a Root to define the FROM clause. Finally, we select the maximum value of the salary field and execute the query.

Once again, we’ve just retrieved the maximum salary for all employees. However, this approach is more complex than the previous one, so it may be a bit overwhelming if we need to implement a simple query. This solution may be useful if we have more complex structures that cannot be handled by simply expanding the repository.

9. Conclusion

In this article, we’ve explored various methods to find the maximum value of a column using Spring Data JPA.

We started with derived queries, which provide a simple and intuitive way to define queries just by method naming conventions. Then, we looked into using JPQL and native queries with the @Query annotation, offering more flexibility and direct control over the SQL being executed.

We also implemented a custom default method in the repository to leverage Java’s Stream API for processing data at the application level. Additionally, we checked how to use pagination and sorting to find the result using only built-in API.

Finally, we utilized the Criteria API for a more programmatic and type-safe approach to building complex queries. By understanding these different approaches, we can choose the most suitable one for a specific use case, balancing simplicity, control, and performance.