Spring Data JPA. 5. Derived Queries

The time has come to create our first queries. Let’s start with the most straightforward technique. Writing queries with it is a child’s play, and I’ll teach you the rules.

>> Read this post in Spanish here <<

Contents

<4 Transactions

CHAPTERS

Basic Principles

The Concept

Let’s create a repository for the Country entity named CountryDerivedQueryRepository. We’ll declare the query methods for this chapter in that interface. They are the repositories methods that talk with the data storage.

The first method:

@Transactional(readOnly = true)
public interface CountryDerivedQueryRepository extends Repository<Country, Long> {

    List<Country> findByNameContainingOrderByNameAsc(String name);

}

That’s all. The method declaration is enough. But which countries does this method find?

The method’s name answers the question. It meticulously determines the entities we want to fetch, which are of the repository domain class type. As a result, Spring can derive a query from the method name.

These derived queries are easy to write and read, even for a beginner in the Spring Data world. Self-explanatory code is the Holy Grail of clean code.

General Structure

“Okay, Daniel, but you didn’t say what the query method does!”

Let’s find out together by dissecting the method to understand which countries it retrieves. Its name consists of a prefix that sets the query type, followed by the result selection criteria and, optionally, the sorting options. So the method findByNameContainingOrderByNameAsc():

searches for countries (findBy)
whose name field (Name) contains (Containing) a certain string (the parameter String name)
sorts the results (OrderBy) by the name field (Name) in ascending order (Asc)
and collects the entities in a List.

The following picture breaks down the signature of the method.

Notice that the method is written in Camel case format, so the fields’ names must start with a capital letter.

These prefixes denote searches: find...By, read...By, get...By, query...By, search...By, and stream...By. All are interchangeable, so these methods are equivalent:

List<Country> findByNameContainingOrderByNameAsc(String name);
List<Country> readByNameContainingOrderByNameAsc(String name);
List<Country> getByNameContainingOrderByNameAsc(String name);
List<Country> queryByNameContainingOrderByNameAsc(String name);
List<Country> streamByNameContainingOrderByNameAsc(String name);
List<Country> searchByNameContainingOrderByNameAsc(String name);

In the course I’ll use find, the usual practice. Later we’ll discuss prefixes for other purposes.

And what about those three dots in the middle of the prefix? You can write anything you wish on them, except the words Distinct, Top, and First, which we’ll see later. For instance, the following method is identical to any of the previous ones—the inclusion of the word “Countries” between «find» and «By» doesn’t alter the query:

List<Country> findPaisesByNameContainingOrderByNameAsc(String name);

Behind the Magic

What hides behind the overwhelming simplicity of derived queries? We won’t dig into the details—and we don’t need it either. It’s enough to tell that when Spring boots up and creates the proxy beans for each repository, it generates a JPQL query for each method representing a derived query. Later, Hibernate will translate that JPQL statement to the SQL language of the underlying database to execute it.

JPQL stands for “Jakarta Persistence Query Language.” It’s almost identical to SQL. We’ll talk about it in the next chapter.

Safety first! During Spring initialization, if Spring cannot generate the JPQL code for a derived query, it throws an exception of type QueryCreationException that cancels the system startup. This behavior ensures that all derived queries are valid and prevents evil surprises at runtime.

For instance, consider this method:

List<Country> findByNombreContaining(String capital);

Can you spot the problem? Country doesn’t have a field called “nombre”. The exception message describes the issue pretty well:

org.springframework.data.repository.query.QueryCreationException: Could not create query for public abstract java.util.List com.danielme.springdatajpa.repository.query.CountryDerivedQueryRepository.findByNombreContaining(java.lang.String); 
Reason: Failed to create query for method 
public abstract java.util.List com.danielme.springdatajpa.repository.query.CountryDerivedQueryRepository.findByNombreContaining(java.lang.String); 
No property 'nombre' found for type 'Country'

Keywords for Selecting Entities

Development environments help you declare derived queries. In particular, Eclipse IDE with Spring Tools and IntelliJ Ultimate offer code auto-completion for query methods’ names. The screenshot below is from IntelliJ.

