Spring Data JDBC、MySQL 和 Thymeleaf 实现分页、多列排序和多列LIKE过滤/搜索功能

Bertha 。 2023-09-25 12:17 37阅读 0赞

在本教程中，您将学习使用 Spring Data JDBC、MySQL 和 Thymeleaf 为现有 Spring Boot 应用程序实现分页、排序和过滤/搜索功能。

![07b33151bdcb402194f418fdbcc747d2.png][]

![eadf1008727541c29fe92cacb8370dcc.png][]

![5972fc78e7da4b88acdd57dea4508cc0.png][]

![807dfc396bf342ed8c3783432ef5e674.png][]

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
        <parent>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-parent</artifactId>
            <version>2.7.3</version>
            <relativePath/> 
        </parent>
        <groupId>net.codejava</groupId>
        <artifactId>ProductManager</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>ProductManager</name>
        <description>Spring Boot Web App</description>
    
        <properties>
            <java.version>17</java.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-data-jdbc</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-thymeleaf</artifactId>
            </dependency>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-devtools</artifactId>
                
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
            </dependency>
    
        </dependencies>
    
        <build>
            <plugins>
                <plugin>
                    <groupId>org.springframework.boot</groupId>
                    <artifactId>spring-boot-maven-plugin</artifactId>
                </plugin>
            </plugins>
        </build>
    
    </project>

application.properties

spring.jpa.hibernate.ddl-auto=none
    spring.datasource.url=jdbc:mysql://localhost:3306/sales?autoReconnect=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
    spring.datasource.username=root
    spring.datasource.password=root
    #logging.level.root=
    #debug=true
    logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG

MySQL建表脚本

CREATE TABLE IF NOT EXISTS `product` (
      `id` int NOT NULL AUTO_INCREMENT,
      `name` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
      `brand` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
      `madein` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
      `price` float NOT NULL,
      PRIMARY KEY (`id`)
    );

Application.java

package com.example;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class Application {
    
    	public static void main(String[] args) {
    		SpringApplication.run(Application.class, args);
    	}
    
    }

Product.java

package com.example.model;
    
    import org.springframework.data.annotation.Id;
    import org.springframework.data.relational.core.mapping.Table;
    
    import lombok.Data;
    
    @Table("product")
    @Data // lomok
    public class Product {
    
        @Id
        private Long id;
        private String name;
        private String brand;
        private String madein;
        private float price;
    
        public Product() {
        }
    
        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 getBrand() {
            return brand;
        }
    
        public void setBrand(String brand) {
            this.brand = brand;
        }
    
        public String getMadein() {
            return madein;
        }
    
        public void setMadein(String madein) {
            this.madein = madein;
        }
    
        public float getPrice() {
            return price;
        }
    
        public void setPrice(float price) {
            this.price = price;
        }
    
    }

ProductRepository.java

package com.example.repository;
    
    import com.example.model.Product;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.repository.PagingAndSortingRepository;
    import org.springframework.stereotype.Repository;
    
    @Repository
    public interface ProductRepository extends PagingAndSortingRepository<Product, Long> {
    
        Page<Product> findAllByNameContainingOrBrandContainingOrMadeinContaining(String name, String brand, String madein, Pageable pageable);
    
    }

ProductController.java

