Customizing JPA Repositories using Derived Query Methods

Lesson 2

Introduction

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.

Understanding Derived Query Methods

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:

Java
1package 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.

Structure of Derived Query Methods

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:

Java
1List<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.

Condition Types

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 Condition Keywords

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

Java
1List<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.

Similarity Condition Keywords

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

Java
1List<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 Condition Keywords

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

Java
1List<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.

Multiple Condition Expressions

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

Java
1List<TodoItem> findByTitleOrPriority(String title, Integer priority);
2List<TodoItem> findByTitleOrPriorityAndIsCompleted(String title, Integer priority, Boolean isCompleted);

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.

Sorting the Results

Derived query methods also support sorting. For instance:

Java
1List<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.

Summary

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.

Enjoy this lesson? Now it's time to practice with Cosmo!

Practice is how you turn knowledge into actual skills.