3

Querying for Data with Spring Boot

In the previous chapter, we learned how Spring Boot manages embedded servlet containers, automatically registers our web controllers, and even provides JSON serialization/deserialization, easing the creation of APIs.

What application doesn’t have data? Spoiler alert – none. That’s why this chapter is focused on learning some of the most powerful (and handy) ways to store and retrieve data.

In this chapter, we’ll cover the following topics:

• Adding Spring Data to an existing Spring Boot application
• DTOs, entities, and POJOs, oh my!
• Creating a Spring Data repository
• Using custom finders
• Using Query By Example to find tricky answers
• Using the custom Java Persistence API (JPA)

Being able to store and retrieve data is a critical need for any application, and this list of topics will provide you with vital abilities.

Where to find this chapter’s code

The source code for this chapter can be found at https://github.com/PacktPublishing/Learning-Spring-Boot-3.0/tree/main/ch3.

Adding Spring Data to an existing Spring Boot application

Imagine we have an application brewing. We showed our program manager some preliminary web pages based on the pitch she is hastily putting together. While excited at that, she signals we need to hook them up to some real data.

But instead of swallowing with dread, we smile from ear to ear. Spring Data is the ticket to powerful data management.

Before we can move forward, though, we must make a choice. What data store exactly do we need?

The most common database used today is a relational one (Oracle, MySQL, PostgreSQL, and so on). As mentioned in a past SpringOne keynote, relational databases comprise 80% of all projects created on Spring Initializr. Choosing a NoSQL (not only SQL) data store requires careful consideration, but here are three options we can explore:

• Redis is principally built as a key/value data store. It’s very fast and very effective at storing huge amounts of key/value data. On top of that, it has sophisticated statistical analysis, time-to-live, and functions.
• MongoDB is a document store. It can store multiple levels of nested documents. It also has the ability to create pipelines that process documents and generate aggregated data.
• Apache Cassandra provides a table-like structure but has the ability to control consistency as a trade-off with performance.

SQL data stores have historically had hard requirements on predefining the structure of the data, enforcing keys, and other aspects that tend to give no quarter.

NoSQL data stores tend to relax some of these requirements. Many don’t require an upfront schema design. They can have optional attributes, such that one record may have different fields from those of other records (in the same keyspace or document).

Some NoSQL data stores give you more flexibility when it comes to scalability, eventual consistency, and fault tolerance. For example, Apache Cassandra lets you run as many nodes as you like and lets you choose how many nodes have to agree on the answer to your query before giving it. A faster answer is less reliable, but 75% agreement may be faster than waiting for all nodes (as is typically the case with relational data stores).

NoSQL data stores typically do not support transactions. Some of them are starting to offer them, in limited contexts. But in general, if a NoSQL data store were to mimic every feature of relational data stores (consistency, transactions, and fixed structure), they’d probably lose the features that make them faster and more scalable.

With that being said, let’s focus on using a traditional data store. The features described in the rest of this chapter are widely available with any data store supported by Spring Data. The next section will explain why.

Using Spring Data to easily manage data

Spring Data has a unique approach to simplifying data access. Spring Data doesn’t use the lowest common denominator approach. This is the tactic where a single API is defined in an interface, and an implementation is offered for every data store.

This tends to reduce access to only the features that all data stores share. Since all data stores offer varying features, the keen aspects of a given data store that make us want to use it usually aren’t found in that shared API!

No, Spring Data takes a different approach. Each data store has multiple ways to access data, but not with an identical API. For starters, almost every Spring Data module has a template that gives us easy access to data store-specific features. Some of these templates include the following:

• RedisTemplate
• CassandraTemplate
• MongoTemplate
• CouchbaseTemplate

These template classes aren’t descendants of some common API. Each Spring Data module has its own core template. They all have a similar lifecycle where they handle resource management. Each template has a mixture of functions, some based on a common data access paradigm and others based on the data store’s native features.

While we can do just about anything using a data store’s template, there are easier ways to access data. Many Spring Data modules include repository support, making it possible to define queries, updates, and deletes, based purely on our domain types. We’ll see how to use more of this later in the chapter.

There are additional ways to define data requirements, including Query By Example and support for the third-party library Querydsl.

Note

While every data store has a template, HibernateTemplate, a long-time part of Spring Framework’s Hibernate solution, is really a tool meant to help legacy apps migrate to Hibernate’s SessionFactory.getCurrentSession() API. The Hibernate team prefers using this approach or migrating toward using JPA's EntityManager directly. For this reason, we won’t be delving into HibernateTemplate in this chapter. However, we will explore the many ways that Spring Data JPA simplifies access to relational data stores.