Still, you should know the keywords that build the logical expressions for entity selection.

I explain them in this section. I’ll show the keyword, including all its synonyms; a method query, declared in CountryDerivedQueryRepository, that uses the keyword; and the relevant part of the SQL statement for HyperSQL that Hibernate will execute. Although I won’t show them, the methods have tests in the CountryDerivedQueryRepositoryTest class.

Generic Filters

Equals, Is. Equals condition.

“Countries admitted by the UN on a given date”:

List<Country> findByUnitedNationsAdmissionEquals(LocalDate date);

where c1_0.united_nations_admission=?

Is can be placed before any other condition to make the method easier to understand.

No keyword. Equals or Is. This query is the same as the previous one:

List<Country> findByUnitedNationsAdmission(LocalDate date);

Not, IsNot. Inequality.

“Countries not affiliated with a given confederation”:

List<Country> findByConfederationIdNot(Long id);

where c1_0.confederation_id!=?

You can combine Not with some keywords. I’ll show the valid combinations.

After, IsAfter. Filters by later dates (more strict) than indicated.

“Countries that joined the UN after a certain date”:

«Países que ingresaron en la ONU a partir de cierta fecha»:

List<Country> findByUnitedNationsAdmissionAfter(LocalDate date);

where c1_0.united_nations_admission>?

Before, isBefore. Filters by earlier dates (less strict) than indicated.

“Countries that joined the UN before a certain date”:

List<Country> findByUnitedNationsAdmissionBefore(LocalDate date);

where c1_0.united_nations_admission<?

Between, isBetween. JPQL BETWEEN operator. Both bounds of the range are included.

“Countries that joined the UN between two dates”:

List<Country> findByUnitedNationsAdmissionBetween(LocalDate dateMin, LocalDate dateMax);

where c1_0.united_nations_admission between ? and ?

LessThan, isLessThan. Strict less than.

“Countries with a population less than a given”:

List<Country> findByPopulationLessThan(int population);

where c1_0.population<?

Less or equal is LessThanEqual, IsLessThanEqual.

GreaterThan, IsGreaterThan. Greater than strict.

“Countries with a population greater than a given one”:

List<Country> findByPopulationGreaterThan(int population);

where c1_0.population>?

Greater than or equal to is GreaterThanEqual, IsGreaterThanEqual.

Null, IsNull. Imposes the condition that a field must be null.

“Countries that are not members of the UN (those without an admission date)”:

List<Country> findByUnitedNationsAdmissionIsNull();

where c1_0.united_nations_admission is null

NotNull, IsNotNull. The field can’t be null.

“Countries that are members of the UN”:

List<Country> findByUnitedNationsAdmissionIsNotNull();

where c1_0.united_nations_admission is not null

In, IsIn. The logical operator IN. It filters a field based on whether its value matches any value within a set of values. That set is a parameter of type Collection (any subtype), array, or varargs.

“Countries whose soccer confederation is any of the ones provided”:

List<Country> findByConfederationIdIn(Collection<Long> ids);

where c1_0.confederation_id in(?,?)

This method selects the results by evaluating an attribute of a relation: the confederation identifier linked to the country. That is, the id field of the relationship, which is represented by the Country#confederation field. Derived queries allow, therefore, navigation through the relationships. That’s pretty useful.

We negate In with con NotIn, IsNotIn.

True, False, IsTrue, IsFalse. They check the value of a logical property.

“Countries that are not part of the OECD”:

List<Country> findByOcdeFalse();

where not(c1_0.ocde)

Empty, IsEmpty. It applies the JPQL EMPTY operator, which checks whether a multiple relationship (a collection) contains at least one entity. Country doesn’t have this type of relationship, but Confederation does. Thus we can write this method in a repository for Confederation:

“Get the confederations without member countries”:

List<Confederation> findByCountriesIsEmpty();

select c1_0.id,c1_0.name 
from confederations c1_0 
where not exists(select 1 from countries c2_0 where c1_0.id=c2_0.confederation_id)

NotEmpty and IsNotEmpty are valid.

Playing with Strings

Like, IsLike. JPQL LIKE operator.