package com.example.controller;
    
    import com.example.model.Product;
    import com.example.repository.ProductRepository;
    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.Controller;
    import org.springframework.ui.Model;
    import org.springframework.web.bind.annotation.*;
    import org.springframework.web.servlet.ModelAndView;
    
    import java.util.ArrayList;
    import java.util.List;
    
    @Controller
    public class ProductController {
    
        private static final int[] PAGE_SIZES = {2, 5, 7, 10};
    
        @Autowired
        private ProductRepository repo;
    
        private Sort.Direction getSortDirection(String direction) {
            if (direction.equals("asc")) {
                return Sort.Direction.ASC;
            } else if (direction.equals("desc")) {
                return Sort.Direction.DESC;
            }
    
            return Sort.Direction.ASC;
        }
    
        @RequestMapping("/")
        public String viewHomePage(Model model) {
    
            return listByPage(model, 1, 2, null, "id", "asc", null, null);
    
        }
    
        @GetMapping("/page/{pageNumber}")
        public String listByPage(Model model, @PathVariable("pageNumber") int currentPage, @RequestParam("pageSize") int pageSize,
                                 @RequestParam(value = "keyword", required = false) String keyword, @RequestParam(value = "sortField1", required = false) String sortField1, @RequestParam(value = "sortDir1", required = false) String sortDir1, @RequestParam(value = "sortField2", required = false) String sortField2, @RequestParam(value = "sortDir2", required = false) String sortDir2) {
    
            List<Sort.Order> orders = new ArrayList<>();
    
            if (sortField1 != null && !("").equals(sortField1) && !("null").equals(sortField1)) {
                orders.add(new Sort.Order(getSortDirection(sortDir1), sortField1));
            }
    
            if (sortField2 != null && !("").equals(sortField2) && !("null").equals(sortField2)) {
    
                orders.add(new Sort.Order(getSortDirection(sortDir2), sortField2));
            }
    
            Pageable pageable = PageRequest.of(currentPage - 1, pageSize, Sort.by(orders));
    
            Page<Product> page;
            if (keyword != null) {
                page = repo.findAllByNameContainingOrBrandContainingOrMadeinContaining(keyword, keyword, keyword, pageable);
            } else {
                page = repo.findAll(pageable);
            }
    
            long totalItems = page.getTotalElements();
            int totalPages = page.getTotalPages();
            int numberOfElements = page.getNumberOfElements();
    
            List<Product> listProducts = page.getContent();
    
            model.addAttribute("totalItems", totalItems);
            model.addAttribute("totalPages", totalPages);
            model.addAttribute("currentPage", currentPage);
            model.addAttribute("pageSize", pageSize);
            model.addAttribute("sortField1", sortField1);
            model.addAttribute("sortDir1", sortDir1);
            model.addAttribute("sortField2", sortField2);
            model.addAttribute("sortDir2", sortDir2);
            model.addAttribute("pageSizes", PAGE_SIZES);
            model.addAttribute("numberOfElements", numberOfElements);
            model.addAttribute("listProducts", listProducts); // next bc of thymeleaf we make the index.html
    
            model.addAttribute("keyword", keyword);
    
            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
        ) {
            repo.save(product);
    
            return "redirect:/";
        }
    
        @RequestMapping("/edit/{id}")
        public ModelAndView showEditProductForm(@PathVariable(name = "id") Long id
        ) {
            ModelAndView modelAndView = new ModelAndView("edit_product");
            Product product = repo.findById(id).orElse(null);
            modelAndView.addObject("product", product);
    
            return modelAndView;
        }
    
        @RequestMapping("/delete/{id}")
        public String deleteProduct(@PathVariable(name = "id") Long id
        ) {
            repo.deleteById(id);
    
            return "redirect:/";
        }
    
    }

index.html

