Spring Data JDBC结合MyBatis、MySQL 和 Thymeleaf 实现分页、排序和全字段LIKE过滤/搜索功能，MyBatis同时使用注解和XML

置顶 allway2

已于 2022-11-01 17:27:13 修改

阅读量459

点赞数 1

文章标签： spring java spring boot

于 2022-10-31 07:40:58 首次发布

本文链接：https://blog.csdn.net/allway2/article/details/127608291

版权

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

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.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>net.codejava</groupId>
    <artifactId>ProductManagerSpringDataJdbcMyBatis</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>ProductManagerSpringDataJdbcMyBatis</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>
            <!-- <scope>true</scope>  -->
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.4.1</version>
        </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

logging.level.net.codejava=DEBUG

ProductManagerApplication.java

package net.codejava;

import org.apache.ibatis.type.MappedTypes;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@MappedTypes(Product.class)
@MapperScan("net.codejava")
@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);
}

ProductMapper.java

package net.codejava;

import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectKey;
import org.apache.ibatis.annotations.Update;
import org.apache.ibatis.type.JdbcType;

public interface ProductMapper {

    @Select("select count(*) from product WHERE CONCAT(id, ' ', name, ' ' , brand, ' ' , madein, ' ' , price) LIKE CONCAT('%',#{keyword},'%')  ")
    int listAllSortPageContainingCount(@Param("keyword") String keyword);

    @Select("select * from product WHERE CONCAT(id, ' ', name, ' ' , brand, ' ' , madein, ' ' , price) LIKE CONCAT('%',#{keyword},'%')  order by ${sortField}  ${sortDir} LIMIT ${pageSize} OFFSET ${pageOffset}")
    List<Product> listAllSortPageContaining(@Param("keyword") String keyword, @Param("sortField") String sortField, @Param("sortDir") String sortDir, @Param("pageSize") Integer pageSize, @Param("pageOffset") Long pageOffset);

    /**
     * This method was generated by MyBatis Generator. This method corresponds
     * to the database table product
     *
     * @mbg.generated Mon Oct 31 06:50:01 CST 2022
     */
    @Delete({
        "delete from product",
        "where id = #{id,jdbcType=INTEGER}"
    })
    int deleteByPrimaryKey(Integer id);

    /**
     * This method was generated by MyBatis Generator. This method corresponds
     * to the database table product
     *
     * @mbg.generated Mon Oct 31 06:50:01 CST 2022
     */
    @Insert({
        "insert into product (name, brand, ",
        "madein, price)",
        "values (#{name,jdbcType=VARCHAR}, #{brand,jdbcType=VARCHAR}, ",
        "#{madein,jdbcType=VARCHAR}, #{price,jdbcType=REAL})"
    })
    @SelectKey(statement = "SELECT LAST_INSERT_ID()", keyProperty = "id", before = false, resultType = Integer.class)
    int insert(Product row);

    /**
     * This method was generated by MyBatis Generator. This method corresponds
     * to the database table product
     *
     * @mbg.generated Mon Oct 31 06:50:01 CST 2022
     */
    @Select({
        "select",
        "id, name, brand, madein, price",
        "from product",
        "where id = #{id,jdbcType=INTEGER}"
    })
    @Results({
        @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
        @Result(column = "name", property = "name", jdbcType = JdbcType.VARCHAR),
        @Result(column = "brand", property = "brand", jdbcType = JdbcType.VARCHAR),
        @Result(column = "madein", property = "madein", jdbcType = JdbcType.VARCHAR),
        @Result(column = "price", property = "price", jdbcType = JdbcType.REAL)
    })
    Product selectByPrimaryKey(Integer id);

    /**
     * This method was generated by MyBatis Generator. This method corresponds
     * to the database table product
     *
     * @mbg.generated Mon Oct 31 06:50:01 CST 2022
     */
    @Select({
        "select",
        "id, name, brand, madein, price",
        "from product"
    })
    @Results({
        @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER, id = true),
        @Result(column = "name", property = "name", jdbcType = JdbcType.VARCHAR),
        @Result(column = "brand", property = "brand", jdbcType = JdbcType.VARCHAR),
        @Result(column = "madein", property = "madein", jdbcType = JdbcType.VARCHAR),
        @Result(column = "price", property = "price", jdbcType = JdbcType.REAL)
    })
    List<Product> selectAll();

    /**
     * This method was generated by MyBatis Generator. This method corresponds
     * to the database table product
     *
     * @mbg.generated Mon Oct 31 06:50:01 CST 2022
     */
    @Update({
        "update product",
        "set name = #{name,jdbcType=VARCHAR},",
        "brand = #{brand,jdbcType=VARCHAR},",
        "madein = #{madein,jdbcType=VARCHAR},",
        "price = #{price,jdbcType=REAL}",
        "where id = #{id,jdbcType=INTEGER}"
    })
    int updateByPrimaryKey(Product row);
}

ProductRepositoryImpl.java

package net.codejava;

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;

public class ProductRepositoryImpl implements ProductRepositoryCustom {

    @Autowired
    private ProductMapper productMapper;

//    public ProductRepositoryImpl(ProductMapper productMapper) {
//        this.productMapper = productMapper;
//    }
    @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);
        int count = productMapper.listAllSortPageContainingCount(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
//        );
        List<Product> products = productMapper.listAllSortPageContaining(keyword, order.getProperty(), order.getDirection().name(), page.getPageSize(), page.getOffset());
        return new PageImpl<Product>(products, page, count);
    }

}

ProductService.java

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.findAllByContaining(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);
    }
}

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

<!-- 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>

            <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 />
                    &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=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>
                                &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>

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/ProductManagerSpringDataJdbcMyBatis

MyBatis同时使用注解和XML下载：GitHub - allwaysoft/ProductManagerSpringBootMyBatisAnnotationXml