Hello! In our previous lesson, we delved into JDBC, JPA, and Spring Data JPA. We explored how to create Spring Data JPA repositories, enabling us to execute fundamental queries like findAll and findById. Today, we’ll advance our skills by learning how to write intricate custom queries using derived query methods. These methods allow you to perform more complex data retrieval operations with minimal effort.

The concept behind derived query methods is straightforward. For simple queries, the method name in your code delineates the corresponding query. You can define methods in your JpaRepositories, and Spring Boot will automatically provide the implementation. For instance, consider the following repository method to find all items by title:

By analyzing this method name, Spring Data JPA can derive and generate the appropriate implementation for execution at runtime.

Derived query methods follow a specific structure: <introducer>By<criteria><and | or>...<criteria><and | or>. Spring Data JPA supports introducers such as find, read, query, count, and get. It's important to note that the keywords find, read, query, and get are interchangeable and perform the same function. For example, consider the following method definitions:

Each of these methods uses an introducer, followed by a conditional criterion.

There are various types of conditions you can use in derived queries:

• Equality Conditions
• Similarity Conditions
• Comparison Conditions

These types help define the nature of the query, ranging from matching exact values to comparing ranges or partial matches.

Equality conditions are useful when you need to match exact values or states. Here are a few examples:

Note that List<TodoItem> findByTitleIs(String title); and List<TodoItem> findByTitle(String title); are equivalent.

These methods allow you to search for items based on exact matches, null checks, or boolean values.

For scenarios requiring partial matches, similarity conditions are particularly useful:

These keywords help deal with patterns and substrings, making the queries more flexible.

Comparison conditions are ideal for range-based or comparative queries. Examples include:

These methods allow querying based on numerical and date comparisons.

To refine your searches further, you can combine conditions using 'and' or 'or':

For example, findByTitleOrPriorityAndIsCompleted retrieves TodoItems that either match the given title or have the specified priority and are completed.

These combinations enable more precise queries by allowing multiple criteria.

Derived query methods also support sorting. For instance:

These methods help you retrieve sorted results, making the data easier to process and understand.

In this lesson, you explored the concept of derived query methods in Spring Data JPA, understanding their structure, the condition types available, and how to sort query results. You also examined different condition keywords for equality, similarity, and comparison to write intricate custom queries. Up next, you’ll get to solidify your understanding through hands-on practice exercises, empowering you to write effective and efficient JPA queries.