Spring Data JPA Find Max Value

If you want to retrieve the maximum value of a specific column/field using Spring Data JPA, you can make use of the JPA's built-in max function. Let's see how to create a derived query method to find the maximum value.

Create JPA Entity

To demonstrate this example, assume we have the following entity Product with an attributes id, name, and price:

@Entity
public class Product {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private Double price;

    // getters, setters, etc.
}

Create a Repository

To find the maximum price for all products, here is the custom query method:

public interface ProductRepository extends JpaRepository {
    Optional findTopByOrderByPriceDesc();
}

Here:

• findTopBy will limit the result to just one record.
• OrderByPriceDesc will order the products by their price in descending order, so the first product will have the highest price.

Using the Repository in Service Class

@Service
public class ProductService {
    @Autowired
    private ProductRepository productRepository;

    public Double getMaxProductPrice() {
        return productRepository.findTopByOrderByPriceDesc()
                .map(Product::getPrice) // Extract the price from the Product
                .orElse(null);
    }
}

Note that the method findTopByOrderByPriceDesc returns an Optional<Product> to handle the scenario when there are no products in the database. In such a case, the method would return an empty Optional, and the orElse(null) would convert it to a null value. Adjust as necessary based on your application's requirements.

Alternatively, if you're using a more complex query or have specific conditions, you can use the @Query annotation to define custom JPQL or native SQL queries within your repository.

For instance, here is a JPQL query to achieve the same result:

@Query("SELECT p FROM Product p WHERE p.price = (SELECT MAX(p.price) FROM Product p)")
Optional findProductWithMaxPrice();