Spring NamedParameterJdbcTemplate 、MySQL 和 Thymeleaf 实现分页、排序和过滤/搜索功能

港控/mmm° 2023-09-26 10:37 58阅读 0赞

在本教程中，您将学习使用 Spring NamedParameterJdbcTemplate 、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>SortingPagingSearchFilterJdbcTemplate</artifactId>
        <version>0.0.1-SNAPSHOT</version>
        <name>SortingPagingSearchFilterJdbcTemplate</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-devtools</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;
    
    public class Product {
    
        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.PageImpl;
    import org.springframework.data.domain.Pageable;
    import org.springframework.data.domain.Sort;
    import org.springframework.data.domain.Sort.Order;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.jdbc.core.BeanPropertyRowMapper;
    import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
    import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
    import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
    import org.springframework.jdbc.core.namedparam.SqlParameterSource;
    import org.springframework.jdbc.support.GeneratedKeyHolder;
    import org.springframework.stereotype.Repository;
    
    @Repository
    
    public class ProductRepository {
    
        @Autowired
        private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    
        // sort by single column name
        public List<Product> findAll(Sort sort) {
    
            Order order = sort.toList().get(0);
    
            return namedParameterJdbcTemplate.query("SELECT * FROM product ORDER BY " + order.getProperty() + " " + order.getDirection().name(),
                    BeanPropertyRowMapper.newInstance(Product.class));
        }
    
        public int count() {
    
            return namedParameterJdbcTemplate.queryForObject("SELECT count(*) FROM product", new HashMap(), Integer.class);
        }
    
        // defaults sorts by Id if order not provided
        public Page<Product> findAll(Pageable page) {
    
            Order order = !page.getSort().isEmpty() ? page.getSort().toList().get(0) : Order.by("ID");
    
            List<Product> products = namedParameterJdbcTemplate.query("SELECT * FROM product ORDER BY " + order.getProperty() + " "
                    + order.getDirection().name() + " LIMIT " + page.getPageSize() + " OFFSET " + page.getOffset(),
                    BeanPropertyRowMapper.newInstance(Product.class));
    
            return new PageImpl<Product>(products, page, count());
        }
    
        public Page<Product> findAllByContaining(String keyword, Pageable page) {
    
            Order order = !page.getSort().isEmpty() ? page.getSort().toList().get(0) : 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());
        }
    
        public void save(Product product) {
    
            GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
            String sql = "insert into product (name, brand,madein,price) values (:name, :brand,:madein,:price)";
            SqlParameterSource paramSource = new MapSqlParameterSource().addValue("name", product.getName()).addValue("brand", product.getBrand()
            ).addValue("madein", product.getMadein()).addValue("price", product.getPrice());
            namedParameterJdbcTemplate.update(sql, paramSource, generatedKeyHolder);
            long id = generatedKeyHolder.getKey().longValue();
            System.out.println(id);
            product.setId(id);
        }
    
        public Product findById(long id) {
    
            String sql = "SELECT * FROM product WHERE id = :id";
            Map parameters = new HashMap();
            parameters.put("id", id);
            return namedParameterJdbcTemplate.queryForObject(sql, parameters, BeanPropertyRowMapper.newInstance(Product.class));
        }
    
        public void update(Product product) {
    
            String sql = "UPDATE product SET name=:name, brand=:brand, madein=:madein, price=:price WHERE id=:id";
            BeanPropertySqlParameterSource param = new BeanPropertySqlParameterSource(product);
            int i = namedParameterJdbcTemplate.update(sql, param);
            System.out.println(i);
        }
    
        public void deleteById(long id) {
    
            String sql = "DELETE FROM product WHERE id = :id";
    
            SqlParameterSource paramSource = new MapSqlParameterSource("id", id);
            int i = namedParameterJdbcTemplate.update(sql, paramSource);
            System.out.println(i);
        }
    }

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(value = "/update", method = RequestMethod.POST)
        public String updateProduct(@ModelAttribute("product") Product product) {
            service.update(product);
    
            return "redirect:/";
        }
    
        @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}" /> 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=price&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="@{/update}" 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/SortingPagingSearchFilterJdbcTemplate][GitHub - allwaysoft_SortingPagingSearchFilterJdbcTemplate]

[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_SortingPagingSearchFilterJdbcTemplate]: https://github.com/allwaysoft/SortingPagingSearchFilterJdbcTemplate