Spring Boot 使用 Spring Data JPA 进行分页和排序

谁践踏了优雅 2023-10-01 16:42 23阅读 0赞

本页将介绍使用 Spring Data JPA + MySQL 数据库进行 Spring Boot 分页和排序。当我们拥有大型数据集时，分页将提高应用程序的性能。它将数据集的较小块呈现给用户。

Spring Data JPA 提供了`PagingAndSortingRepository` 扩展接口[CrudRepository][]以在 Spring Boot 应用程序中实现分页和排序。接口中声明了两个方法`PagingAndSortingRepository`：

1.  `Iterable<T> findAll(Sort sort)` – 返回按给定选项排序的所有实体。
2.  `Page<T> findAll(Pageable pageable)`– 返回满足 Pageable 对象中提供的分页限制的实体页面[。][Link 1]

![1cbf3a4001dd12607594841a945ef72f.png][]

### 使用的技术 ###

查找此应用程序中使用的所有技术的列表。

1.  弹簧工具套件 4
2.  JDK 8
3.  Spring Boot 2.1.9.RELEASE
4.  Spring Data 2.1.9.RELEASE
5.  MySQL 数据库
6.  Maven 3

### 需要依赖 ###

要在应用程序中启用分页和排序，以下依赖项必须在构建路径中。将它们添加到*pom.xml*。

<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-web</artifactId>
    	</dependency>
    	<dependency>
    		<groupId>mysql</groupId>
    		<artifactId>mysql-connector-java</artifactId>
    		<scope>runtime</scope>
    	</dependency>
    </dependencies>

复制

### 项目结构 ###

我们在 STS 4 IDE 中的应用程序的最终项目结构将如下所示：

![a239e8e242b6858e468802bcd22a2a06.png][]

### 1. 初始设置 ###

没有数据集，我们什么都做不了。分页和排序将应用于数据，因此第一步是创建一些虚拟数据集。在这里，我们提供了 MySQL 数据库模式。

employee.sql

CREATE DATABASE /*!32312 IF NOT EXISTS*/`websparrow` /*!40100 DEFAULT CHARACTER SET latin1 */;
    
    /*Table structure for table `employee` */
    
    DROP TABLE IF EXISTS `employee`;
    
    CREATE TABLE `employee` (
      `id` bigint(20) NOT NULL AUTO_INCREMENT,
      `email` varchar(255) DEFAULT NULL,
      `first_name` varchar(255) DEFAULT NULL,
      `last_name` varchar(255) DEFAULT NULL,
      `city` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `UK_fopic1oh5oln2khj8eat6ino0` (`email`)
    ) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
    
    /*Data for the table `employee` */
    
    insert  into `employee`(`id`,`email`,`first_name`,`last_name`,`city`) values (1,'sandeep@example.com','Sandeep','Jaiswal','New Delhi'),(2,'prince@example.com','Prince','Kumar','Mumbai'),(3,'abhinav@example.com','Abhinav','Rathi','New Delhi'),(17,'gaurav@example.com','Gaurav','Aggrawal','Mumbai'),(18,'sandeep.sharma@example.com','Sandeep','Sharma','Pune'),(19,'sunny@example.com','Shani','Tripathi','Banglore'),(20,'ritesh@example.com','Ritesh','Singh','Hyderabad'),(21,'piyush@example.com','Piyush','Kumar','Banglore'),(22,'sunandana@example.com','Sunandana','Tiwari','Kolkata'),(23,'kumkum@example.com','Kumkum','Singh','New Delhi'),(24,'pooja@example.com','Pooja','Kumari','Pune'),(25,'naga@example.com','Naga','Reddy','Chennai'),(26,'jitendra@example.com','Jitendra','Upadhyay','Mumbai'),(27,'ashish@example.com','Ashish','Singh','Pune'),(28,'ashutosh@example.com','Ashutosh','Pandey','Mumbai'),
    (29,'vipin@example.com','Vipin','Kumar','Hyderabad');

复制

### 2. 实体 ###

假设我们有一个`Employee`实体，每个员工都由**id**唯一标识。

Employee.java

package org.websparrow.entity;
    
    @Entity
    @Table(name = "employee")
    public class Employee implements Serializable {
    
    	private static final long serialVersionUID = 5009021772979651472L;
    	@Id
    	private Integer id;
    	private String firstName;
    	private String lastName;
    	private String email;
    	private String city;
    
    	// constructor, getters, and setters
    }

