Spring Boot 全文搜索与 MySQL 数据库教程

通过这篇 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”的精确匹配搜索结果：

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

 你看，除了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');

结果将是这样的：

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

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

结果将是这样的：

 您还可以在使用 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 />
                &nbsp;
                <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 />
                    &nbsp; <input type="submit" value="Search" /> &nbsp; <input
                        type="button" value="Clear" id="btnClear" onclick="clearSearch()" />
                </form>
            </div>

            <div>&nbsp;</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>
                                &nbsp;&nbsp;&nbsp; <a th:href="@{'/delete/' + ${product.id}}">Delete</a>
                            </td>
                        </tr>
                    </tbody>
                </table>
            </div>

            <div>&nbsp; &nbsp;</div>

            <div>
                Total items: [[${totalItems}]] - Page [[${currentPage}]] of
                [[${totalPages}]] &nbsp; &nbsp; <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> &nbsp;&nbsp; <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> &nbsp;&nbsp; <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>
                    &nbsp; &nbsp; <span th:unless="${i != currentPage}">[[${i}]]</span>
                    &nbsp; &nbsp;
                </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>
                &nbsp;&nbsp; <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>
                &nbsp;&nbsp;

            </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 Data JPA 分页和排序示例

运行输出

 

 

7. MySQL全文搜索的优缺点

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