Spring Data JDBC、MySQL 和 Thymeleaf 实现分页、排序和多列LIKE过滤/搜索功能，并实现基于@Version注解的乐观锁定，防止更新丢失

红太狼 2023-09-25 12:43 63阅读 0赞

在本教程中，您将学习使用 Spring Data JDBC、MySQL 和 Thymeleaf 为现有 Spring Boot 应用程序实现分页、排序和过滤/搜索功能，并实现基于@Version注解的乐观锁定，防止更新丢失。

![53a3c9869ad6448caa591d9e0436c949.png][]

![19873d0c05f346eea81557f0beb39dae.png][]

![9ce7b75f5cce4efd866d405ce02bcc6e.png][]

![55e2246f38e9460ea7b29630bf875ec6.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,
      `version` bigint DEFAULT '0',
      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;
    import org.springframework.data.annotation.Version;
    import org.springframework.data.relational.core.mapping.Column;
    
    @Table("product")
    @Data // lomok
    public class Product {
    
        @Id
        private Long id;
        private String name;
        private String brand;
        private String madein;
        private float price;
        @Column("version")
        @Version
        private Long version;
    
    }

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.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.bind.annotation.RequestParam;
    import org.springframework.web.servlet.ModelAndView;
    
    import java.util.List;
    import org.springframework.data.domain.PageRequest;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.domain.Sort;
    
    @Controller
    public class ProductController {
    
        private static final int[] PAGE_SIZES = {2, 5, 7, 10};
    
        @Autowired
        private ProductRepository repo;
    
        @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, 2, "name", "asc", keyword);
    
        }
    
        @GetMapping("/page/{pageNumber}")
        public String listByPage(Model model, @PathVariable("pageNumber") int currentPage, @RequestParam("pageSize") int pageSize,
                @RequestParam("sortField") String sortField, @RequestParam("sortDir") String sortDir, @RequestParam(value = "keyword", required = false) String keyword) {
    
            Sort sort = Sort.by(sortField);
            sort = sortDir.equals("asc") ? sort.ascending() : sort.descending();
    
            Pageable pageable = PageRequest.of(currentPage - 1, pageSize, sort); // 7 rows per page
            Page<Product> page = null;
            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();
            // int currentPage = page.previousPageable().getPageNumber();
    
            List<Product> listProducts = page.getContent();
    
            model.addAttribute("totalItems", totalItems);
            model.addAttribute("totalPages", totalPages);
            model.addAttribute("currentPage", currentPage);
            model.addAttribute("pageSize", pageSize);
            model.addAttribute("pageSizes", PAGE_SIZES);
            model.addAttribute("numberOfElements", numberOfElements);
            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) {
            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).get();
            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>Product Manager</h1>
                    <a href="/new">Create New Product</a> <br />
                    <br />
                </div>
    
                <div>
                    <form th:action="@{/page/1}">
                        <input type="hidden" name="pageSize" th:value="${pageSize}" />
                        <input type="hidden" name="sortField" th:value="${sortField}" /> <input
                            type="hidden" name="sortDir" th:value="${sortDir}" /> Name 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} +'&pageSize=' + ${pageSize} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                        Product ID</a>
                                </th>
                                <th><a
                                        th:href="@{'/page/' + ${currentPage} + '?sortField=name&sortDir=' + ${reverseSortDir} +'&pageSize=' + ${pageSize}+ ${keyword != null ? '&keyword=' + keyword : ''}}">
                                        Name</a>
                                </th>
                                <th><a
                                        th:href="@{'/page/' + ${currentPage} + '?sortField=brand&sortDir=' + ${reverseSortDir}+'&pageSize=' + ${pageSize} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                        Brand</a>
                                </th>
                                <th><a
                                        th:href="@{'/page/' + ${currentPage} + '?sortField=madein&sortDir=' + ${reverseSortDir}+'&pageSize=' + ${pageSize} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                        Made In</a>
                                </th>
                                <th><a
                                        th:href="@{'/page/' + ${currentPage} + '?sortField=price&sortDir=' + ${reverseSortDir}+'&pageSize=' + ${pageSize} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                        Price</a>
                                </th>
                                <th><a>Actions</a></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?sortField=' + ${sortField} + '&sortDir=' + ${sortDir} +'&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} + '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir} +'&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} + '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir} +'&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} + '?sortField=' + ${sortField} + '&sortDir=' + ${sortDir} +'&pageSize=' + ${pageSize}+ ${keyword != null ? '&keyword=' + keyword : ''}}">最后页>|</a>
                    <span th:unless="${currentPage < totalPages}">最后页>|</span>
                      
    
                </div>
    
    
    
                <script type="text/javascript" src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
                <script type="text/javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
                <script type="text/javascript">
                                function clearSearch() {
                                    window.location = "/";
                                }
                </script>
                <script type="text/javascript" th:inline="javascript">
                    /*<![CDATA[*/
                    $(document).ready(function () {
                        changePageSize();
                        changePage();
                    });
                    function changePageSize() {
                        $('#pageSizeSelect').change(function (evt) {
                            window.location.replace("/page/1/?sortField=id&sortDir=asc&pageSize=" + this.value);
                        });
    
                    }
                    /*]]>*/
                </script>
    
                <script th:inline="javascript">
                    function changePage() {
                        $('#pageSelect').change(function (evt) {
                            window.location.replace("/page/" + this.value + "/?sortField=id&sortDir=asc&pageSize=" + [[${pageSize}]]);
                        });
                    }
                </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">
                    <input type="hidden" th:field="*{version}"  />
                    <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>

下载：

[https://github.com/allwaysoft/ProductManagerOptimisticLocking][https_github.com_allwaysoft_ProductManagerOptimisticLocking]

[53a3c9869ad6448caa591d9e0436c949.png]: https://img-blog.csdnimg.cn/53a3c9869ad6448caa591d9e0436c949.png
[19873d0c05f346eea81557f0beb39dae.png]: https://img-blog.csdnimg.cn/19873d0c05f346eea81557f0beb39dae.png
[9ce7b75f5cce4efd866d405ce02bcc6e.png]: https://img-blog.csdnimg.cn/9ce7b75f5cce4efd866d405ce02bcc6e.png
[55e2246f38e9460ea7b29630bf875ec6.png]: https://img-blog.csdnimg.cn/55e2246f38e9460ea7b29630bf875ec6.png
[https_github.com_allwaysoft_ProductManagerOptimisticLocking]: https://github.com/allwaysoft/ProductManagerOptimisticLocking