复制

### 2. 创建存储库 ###

要访问`Employee`，我们需要`EmployeeRepository`扩展`PagingAndSortingRepository`Spring Data 提供的 ，并在应用程序中启用分页和排序功能。您可以添加您的[派生查询方法][Link 2]。

EmployeeRepository.java

package org.websparrow.repository;
    
    import org.springframework.data.repository.PagingAndSortingRepository;
    import org.springframework.stereotype.Repository;
    import org.websparrow.entity.Employee;
    
    @Repository
    public interface EmployeeRepository
    		extends PagingAndSortingRepository<Employee, Integer> {
    
    }

复制

### 3.控制器 ###

`EmployeeController`类[自动装配][Link 3]以`EmployeeRepository`从数据库中获取数据并公开 REST 端点。

#### 3.1 分页 ####

创建一个`PageRequest`对象，它是`Pageable`接口的实现，将`PageRequest`对象作为参数传递给存储库方法。

我们可以`PageRequest`通过传入请求的*页码*和*页面大小*来创建一个对象。

> **注意：**默认页数从零开始。

@GetMapping(value = "/employee/page/{pageNo}")
    public Page<Employee> getEmployeeWithPaination(@PathVariable int pageNo) {
    
    	/**
    	 * Return the Page object containing list of 4 employees of requested
    	 * page no.
    	 */
    	Pageable pageable = PageRequest.of(pageNo, PAGE_SIZE);
    	return employeeRepository.findAll(pageable);
    }

复制

#### 3.2 分页和排序 ####

同样，我们可以通过传递类的实例对分页结果进行排序`Sort`。

@GetMapping(value = "/employee/page/sort/{pageNo}")
    public Page<Employee> getEmployeePaginatedAndSorted(
    		@PathVariable int pageNo) {
    
    	/**
    	 * Return the Page object containing list of 3 employees of requested
    	 * page and sorted by the first name
    	 */
    	Pageable pageableSortedByFirstName = PageRequest.of(pageNo, PAGE_SIZE,
    			Sort.by("firstName"));
    
    	/**
    	 * Return the Page object containing list of 3 employees of requested
    	 * page and sorted by the city in descending order
    	 */
    	Pageable pageableSortedByCityDesc = PageRequest.of(pageNo, PAGE_SIZE,
    			Sort.by("city").descending());
    
    	/**
    	 * Return the Page object containing list of 3 employees of page 0 and
    	 * sorted by the city in descending order and first name in ascending
    	 * order
    	 */
    	Pageable pageableSortedByCityDescFirstNameAsc = PageRequest.of(pageNo,
    			PAGE_SIZE, Sort.by("city").descending().and(Sort.by("firstName")));
    
    	// return employeeRepository.findAll(pageableSortedByFirstName);
    	
    	// return employeeRepository.findAll(pageableSortedByCityDesc);
    		
    	return employeeRepository.findAll(pageableSortedByCityDescFirstNameAsc);
    }

复制

#### 3.3 排序 ####

但是，如果我们想要排序形式的所有记录，只需传递`Sort`to的实例。`Iterable<T> findAll(Sort sort)`

@GetMapping(value = "/employee/sort")
    public Iterable<Employee> getEmployeeSored() {
    
    	/**
    	 * Returns all entities sorted by the given options.
    	 */
    	return employeeRepository.findAll(Sort.by("lastName"));
    }

复制

最后，完整的`EmployeeController`类如下所示：

EmployeeController.java

