通过这篇 Spring Boot 教程，我想和大家分享一下如何在一个基于 Spring 框架的 Java Web 应用程序中，使用 MySQL 数据库实现全文搜索功能。详细地，您将了解到：

  • 为什么使用全文搜索，它与完全匹配搜索有何不同？
  • 在 MySQL 数据库中创建全文索引
  • 使用 Spring Data JPA 编写全文搜索查询
  • 在应用程序的服务层、控制器层和视图层实现搜索功能

我想您正在开发一个包含产品列表功能的 Spring Boot 项目。现在你想用全文搜索来实现产品搜索功能。

技术：Spring 框架、Spring Boot、Spring MVC、Spring Data JPA、Hibernate、Thymleaf、Bootstrap。

软件程序：Java Development Kit (JDK)、Java IDE（Eclipse、Spring Tool Suite、IntelliJ…）、MySQL 数据库服务器和 MySQL Workbench。

1. 为什么要全文搜索？

简而言之，全文搜索比精确匹配搜索提供更相关、更自然的结果。以下屏幕截图显示了关键字“iphone 7”的精确匹配搜索结果：

spring boot查询es多个索引 springboot全文检索_java

 使用完全匹配搜索，结果仅显示名称或描述中包含“iphone 7”一词的产品。但是对于全文搜索，结果将如下所示（对于相同的关键字）：

spring boot查询es多个索引 springboot全文检索_搜索_02

 你看，除了iPhone 7，它还返回了iPhone XS、iPhone X、iPhone 6S……这对最终用户更有帮助，对吧？

此外，全文搜索提供高级搜索选项，如自然语言查询和特殊搜索运算符，具体取决于数据库引擎。

2.在MySQL数据库中创建全文索引

MySQL 使在表上启用全文搜索变得简单易行。假设我们有包含以下列的表product ：

CREATE TABLE `product` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(256) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`alias` VARCHAR(256) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`brief` VARCHAR(1024) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`detail` LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

并且我们需要实现产品搜索功能，允许用户输入关键字，它将搜索 3 列中的信息：name、brief和detail。

然后我们需要为这个表中的这些列创建一个 FULLTEXT 索引。在 MySQL Workbench 中更改表，单击 Indexes 选项卡。输入类型为FULLTEXT的新索引名称full_text_index（或任何唯一名称），

然后在索引列中，检查 3 列name、brief和detai。然后单击应用按钮以保存更改。

或者，您可以执行以下 SQL 语句来创建索引：

ALTER TABLE `dbname`.`product`
ADD FULLTEXT INDEX `full_text_index` (`name`, `brief`, `detail`) VISIBLE;



一旦创建全文索引，MySQL 将立即自动启动索引进程。并且默认搜索模式是自然语言。

现在，您可以尝试执行以下 SQL Select 查询来测试全文索引：

SELECT id, name FROM fulltextdb.product
WHERE MATCH (name, brief, detail) AGAINST ('apple 7');

结果将是这样的：

spring boot查询es多个索引 springboot全文检索_mysql_03

默认情况下，结果按与给定关键字匹配的行的相关性排序。如果您想将相关性视为分数数字，请执行此查询：



SELECT id, name, MATCH (name, brief, detail) AGAINST ('apple 7') as score
FROM product WHERE MATCH (name, brief, detail) AGAINST ('apple 7');



结果将是这样的：

spring boot查询es多个索引 springboot全文检索_搜索_04

 您还可以在使用 SQL 脚本创建新表时指定 FULLTEXT 索引。例如：

