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:
Java1package com.codesignal.repositories; 2 3import com.codesignal.entities.TodoItem; 4import org.springframework.data.jpa.repository.JpaRepository; 5import java.util.List; 6 7public interface TodoItemRepository extends JpaRepository<TodoItem, Long> { 8 List<TodoItem> findByTitleContaining(String title); 9}
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:
Java1List<TodoItem> findByTitle(String title); 2List<TodoItem> readByTitleContaining(String title); 3long countByIsCompleted(boolean isCompleted);
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:
Java1List<TodoItem> findByTitle(String title); 2List<TodoItem> findByTitleIs(String title); 3List<TodoItem> findByTitleEquals(String title); 4List<TodoItem> findByTitleIsNot(String title); 5List<TodoItem> findByTitleIsNull(); 6List<TodoItem> findByTitleIsNotNull(); 7List<TodoItem> findByIsCompletedTrue(); 8List<TodoItem> findByIsCompletedFalse();
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:
Java1List<TodoItem> findByTitleStartingWith(String prefix); 2List<TodoItem> findByTitleEndingWith(String suffix); 3List<TodoItem> findByTitleContaining(String substring); 4List<TodoItem> findByTitleLike(String pattern);
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:
Java1List<TodoItem> findByPriorityLessThan(Integer priority); 2List<TodoItem> findByPriorityLessThanEqual(Integer priority); 3List<TodoItem> findByPriorityGreaterThan(Integer priority); 4List<TodoItem> findByPriorityGreaterThanEqual(Integer priority); 5List<TodoItem> findByPriorityBetween(Integer startPriority, Integer endPriority); 6List<TodoItem> findByPriorityIn(Collection<Integer> priorities); 7List<TodoItem> findByDueDateAfter(LocalDateTime dueDate); 8List<TodoItem> findByDueDateBefore(LocalDateTime dueDate);
These methods allow querying based on numerical and date comparisons.
To refine your searches further, you can combine conditions using 'and' or 'or':
Java1List<TodoItem> findByTitleOrPriority(String title, Integer priority); 2List<TodoItem> findByTitleOrPriorityAndIsCompleted(String title, Integer priority, Boolean isCompleted);
For example, findByTitleOrPriorityAndIsCompleted
retrieves TodoItem
s 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:
Java1List<TodoItem> findByTitleOrderByTitle(String title); 2List<TodoItem> findByTitleOrderByTitleAsc(String title); 3List<TodoItem> findByTitleOrderByTitleDesc(String title);
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.