“All countries with capital name as given”:

List<Country> findByCapitalLike(String capital);

 where c1_0.capital like ?

Denied with NotLike, IsNotLike.

Spring Data sanitizes string query variables. This means that if they include wildcard characters supported by LIKE (%, _, etc.), these characters will be treated as common characters. Accordingly, you’ll see in SQL statements expressions that escape wildcards:

like ? escape ''

How do we search for partial strings if Spring Data escapes %? Check out the following keyword.

Containing, IsContaining, Contains. These keywords apply the LIKE operator by wrapping with the wildcard % the string to search for.

“All countries whose capital name contains a given string”:

«Todos los países cuyo nombre de capital contiene a una cadena dada»:

List<Country> findByCapitalContaining(String capital);

where c1_0.capital like ?

It’s negated with NotContaining, IsNotContaining.

The key of the example is in the input variable for the query:

countryRepository.findByCapitalContaining("City");

binding parameter [1] as [VARCHAR] - [%City%]

Spring already appends the wildcard % to the string, so don’t do this:

countryRepository.findByCapitalLike("%City%");

Spring Data sanitizes the argument and adds % to it; hence this is the variable that the SQL query will receive:

binding parameter [1] as [VARCHAR] - [%%City%%]

StartingWith, StartsWith, IsStartingWith. The string must start with a prefix. This is checked by appending the wildcard % to the string.

“Countries whose name starts with a given string”:

List<Country> findByNameStartingWith(String name);

countryRepository.findByNameStartingWith("The");

where c1_0.name like ?

binding parameter [1] as [VARCHAR] - [The%]

EndingWith, IsEndingWith, EndsWith. The string must end with a given suffix. This is checked by appending the wildcard % to beggining of the string.

“Countries whose capital ends with a given string”:

List<Country> findByCapitalEndingWith(String capital);

countryRepository.findByCapitalEndingWith("City");

where c1_0.capital like ?

binding parameter [1] as [VARCHAR] - [%City]

IgnoreCase, IgnoringCase. Compares strings with the equals (=) operator ignoring the capitalization thanks to the JPQL UPPER function, which converts the received argument to uppercase.

“The country whose capital is the one indicated, ignoring the capitalization”:

Optional<Country> findByCapitalIgnoreCase(String capital);

where upper(c1_0.capital)=upper(?)

The result is unique (*), so its type is the entity class. If there’s a chance that the result isn’t found, we may use Optional too. In both cases, if the result is multiple, IncorrectResultSizeDataAccessException will be thrown.

(*) It may be multiple if there are capitals with the same name but different capitalizations.

You can combine these keywords that ignore capitalization with containing, startingWith, and endingWith:

List<Country> findByNameContainingIgnoreCase(String name);
List<Country> findByNameStartingWithIgnoringCase(String name);
List<Country> findByCapitalEndingWithIgnoreCase(String capital);

The three methods perform the same SQL:

where upper(c1_0.name) like upper(?) escape ''

Yet the position of the wildcard % in the variable changes:

binding parameter [1] as [VARCHAR] - [%republic%]
binding parameter [1] as [VARCHAR] - [republic%]
binding parameter [1] as [VARCHAR] - [%republic]

Joining Filters with And \ Or

Do you want to find entities by several fields? Join the selection criteria with And and Or.

And joins criteria.

“Countries containing a certain string in their name and affiliated to a certain confederation given by their identifier”:

List<Country> findByNameContainingAndConfederationId(String name, Long id);

where c1_0.name like ? and c1_0.confederation_id=?

The position of each parameter must match the position in the method name of the field of the entity class to which the parameter is bound. Thus, the signature of the above method is not arbitrary. The name of the country must be received first (String name), followed by the identifier (Long id) of the confederation. Due to this convention, the names of the parameters are irrelevant.

The union of keywords lengthens the method name. Paradoxical as it may sound, a method with a descriptive but long name is hard to read. We’ll address this issue in the next chapter.

Or represents, of course, the Or condition.

“Countries containing in their name or capital a given string”:

List<Country> findByNameContainingOrCapitalContaining(String name, String capital);