There’s a common undercurrent in all these approaches. Writing the select statements, whether they are for Redis, MongoDB, or Cassandra, is not only tedious but also costly to maintain. Considering a huge portion of queries are simply copying in structural values that map onto domain types and field names, Spring Data leverages domain information to help developers write queries.

Instead of rolling queries by hand, shifting the language of data access to domain objects and their fields lets us shift our problem-solving to business use cases.

Nevertheless, there is always a handful of use cases that require handwritten queries. For example, there are monthly customer deliverables that require joining 20 tables or a customer report that has a varying mix of inputs.

There is always the option to sidestep any of Spring Data’s help and instead write the query directly.

Through the rest of this chapter, we’ll explore how these various forms of data access provided by Spring Data let us focus on solving customer problems instead of battling query typos.

Adding Spring Data JPA to our project

Before we can do anything with Spring Data, we must add it to our project. While we spent a bit of time in the previous section discussing various data stores, let’s settle on a relational database.

To do that, we will use Spring Data JPA. To get off the ground, we’ll choose a simple embedded database, H2. This database is a Java Database Connectivity (JDBC) based relational database written in Java. It’s effective for prototyping efforts.

To add Spring Data JPA and H2 to our already drafted app, we can easily use the same tactic from Chapter 2, Creating a Web Application with Spring Boot (using start.spring.io to build apps):

1. Visit start.spring.io.
2. Enter the same project artifact details as before.
3. Click on DEPENDENCIES.
4. Select Spring Data JPA and H2.
5. Click on EXPLORE.
6. Look for the pom.xml file and click on it.
7. Copy the new bits onto the clipboard.
8. Open up our previous project inside our IDE.
9. Open our pom.xml file and paste the new bits into the right places.

Hit the refresh button in the IDE, and we’re ready to go!

Now with Spring Data JPA and H2 added to our project, we are ready to start designing our data structures in the next section!

DTOs, entities, and POJOs, oh my!

Before we start slinging code, we need to understand a fundamental paradigm: data transfer objects (DTOs) versus entities versus plain old Java objects (POJOs).

The differences between these three conventions aren’t something that is directly enforced by any sort of tool. That’s why it’s a paradigm and not a coding construct. So, what exactly are DTOs, entities, and POJOs?

• DTO: A class whose purpose is to transfer data, usually from server to client (or vice versa)
• Entity: A class whose purpose is to store/retrieve data to/from a data store
• POJO: A class that doesn’t extend any framework code nor has any sort of restrictions baked into it

Entities

When we write code to query data from a database, the class where our data ends up is commonly called an entity. This concept was turned into a standard when JPA was rolled out. Literally, every class involved with storing and retrieving data through JPA must be annotated with @Entity.

But the concept of entities doesn’t stop with JPA. Classes used to ferry data in and out of MongoDB, although they require no such annotation, can also be considered entities. Classes that are involved in data access typically have requirements levied on them by the data store. JPA specifically wraps entity objects returned from queries with proxies. This lets the JPA provider track updates so that it knows when to actually push updates out to the data store (known as flushing) and also helps it to better handle entity caching.

Speaking of entities, we need to lay out the details for the video type we wish to store in the database in this chapter. The following code will be suitable for this chapter’s needs:

@Entity
class VideoEntity {
  private @Id @GeneratedValue Long id;
  private String name;
  private String description;
  protected VideoEntity() {
    this(null, null);
  }
  VideoEntity(String name, String description) {
    this.id = null;
    this.description = description;
    this.name = name;
  }
  // getters and setters
}

This preceding code has the following characteristics:

• @Entity is JPA’s annotation to signal this is a JPA-managed type.
• @Id is JPA’s annotation to flag the primary key.
• @GeneratedValue is a JPA annotation to offload key generation to the JPA provider.
• JPA requires a no-argument constructor method that is either public or protected.
• We also have a constructor where the id field isn’t provided: a constructor designed for creating new entries in the database. When the id field is null, it tells JPA we want to create a new row in the table.

We aren’t going to spend a lot of time talking about modeling entities with JPA. There are entire books dedicated to the intricacies of entity modeling.

DTOs

DTOs, on the other hand, are typically used in the web layer of applications. These classes are more concerned with taking data and ensuring it’s properly formatted for either HTML generation or JSON handling. Jackson, Spring Boot’s default JSON serialization/deserialization library, comes with a fistful of annotations to customize JSON rendering.

Note

It should be said that DTOs aren’t confined to JSON. Using XML or any other form of data exchange format has the same need of ensuring proper formatting of data. JSON just happens to be the most popular format in today’s industry; hence, the reason Spring Boot puts Jackson on classpath by default when we choose Spring Web.