CREATE TABLE `product` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(256) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`alias` VARCHAR(256) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`brief` VARCHAR(1024) NOT NULL COLLATE 'utf8mb4_unicode_ci',
	`detail` LONGTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
	PRIMARY KEY (`id`) USING BTREE,
	FULLTEXT INDEX `full_text_index` (`name`, `brief`, `detail`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

阅读此MySQL 文档以了解有关 MySQL 中的全文搜索功能的更多信息。



3. 使用 Spring Data JPA 编写全文搜索查询

接下来，让我们在我们的 Spring Boot 项目中更新存储库层，用于声明全文搜索方法，如下所示：

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.jpa.repository.JpaRepository;
import org.springframework.data.repository.PagingAndSortingRepository;

// public interface ProductRepository extends JpaRepository<Product, Long> {
public interface ProductRepository extends PagingAndSortingRepository<Product, Long> {

    @Query("SELECT p FROM Product p WHERE " + "CONCAT(p.id, ' ', p.name, ' ' , p.alias, ' ' , p.brief, ' ' , p.detail)"
            + "LIKE %?1%")
    public Page<Product> findAll(String keyword, Pageable pageable);

    @Query(value = "SELECT * FROM product WHERE MATCH(name, brief, detail) "
            + "AGAINST (?1)", nativeQuery = true
    )
    public Page<Product> search(String keyword, Pageable pageable);

}

你看，因为全文搜索是依赖于数据库的，所以我们需要声明一个原生查询，在这个例子中就是 MySQL 查询。请注意，search()方法需要Pageable类型的第二个参数，这意味着搜索结果可以分页。

实体类

package net.codejava;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Product {

    private Long id;
    private String name;
    private String alias;
    private String brief;
    private String detail;

    public Product() {
    }

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAlias() {
        return alias;
    }

    public void setAlias(String alias) {
        this.alias = alias;
    }

    public String getBrief() {
        return brief;
    }

    public void setBrief(String brief) {
        this.brief = brief;
    }

    public String getDetail() {
        return detail;
    }

    public void setDetail(String detail) {
        this.detail = detail;
    }

}

4.在服务层实现搜索功能



接下来，我们需要更新服务层，以实现搜索功能。像这样更新ProductService类：

package net.codejava;

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

@Service
public class ProductService {

    @Autowired
    private ProductRepository repo;

    public Page<Product> listAll(int pageNumber, String sortField, String sortDir, String keyword) {

        Sort sort = Sort.by(sortField);
        sort = sortDir.equals("asc") ? sort.ascending() : sort.descending();

        Pageable pageable = PageRequest.of(pageNumber - 1, 7, sort); // 7 rows per page

        if (keyword != null) {
            return repo.findAll(keyword, pageable);
        }
        return repo.findAll(pageable);
    }

    public void save(Product product) {
        repo.save(product);
    }

    public Product get(Long id) {
        return repo.findById(id).get();
    }

    public void delete(Long id) {
        repo.deleteById(id);
    }

    public static final int SEARCH_RESULT_PER_PAGE = 10;

    public Page<Product> search(String keyword, int pageNum) {
        Pageable pageable = PageRequest.of(pageNum - 1, SEARCH_RESULT_PER_PAGE);
        return repo.search(keyword, pageable);
    }
}



5.在控制器层实现搜索功能

接下来，我们需要更新控制器层，用于处理搜索请求并返回搜索结果。下面是一个示例，展示了如何在控制器层中实现搜索功能：

package net.codejava;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import java.util.List;

@Controller
public class AppController {

    @Autowired
    private ProductService service;

    @RequestMapping("/")
    public String viewHomePage(Model model) {
        // String keyword = "reebok";
        String keyword = null;

        /*
		 * if (keyword != null) { return listByPage(model, 1, "name", "asc", keyword); }
         */
        return listByPage(model, 1, "name", "asc", keyword);

    }

    @GetMapping("/page/{pageNumber}")
    public String listByPage(Model model, @PathVariable("pageNumber") int currentPage,
            @Param("sortField") String sortField, @Param("sortDir") String sortDir, @Param("keyword") String keyword) {

        Page<Product> page = service.listAll(currentPage, sortField, sortDir, keyword);

        long totalItems = page.getTotalElements();
        int totalPages = page.getTotalPages();
        // int currentPage = page.previousPageable().getPageNumber();

        List<Product> listProducts = page.getContent();

        model.addAttribute("totalItems", totalItems);
        model.addAttribute("totalPages", totalPages);
        model.addAttribute("currentPage", currentPage);
        model.addAttribute("listProducts", listProducts); // next bc of thymeleaf we make the index.html

        model.addAttribute("sortField", sortField);
        model.addAttribute("sortDir", sortDir);
        model.addAttribute("keyword", keyword);

        String reverseSortDir = sortDir.equals("asc") ? "desc" : "asc";
        model.addAttribute("reverseSortDir", reverseSortDir);

        return "index";
    }

    @RequestMapping("/new")
    public String showNewProductForm(Model model) {
        Product product = new Product();
        model.addAttribute("product", product);

        return "new_product";
    }

    @RequestMapping(value = "/save", method = RequestMethod.POST)
    public String saveProduct(@ModelAttribute("product") Product product) {
        service.save(product);

        return "redirect:/";
    }

    @RequestMapping("/edit/{id}")
    public ModelAndView showEditProductForm(@PathVariable(name = "id") Long id) {
        ModelAndView modelAndView = new ModelAndView("edit_product");
        Product product = service.get(id);
        modelAndView.addObject("product", product);

        return modelAndView;
    }

    @RequestMapping("/delete/{id}")
    public String deleteProduct(@PathVariable(name = "id") Long id) {
        service.delete(id);

        return "redirect:/";
    }

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

}

启动类

package net.codejava;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class ProductManagerApplication {

	public static void main(String[] args) {
		SpringApplication.run(ProductManagerApplication.class, args);
	}

}



6. 为搜索功能更新视图层

最后，我们需要使用 HTML、Thymeleaf 和 Bootstrap 代码更新视图层。使用以下代码将搜索表单放入每个页面的标题中：

index.html

<!-- http://localhost:8086/ -->

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="utf-8" />
        <title>Product Manager</title>
    </head>

    <body>
        <div align="center">
            <div>
                <h1>Product Manager</h1>
                <a href="/new">Create New Product</a> <br />
                <br />
            </div>

            <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 />
                 
                <input type="submit" value="Search" class="btn btn-outline-success my-2 my-sm-0" />
            </form>


            <div>
                <form th:action="@{/page/1}">
                    <input type="hidden" name="sortField" th:value="${sortField}" /> <input
                        type="hidden" name="sortDir" th:value="${sortDir}" /> Filter: <input
                        type="text" name="keyword" size="50" th:value="${keyword}" required />
                      <input type="submit" value="Search" />   <input
                        type="button" value="Clear" id="btnClear" onclick="clearSearch()" />
                </form>
            </div>

            <div> </div>

            <div>
                <table border="1" cellpadding="10">
                    <thead>
                        <tr>
                            <th><a
                                    th:href="@{'/page/' + ${currentPage} + '?sortField=id&sortDir=' + ${reverseSortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                    Product ID</a>
                            </th>
                            <th><a
                                    th:href="@{'/page/' + ${currentPage} + '?sortField=name&sortDir=' + ${reverseSortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                    Name</a>
                            </th>
                            <th><a
                                    th:href="@{'/page/' + ${currentPage} + '?sortField=alias&sortDir=' + ${reverseSortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                    Alias</a>
                            </th>
                            <th><a
                                    th:href="@{'/page/' + ${currentPage} + '?sortField=brief&sortDir=' + ${reverseSortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                    Brief</a>
                            </th>
                            <th><a
                                    th:href="@{'/page/' + ${currentPage} + '?sortField=detail&sortDir=' + ${reverseSortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                    Detail</a>
                            </th>
                            <th><a>Actions</a></th>
                        </tr>
                    </thead>
                    <tbody>
                        <tr th:each="product : ${listProducts}">
                            <td th:text="${product.id}">Product ID</td>
                            <td th:text="${product.name}">Name</td>
                            <td th:text="${product.alias}">Alias</td>
                            <td th:text="${product.brief}">Brief</td>
                            <td th:text="${product.detail}">Detail</td>
                            <td><a th:href="@{'/edit/' + ${product.id}}">Edit</a>
                                    <a th:href="@{'/delete/' + ${product.id}}">Delete</a>
                            </td>
                        </tr>
                    </tbody>
                </table>
            </div>

            <div>   </div>

            <div>
                Total items: [[${totalItems}]] - Page [[${currentPage}]] of
                [[${totalPages}]]     <a th:if="${currentPage > 1}"
                                                   th:href="@{'/page/1?sortField=' + ${sortField} + '&sortDir=' + ${sortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">First</a>
                <span th:unless="${currentPage > 1}">First</span>    <a
                    th:if="${currentPage > 1}"
                    th:href="@{'/page/' + ${currentPage - 1} + '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">Previous</a>
                <span th:unless="${currentPage > 1}">Previous</span>    <span
                    th:each="i: ${#numbers.sequence(1, totalPages)}"> <a
                        th:if="${i != currentPage}"
                        th:href="@{'/page/' + ${i}} + '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir} + ${keyword != null ? '&keyword=' + keyword : ''}">[[${i}]]</a>
                        <span th:unless="${i != currentPage}">[[${i}]]</span>
                       
                </span> <a th:if="${currentPage < totalPages}"
                           th:href="@{'/page/' + ${currentPage + 1} + '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">Next</a>
                <span th:unless="${currentPage < totalPages}">Next</span>
                   <a th:if="${currentPage < totalPages}"
                                th:href="@{'/page/' + ${totalPages} + '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">Last</a>
                <span th:unless="${currentPage < totalPages}">Last</span>
                  

            </div>
            <script type="text/javascript">
                function clearSearch() {
                    window.location = "/";
                }
            </script>
        </div>
    </body>
</html>

这是搜索结果页面的示例：

search_result.html

<!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>
    </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="@{'/edit/'+${product.id}}" th:title="${product.name}">
                            <b>[[${product.name}]]</b>

                        </a>
                        <br/>
                        <b>[[${product.brief}]]</b>
                        <br/>
                        <b>[[${product.detail}]]</b>
                        <hr />
                    </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>



edit_product.html 


<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="ISO-8859-1">
            <title>Edit product</title>
    </head>
    <body>
        <div align="center">
            <h1>Edit product</h1>
            <br />
            <form action="#" th:action="@{/save}" th:object="${product}"
                  method="post">
                <table border="0" cellpadding="10">
                    <tr>
                        <td>Product ID:</td>
                        <td><input type="text" th:field="*{id}" readonly="readonly" />
                        </td>
                    </tr>
                    <tr>
                        <td>Product Name:</td>
                        <td><input type="text" th:field="*{name}" /></td>
                    </tr>
                    <tr>
                        <td>Alias</td>
                        <td><input type="text" th:field="*{alias}" /></td>
                    </tr>
                    <tr>
                        <td>Brief:</td>
                        <td><input type="text" th:field="*{brief}" /></td>
                    </tr>
                    <tr>
                        <td>Detail:</td>
                        <td><input type="text" th:field="*{detail}" /></td>
                    </tr>
                    <tr>
                        <td colspan="2"><button type="submit">Save</button></td>
                    </tr>

                </table>
            </form>

        </div>
    </body>
</html>

new_product.html

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml"
      xmlns:th="http://www.thymeleaf.org">
    <head>
        <meta charset="ISO-8859-1">
            <title>Create new product</title>
    </head>
    <body>
        <div align="center">
            <h1>Create new product</h1>
            <br />
            <form action="#" th:action="@{/save}" th:object="${product}"
                  method="post">

                <table border="0" cellpadding="10">
                    <tr>
                        <td>Product Name:</td>
                        <td><input type="text" th:field="*{name}" /></td>
                    </tr>
                    <tr>
                        <td>Alias:</td>
                        <td><input type="text" th:field="*{alias}" /></td>
                    </tr>
                    <tr>
                        <td>Brief:</td>
                        <td><input type="text" th:field="*{brief}" /></td>
                    </tr>
                    <tr>
                        <td>Detail:</td>
                        <td><input type="text" th:field="*{detail}" /></td>
                    </tr>
                    <tr>
                        <td colspan="2"><button type="submit">Save</button></td>
                    </tr>

                </table>
            </form>

        </div>
    </body>
</html>


运行输出

spring boot查询es多个索引 springboot全文检索_spring_05

 

spring boot查询es多个索引 springboot全文检索_spring boot查询es多个索引_06

 

spring boot查询es多个索引 springboot全文检索_java_07

7. MySQL全文搜索的优缺点

MySQL 提供了内置的全文搜索功能，这使得将全文搜索功能集成到您的应用程序中变得简单易行。全文搜索适用于单个表格。但是，如果查询涉及到多个表，这将是困难和复杂的。