Spring Data JDBC结合JdbcTemplate、MySQL 和 Thymeleaf 实现分页、排序和全字段LIKE过滤/搜索功能

一时失言乱红尘 2023-09-26 10:52 54阅读 0赞

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

![4838e2798a5c414fb50f4ec8d3cb34d3.png][]

![56dee575b1944dcdb6ac0acd7b5f5666.png][]

![5b82b39319fc4772bb7dd81d3b5210e8.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

ProductManagerApplication.java

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);
    	}
    
    }

Product.java

package net.codejava;
    
    import org.springframework.data.annotation.Id;
    import org.springframework.data.relational.core.mapping.Column;
    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;
    
        protected 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 net.codejava;
    
    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>, ProductRepositoryCustom {
    
        Page<Product> findAllByNameContaining(String name, Pageable pageable);
    
        Page<Product> findAllByNameLikeOrBrandLikeOrMadeinLike(String name, String brand, String madein, Pageable pageable);
    }

ProductRepositoryCustom.java

package net.codejava;
    
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.Pageable;
    
    public interface ProductRepositoryCustom {
    
        public Page<Product> findAllByContaining(String keyword, Pageable page);
    }

ProductRepositoryImpl.java

package net.codejava;
    
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.List;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.PageImpl;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.domain.Sort;
    import org.springframework.jdbc.core.JdbcTemplate;
    
    public class ProductRepositoryImpl implements ProductRepositoryCustom {
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        private Product mapUserResult(final ResultSet rs) throws SQLException {
            Product product = new Product();
            product.setId(rs.getLong("id"));
            product.setName(rs.getString("name"));
            product.setBrand(rs.getString("brand"));
            product.setMadein(rs.getString("madein"));
            product.setPrice(rs.getLong("price"));
            return product;
        }
    
        @Override
        public Page<Product> findAllByContaining(String keyword, Pageable page) {
    
            int count = jdbcTemplate.queryForObject("SELECT count(* ) FROM product WHERE CONCAT(id, ' ', name, ' ' , brand, ' ' , madein, ' ' , price) LIKE CONCAT('%',?,'%') ", Integer.class, keyword);
    
            Sort.Order order = !page.getSort().isEmpty() ? page.getSort().toList().get(0) : Sort.Order.by("ID");
    
            List<Product> products = jdbcTemplate.query("SELECT * FROM product WHERE CONCAT(id, ' ', name, ' ' , brand, ' ' , madein, ' ' , price) LIKE CONCAT('%',?,'%') ORDER BY " + order.getProperty() + " "
                    + order.getDirection().name() + " LIMIT " + page.getPageSize() + " OFFSET " + page.getOffset(),
                    (rs, rowNum) -> mapUserResult(rs), keyword
            );
    
            return new PageImpl<Product>(products, page, count);
        }
    
    }

使用NamedParameterJdbcTemplate和BeanPropertyRowMapper的ProductRepositoryImpl.java

package net.codejava;
    
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.data.domain.Page;
    import org.springframework.data.domain.PageImpl;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.domain.Sort;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
    
    public class ProductRepositoryImpl implements ProductRepositoryCustom {
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        public int count() {
            return jdbcTemplate.queryForObject("SELECT count(*) FROM product", Integer.class);
        }
    
        @Override
        public Page<Product> findAllByContaining(String keyword, Pageable page) {
            NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    
            Sort.Order order = !page.getSort().isEmpty() ? page.getSort().toList().get(0) : Sort.Order.by("ID");
            Map parameters = new HashMap();
            parameters.put("keyword", keyword);
    
            List<Product> products = namedParameterJdbcTemplate.query("SELECT * FROM product WHERE CONCAT(id, ' ', name, ' ' , brand, ' ' , madein, ' ' , price) LIKE CONCAT('%',:keyword,'%') ORDER BY " + order.getProperty() + " "
                    + order.getDirection().name() + " LIMIT " + page.getPageSize() + " OFFSET " + page.getOffset(),
                    parameters, BeanPropertyRowMapper.newInstance(Product.class)
            );
    
            return new PageImpl<Product>(products, page, count());
        }
    
    }

ProductService.java

AppController.java

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:/";
    	}
    
    }

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="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} + ${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=brand&sortDir=' + ${reverseSortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                        Brand</a>
                                </th>
                                <th><a
                                        th:href="@{'/page/' + ${currentPage} + '?sortField=madein&sortDir=' + ${reverseSortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                        Made In</a>
                                </th>
                                <th><a
                                        th:href="@{'/page/' + ${currentPage} + '?sortField=id&sortDir=' + ${reverseSortDir} + ${keyword != null ? '&keyword=' + keyword : ''}}">
                                        Price</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.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>
                        </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>

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\_JdbcTemplate\_Sorting\_Paging\_Search\_Filter][GitHub - allwaysoft_Spring-Data-JDBC_JdbcTemplate_Sorting_Paging_Search_Filter]

[4838e2798a5c414fb50f4ec8d3cb34d3.png]: https://img-blog.csdnimg.cn/4838e2798a5c414fb50f4ec8d3cb34d3.png
[56dee575b1944dcdb6ac0acd7b5f5666.png]: https://img-blog.csdnimg.cn/56dee575b1944dcdb6ac0acd7b5f5666.png
[5b82b39319fc4772bb7dd81d3b5210e8.png]: https://img-blog.csdnimg.cn/5b82b39319fc4772bb7dd81d3b5210e8.png
[GitHub - allwaysoft_Spring-Data-JDBC_JdbcTemplate_Sorting_Paging_Search_Filter]: https://github.com/allwaysoft/Spring-Data-JDBC_JdbcTemplate_Sorting_Paging_Search_Filter