where c1_0.name like ? or c1_0.capital like ?

Ordering the Results

Let’s put some order in chaos.

We append the fields that set the order for each field to the OrderBy keyword. For each field, we have to provide the direction of the sorting: ascending (Asc) or descending (Desc). To say it plainly: sorting “from lowest to highest” (ascending) or “from highest to lowest” (descending).

“All countries ordered according to their date of joining the UN in ascending order (oldest first).”

List<Country> findByOrderByUnitedNationsAdmissionAsc();

If the last field in the OrderBy expression is sorted in ascending order, Asc is optional. That’s the case for the previous method, so we can write this:

List<Country> findByOrderByUnitedNationsAdmission();

Let’s write a sort based on a couple of attributes. The next example sorts the countries first by their date of admittance into the UN and then by their name. In both cases the direction is ascending.

List<Country> findByOrderByUnitedNationsAdmissionAscName();

order by c1_0.united_nations_admission asc,c1_0.name asc

The Asc that follows UnitedNationsAdmission is essential because it separates the declaration of that field from the declaration of Name. Without that Asc, the method doesn’t define a valid derived query. And, as I said, Name doesn’t require Asc— it’s the last field in the OrderBy expression.

Note that different sorting options for the same query require us to write new query methods:

List<Country> findByOrderByNameAsc();
List<Country> findByOrderByUnitedNationsAdmissionDesc();

In practice, we’ll rely on the most sophisticated and elegant programming technique: copy and paste the derived query and then change the string after OrderBy. No remorse!

Jokes aside, it’s obvious we need a way to set the sort options dynamically, that is, at runtime. Chapter 8 takes a deep dive into dynamic sorting. Here´s a teaser:

List<Country> findByNameContaining(String name, Sort sort);

Sort sortByPopulation = Sort.by("population");
List<Country> republics = repository.findByNameContaining("Republic", sortByPopulation);

As you can see, the query method receives a Sort argument with the sorting options.

Limiting Results with Top or First

You can limit the maximum number of results to be retrieved by placing First or Top between the method prefix and By. Both are interchangeable. After them, you set the number of results with a number; otherwise, the value 1 is applied.

“Which are the three most populated countries?”:

List<Country> findTop3ByOrderByPopulationDesc();

order by c1_0.population desc fetch first ? rows only

We find the three least populated by reversing the sorting direction:

List<Country> findTop3ByOrderByPopulationAsc();

Like SortBy, Top \First is too rigid. The limit is fixed. Chapter 9 discusses how to set a dynamic limit with the Limit interface and paginate the results with the Pageable interface. Again, I show a quick example:

Unsupported Keywords

Spring Data JPA doesn’t support the following Spring Data keywords: Near, IsNear, Within, IsWithin, MatchesRegex, Matches, Regex, and Exists.

Duplicate results

If you want the distinct results of a query that may return duplicates, type Distinct between find...By. This word is equivalent to the SQL \ JPQL DISTINCT option.

This situation doesn’t happen in the sample project.

Other Operations

Three prefixes complement entity searches. They support the same entity selection criteria as find…By.

Counting Entities

count…By returns the number of entities found. It supports the Distinct keyword.

“How many countries are members of a certain confederation?”:

long countByConfederationId(Long id);

select count(c1_0.id) from countries c1_0 where c1_0.confederation_id=?

You can count all the entities belonging to the repository domain class:

long count();

count() pertenece al repositorio genérico CrudRepository.

As expected, invoking a method countBy is way faster than getting the list with all the entities and then calling size(). The latter is an aberration that I warned about in Chapter 3.

Remember: get only the indispensable data from the database at each moment.

Checking Entities’ Existence Like a Boss

exists…By. Indicates whether at least one result exists.

“Find out whether a country with a certain capital exists”:

boolean existsByCapitalIgnoringCase(String capital);

select c1_0.id from countries c1_0 
where upper(c1_0.capital)=upper(?) fetch first ? rows only

This way of determining the existence of entities is fast—the SQL sentence only retrieves the identifier of the first result.

Deleting Entities