Why do we need DTOs and entities? Because a keen lesson learned over recent years is that classes are easier to maintain and update if they try to concentrate on doing just one task. In fact, there’s a name for this concept: the single-responsibility principle (SRP).

A class that tries to be both a DTO and an entity is harder to manage in the long run. Why? Because there are two stakeholders: the web layer and the persistence layer.

Tip

Short-term versus long-term goals. Notice how I said that a class that tries to be both a DTO and an entity is harder to manage in the long run? That’s true. But what about that demo you give to your CTO when you’re pitching Spring Boot? That is a short-term scenario where you need to get a point across. You aren’t trying to build a long-lasting app, but instead, a quick demo. In those scenarios, it’s okay to let one class serve as both DTO and entity. But anything slated for production will probably be better maintained over the long run if those two ideas are decoupled. For a more detailed discussion, check out my video, DTOs vs. Entities, at https://springbootlearning.com/dtos-vs-entities.

We’ve mentioned DTOs and entities. Where do POJOs fit into all this?

POJOs

Spring has a long history of supporting a POJO-oriented programming style. Before Spring, many if not most Java-based frameworks required developers to extend various classes. This drives user code to hook into the framework to make things happen.

These sorts of classes were hard to work with. They didn’t lend themselves to writing test cases due to requirements inherited from the framework. It often required spinning everything up to verify that user-created code was working right. Overall, it made for a heavy-handed coding experience.

A POJO-based approach meant writing user code that didn’t have to extend any framework code.

Spring’s concept of registering beans with an application context made it possible to avoid this heavy style. Registered beans with a built-in lifecycle opened the door to wrapping these POJO-based objects with proxies that allowed the application of services.

One of Spring’s earliest features was transactional support. Due to Spring’s revolutionary nature of registering something such as VideoService with the application context, you could easily wrap a bean with a proxy that applied Spring’s TransactionTemplate to every method call made from an outside caller.

This made it easy to unit test VideoService, ensuring it did its job while making transactional support a configuration step that the service didn’t even have to know about.

When Java 5 emerged with its support for annotations, it became even easier to apply things. Transactional support could be applied with a simple @Transaction annotation.

By keeping services light and POJO-oriented, Spring effected a lightening of development.

Perhaps the application of annotations (and nothing else) is debatable as to whether it’s really a POJO. But the idea of having to verify an application built up out of POJOs lets us build confidence in our system faster.

With all this prep work done, it’s time to dig and start writing queries, as we’ll see in the next section!

Creating a Spring Data repository

What is the best query? The one we don’t have to write!

This may sound absurd, but Spring Data really makes it possible to write lots of queries…without writing them. The simplest one is based on the repository pattern.

This pattern was originally published in Patterns of Enterprise Application Architecture, Martin Fowler, Addison-Wesley.

A repository essentially gathers all the data operations for a given domain type in one place. The application talks to the repository in domain speak, and the repository in turn talks to the data store in query speak.

Before Spring Data, we had to write this translation of action by hand. But Spring Data provides the means to read the metadata of the data store and perform query derivation.

Let’s check it out. Create a new interface called VideoRepository.java, and add the following code:

public interface VideoRepository extends JpaRepository
  <VideoEntity, Long> {
}

The preceding code can be explained as follows:

• It extends JpaRepository with two generic parameters: VideoEntity and Long (the domain type and the primary key type)
• JpaRepository, a Spring Data JPA interface, contains a set of already supported Change Replace Update Delete (CRUD) operations

Believe it or not, this is all we need to get going.

One of the most important things to understand is that by peeking inside of JpaRepository using our IDE, we’ll discover that this class hierarchy ends with Repository. This is a marker interface with nothing inside it.

Spring Data is coded to look for all Repository instances and apply its various query derivation tactics. This means that any interface we create that extends Repository or any of its subinterfaces will be picked up by Spring Boot’s component scanning and automatically registered for us to use.

But that is not all. JpaRepository comes loaded with various ways to fetch data, as shown with the following operations:

• findAll(), findAll(Example<S>), findAll(Example<S>, Sort), findAll(Sort), findAllById(Iterable<ID>), findById(ID), findAll(Pageable), findAll(Example<S>, Pageable), findBy(Example<S>), findBy(Example<S>, Pageable), findBy(Example<S>, Sort), findOne(Example<S>)
• deleteById(ID), deleteAll(Iterable<T>), deleteAllById(Iterable<ID>), deleteAllByIdInBatch(Iterable<ID>), deleteAllInBatch()
• save(S), saveAll(Iterable<S>), saveAllAndFlush(Iterable<S>), saveAndFlush(S)
• count(), count(Example<S>), existsById(ID)

