吴裕雄天生自然Spring Boot排序与分页查询

    在实际应用开发中，排序与分页查询是必需的。幸运的是Spring Data JPA充分考虑了排序与分页查询的场景，为我们提供了Sort类、Page接口以及Pageable接口。

例如，如下数据访问接口：

public interface AuthorRepository extends JpaRepository<Author, Integer>{
    List<Author> findByAnameContaining(String aname, Sort sort);
}

那么，我们在Service层可以这样使用排序：

public List<Author> findByAnameContaining(String aname, String sortColum) {
    //按sortColum降序排序
    return authorRepository.findByAnameContaining(aname, new Sort(Direction.DESC, sortColum));
}

    可以使用Pageable接口的实现类PageRequest的of方法构造分页查询对象，示例代码如下：
    Page<Author> pageData = authorRepository.findAll(PageRequest.of(page-1, size, new Sort(Direction.DESC, "id")));
    其中Page接口可以获得当前页面的记录、总页数、总记录数等信息，示例代码如下：
//获得当前页面的记录
List<Author> allAuthor = pageData.getContent();
model.addAttribute("allAuthor",allAuthor);
//获得总记录数
model.addAttribute("totalCount", pageData.getTotalElements());
//获得总页数
model.addAttribute("totalPage", pageData.getTotalPages());

1．创建持久化实体类
2．创建数据访问层
3．创建业务层
4．创建控制器类
5．创建View视图页面
6．运行

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.sourcepage</groupId>
    <artifactId>SpringBootSourcePage</artifactId>
    <version>0.0.1-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.0.0.RELEASE</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>

    <properties>
        <!-- 声明项目配置依赖编码格式为 utf-8 -->
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <fastjson.version>1.2.24</fastjson.version>
    </properties>

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</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>

        <!-- 添加MySQL依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
            <version>8.0.13</version><!--$NO-MVN-MAN-VER$ -->
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

server.port=8089

server.servlet.context-path=/ch6_4
spring.datasource.url=jdbc:mysql://localhost:3306/springbootjpa?serverTimezone=UTC&autoReconnect=true
spring.datasource.username=root
spring.datasource.password=admin
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.database=MYSQL
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=update
spring.jackson.serialization.indent-output=true 

package com.ch.ch6_4.entity;

import java.io.Serializable;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
@Table(name = "author_table")
@JsonIgnoreProperties(value = { "hibernateLazyInitializer" })
public class Author implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    // 作者名
    private String aname;

    // 文章列表，作者与文章是一对多的关系
    @OneToMany(mappedBy = "author", cascade = CascadeType.ALL, targetEntity = Article.class, fetch = FetchType.LAZY)
    private List<Article> articleList;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getAname() {
        return aname;
    }

    public void setAname(String aname) {
        this.aname = aname;
    }

    public List<Article> getArticleList() {
        return articleList;
    }

    public void setArticleList(List<Article> articleList) {
        this.articleList = articleList;
    }
}

package com.ch.ch6_4.entity;

import java.io.Serializable;

import javax.persistence.Basic;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.Lob;
import javax.persistence.ManyToOne;
import javax.persistence.Table;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.Size;

import com.fasterxml.jackson.annotation.JsonIgnore;
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

@Entity
@Table(name = "article_table")
@JsonIgnoreProperties(value = { "hibernateLazyInitializer" })
public class Article implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int id;
    // 标题
    @NotEmpty(message = "标题不能为空")
    @Size(min = 2, max = 50)
    @Column(nullable = false, length = 50)
    private String title;
    // 文章内容
    @Lob // 大对象，映射 为MySQL的Long文本类型
    @Basic(fetch = FetchType.LAZY)
    @NotEmpty(message = "内容不能为空")
    @Size(min = 2)
    @Column(nullable = false)
    private String content;
    // 所属作者，文章与作者是多对一的关系
    @ManyToOne(cascade = { CascadeType.MERGE, CascadeType.REFRESH }, optional = false)
    // 可选属性optional=false,表示author不能为空。删除文章，不影响用户
    @JoinColumn(name = "id_author_id") // 设置在article表中的关联字段(外键)
    @JsonIgnore
    private Author author;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public Author getAuthor() {
        return author;
    }

    public void setAuthor(Author author) {
        this.author = author;
    }
}

2．创建数据访问层

public interface AuthorRepository extends JpaRepository<Author, Integer>{
    /**
     * 查询作者名含有name的作者列表，并排序
     */
    List<Author> findByAnameContaining(String aname, Sort sort);
}

package com.ch.ch6_4.repository;

import java.util.List;

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;

import com.ch.ch6_4.entity.Author;

public interface AuthorRepository extends JpaRepository<Author, Integer> {
    /**
     * 查询作者名含有name的作者列表，并排序
     */
    List<Author> findByAnameContaining(String aname, Sort sort);
}

3．创建业务层