<!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>产品管理</h1>
                    <a href="/new">添加产品</a> <br />
                    <br />
                </div>
    
                <div>
                    <form th:action="@{/page/1}">
                        <input id="pageSize" type="hidden" name="pageSize" th:value="${pageSize}" />
                        <input id="sortField1" type="hidden" name="sortField1" th:value="${sortField1}" />
                        <input id="sortDir1" type="hidden" name="sortDir1" th:value="${sortDir1}" />
                        <input id="sortField2" type="hidden" name="sortField2" th:value="${sortField2}" />
                        <input id="sortDir2" type="hidden" name="sortDir2" th:value="${sortDir2}" />
                        多字段模糊查询: <input
                            type="text" name="keyword" size="50" th:value="${keyword}" required />
                          <input type="submit" value="搜索" />   <input
                            type="button" value="重置" id="btnClear" onclick="clearSearch()" />
                    </form>
                </div>
    
                <div>
                    <form th:action="@{/page/1}">
                        <input type="hidden" name="pageSize" th:value="${pageSize}" />
                        <input
                            type="hidden" name="keyword" th:value="${keyword}" />
                        排序字段：1: <select name="sortField1">
                            <option value ="id"  th:selected="${sortField1} == 'id'">id</option>
                            <option value ="name" th:selected="${sortField1} == 'name'">name</option>
                            <option value ="brand" th:selected="${sortField1} == 'brand'">brand</option>
                            <option value ="madein" th:selected="${sortField1} == 'madein'">madein</option>
                            <option value ="price" th:selected="${sortField1} == 'price'">price</option>
                        </select><select name="sortDir1">
                            <option value ="asc" th:selected="${sortDir1} == 'asc'">asc</option>
                            <option value ="desc" th:selected="${sortDir1} == 'desc'">desc</option>
                        </select>
                        2: <select name="sortField2">
                            <option value ="">无</option>
                            <option value ="id"  th:selected="${sortField2} == 'id'">id</option>
                            <option value ="name" th:selected="${sortField2} == 'name'">name</option>
                            <option value ="brand" th:selected="${sortField2} == 'brand'">brand</option>
                            <option value ="madein" th:selected="${sortField2} == 'madein'">madein</option>
                            <option value ="price" th:selected="${sortField2} == 'price'">price</option>
                        </select><select name="sortDir2">
                            <option value ="asc" th:selected="${sortDir2} == 'asc'">asc</option>
                            <option value ="desc" th:selected="${sortDir2} == 'desc'">desc</option>
                        </select>
    
                          <input type="submit" value="确定" />  
                    </form>
                </div>
    
                <div> </div>
    
                <div>
                    <table border="1" cellpadding="10">
                        <thead>
                            <tr>
                                <th>
                                    Product ID
                                </th>
                                <th>Name
                                </th>
                                <th>Brand
                                </th>
                                <th>Made In
                                </th>
                                <th>Price
                                </th>
                                <th>Actions</th>
                            </tr>
                        </thead>
                        <tbody>
                            <th:block th:each="product : ${listProducts}">
                                <tr >
                                    <td th:text="${product.id}">Product ID</td>
                                    <td th:text="${product.name}">Name</td>
                                    <td th:text="${product.brand}">Brand</td>
                                    <td th:text="${product.madein}">Made in</td>
                                    <td th:text="${product.price}">Price</td>
                                    <td><a th:href="@{'/edit/' + ${product.id}}">Edit</a>
                                            <a th:href="@{'/delete/' + ${product.id}}">Delete</a>
                                    </td>
                                </tr>
                            </th:block>
                        </tbody>
                    </table>
                </div>
    
                <div>   </div>
    
                <div>
    
                    总计<span th:text="${totalItems}">99</span>行，当前显示<span th:text="(${currentPage} - 1)*${pageSize} +1">1</span> -
                    <span th:text="(${currentPage} - 1)*${pageSize} +${numberOfElements}">5</span>行   每页<select class="form-control pagination" id="pageSizeSelect">
                        <option th:each="selectedPageSize : ${pageSizes}" th:text="${selectedPageSize}"
                                th:value="${selectedPageSize}"
                                th:selected="${selectedPageSize} == ${pageSize}">5</option>
                    </select>行
                        转到第<select class="form-control pagination" id="pageSelect">
                        <option th:each="i: ${#numbers.sequence(1, totalPages)}" th:text="${i}"
                                th:value="${i}"
                                th:selected="${i} == ${currentPage}">2</option>
                    </select>页，总计<span th:text="${totalPages}">99</span>页   <a href="First.html" th:if="${currentPage > 1}"
                                                                                    th:href="@{'/page/1?sortField1=' + ${sortField1}+ '&sortDir1=' + ${sortDir1}+ '&sortField2=' + ${sortField2}+ '&sortDir2=' + ${sortDir2} + '&pageSize=' + ${pageSize}+ ${keyword != null ? '&keyword=' + keyword : ''}}">|<最前页</a>
                    <span th:unless="${currentPage > 1}">|<最前页</span>    <a href="Previous.html"
                                                                                         th:if="${currentPage > 1}"
                                                                                         th:href="@{'/page/' + ${currentPage - 1} + '?sortField1=' + ${sortField1}+ '&sortDir1=' + ${sortDir1}+ '&sortField2=' + ${sortField2}+ '&sortDir2=' + ${sortDir2}+ '&pageSize=' + ${pageSize}+ ${keyword != null ? '&keyword=' + keyword : ''}}"><前一页</a>
                    <span th:unless="${currentPage > 1}"><前一页</span>     <a href="Next.html" th:if="${currentPage < totalPages}"
                                                                                         th:href="@{'/page/' + ${currentPage + 1} + '?sortField1=' + ${sortField1}+ '&sortDir1=' + ${sortDir1}+ '&sortField2=' + ${sortField2}+ '&sortDir2=' + ${sortDir2}+ '&pageSize=' + ${pageSize}+ ${keyword != null ? '&keyword=' + keyword : ''}}">后一页></a>
                    <span th:unless="${currentPage < totalPages}">后一页></span>
                       <a href="Last.html" th:if="${currentPage < totalPages}"
                                    th:href="@{'/page/' + ${totalPages} + '?sortField1=' + ${sortField1}+ '&sortDir1=' + ${sortDir1}+ '&sortField2=' + ${sortField2}+ '&sortDir2=' + ${sortDir2}+ '&pageSize=' + ${pageSize}+ ${keyword != null ? '&keyword=' + keyword : ''}}">最后页>|</a>
                    <span th:unless="${currentPage < totalPages}">最后页>|</span>
                      
    
                </div>
    
                <script type="text/javascript">
                    function clearSearch() {
                        window.location = "/";
                    }
                    document.getElementById("pageSizeSelect").addEventListener("change", function () {
                        window.location.replace("/page/1/?sortField1=id&sortDir1=asc&pageSize=" + this.value);
                    });
                    document.getElementById("pageSelect").addEventListener("change", function () {
                        window.location.replace("/page/" + this.value + "/?sortField1=" + document.getElementById('sortField1').value + "&sortDir1=" + document.getElementById('sortDir1').value + "&pageSize=" + document.getElementById('pageSize').value);
                    });
    
                </script>
    
            </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>Brand</td>
    					<td><input type="text" th:field="*{brand}" /></td>
    				</tr>
    				<tr>
    					<td>Made in:</td>
    					<td><input type="text" th:field="*{madein}" /></td>
    				</tr>
    				<tr>
    					<td>Price:</td>
    					<td><input type="text" th:field="*{price}" /></td>
    				</tr>
    				<tr>
    					<td colspan="2"><button type="submit">Save</button></td>
    				</tr>
    
    			</table>
    		</form>
    
    	</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>Brand</td>
    					<td><input type="text" th:field="*{brand}" /></td>
    				</tr>
    				<tr>
    					<td>Made in:</td>
    					<td><input type="text" th:field="*{madein}" /></td>
    				</tr>
    				<tr>
    					<td>Price:</td>
    					<td><input type="text" th:field="*{price}" /></td>
    				</tr>
    				<tr>
    					<td colspan="2"><button type="submit">Save</button></td>
    				</tr>
    
    			</table>
    		</form>
    
    	</div>
    </body>
    </html>