These aren’t all found directly in JpaRepository. Some are in other Spring Data repository interfaces further up the hierarchy including ListPagingAndSortingRepository, ListCrudRepository, and QueryByExampleExecutor.

The generic types sprinkled in the various signatures may seem a little confusing. Check out the following list to decode them:

• ID: The generic type of the repository’s primary key
• T: The generic type of the repository’s immediate domain type
• S: The generic subtype that extends T (sometimes used for projection types)

There are some container types that are also used in many places. These can be described as follows:

• Iterable: An iterable type that does not require all its elements to be fully realized in memory
• Example: An object used to serve Query By Example

As we work our way through this chapter, we’ll cover these various operations and how we can use them to create a powerpack of data access.

While all these operations provide an incredible amount of power, one thing they lack is the ability to query with more specific criteria. The next section provides the means to start crafting more detailed queries.

Using custom finders

To create a custom finder, go back to the repository we created earlier, VideoRepository, and add the following method definition:

List<VideoEntity> findByName (String name);

The preceding code can be explained as follows:

• The findByName(String name) method is called a custom finder. We never have to implement this method. Spring Data will do it for us as described in this section.
• The return type is List<VideoEntity>, indicating it must return a list of the repository’s domain type.

This interface method is all we need to write a query. The magic of Spring Data is that it will parse the method name. All repository methods that start with findBy are flagged as queries. After that, it looks for field names (Name) with some optional qualifiers (Containing and/or IgnoreCase). Since this is a field, it expects there to be a corresponding argument (String name). The name of the argument doesn’t matter.

Spring Data JPA will literally translate this method signature into select video.* from VideoEntity video where video.name = ?1. As a bonus, it even performs proper binding on the incoming argument to avoid SQL injection attacks. It will convert every row coming back into a VideoEntity object.

Tip

What are SQL injection attacks? Anytime you give system users the chance to enter a piece of data and have it spliced into a query, you run the risk of someone inserting bits of SQL to maliciously attack the system. In general, blindly copying and pasting user inputs with production queries is a risky move. Binding arguments provide a much safer approach, forcing all user inputs to come in the data store’s front door and get applied properly to query creation.

The ability to write type-safe queries based on domain types cannot be overstated. We also don’t need to cope with the names of tables or their columns. Spring Data will use all the built-in metadata to craft the SQL needed to talk to our relational database.

On top of that, because this is JPA, we don’t even have to sweat database dialects. Whether we are talking to MySQL, PostgreSQL, or some other instance, JPA will largely handle those idiosyncrasies.

There are additional operators used by custom finders:

• And and Or can be used to combine multiple property expressions. You can also use Between, LessThan, and GreaterThan
• You can apply IsStartingWith, StartingWith, StartsWith, IsEndingWith, EndingWith, EndsWith, IsContaining, Containing, Like, IsNotContaining, NotContaining, and NotContains
• You can apply IgnoreCase against a single field, or if you want to apply it to all properties, use AllIgnoreCase at the end
• You can apply OrderBy with Asc or Desc against a field when you know the ordering in advance

Note

Containing versus StartsWith versus EndsWith versus Like

In Jakarta Persistence Query Language (JPQL) , % is a wildcard you can use for doing a partial match with LIKE. To apply it yourself, just apply Like onto the finder, for example, findByNameLike(). But if you’re doing something simple, like putting the wildcard at the beginning, just use StartsWith and provide the partial token. Spring Data will plug in the wildcard for you. EndsWith puts the wildcard at the end and Containing puts one on each side. If you need something more complicated, then Like puts you in control, as in %firstthis%thenthis%.

Custom finders can also navigate relationships. For example, if the repository’s domain type were Person and it had an Address field with ZipCode, we could write a custom finder called findByAddressZipCode(ZipCode zipCode). This will generate a join to find the right results.

In the event that Spring Data runs into an ambiguous situation, it’s possible to resolve things. For example, if that Person object just mentioned also had an addressZip field, Spring Data would naturally take that over navigating across a relationship. To force it to navigate properly, use an underscore (_) like this: findByAddress_ZipCode(ZipCode zipCode).

Assuming we wanted to apply some of these techniques, what about creating a search box for our web app from the previous chapter?

Let’s add a search box to the Mustache template we created in Chapter 2, Creating a Web Application with Spring Boot, index.mustache, as follows:

<form action="/multi-field-search" method="post">
  <label for="name">Name:</label>
  <input type="text" name="name">
  <label for="description">Description:</label>
  <input type="text" name="description">
  <button type="submit">Search</button>
</form>

The preceding code can be described as follows:

• The action denotes /multi-field-search as the target URL, with an HTTP method of POST
• There is a label and a text input for both name and description
• The button labeled Search will actuate the whole form

When the user enters search criteria in either box and clicks Submit, it will POST a form to /multi-field-search.

To handle this, we need a new method in our controller class that can parse this. As mentioned in the previous chapter, Mustache needs a data type to collect the name and description fields. A Java 17 record is perfect for defining such lightweight data types.

Create VideoSearch.java and add the following code:

record VideoSearch(String name, String description) {
}

This Java 17 record has two String fields—name and description—that perfectly match up with the names defined earlier in the HTML form.

Using this data type, we can add another method to HomeController from Chapter 2, Creating a Web Application with Spring Boot, to process the search request:

@PostMapping("/multi-field-search")
public String multiFieldSearch( //
  @ModelAttribute VideoSearch search, //
  Model model) {
  List<VideoEntity> searchResults = //
    videoService.search(search);
  model.addAttribute("videos", searchResults);
  return "index";
}

The preceding controller method can be described as follows:

• @PostMapping("/multi-field-search") is Spring MVC’s annotation to mark the method for processing HTTP POST requests to the URL.
• The search argument has the VideoSearch record type. The @ModelAttribute annotation is Spring MVC’s signal to deserialize the incoming form. The Model argument is a mechanism to send information out for rendering.
• There is a newly minted search() method where our VideoSearch criteria get forwarded to VideoService (which we’ll define further down). The results are inserted into the Model object under the name videos.
• The method finally returns the name of the template to render, index. As a refresher from the previous chapter, Spring Boot is responsible for translating this name to src/main/resources/templates/index.mustache.

In defining the web method for handling search requests, we must now design a VideoService method to do a search. This is where it gets a little tricky. So far, we’ve simply ferried the details of the request.

Now, it’s time to make the request, and all kinds of things could happen:

• The user could have entered both name and description details
• The user could have entered only the name field
• The user could have entered only the description field

What information the user enters could swing things one way or the other. For example, if the name field were empty, we don’t want to attempt to match against an empty string, because that would match everything.

We need a method signature as follows:

public List<VideoEntity> search(VideoSearch videoSearch)

The preceding code meets our obligation of taking a VideoSearch input and translating it to a list of VideoEntity objects.

From here, we need to switch to using a both name and description, based on the inputs, with the first path being as follows:

if (StringUtils.hasText(videoSearch.name())
  && StringUtils.hasText(videoSearch.description())) {
  return repository
    .findByNameContainsOrDescriptionContainsAllIgnoreCase(
      videoSearch.name(), videoSearch.description());
}

The preceding code has some key components:

• StringUtils is a Spring Framework utility that lets us check that both fields of the VideoSearch record actually have some text and are neither empty nor null.
• Assuming both fields are populated, we can then invoke a custom finder that matches the name field and the description field, but with the Contains qualifiers and the AllIgnoreCase modifier. Basically, we’re looking for a partial match on both fields, and the casing shouldn’t be an issue.

If either field is empty (or null), then we need additional checks, as follows:

if (StringUtils.hasText(videoSearch.name())) {
  return repository.findByNameContainsIgnoreCase
    (videoSearch.name());
}
if (StringUtils.hasText(videoSearch.description())) {
  return repository.findByDescriptionContainsIgnoreCase
    (videoSearch.description());
}

The preceding code is somewhat similar, but varies:

• Using the same StringUtils utility method as before, check if the name field has text. If so, invoke the custom finder matching on name with the Contains and IgnoreCase qualifiers.
• Also, check whether the description field has text. If so, use the custom finder that matches on description with Contains and IgnoreCase qualifiers.

Finally, if both fields are empty (or null), there is but one result to return:

return Collections.emptyList();

Since this is the final state of what can happen, there is no need for an if clause. If our code has made it here, there is nothing to do but return an empty list.

If you felt that this series of if clauses is a little clunky, I’d agree! There are yet more ways to query the database using Spring Data JPA, and we’ll investigate further. Later in this chapter, we’ll see how we can use some of these tactics to our advantage and see about making a slicker, smoother solution.

Sorting the results

There are several ways to sort data. We just mentioned adding an OrderBy clause earlier in this chapter. This is a static approach, but it’s also possible to delegate this to the caller.

Any custom finder can also have a Sort parameter, allowing the caller to decide how to sort the results:

Sort sort = Sort.by("name").ascending()
  .and(Sort.by("description").descending());

This fluent Sort API lets us build up a series of columns and allows us to choose whether these columns should be sorted in ascending or descending order. This is also the order in which the sorting will be applied.

If you’re worried about using string values to represent columns, then ever since the days of Java 8, Spring Data has also supported strongly-typed sort criteria, as follows:

TypedSort<Video> video = Sort.sort(Video.class);
Sort sort = video.by(Video::getName).ascending()
  .and(video.by(Video::getDescription).descending());

Limiting query results

There are several ways to constrain the results. Why is this needed? Just imagine querying a table with 100,000 rows. Don’t want to fetch all of that, right?

Some of the options we can apply to custom finders include the following:

• First or Top: Finds the first entry in the result set, for example, findFirstByName(String name) or findTopByDescription(String desc).
• FirstNNN or TopNNN: Finds the first NNN entries in the result set, for example, findFirst5ByName(String name) or findTop3ByDescription(String desc).
• Distinct: Apply this operator for data stores that support it, for example, findDistinct ByName(String name).
• Pageable: Request a page of data, for example, PageRequest.of(1, 20) will find the first page (0 being the first page) with a page size of 20. It’s also possible to provide a Sort parameter to Pageable.

It’s also important to point out that not only can we write custom finders, but we can also write a custom existsBy, deleteBy, and countBy method. They all support the same conditions described in this section.

Look at the following set of examples:

• countByName(String name): Runs the query but with a COUNT operator applied, returning an integer instead of the domain type
• existsByDescription(String description): Runs the query but gleans whether or not the result is empty
• deleteByTag(String tag): DELETE instead of SELECT

Tip

SQL vs. JPQL – What query does Spring Data JPA actually write? JPA provides a construct to build up queries known as EntityManager. EntityManager provides APIs to assemble queries using JPQL. Spring Data JPA parses methods from the repository method and talks to EntityManager on your behalf. Under the covers, JPA is responsible for converting JPQL to Structured Query Language (SQL), the language relational data stores speak. Certain concepts, like injection attacks, don’t really matter whether we are talking about JPQL or SQL. But when it comes down to the actual queries, it’s important to be sure we’re talking about the right thing.

Custom finders are incredibly powerful. They make it possible to capture business concepts rapidly without fiddling with writing queries.

But there’s a fundamental trade-off that may not make them ideal for every situation.

Custom finders are almost completely fixed. True, we can provide custom criteria through the arguments, and it’s possible to dynamically adjust sorting and paging. But the columns we choose for the criteria and how they are combined (IgnoreCase, Distinct, and so on) are fixed when we write them.

We’ve seen a limitation of this in the previous search box scenario. Simply having two parameters, name and description, sent us down a path to write a series of if clauses to pick the right custom finder.

Imagine how this would explode if we added more and more options. To cut to the chase, this approach results in a combinatorial explosion of finder methods to cover it all and the if statements quickly become long-winded and a little hard to reason about. What happens if we add another field?

The problem, as stated, is that custom finders are rather static in the criteria we can apply. Thankfully, Spring Data offers a way out of such fluid situations, which we’ll cover in the next section.

Using Query By Example to find tricky answers

So, what happens when the exact criteria for a query vary from request to request? In short, we need a way to feed Spring Data an object that captures the fields we’re interested in while ignoring the ones that we aren’t.

The answer is Query By Example.

Query By Example lets us create a probe, which is an instance of the domain object. We populate the fields with criteria we want to apply and leave the ones we aren’t interested in empty (null).

We then wrap the probe, creating an Example. Check out the following example:

VideoEntity probe = new VideoEntity();
probe.setName(partialName);
probe.setDescription(partialDescription);
probe.setTags(partialTags);
Example<VideoEntity> example = Example.of(probe);

The preceding code can be broken down as follows:

• The first few lines are where we create the probe, presumably pulling down fields from a Spring MVC web method where they were posted, some populated, some null
• The last line wraps the Example<VideoEntity> probe with a policy of exactly matching only the non-null fields

Earlier, when discussing the calamity of custom finders (under the Using customer finders section), we mentioned applying an AllIgnoreCase clause. To do the same for Query By Example, we’d have to alter our example as follows:

Example<VideoEntity> example = Example.of(probe,
  ExampleMatcher.matchingAll()
    .withIgnoreCase()
    .withStringMatcher(StringMatcher.CONTAINING));

Assuming we were using the exact same probe as before, ExampleMatcher alters things as follows:

• It matches on all fields, essentially an And operation as before. However, if we wanted to switch to an Or operation, we could switch to matchingAny().
• withIgnoreCase() tells Spring Data to make the query case insensitive. It essentially applies a lower() operation on all the columns (so adjust any indexes suitably!).
• withStringMatcher() applies a CONTAINING filter to make it a partial match on all non-null columns. Under the hood, Spring Data wraps each column with a wildcard and then applies the LIKE operator.

Assuming we put together Example<VideoEntity>, how do we use it? The JpaRepository interface we are leveraging comes with findOne(Example<S> example) and findAll(Example<S> example).

Tip

JpaRepository inherits these Example-based operations from QueryByExample Executor. If you are rolling your own extension of Repository, you can either extend QueryByExampleExecutor or add the findAll(Example<S>) methods by hand. Either way, as long as the method signature is there, Spring Data will happily execute your Query By Example.

So far, we have looked at how some sort of search box or filter on the web page could be used to assemble a probe. If we decided to switch from a multi-field setup and instead had a universal search box where there is only one input, it would take little effort to adapt it!

Let’s see if we can noodle out such a search box:

<form action="/universal-search" method="post">
  <label for="value">Search:</label>
  <input type="text" name="value">
  <button type="submit">Search</button>
</form>

This form in the preceding code is quite similar to the HTML template created earlier in this chapter, with the following exceptions:

• The target URL is /universal-search
• There is only one input, value

Again, to transport this piece of input data, we need to wrap it with a DTO. Thanks to Java 17 records, this is super simple. Just create a UniversalSearch record as follows:

record UniversalSearch(String value) {
}

The preceding DTO contains one entry: value.

To process this new UniversalSearch, we need a new web method:

@PostMapping("/universal-search")
public String universalSearch(
  @ModelAttribute UniversalSearch search, Model model) {
    List<VideoEntity> searchResults =
      videoService.search(search);
  model.addAttribute("videos", searchResults);
  return "index";
}

The preceding search handler is quite similar to the multi-field one we made earlier, with the following exceptions:

• It responds to /universal-search
• The incoming form is captured in the single-value UniversalSearch type
• The search DTO is passed on to a different search() method, which we’ll write further down in this section
• The search results are stored in the Model field to be rendered by the index template

Now, we’re poised to leverage Query By Example by creating a VideoService.search() method that takes in one value and applies it to all the fields, as follows:

public List<VideoEntity> search(UniversalSearch search) {
  VideoEntity probe = new VideoEntity();
  if (StringUtils.hasText(search.value())) {
    probe.setName(search.value());
    probe.setDescription(search.value());
  }
  Example<VideoEntity> example = Example.of(probe, //
    ExampleMatcher.matchingAny() //
      .withIgnoreCase() //
      .withStringMatcher(StringMatcher.CONTAINING));
  return repository.findAll(example);
}

The preceding alternative search method can be explained as follows:

• It takes in the UniversalSearch DTO.
• We create a probe based on the same domain type as the repository and copy the value attribute into the probe’s Name and Description fields, but only if there is text. If the value attribute is empty, the fields are left null.
• We assemble an Example<VideoEntity> using the Example.of static method. However, in addition to providing the probe, we also provide additional criteria of ignoring the casing and applying a CONTAINING match, which puts wildcards on both sides of every input.
• Since we’re putting the same criteria in all fields, we need to switch to matchingAny(), that is, an Or operation.

With a single design change to the UI and by switching to Query By Example, we were able to adjust the backend to find results.

This isn’t just effective and maintainable, it’s pretty easy to read and understand what’s happening. If we start adding more attributes to this video-based structure, it doesn’t look hard to adjust.

Tip

In case you’re thinking you can simply create a finder that matches on all fields, providing null to the columns you want to ignore, this won’t work. This is your friendly reminder that in relational databases, null doesn’t equal null. That’s why Spring Data also has IsNull and IsNotNull as qualifiers; for example, findByNameIsNull will find any entries where the name field is null.

However, that is not all. There are other ways to fashion queries, including a more fluent way, as shown in the next section.

Using custom JPA

If all else fails and we can’t seem to bend Spring Data’s query derivation tactics to meet our needs, it’s possible to write the JPQL ourselves.

In our repository interface, we can create a query method as follows:

@Query("select v from VideoEntity v where v.name = ?1")
List<VideoEntity> findCustomerReport(String name);

The preceding method can be explained as follows:

• @Query is Spring Data JPA’s way to supply a custom JPQL statement.
• It’s possible to include positional binding parameters using ?1 to tie it to the name argument.
• Since we are providing the JPQL, the name of the method no longer matters. This is our opportunity to pick a better name than what custom finders constrained us to.
• Because the return type is List<VideoEntity>, Spring Data will form a collection.

