Spring Data JPA Filter Search Examples

In this tutorial, you will learn to implement filter/search functionality for an existing Spring Boot application using Spring Data JPA, Hibernate, MySQL and Thymeleaf. Suppose that we have an existing Spring Boot application that displays a list of products as follows:

There can be many products in the database, so we want to add search function that helps the users to easily find products based on a specific keyword.

The code examples in this tutorial are based on the Project Manager project which can be downloaded from this Spring Boot CRUD tutorial.

1. Build Search Query

I don’t use Criteria API because the search query is static – a simple JPQL is enough. Update the ProductRepository interface to declare the search query as follows:

public interface ProductRepository extends JpaRepository<Product, Long> {
	
	@Query("SELECT p FROM Product p WHERE p.name LIKE %?1%"
			+ " OR p.brand LIKE %?1%"
			+ " OR p.madein LIKE %?1%"
			+ " OR CONCAT(p.price, '') LIKE %?1%")
	public List<Product> search(String keyword);
}

As you can see, this query searches for products having one of its fields matching the specified keyword. Products whose any fields (name, brand, madein or price) containing the keyword will be included in the result.

Note that for number field, we must concatenate it with an empty string so its value can be compared using the LIKE operator:

CONCAT(p.price, '')

You can simplify the search query by concatenating all the related fields like this:

@Query("SELECT p FROM Product p WHERE CONCAT(p.name, p.brand, p.madein, p.price) LIKE %?1%")
public List<Product> search(String keyword);

More accurately, we should concatenate each field with a whitespace to avoid undesired results from the string concatenation. So the final search query would be:

@Query("SELECT p FROM Product p WHERE CONCAT(p.name, ' ', p.brand, ' ', p.madein, ' ', p.price) LIKE %?1%")

As you can see, using a search query like this is easy to understand and much simpler than using Criteria API which is more suitable for dynamic queries.

2. Update Spring Service and Controller Classes

Next, update the listAll() method of the ProductService class to take a keyword as parameter like this:

@Service
public class ProductService {
	@Autowired
	private ProductRepository repo;
	
	public List<Product> listAll(String keyword) {
		if (keyword != null) {
			return repo.search(keyword);
		}
		return repo.findAll();
	}
	
	...
}

If keyword is null, invoke the default findAll() method of the repository interface (provided by Spring Data JPA).

And update the handler method in the controller class to read keyword from parameter in the URL:

@Controller
public class AppController {
	@Autowired
	private ProductService service;
	
	@RequestMapping("/")
	public String viewHomePage(Model model, @Param("keyword") String keyword) {
		List<Product> listProducts = service.listAll(keyword);
		model.addAttribute("listProducts", listProducts);
		model.addAttribute("keyword", keyword);
		
		return "index";
	}
	
	...
}

As you can see, beside the search result stored in the listProducts object added to the model, the keyword is also added to the model so in the search form we can display the keyword again.

3. Code Search form with Thymeleaf

To add a filter textbox with Search button in the view page, add the following code in the top of the index.html file:

<form th:action="@{/}">
	Filter: <input type="text" name="keyword" id="keyword" size="50" th:value="${keyword}" required />
	&nbsp;
	<input type="submit" value="Search" />
	&nbsp;
	<input type="button" value="Clear" id="btnClear" onclick="clearSearch()" />
</form>

As you can see, we use Thymeleaf to render the values of the form action and the keyword. The Clear button allows the users to reset the search form. Also write the following Javascript code:

<script type="text/javascript">
	function clearSearch() {
		window.location = "[[@{/}]]";
	}
</script>

Clear search will display all products. Note that we use Thymeleaf expression [[@{/}]] to render the homepage URL properly.

4. Test Search / Filter Function

Now, start our Spring Boot application. Go to the homepage and enter phone as keyword and click Search button. The result would be displayed as follows:

Because we search for any fields that containing the keyword, type usa will show all products made in the USA:

So far I’ve share with you a solution to implement filter/search function or an existing Spring Boot project using Spring Data JPA, Hibernate, MySQL and Thymeleaf. You can also watch the video version of this tutorial below, and download the sample project under the attachments section.

Other Spring Boot Tutorials:

• Spring Data JPA Paging and Sorting Examples
• Spring Boot automatic restart using Spring Boot DevTools
• Spring Boot Form Handling Tutorial with Spring Form Tags and JSP
• How to create a Spring Boot Web Application (Spring MVC with JSP/ThymeLeaf)
• Spring Boot - Spring Data JPA - MySQL Example
• Spring Boot CRUD Example with Spring MVC – Spring Data JPA – ThymeLeaf - Hibernate - MySQL
• Spring Boot Hello World RESTful Web Services Tutorial
• How to use JDBC with Spring Boot
• Spring Boot CRUD Web Application with JDBC - Thymeleaf - Oracle
• Spring Boot RESTful CRUD API Examples with MySQL database
• How to package Spring Boot application to JAR and WAR
• Spring Boot Security Authentication with JPA, Hibernate and MySQL

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:

ProductManagerFilterSearch.zip

[Sample Spring Boot project]

73 kB