Three steps to extend a Spring Data JPA repository with your own code
Tags: spring, repository, java, jpa
Published: 2020-02-21 01:37:14

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

# What about an advanced search?

What if you want to search by any field available on the Restaurant and combine then without having to select specific searches.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/aex4l3r7r7zbngzlgif8.gif)

Well, it's not Google, but it's powerful enough for a lot of use cases.

# Let's prepare the app

Well, you know the drill. Let's build what's necessary for the app to work with this new advanced search option.

The search form looks like this:

```html
<form th:action="@{/advancedSearch/perform}">
    <div class="card mb-auto">
        <div class="card-header">
            <h4>Filter Restaurants</h4>
        </div>
        <div class="card-body">
            <div class="form-group row small">
                <div class="col col-sm-4 float-left"></div>
                <div class="col col-sm-8 float-right">
                    <a class="float-right" href="/">Simple search</a>
                </div>
            </div>
            <div class="form-group row">
                <label class="col col-sm-4" for="name">Name</label>
                <input class="form-control col-sm-8" id="name" placeholder="<empty>" th:name="name"
                       th:value="${search.name}" type="text"/>
            </div>
            <div class="form-group row">
                <label class="col col-sm-4" for="address">Address</label>
                <input class="form-control col-sm-8" id="address" placeholder="<empty>" th:name="address"
                       th:value="${search.address}" type="text"/>
            </div>
            <div class="form-group row">
                <label class="col col-sm-4" for="minDeliveryFee">Delivery Fee</label>
                <label class="col col-sm-1" for="minDeliveryFee">Min</label>
                <input class="form-control col-sm-3" id="minDeliveryFee" placeholder="<min>" th:name="minDeliveryFee"
                       th:value="${search.minDeliveryFee}" type="text"/>
                <label class="col col-sm-1" for="maxDeliveryFee">Max</label>
                <input class="form-control col-sm-3" id="maxDeliveryFee" placeholder="<max>" th:name="maxDeliveryFee"
                       th:value="${search.maxDeliveryFee}" type="text"/>
            </div>
            <div class="form-group row">
                <label class="col col-sm-4" for="cuisine">Cuisine</label>
                <input class="form-control col-sm-8" id="cuisine" placeholder="<empty>" th:name="cuisine"
                       th:value="${search.cuisine}" type="text"/>
            </div>
            <div class="form-group row">
                <label class="col col-sm-4" for="city">City</label>
                <input class="form-control col-sm-8" id="city" placeholder="<empty>" th:name="city"
                       th:value="${search.city}" type="text"/>
            </div>
            <div class="form-group row">
                <div class="col col-sm-4"></div>
                <input class="btn btn-primary col col-sm-8" type="submit" value="Submit">
            </div>
        </div>
    </div>
</form>
```

We need new controller methods to handle the new page and the search operation:

```java
@RequestMapping("/advancedSearch")
public String advancedSearch(Model model) {
    model.addAttribute("restaurants", restaurantRepository.findAll());
    model.addAttribute("search", new AdvancedSearch());
    return "advancedSearch";
}

@RequestMapping("/advancedSearch/perform")
public String advancedSearchWithQuery(@ModelAttribute AdvancedSearch advancedSearch, Model model) {
    model.addAttribute("restaurants", restaurantRepository.advancedSearch(advancedSearch));

    model.addAttribute("search", advancedSearch);
    return "advancedSearch";
}
```

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/jvo51qw5q568q0wyf5hb.gif)

Keep an eye on:

- The `@ModelAttribute` annotation: it maps the input to a new class called...
- `AdvancedSearch`. This is a simple bean with fields to hold data coming from the form. With the help of [Project Lombok](https://projectlombok.org/) this class is [quite simple](https://github.com/brunodrugowick/jpa-queries-blog-post/blob/master/src/main/java/dev/drugowick/jpaqueriesblogpost/web/pages/dto/AdvancedSearch.java).
  - We use this class to pass data between the form and the app on both directions. This is how we can show the query inputs to the user even after the page is refreshed to show the search results (remember this is not your typical SPA, ok?).

# Custom Repository Methods

But you may also have noticed that we are calling a new method on the `restaurantRepository` called `advancedSearch` passing the homonym `advancedSearch` object via parameter. No, that's not a Spring Data JPA default method (would be nice, hã?), but the ability to create our own custom methods is the powerful stuff we're learning here!

Let's see how in 3 steps.

## Step 1: Create a new interface to hold the method declarations

```java
public interface CustomRestaurantRepository {
    List<Restaurant> advancedSearch(AdvancedSearch advancedSearch);
}
```

Notice the definition of the `advancedSearch` method. That's all we need right now.

## Step 2: Make your Spring Data JPA repository extend your new interface

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

Notice now that our repository extends both `JPARepository` (from Spring Data JPA project) and `CustomRestaurantRepository` (a class of our own to define repository methods). We can now call our new method, but what about its code?

## Step 3: Implement the custom method

Now it's just a matter of implementing the code we need. We will create a `CustomRestaurantRepositoryImpl` class that implements our newly created `CustomRestaurantRepository` interface.

```java
@Repository
public class CustomRestaurantRepositoryImpl implements CustomRestaurantRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List<Restaurant> advancedSearch(AdvancedSearch advancedSearch) {

        var jpql = new StringBuilder();
        jpql.append("from Restaurant where 1=1 ");

        var parameters = new HashMap<String, Object>();

        if (StringUtils.hasLength(advancedSearch.getName())) {
            jpql.append("and name like :name ");
            parameters.put("name", "%" + advancedSearch.getName() + "%");
        }

        if (StringUtils.hasLength(advancedSearch.getAddress())) {
            jpql.append("and address like :address ");
            parameters.put("address", "%" + advancedSearch.getAddress() + "%");
        }

        if (advancedSearch.getMinDeliveryFee() != null) {
            jpql.append("and deliveryFee >= :startFee ");
            parameters.put("startFee", advancedSearch.getMinDeliveryFee());
        }

        if (advancedSearch.getMaxDeliveryFee() != null) {
            jpql.append("and deliveryFee <= :endingFee ");
            parameters.put("endingFee", advancedSearch.getMaxDeliveryFee());
        }

        if (StringUtils.hasLength(advancedSearch.getCuisine())) {
            jpql.append("and cuisine.name like :cuisine ");
            parameters.put("cuisine", "%" + advancedSearch.getCuisine() + "%");
        }

        if (StringUtils.hasLength(advancedSearch.getCity())) {
            jpql.append("and city like :city ");
            parameters.put("city", "%" + advancedSearch.getCity() + "%");
        }

        TypedQuery<Restaurant> query = entityManager.createQuery(jpql.toString(), Restaurant.class);

        parameters.forEach((key, value) -> query.setParameter(key, value));

        return query.getResultList();
    }
}
```

A lot to unpack here:

- First, we get ahold of an `EntityManager` injecting it via `@PersistenceContext`. With this we can perform operations via JPA.
- Then we override the `advancedSearch` method to:
  - Check each and every property of the `AdvancedSearch` object adding it, if not null, to a custom JPQL query.
  - Match the appropriate parameters. First, on a temporary map and then actually mapping on the query.
  - Execute the query returning the results.
- Last, but not least, the suffix `Impl` is what actually tell Spring Data JPA that this is a custom implementation of the existing `RestaurantRepository`. Adding our interface and making the Spring Data JPA interface extend is only to make the code readable. You should do this!

# Additional challenge

Notice that the example app has also an option to select the logical operator to use when performing the advanced search, `AND` or `OR`. You may want to try to implement it yourself, but if you don't want to, [here's the implementation](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/a5d756ce3f968beeea536d0740005ae2e36c19a0) for you.

That's the final result:

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/aex4l3r7r7zbngzlgif8.gif)

# 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

The preparation and core code is [here](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/b7fb9718824cfe9fa74113ed2ad772a32690a46f).
The logical operator addition is [here](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/a5d756ce3f968beeea536d0740005ae2e36c19a0).
And there's a UI improvement I did [here](https://github.com/brunodrugowick/jpa-queries-blog-post/commit/06dbc0f30d2e108282176b3a2044bf7ef53e8357)

{% github https://github.com/brunodrugowick/jpa-queries-blog-post no-readme %}