[java, spring, jpa, query]


You'll have a better experience reading in DEV

Click here to continue reading this post there >>

However, if you want to know more about the project to mirror my posts from DEV here (and why), go ahead and read more.

You can continue to read here too, it's up to you... =]


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:

brunodrugowick

Querying your Spring Data JPA Repository - Basic Setup

Bruno Drugowick ・ Jan 12 '20 ・ 4 min read

#java #spring #jpa #repository

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

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:

<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>
Enter fullscreen mode Exit fullscreen mode

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:

@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";
}
Enter fullscreen mode Exit fullscreen mode

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:

List<Restaurant> findAllByNameContaining(String query);
Enter fullscreen mode Exit fullscreen mode

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

model.addAttribute("restaurants", restaurantRepository.findAllByNameContaining(query));
Enter fullscreen mode Exit fullscreen mode

... your new search is working! Want to try?

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

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

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

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);
}
Enter fullscreen mode Exit fullscreen mode

You can find more information, including a list of supported keywords on the official documentation.

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

Adds the search bar: d5a02bb
Adds search options and cuisine page: dd6793b commit.

GitHub logo brunodrugowick / jpa-queries-blog-post

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

…