package org.websparrow.controller;
    
    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.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.PathVariable;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    import org.websparrow.entity.Employee;
    import org.websparrow.repository.EmployeeRepository;
    
    @RestController
    @RequestMapping("/api")
    public class EmployeeController {
    
    	private static final int PAGE_SIZE = 3;
    
    	@Autowired
    	private EmployeeRepository employeeRepository;
    
    	@GetMapping(value = "/employee/page/{pageNo}")
    	public Page<Employee> getEmployeePaginated(@PathVariable int pageNo) {
    
    		/**
    		 * Return the Page object containing list of 3 employees of requested
    		 * page no.
    		 */
    		Pageable pageable = PageRequest.of(pageNo, PAGE_SIZE);
    		return employeeRepository.findAll(pageable);
    	}
    
    	@GetMapping(value = "/employee/page/sort/{pageNo}")
    	public Page<Employee> getEmployeePaginatedAndSorted(
    			@PathVariable int pageNo) {
    
    		/**
    		 * Return the Page object containing list of 3 employees of requested
    		 * page and sorted by the first name
    		 */
    		Pageable pageableSortedByFirstName = PageRequest.of(pageNo, PAGE_SIZE,
    				Sort.by("firstName"));
    
    		/**
    		 * Return the Page object containing list of 3 employees of requested
    		 * page and sorted by the city in descending order
    		 */
    		Pageable pageableSortedByCityDesc = PageRequest.of(pageNo, PAGE_SIZE,
    				Sort.by("city").descending());
    
    		/**
    		 * Return the Page object containing list of 3 employees of page 0 and
    		 * sorted by the city in descending order and first name in ascending
    		 * order
    		 */
    		Pageable pageableSortedByCityDescFirstNameAsc = PageRequest.of(pageNo,
    				PAGE_SIZE,
    				Sort.by("city").descending().and(Sort.by("firstName")));
    
    		// return employeeRepository.findAll(pageableSortedByFirstName);
    
    		// return employeeRepository.findAll(pageableSortedByCityDesc);
    
    		return employeeRepository.findAll(pageableSortedByCityDescFirstNameAsc);
    	}
    
    	@GetMapping(value = "/employee/sort")
    	public Iterable<Employee> getEmployeeSored() {
    
    		/**
    		 * Returns all entities sorted by the given options.
    		 */
    		return employeeRepository.findAll(Sort.by("lastName"));
    	}
    }

复制

### 4. application.properties ###

*在application.properties*文件中配置数据库连接字符串以建立应用程序和数据库之间的连接。

application.properties

# MySQL database connection strings
    spring.datasource.username=root
    spring.datasource.password=root
    spring.datasource.url=jdbc:mysql://localhost:3306/websparrow
    
    # JPA property settings
    spring.jpa.hibernate.ddl-auto=update
    spring.jpa.properties.hibernate.show_sql=true

复制

### 运行应用程序 ###

该类 `SpringBootPagintionApp` 包含主要方法并负责启动应用程序。

SpringBootPaginationApp.java

package org.websparrow;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    @SpringBootApplication
    public class SpringBootPaginationApp {
    
    	public static void main(String[] args) {
    		SpringApplication.run(SpringBootPaginationApp.class, args);
    	}
    }

复制

### 测试应用程序 ###

要测试应用程序，请通过执行上述类启动 Spring Boot 应用程序，并在您喜欢的 Web 浏览器中点击以下 URL：

> **注意：**默认页数从零开始。

1.  要获取分页记录：http://localhost:8080/api/employee/page/0 其中零 (0) 是请求的页码。
2.  分页和排序记录：http://localhost:8080/api/employee/page/sort/3 其中 3 是请求的页码。
3.  排序记录：http://localhost:8080/api/employee/sort

下载源代码：[spring-boot-pagination-and-sorting-using-spring-data-jpa.zip][]

#### 参考 ####

1.  [Spring Data 派生的 findBy 查询方法示例][Link 2]
2.  [Spring Data CrudRepository 接口示例][CrudRepository]
3.  [Spring Boot RESTful Web 服务示例][Spring Boot RESTful Web]
4.  [接口 PagingAndSortingRepository][PagingAndSortingRepository]

[CrudRepository]: https://www.websparrow.org/spring/spring-data-crudrepository-interface-example
[Link 1]: https://www.websparrow.org/spring/spring-boot-pagination-and-sorting-using-spring-data-jpa#
[1cbf3a4001dd12607594841a945ef72f.png]: https://img-blog.csdnimg.cn/img_convert/1cbf3a4001dd12607594841a945ef72f.png
[a239e8e242b6858e468802bcd22a2a06.png]: https://img-blog.csdnimg.cn/img_convert/a239e8e242b6858e468802bcd22a2a06.png
[Link 2]: https://www.websparrow.org/spring/spring-data-derived-findby-query-methods-example
[Link 3]: https://www.websparrow.org/spring/spring-autowired-annotation-example
[spring-boot-pagination-and-sorting-using-spring-data-jpa.zip]: https://www.websparrow.org/wp-content/uploads/2019/11/spring-boot-pagination-and-sorting-using-spring-data-jpa.zip
[Spring Boot RESTful Web]: https://www.websparrow.org/spring/spring-boot-restful-web-service-example
[PagingAndSortingRepository]: https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/repository/PagingAndSortingRepository.html