Spring Data JPA Query Methods
Tags: java, spring, jpa, query
Published: 2020-01-15 07:19:06

This is the post #3 of the series "Querying your Spring Data JPA Repository".

If you're following the series, by now you have an app with a list of Restaurants. The list is fetched using the method `findAll()` which you did not implement, because it was provided by Spring Data JPA when you created the `RestaurantRepository` class extending Spring's `JpaRepository` class.

Take a look at the previous post if you haven't already: 

{% link brunodrugowick/querying-your-spring-data-jpa-repository-basic-setup-1i8p %}

# The Task

Let's say that now you have to add a search bar on top of the list in order to filter it. The search must match any Restaurant name that contains the text the user types.

![Search Restaurants](https://thepracticaldev.s3.amazonaws.com/i/d2rnejoxbpen63ip8qv6.jpg)

As you can see, if the user types `Ma` three Restaurants are shown: `Mamma Mia`; `La Maison du Croissant`; and `Marcante Pizzaria`. They all contain `Ma` in their names.

# Preparing the application

Well, let's prepare our app to work with this new requirement.

### Search bar

Let's add a search bar with the following Thymeleaf code on the `index.html` file:

```html
<form th:action="@{/search}">
    Search by:
    <select name="field">
        <option value="name">Name</option>
    </select>
    <input placeholder="Your search query" th:name="query" th:value="${query}" type="text"/>
    <input type="submit" value="Search"/>
</form>
```

That's a simple form that hits on `/search` address with two request parameters:

- `field` which contains the name of the field the user wants to search on (supporting only `name` for now).
- `query` containing whatever the user types into the search field.

The URL after clicking Search with `Ma` on the search field would be `https://whatever-your-server-is/search?field=name&query=Ma`.

### Controller method

We need a method on the `IndexPage.java` controller to handle the search for the user. This is the method that does the trick:

```java
@RequestMapping("/search")
public String indexWithQuery(@RequestParam("query") String query,
                             @RequestParam("field") String field,
                             Model model) {
    if (field.equals("name")) {
        // TODO add search by name here.
    }
    model.addAttribute("field", field);
    model.addAttribute("query", query);
    return "index";
}
```

Let's understand what's important here:

- `@RequestMapping("/search")` ties this method to the request that the form we added earlier will perform.
- `@RequestParam("query") String query` and `@RequestParam("field") String field` bind the request params (the variables after `?` on the URL) to Java variables we can use.

The rest of the code:
- performs the search (not implemented yet);
- returns the parameters the user selected/typed on the search form (seems reasonable to show this information on the filtered list of restaurants);
- and redirects the user to the index page with the new information we just (not yet :) got. =P

# Finally querying the Repository

Well, looks like we have to do the heavy work now, which is to find all the Restaurants containing a certain string on their names.

We do that with this **huge** and **complicated** code on the `RestaurantRepository.java` interface:

```java
List<Restaurant> findAllByNameContaining(String query);
```

And now, by replacing that `TODO` we added earlier on the `IndexPage.java` controller with the following:

```java
model.addAttribute("restaurants", restaurantRepository.findAllByNameContaining(query));
```

... your new search is working! [Want to try?](https://jpa-queries-blog-post.herokuapp.com/search?field=name&query=Ma) 

# How it works

It's quite simple, actually: Spring Data parses the method name based on certain criteria and creates a query for you. Here's how it goes for this example (`findAllByNameContaining(String name)`):

- `find` determines that entities should be returned. Could also be `read`, `query` and `get` to the same effect. If it was `count` would return the number of entities.
- `All` is ignored. Note that some keywords may be used here.
- `By` indicates to the parser the start of the actual criteria.
- `Name` tells Spring Data that there's a property called `name` on the underlying entity of this repository (`Restaurant`) that will be used as search criteria. In other words, there'll be a `where` clause in that property.
- `Containing` specifies the operator for the `where` clause. If not provided, `=` is implied.
- `(String name)` indicates the parameter that will be received in runtime to use as value on the `where` clause.

I explained, but it's pretty self explanatory, don't you think? Well, with that in mind, let's think of some other valid methods following the same structure:

- `findAllByCuisineNameContaining(String cuisine)` returns Restaurants which cuisine contains the provided value/string. Notice we are querying Restaurants but using fields from the Cuisine entity because Spring Data traverses nested properties!
- `findAllByDeliveryFeeIsLessThanEqual(BigDecimal deliveryFee)` returns Restaurants with delivery fee less than or equal the provided `deliveryFee` value. Notice the parameter type matches the entity's property type.

Now you have more options on your search:

![More search options](https://thepracticaldev.s3.amazonaws.com/i/jo3rsfcfqjwhu5tsjzgj.png)

Other valid methods:

- `countByCuisineName(String cuisine)` to return the number of Restaurants with a given cuisine type.
- `findTopByCuisineNameOrderByDeliveryFeeAsc(String cuisine)` to return the restaurant with the cheapest delivery fee of a given cuisine.

And their usage:
![Cuisine queries](https://thepracticaldev.s3.amazonaws.com/i/y1r0pjythk85qh1as1mn.png)

The repository with all the queries is not even a concrete class, there's no implementation on your code:

```java
public interface RestaurantRepository extends JpaRepository<Restaurant, Long> {

    List<Restaurant> findAllByNameContaining(String query);

    List<Restaurant> findAllByCuisineNameContaining(String cuisine);

    List<Restaurant> findAllByDeliveryFeeIsLessThanEqual(BigDecimal deliveryFee);

    long countByCuisineName(String cuisine);

    Restaurant findTopByCuisineNameOrderByDeliveryFeeAsc(String cuisine);
}
```

You can find more information, including a list of supported keywords on the [official documentation](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods).

# The example app

The working app is [here](https://jpa-queries-blog-post.herokuapp.com/) (wait for Heroku to load the app, it takes a few seconds on the free tier).

# Commits related to this post

Adds the search bar: [d5a02bb](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/d5a02bb903482e988e714e5c3b7ae64e81b49bb8)
Adds search options and cuisine page:  [dd6793b](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/dd6793b957edb5426b1846750e0d4a460836b2ea) commit. 

{% github brunodrugowick/jpa-queries-blog-post no-readme %}