Using @Query essentially sidesteps any query writing done by Spring Data and uses the user’s supplied query with one exception: Spring Data JPA will still apply ordering and paging. Because SORT clauses can be appended at the end of queries, Spring Data JPA will let us provide a Sort argument and apply it.

While we are focusing on Spring Data JPA details such as JPQL, almost every other Spring Data module has a corresponding @Query annotation. Each data store lets us write custom queries in the data store’s query language, for example: MongoQL, Cassandra Query Language (CQL), or even Nickel/Couchbase Query Language (N1QL).

In terms of Spring Data JPA, it must be stressed that this annotation lets us provide JPQL. Now, the previous example is a bit simplistic for a custom query. If you were thinking that based on what you’ve read up to this point, it would be a perfect candidate for findByName(String name), you’re right!

However, sometimes we have that custom query that requires joining lots of different tables. Maybe something more similar to the following:

@Query("select v FROM VideoEntity v " //
    + "JOIN v.metrics m " //
    + "JOIN m.activity a " //
    + "JOIN v.engagement e " //
    + "WHERE a.views < :minimumViews " //
    + "OR e.likes < :minimumLikes")
List<VideoEntity> findVideosThatArentPopular( //
    @Param("minimumViews") Long minimumViews, //
    @Param("minimumLikes") Long minimumLikes);

The preceding code can be explained as follows:

• This @Query shows a JPQL statement that joins four different tables together, using standard inner joins.
• :minimumViews and :minimumLikes are named parameters (instead of the default positional parameters). They are bound to the method arguments by the Spring Data @Param("minimumViews") and @Param("minimumLikes") annotations.

The preceding method is getting closer to something @Query is good for. A comparable custom finder would be findByMetricsActivityViewsLessThanOrEngagementLikesLessThan(Long minimumViews, Long minimumLikes).

Tip

Choosing between custom finders and @Query is hard. To be honest, for this example where we join four tables together, I’d still take that custom finder, because I know it will be right. But as that finder method gets longer and longer, things begin to shift more favorably toward writing the query by hand. A key factor is the number of WHERE clauses as well as the number of complex (that is, outer) JOIN clauses. Essentially, the harder it gets to capture it in a simple name, the better it becomes to take control of the whole query.

And if JPQL is getting in the way, it’s possible to even move past that and write pure SQL using @Query’s nativeQuery=true argument.

Spring Data JPA 3.0 includes JSqlParser, a SQL-parsing library, making it possible to write queries as follows:

@Query(value="select * from VIDEO_ENTITY where NAME = ?1", nativeQuery=true)
List<VideoEntity> findCustomWithPureSql(String name);

Why do we write queries as shown in the preceding code? There are several possible reasons, actually:

• Perhaps we need access to a customer report, but all the relevant tables don’t really connect to the stuff the rest of our finders operate on. Is it worth it to climb through configuring a stack of entity types for one report? It may be easier to focus on writing the pure SQL for the report.
• I’ve seen reports that literally join 20 tables with complex left outer joins, correlated subqueries, and other complexities. Converting it to JPQL for the sake of using JPA didn’t make sense.

The criteria for swapping our custom finders for native SQL are pretty close to whether or not we would swap it out for custom JPQL. It really hinges on how comfortable we are with JPQL versus SQL.

Tip

Personally, if I were using @Query, I’d probably switch to pure SQL, not JPQL. But that’s probably because I have many more years of experience with SQL than with JPQL. Having worked on a 24x7 system with five 9s of availability with over 200 queries, I can write left outer joins and correlated subqueries in my sleep. The JPQL equivalent would require too much study. But, perhaps JPQL is your thing. If you jam in the land of JPQL, then run with that. Whatever gets the job done, go for it!

Another factor to consider is that Spring Data JPA doesn’t support dynamic sorting when doing native queries. Doing so would require manipulating the SQL by adding the SORT clauses. It is possible to support paging requests with a Pageable argument. But it requires that we also fill in @Query’s countQuery entry, providing the SQL statement to count. (Spring Data JPA can iterate over the result set, providing pages of results.)

It’s also important to understand that Spring Data will still handle connection management and transaction handling.

Summary

Over the course of this chapter, we have learned a multitude of ways to fetch data using Spring Data JPA. We then hooked several variations of queries into some search boxes. We used Java 17 records to quickly assemble DTOs to ferry form requests into web methods and onto VideoService.

We tried to assess when it makes sense to use various querying tactics.

In the next chapter, Securing an Application with Spring Boot, we’ll explore how to lock down our application and get it ready for production.