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.

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:



<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:



@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";
}

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 this class is quite simple.
- 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



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



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.



@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 for you.

That's the final result:

The example app

The working app is here (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.
The logical operator addition is here.
And there's a UI improvement I did here

Blog

Three steps to extend a Spring Data JPA repository with your own code

Bruno Drugowick

What about an advanced search?

Let's prepare the app

Custom Repository Methods

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

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

Step 3: Implement the custom method

Additional challenge

The example app

Commits related to this post

brunodrugowick / jpa-queries-blog-post

A demo project for a blog post about (Spring Data) JPA.

Join Our Newsletter. No Spam, Only the good stuff.

Related