Spring Boot Full Text Search with MySQL Database Tutorial

Through this Spring Boot tutorial, I’d like to share with you about how to implement full text search functionality in a Java web application based on Spring framework, with MySQL database. In details, you will learn:

• Why using full-text search, and how it differs from exact-match search?
• Create a full text index in MySQL database
• Write a full text search query with Spring Data JPA
• Implement search function in service, controller and view layers of the application

I suppose that you’re developing a Spring Boot project that includes product listing function. And now you want to implement product search function with full-text search.

Technologies: Spring framework, Spring Boot, Spring MVC, Spring Data JPA, Hibernate, Thymleaf, Bootstrap.

Software programs: Java Development Kit (JDK), Java IDE (Eclipse, Spring Tool Suite, IntelliJ…), MySQL database server, and MySQL Workbench.

1. Why Full Text Search?

Simply put, full-text search provides more relevant and more natural results than exact-match search. The following screenshot illustrate a result of exact-match search for the keyword ‘iphone 7’:

With exact-match search, the result showing only products having the word ‘iphone 7’ in its name or description. But with full-text search, the result would look like this (for the same keyword):

You see, besides iPhone 7, it also returns iPhone XS, iPhone X, iPhone 6S… which is more helpful for the end users, right?

In addition, full text search provides advanced search options such as natural language query and special search operators, depending on the database engine.

2. Create a Full Text Index in MySQL Database

MySQL makes it simple & easy to enable full-text search on a table. Suppose that we have the table products that includes the following columns:

And we need to implement the product search function that allows the user to enter a keyword, and it will search for information in 3 columns: name, short_description and full_description.

Then we need to create a FULLTEXT Index for these columns in this table. Alter the table in MySQL Workbench, click Indexes tab. Enter the new Index name full_text_index (or whatever name that is unique) with the type is FULLTEXT, as shown below:

Then in the index columns, check 3 columns name, short_description and full_description. Then click Apply button to save the changes.

Alternately, you can execute the following SQL statement to create the index:

ALTER TABLE `dbname`.`products` 
ADD FULLTEXT INDEX `full_text_index` (`name`, `short_description`, `full_description`) VISIBLE;

MySQL will start the index process automatically and immediately, once a full-text index is created. And the default search mode is natural language.

Now, you can try to execute the following SQL Select query to test the full-text index:

SELECT id, name FROM shopmedb.products 
WHERE MATCH (name, short_description, full_description) AGAINST ('iphone 7');

The result would be like this:

By default, the result is ordered by the relevance of rows matching the given keyword. Execute this query if you want to see the relevancy as score numbers:

SELECT id, name, MATCH (name, short_description, full_description) AGAINST ('iphone 7') as score
FROM shopmedb.products WHERE MATCH (name, short_description, full_description) AGAINST ('iphone 7');

The result would be like this:

You can also specify a FULLTEXT index when creating a new table using SQL script. For example:

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(256) NOT NULL,
  `alias` varchar(256) DEFAULT NULL,
  `short_description` varchar(1024) NOT NULL,
  `full_description` longtext NOT NULL,
  PRIMARY KEY (`id`),
  
  FULLTEXT KEY `full_text_index` (`name`,`short_description`,`full_description`),

);

Read this MySQL doc to learn more about Full-Text Search Functions in MySQL.

3. Write Full Text Search Query with Spring Data JPA

Next, let’s update the repository layer in our Spring Boot project, for declaring a full-text search method as shown below:

package net.codejava;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.shopme.common.entity.product.Product;

public interface ProductRepository extends PagingAndSortingRepository<Product, Integer> {
	
	@Query(value = "SELECT * FROM products WHERE MATCH(name, short_description, full_description) "
			+ "AGAINST (?1)", nativeQuery = true)			
	public Page<Product> search(String keyword, Pageable pageable);
	
}

You see, because full-text search is database-dependent, so we need to declare a native query, which is MySQL query in this case. Note that the search()method requires the 2^nd parameter of type Pageable, meaning the search result can be paginated.

NOTE: if pagination doesn't work, you need to specify a count query that is used for the pagination, something like this: 

@Query(value = "SELECT * FROM products WHERE MATCH...",
	nativeQuery = true,
	countQuery = "SELECT COUNT(*) FROM products WHERE MATCH...")
public Page<Product> search(String keyword, Pageable pageable);

4. Implement Search Function in Service Layer

Next, we need to update service layer, for implementing the search function. Update the ProductService class like this:

package net.codejava;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;

import com.shopme.common.entity.product.Product;

@Service
public class ProductService {

	public static final int SEARCH_RESULT_PER_PAGE = 10;
	
	@Autowired
	private ProductRepository repo;
	
	public Page<Product> search(String keyword, int pageNum) {
		Pageable pageable = PageRequest.of(pageNum - 1, SEARCH_RESULT_PER_PAGE);		
		return repo.search(keyword, pageable);
	}
}

5. Implement Search Function in Controller Layer

Next, we need to update controller layer, for handling search request and returning search result. Here’s an example showing how to implement search functionality in controller layer:

package net.codejava;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;

import com.shopme.common.entity.product.Product;

@Controller
public class ProductController {
	
	@Autowired ProductService productService;
	

	@GetMapping("/search")
	public String search(String keyword, Model model) {
		return searchByPage(keyword, model, 1);
	}
	
	@GetMapping("/search/page/{pageNum}")
	public String searchByPage(String keyword, Model model, 
						@PathVariable(name = "pageNum") int pageNum) {
			
		Page<Product> result = productService.search(keyword, pageNum);
		
		List<Product> listResult = result.getContent();
		
		model.addAttribute("totalPages", result.getTotalPages());
		model.addAttribute("totalItems", result.getTotalElements());
		model.addAttribute("currentPage", pageNum);	
		
		long startCount = (pageNum - 1) * ProductService.SEARCH_RESULT_PER_PAGE + 1;
		model.addAttribute("startCount", startCount);
		
		long endCount = startCount + ProductService.SEARCH_RESULT_PER_PAGE - 1;
		if (endCount > result.getTotalElements()) {
			endCount = result.getTotalElements();
		}
		
		model.addAttribute("endCount", endCount);		
		model.addAttribute("listResult", listResult);
		model.addAttribute("keyword", keyword);		
		
		return "search_result";		
	}
	
}

6. Update View Layer for Search function

Finally, we need to update the view layer with HTML, Thymeleaf and Bootstrap code. Put a search form into the header of every page with the following code:

<form class="form-inline my-2 my-lg-0" th:action="@{/search}" method="get">
	<input type="search" name="keyword" th:value="${keyword}" 
		class="form-control mr-sm-2" placeholder="keyword" required />
	&nbsp;
	<input type="submit" value="Search" class="btn btn-outline-success my-2 my-sm-0" />
</form>

And here’s an example of the search result page:

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0, minimum-scale=1.0">
<title>Search Result</title>
<link rel="stylesheet" type="text/css" th:href="/@{/webjars/bootstrap/css/bootstrap.min.css}" />
<script type="text/javascript" th:src="/@{/webjars/jquery/jquery.min.js}"></script>
<script type="text/javascript" th:src="/@{/webjars/bootstrap/js/bootstrap.min.js}"></script>
</head>
<body>
<div class="container-fluid">
		
	<div>
		<h2>Search Results for '[[${keyword}]]'</h2>
		<br/>
	</div>
	
	<div class="row">
		<div class="col-sm-2" th:each="product : ${listResult}">
			<div>
				<a th:href="/@{'/p/' + ${product.alias}}" th:title="${product.name}">
					<img th:src="/@{${product.mainImagePath}}" height="150" />
					<br/>
					<b>[[${product.shortName}]]</b>
				</a>
			</div>	
		</div>
	</div>	
	
	<div class="text-center m-1" th:if="${totalItems > 0}">
		<span>Showing results # [[${startCount}]] to [[${endCount}]] of [[${totalItems}]]</span>
	</div>
	<div th:unless="${totalItems > 0}">
		<h3>No match found for keyword '[[${keyword}]]'.</h3>
	</div>
	
	<!-- code for pagination buttons goes here... -->	
	
</div>
</body>
</html>

For showing pagination buttons, refer to this article: Spring Data JPA Paging and Sorting Examples

7. Pros and Cons of Full Text Search with MySQL

MySQL provides built-in full text search capability, which makes it simple and easy to integrate full-text search functionality into your application. The full-text search works well with a single table. However, it would be difficult and complex if the query involves in multiple tables.

That’s my tutorial about implementing full-text search function into a Spring Boot application with MySQL database. I hope you find it helpful. To see the coding and testing in action, I recommend you watch the following video:

Other Spring Boot Tutorials:

• 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 Role-Based Authorization Tutorial
• Deploy Spring Boot App with MySQL Database on Heroku

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.