Spring Data JPA Custom Queries

The Core Idea: Query Derivation

Instead of you writing the query, Spring Data JPA parses the method name, identifies keywords and entity properties, and constructs the appropriate JPQL (Java Persistence Query Language) query for you.

The general pattern is find...By<PropertyName>. Let's break it down:

• Prefix: The method starts with a verb like find, read, get, query, or count. find is the most common.
• Delimiter: The first By keyword acts as a separator, telling Spring Data that the criteria for the WHERE clause will follow.
• Predicate: The expression after By defines the query conditions. In the simplest case, this is just the name of a property in your entity. The property name must match the field name in your Java entity class (it is case-sensitive and uses camelCase).

The following video provides an excellent introduction to this concept, showing how a simple method name is translated into a SQL query.

So, if we have a Product entity with a name field, we can declare this method in our ProductRepository:

import java.util.List;

public interface ProductRepository extends JpaRepository<Product, Long> {
    List<Product> findByName(String name);
}

When you call this method, Spring Data JPA generates and executes a query equivalent to SELECT p FROM Product p WHERE p.name = ?1.

Building More Expressive Queries

The true power of this feature comes from combining keywords to build more complex queries. You can create sophisticated data retrieval logic without ever writing a line of JPQL.

Let's explore the building blocks.

Combining Conditions

You can combine multiple criteria using the And and Or keywords. The order of the method parameters must match the order of the properties in the method name.

• findByLastnameAndFirstname(String lastname, String firstname)
• findByLastnameOrFirstname(String lastname, String firstname)

Note: When you mix And and Or, And has higher precedence. For findByAgeAndStatusOrRegistrationDate(...), the condition will be (age = ? AND status = ?) OR registrationDate = ?. For complex precedence, it's better to use the @Query annotation, which we'll cover in the next lesson.

Comparison and Matching Keywords

You can use various keywords to specify conditions beyond simple equality. This is where derived queries become truly versatile.

The image below provides a fantastic summary of common keywords and the JPQL they generate.

Here are some of the most useful keywords:

Traversing Relationships

A powerful feature is the ability to query based on properties of a related entity. You simply chain the property names together. For instance, if a Product has a @ManyToOne relationship with a Manufacturer entity, and Manufacturer has a country field, you can do this:

List<Product> findByManufacturerCountry(String country);

Spring Data JPA understands this and will generate a query with the necessary JOIN to filter products by the country of their manufacturer.

To see these more advanced features in action, the next video is an excellent, comprehensive guide.

For an authoritative and detailed explanation of how Spring parses these method names, including how to resolve ambiguity when traversing properties, the official documentation is the best source.

Controlling Query Results

Beyond filtering, you can also control the sorting, limiting, and pagination of your results directly from the method name or by using special parameter types.

Static Sorting

You can specify the sorting order directly in the method name using the OrderBy keyword, followed by the property name and an optional direction (Asc or Desc).

List<Product> findByCategoryOrderByPriceDesc(String category);

This will find all products in a given category and return them sorted by price, from highest to lowest.

Limiting Results

To limit the number of results, you can use the Top or First keywords. They are interchangeable.

List<Product> findTop5ByOrderByPriceDesc(); // Get the 5 most expensive products.
Product findFirstByCategory(String category); // Get the first product found for a category.

Dynamic Sorting and Pagination

For more flexibility, you can pass special Sort and Pageable objects as parameters to your query methods. This allows the client of your code to decide how to sort or paginate the data at runtime.

Page<Product> findByCategory(String category, Pageable pageable);

When you use Pageable, Spring Data JPA not only limits the results to the requested "page" but also automatically executes a second count query to determine the total number of elements, which is returned as part of the Page object.

This video segment demonstrates these result-controlling features.

Best Practices: When to Use Derived Queries

Derived queries are extremely useful, but they are not a silver bullet. Knowing when to use them is key to writing clean and maintainable code.

The main guideline is readability. If the query can be clearly expressed in the method name, it's a great candidate. If the method name becomes excessively long and convoluted, it's a sign that you should use a different approach.

Conclusion

In this lesson, you've learned how to move beyond basic CRUD and write custom queries by simply naming your repository methods according to Spring Data JPA's conventions.

Key Takeaways:

• Spring Data JPA can automatically generate queries from method names in a repository interface (e.g., findBy<PropertyName>).
• You can combine multiple conditions using And and Or.
• A rich set of keywords like LessThan, Containing, and IgnoreCase allows for expressive filtering.
• You can query across entity relationships by chaining property names (e.g., findByManufacturerName).
• Results can be controlled using OrderBy for sorting and Top/First for limiting.
• Derived queries are ideal for simple to moderately complex queries where the method name remains readable.

While derived queries are powerful, they can't handle every scenario. What if you need to perform a complex join, use a subquery, or write a query that is just too complex to be expressed in a method name?

In our next lesson, we will address this by learning how to define custom JPQL or native SQL queries using the @Query annotation. This will give you complete control over the queries your repository executes.