The delete...By\remove...By prefix remains to be discussed. Both does the same task: deleting entities that match certain criteria. Like all operations that write to the database, delete...By\remove...By methods must be invoked inside a transaction with write permission. This requirement is explained here.

For instance, this method deletes the countries affiliated to a given confederation:

@Transactional
long deleteByConfederationId(Long id);

In the example, the method returns the number of deleted countries. It can also return the deleted entities or void:

@Transactional
List<Country> deleteByConfederationId(Long id);

Spring Data JPA removes the entities one by one with the entity manager operation remove(), which takes the entity to delete. Therefore the method can return the deleted entities because they were obtained by calling remove().

This approach has a downside. If there are “many” entities to be individually deleted, their removal will be slow. How many entities are too many? It depends. You should test the edge cases for your project.

When you detect this performance issue, consider a batch deletion. One way to do it is with the method deleteInBatch() \ deleteAllByIdInBatch(), as explained in Chapter 3. Other option, more flexible, is writing a JPQL query. I show a example in the next chapter.

Return Types

In the examples, the derived queries return the most common types (List, Optional), as well as the special cases of long (count...By) and boolean (exists...By). Although they cover most cases, let’s review all the options. We’ll assume that the generic type T is the entity class declared by the repository. Chapter 7 covers what other “things” T can represent.

Pay attention: the content of this section is valid for other types of queries.

Single Result

You return the repository entity as is. Since it may be null if not found, I encourage you to use Optional<T>, as I did in findByCapitalIgnoreCase():

Optional<Country> findByCapitalIgnoreCase(String capital);

Why? Anyone can deduce that the query may not find the result just by seeing Optional. Plus, Optional enables us to process the method response in a functional style.

When there’s no return, use void. This would be the situation of deleteByConfederationId() if it didn’t return the deleted entities or their number.

@Transactional
void deleteByConfederationId(Long id);

Primitive types and their equivalent classes (Long, Integer…) are returned as is. In derived queries, it only makes sense to mention the numeric result of count...By and the logic result of exists...By.

Multiple Results

There are multiple alternatives. Take your pick!

You collect the results with these interfaces: Iterable<T>, Collection<T>, Set<T>, Stream<T>, and Streamable<T>. The latter is a Spring functional interface that facilitates the usage of Iterable. You can also utilize these classes: ArrayList<T>, LinkedList<T>, and LinkedHashSet<T>. Except for Stream, the mentioned types are subtypes of Iterable, as the following diagram illustrates.

Same query, different data structures:

Iterable<Country> findByNameContainingOrderByNameAsc(String name);
Collection<Country> findByNameContainingOrderByNameAsc(String name);
LinkedHashSet<Country> findByNameContainingOrderByNameAsc(String name);

Stream

Stream is special. The query method that returns Stream must satisfy two requirements:

it is transactional

it closes the stream after its consumption

These requirements are well justified. They allow JPA vendors to provide Stream implementations capable of traversing an open cursor with the database, something only viable within a transaction. This way, you retrieve the data on demand. Once consumed, the stream must close the cursor. That’s the theory; the reality depends on the JPA vendor and the JDBC driver.

An example:

Stream<Country> findAsStreamByNameContainingOrderByNameAsc(String name);

The “AsStream” expression doesn’t affect the query because it’s written between “find” and “By”. I had to write it to distinguish this method from another one we had already written, named findByNameContainingOrderByNameAsc(). A more appropriate alternative is to choose the stream...By prefix:

Stream<Country> streamByNameContainingOrderByNameAsc(String name);

This test calls the previous method and meets the two requirements demanded by streams:

@Test
@Transactional(readOnly = true)
void testFindByNameContainingAsStream() {
    List<Country> republics;

    try (Stream<Country> stream = 
            countryRepository.findAsStreamByNameContainingOrderByNameAsc("Republic"))  {
            republics = stream.toList();
     }

   assertThat(republics)
           .extracting(Country::getId)
           .containsExactly(KOREA_ID, DOMINICAN_ID);
 }

Without the transaction, the test fails. The error message is crystal clear:

org.springframework.dao.InvalidDataAccessApiUsageException: You're trying to execute a streaming query method without a surrounding transaction that keeps the connection open so that the Stream can actually be consumed; Make sure the code consuming the stream uses @Transactional or any other way of declaring a (read-only) transaction

Regardless of how Hibernate implements streams for HyperSQL, for efficiency reasons, you shouldn’t get entities in a Stream or a collection just to select a subset of them. Always make the selection in the query to get only the essential records and columns from the database. I am annoying you with this recommendation, but its systematic violation will penalize the performance of your applications.

Whenever you want to get entities progressively, use the pagination technique. It’s efficient, easy to apply, and compatible with all databases. As I said before, Chapter 9 will go over this topic.

Asynchronous Methods (@Async)

Repository methods can be asynchronous. Spring executes an asynchronous method in a new Thread, so it doesn’t block the calling code. This code continues its execution without waiting for the asynchronous method to complete. With this strategy, we parallelize the execution of methods in order to do several things at the same time.

This post explains how to use this technique in Spring:

Spring Framework: asynchronous methods with @Async, Future and TaskExecutor

In short, you transform a regular method into an asynchronous method with the @Async annotation. Also, you have to enable this Spring feature by marking a configuration class with @EnableAsync. These classes are the ones annotated with @SpringBootApplication or @Configuration.

Note. @Async is affected by the Spring proxy behavior I explained for @Transactional.

The return (T) of an asynchronous method must be encapsulated into special types. In the case of Spring Data, they’re the CompletableFuture<T> class of the Java API and Spring’s ListenableFuture<T> interface. Note that the latter was marked as deprecated in Spring Boot 3 and Spring 6 to promote CompletableFuture.

You can also utilize Future<T>, the superinterface of any class and interface representing an asynchronous response. In this case, the Future object returned is an object of the FutureTask<T> class.

Here’s a UML diagram with the above types. For the sake of simplicity, it only shows the public methods of Future (CompletableFuture exposes more than eighty methods!).

In all cases, as soon as the asynchronous method returns its result, the Future object contains it and lets us get it.

Let’s activate Spring’s asynchronous capabilities:

@SpringBootApplication
@EnableAsync
public class SpringBootApp {

The next derived query is asynchronous. It returns the countries inside a CompletableFuture object.

@Async
CompletableFuture<List<Country>> findAsyncCountriesByNameContaining(String name);

Let’s call findAsyncCountriesByName():

@Test
void testFindAsyncCountriesByNameContaining() throws ExecutionException, InterruptedException {
    Future<List<Country>> republicsAsyncList = countryRepository.findAsyncCountriesByNameContaining(REPUBLIC_STRING);

    assertThat(republicsAsyncList.get())
            .extracting(Country::getId)
            .containsExactlyInAnyOrder(KOREA_ID, DOMINICAN_ID);
}

The republicsAsyncList.get() sentence waits for findAsyncCountriesByNameContaining() to finish. At that moment, get() either returns the list with the countries or throws a checked exception if there was an error. That explains the throws clause you see in the test.

The preceding code, however, doesn’t benefit from parallel programming asynchrony. It calls the asynchronous method and waits for it to complete, which is the same as calling any non-asynchronous method. The mentioned tutorial contains realistic examples.

Other Types

This link details all result types available for the latest Spring Data release. We’ll ignore:

those compatible with the Vavr library
those related to the reactive programming implemented by Spring and RxJava
geometric types, incompatible with JPA

Slice and Page interfaces are tied to pagination, the subject of Chapter 9.

Summary

The keys of the chapter:

Derived queries are derived from the name of the method. The name concatenates a prefix, such as findBy, the criteria for selecting entities built according to certain keywords, and an optional ordering (OrderBy). The entities are of the repository domain class type.
Method parameters serve as query variables. The order of the parameters must match the order in which the variables to which they are bound appear in the method name.
In addition to searching entities (prefix findBy and its synonyms), you can delete them (deleteBy), count them (countBy), and check their existence (existsBy).
You have multiple choices for collecting the results. List and Optional are the “usual suspects.”
Like any Spring bean, repositories support the @Async annotation and the Future interface.