下载：[GitHub - allwaysoft/Spring-Data-JDBC\_Sorting\_Paging\_Search\_Filter-Thymeleaf-Sort-by-multiple-Columns][GitHub - allwaysoft_Spring-Data-JDBC_Sorting_Paging_Search_Filter-Thymeleaf-Sort-by-multiple-Columns]

[07b33151bdcb402194f418fdbcc747d2.png]: https://img-blog.csdnimg.cn/07b33151bdcb402194f418fdbcc747d2.png
[eadf1008727541c29fe92cacb8370dcc.png]: https://img-blog.csdnimg.cn/eadf1008727541c29fe92cacb8370dcc.png
[5972fc78e7da4b88acdd57dea4508cc0.png]: https://img-blog.csdnimg.cn/5972fc78e7da4b88acdd57dea4508cc0.png
[807dfc396bf342ed8c3783432ef5e674.png]: https://img-blog.csdnimg.cn/807dfc396bf342ed8c3783432ef5e674.png
[GitHub - allwaysoft_Spring-Data-JDBC_Sorting_Paging_Search_Filter-Thymeleaf-Sort-by-multiple-Columns]: https://github.com/allwaysoft/Spring-Data-JDBC_Sorting_Paging_Search_Filter-Thymeleaf-Sort-by-multiple-Columns/