创建名为com.ch.ch6_4.service的包，并在该包中创建名为ArticleAndAuthorService的接口和接口实现类ArticleAndAuthorServiceImpl。

    @Override
    public List<Author> findByAnameContaining(String aname, String sortColum) {
        //按sortColum降序排序
    return authorRepository.findByAnameContaining(aname, new Sort(Direction.DESC, sortColum));
    }
    @Override
    public String findAllAuthorByPage(Integer page, Model model) {
        if(page == null) {//第一次访问findAllAuthorByPage方法时
            page = 1;
        }
        int size = 2;//每页显示2条
        //分页查询，of方法的第一个参数代表第几页（比实际小1），
//第二个参数代表页面大小，第三个参数代表排序规则
        Page<Author> pageData =
 authorRepository.findAll(PageRequest.of(page-1, size, new Sort(Direction.DESC, "id")));
        //获得当前页面数据并转换成List<Author>，转发到视图页面显示
        List<Author> allAuthor = pageData.getContent();
        model.addAttribute("allAuthor",allAuthor);
        //共多少条记录
        model.addAttribute("totalCount", pageData.getTotalElements());
        //共多少页
        model.addAttribute("totalPage", pageData.getTotalPages());
        //当前页
        model.addAttribute("page", page);
        return "index";
    }

package com.ch.ch6_4.service;

import java.util.List;

import org.springframework.ui.Model;

import com.ch.ch6_4.entity.Author;

public interface ArticleAndAuthorService {
    /**
     * name代表作者名的一部分（模糊查询），sortColum代表排序列
     */
    List<Author> findByAnameContaining(String aname, String sortColum);

    /**
     * 分页查询作者，page代表第几页
     */
    public String findAllAuthorByPage(Integer page, Model model);
}

package com.ch.ch6_4.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.stereotype.Service;
import org.springframework.ui.Model;

import com.ch.ch6_4.entity.Author;
import com.ch.ch6_4.repository.AuthorRepository;

@Service
public class ArticleAndAuthorServiceImpl implements ArticleAndAuthorService {
    @Autowired
    private AuthorRepository authorRepository;

    @Override
    public List<Author> findByAnameContaining(String aname, String sortColum) {
        // 按sortColum降序排序
        return authorRepository.findByAnameContaining(aname, new Sort(Direction.DESC, sortColum));
    }

    @Override
    public String findAllAuthorByPage(Integer page, Model model) {
        if (page == null) {// 第一次访问findAllAuthorByPage方法时
            page = 1;
        }
        int size = 2;// 每页显示2条
        // 分页查询，of方法的第一个参数代表第几页（比实际小1），第二个参数代表页面大小，第三个参数代表排序规则
        Page<Author> pageData = authorRepository
                .findAll(PageRequest.of(page - 1, size, new Sort(Direction.DESC, "id")));
        // 获得当前页面数据并转换成List<Author>，转发到视图页面显示
        List<Author> allAuthor = pageData.getContent();
        model.addAttribute("allAuthor", allAuthor);
        // 共多少条记录
        model.addAttribute("totalCount", pageData.getTotalElements());
        // 共多少页
        model.addAttribute("totalPage", pageData.getTotalPages());
        // 当前页
        model.addAttribute("page", page);
        return "index";
    }
}

4．创建控制器类

创建com.ch.ch6_4.controller的包，并在该包中创建名为TestSortAndPage的控制器类。

package com.ch.ch6_4.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.ch.ch6_4.entity.Author;
import com.ch.ch6_4.service.ArticleAndAuthorService;

@Controller
public class TestSortAndPage {
    @Autowired
    private ArticleAndAuthorService articleAndAuthorService;

    @RequestMapping("/findByAnameContaining")
    @ResponseBody
    public List<Author> findByAnameContaining(String aname, String sortColum) {
        return articleAndAuthorService.findByAnameContaining(aname, sortColum);
    }

    @RequestMapping("/findAllAuthorByPage")
    /**
     * @param page第几页
     */
    public String findAllAuthorByPage(Integer page, Model model) {
        return articleAndAuthorService.findAllAuthorByPage(page, model);
    }
}

5．创建View视图页面

在src/main/resources/templates目录下，创建视图页面index.html。

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>显示分页查询结果</title>
<link rel="stylesheet" th:href="@{css/bootstrap.min.css}" />
<link rel="stylesheet" th:href="@{css/bootstrap-theme.min.css}" />
</head>
<body>
    <div class="panel panel-primary">
        <div class="panel-heading">
            <h3 class="panel-title">Spring Data JPA分页查询</h3>
        </div>
    </div>
    <div class="container">
        <div class="panel panel-primary">
            <div class="panel-body">
                <div class="table table-responsive">
                    <table class="table table-bordered table-hover">
                        <tbody class="text-center">
                            <tr th:each="author:${allAuthor}">
                                <td>
                                    <span th:text="${author.id}"></span>
                                </td>
                                <td>
                                    <span th:text="${author.aname}"></span>
                                </td>
                            </tr>
                            <tr>
                            <td colspan="2" align="right">
                                <ul class="pagination">
                                    <li><a>第<span th:text="${page}"></span>页</a></li>
                                    <li><a>共<span th:text="${totalPage}"></span>页</a></li>
                                    <li><a>共<span th:text="${totalCount}"></span>条</a></li>
                                    <li>
                                    <a th:href="@{findAllAuthorByPage(page=${page-1})}" th:if="${page != 1}">上一页</a>
                                    </li>
                                    <li><a th:href="@{findAllAuthorByPage(page=${page+1})}" th:if="${page != totalPage}">下一页</a>
                                    </li>
                                </ul>
                            </td>
                        </tr>
                        </tbody>
                    </table>
                </div>
            </div>
        </div>
    </div>
</body>
</html>

首先，运行Ch64Application主类。然后，通过“http://localhost:8089/ch6_4/findByAnameContaining?aname=陈&sortColum=id”查询作者名含有“陈”的作者列表，并按照id降序。

package com.ch.ch6_4;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Ch64Application {
    public static void main(String[] args) {
        SpringApplication.run(Ch64Application.class, args);
    }
}