Spring Data JDBC – 分页示例

柔情只为你懂 2023-09-26 10:16 76阅读 0赞

从**Spring Data JDBC 2.0 起**支持***PagingAndSortingRepository*** ，以提供使用**分页**和**排序**抽象检索实体的其他方法。在本教程中，我们将演示一个**使用 Spring 数据 JDBC** 的示例，以演示如何实现和测试基本的**分页**和**排序**操作。

### 1. 依赖关系配置 ###

**1.1. Maven 依赖关系**

要开始使用弹簧启动与弹簧数据JDBC，您需要依赖项`spring-boot-starter-data-jdbc`。

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.0.RELEASE</version>
        <relativePath /> 
      </parent>
    
            <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-web</artifactId>
        </dependency>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>
        <dependency>
          <groupId>com.h2database</groupId>
          <artifactId>h2</artifactId>
        </dependency>
        <dependency>
          <groupId>org.projectlombok</groupId>
          <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <scope>runtime</scope>
        </dependency>
        <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-test</artifactId>
          <scope>test</scope>
        </dependency>
      </dependencies>

### 2. 用于分页和排序的实体 ###

2.1. 本教程仅重点介绍使用 **Spring 数据 JDBC*****PagingAndSortingRepository******的分页和排序***操作。

@Data // lomok
    public class User {
    
      @Id
      private Long id;
      private String userName;
      private String password;
      private Date createdTime;
      private Date updatedTime;
      @Column("DOB") // to map db column if property not same as column name
      private Date dateofBirth;
      private UserType userType; // Enum Type
    
      @org.springframework.data.annotation.Transient // to not persist into DB (just to expose to view/client)
      private String dateOfBirthString;
      
      // to display on view
      public String getDateOfBirthString() {
        return this.dateofBirth.toString();
      }
    }

public enum UserType {
      
      EMPLOYEE, STUDENT;
    }

2.2. 用于测试的示例数据

INSERT INTO `USER` VALUES 
         (1,'PeterM','ABC123abc*','peter@email.com','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17'),
         (2,'Mike','password','mike@email.com','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18'),
         (3,'KingPeter','password','kingpeter@email.com','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18'),
         (4,'PeterH','ABC123abc*','peterh@email.com','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17'),
         (5,'Kelvin','password','kelvin@email.com','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18'),
         (6,'PeterLouise','password','peterl@email.com','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18'),
         (7,'JustinB','ABC123abc*','justin@email.com','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17'),
         (8,'AshjaA','password','ashja@email.com','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18'),
         (9,'JenniferH','password','jennifer@email.com','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18'),
         (10,'DonaldT','ABC123abc*','donald@email.com','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17'),
         (11,'HilloryK','password','hillory@email.com','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18'),
         (12,'MartinKing','password','martin@email.com','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18');

### 3. 实现***PagingAndSortingRepository*** ###

@Repository
    public interface UserPaginationAnSortRepository extends PagingAndSortingRepository<User, Long>{
      
    }

### 4. 测试 ###

实现了 **API 控制器**和**测试用例，**以访问数据库中**的用户**表数据以进行分页和排序。首先，让我们看一下单元测试。

5.1. **分页和排序操作的测试**：

**仅通过排序顺序结果获取**：

// By user name in descending order
        Sort sort = Sort.by(Direction.fromString("DESC"), "userName");
    
        // Sorted Users
        List<User> sortedUsers = (List<User>) userRepository.findAll(sort);

**仅获取分页结果** ：

// total 12 users in sample data, set 5 users per page - total 3 pages
        PageRequest pageable = PageRequest.of(0, 5);
    
        // paged users - each page should have 5 users
        Page<User> pagedUsers = userRepository.findAll(pageable);

**获取分页和排序结果**：

提供***可分页***以获取分页结果。页面请求是**可分页**的基本 Java Bean 实现。

PageRequest pageable = PageRequest.of(0, 5, Direction.fromString("DESC"), "userName");
    
        Page<User> pagedUsers = userRepository.findAll(pageable);

**5.2. 提供分页结果的 API 控制器**

@RestController
    @RequestMapping("/api/users")
    public class UserController {
    
      @Autowired
      private UserPaginationAnSortRepository userRepository;
    
      public UserController() {
        super();
      }
    
      /*
             *  Other API operations
             */
    
      // Read - by sorted and paginated
      
      @GetMapping(params = { "page", "size", "sortBy" })
      public List<User> findAllBySortAndPage(@RequestParam("page") final int page, @RequestParam("size") final int size,
          @RequestParam("sortBy") final String sortBy, @RequestParam("sortOrder") final String sortOrder) {
    
        PageRequest pageable = PageRequest.of(page, size, Direction.fromString(sortOrder), sortBy);
    
        Page<User> result = userRepository.findAll(pageable);
    
        if (!result.isEmpty())
          return result.getContent();
        else
          return new ArrayList<User>();
      }
    
      // Read - by only paginated
      
      @GetMapping(params = { "page", "size" })
      public List<User> findAllByPage(@RequestParam("page") final int page, @RequestParam("size") final int size) {
    
        PageRequest pageable = PageRequest.of(page, size);
    
        Page<User> result = userRepository.findAll(pageable);
    
        if (!result.isEmpty())
          return result.getContent();
        else
          return new ArrayList<User>();
      }
    
      // Read - by only sorted
      
      @GetMapping(params = { "sortBy" })
      public List<User> findAllBySort(@RequestParam("sortBy") final String sortBy,
          @RequestParam("sortOrder") final String sortOrder) {
    
        Sort sort = Sort.by(Direction.fromString(sortOrder), sortBy);
    
        return (List<User>) userRepository.findAll(sort);
    
      }
    
    }

### 6. 使用可分页和排序自定义查询结果 ###

由于Spring数据JDBC 2.0查询方法也支持，因此您可以使用如下查询表达式实现分页和排序。

@Repository
    public interface UserPaginationAndSortRepository extends PagingAndSortingRepository<User, Long>{
      
      /* query expression using pageable */
      List<User> findByUserType(String userType, Pageable pageable);
      
      /* query expression using Sort */
      List<User> findByUserType(String userType, Sort sort);
      
    }

**测试：**

PageRequest pageable = PageRequest.of(0, 5, Direction.fromString("DESC"), "userName");
        List<User> pagedAndSortedUsers = userRepository.findByUserType(UserType.EMPLOYEE.toString(), pageable);
        
        // Defining sort
        //Sort sort = Sort.by("userName").ascending();
        
        // defining Type-safe Sort
        TypedSort<User> tSort = Sort.sort(User.class);
        List<User> sortedUsers = userRepository.findByUserType(UserType.EMPLOYEE.toString(), tSort.by("userName").ascending());

### 7. 结论 ###

在本教程中，我们演练了一个带有**Spring数据JDBC**示例的示例，以演示如何实现和测试基本的**分页**和**排序**操作。

在 [Git 中心][Git]查看源代码。

**其他春季数据 JDBC 示例** ：

1.  [春季数据 JDBC – 嵌入式实体][JDBC _]
2.  [春季数据JDBC – 一对多][JDBC _ 1]
3.  [春季数据JDBC - 多对多][JDBC -]
4.  [春季数据JDBC – 一对一][JDBC _ 2]
5.  [春季数据 JDBC – 查询派生][JDBC _ 3]
6.  [弹簧启动 – 加载初始数据][Link 1]

### 8. 参考资料 ###

1.  [春季爪哇文档][Link 2]
2.  [弹簧启动数据 JDBC 示例][JDBC]
3.  [龙目岛数据][Link 3]
4.  [J单元 5 测试订单][J_ 5]
5.  [弹簧靴 Junit 5 测试][Junit 5]

[Git]: https://github.com/javabydeveloper/spring-boot/tree/master/spring-boot-data-jdbc-example/src/test/java/com/javabydeveloper/demo
[JDBC _]: https://javabydeveloper.com/spring-data-jdbc-embedded-entities-examples/
[JDBC _ 1]: https://javabydeveloper.com/spring-data-jdbc-one-to-many-example/
[JDBC -]: https://javabydeveloper.com/spring-data-jdbc-many-to-many-example/
[JDBC _ 2]: https://javabydeveloper.com/spring-data-jdbc-one-to-one-example/
[JDBC _ 3]: https://javabydeveloper.com/spring-data-jdbc-query-derivation-examples/
[Link 1]: https://javabydeveloper.com/spring-boot-loading-initial-data/
[Link 2]: https://docs.spring.io/spring-data/commons/docs/current/api/org/springframework/data/repository/PagingAndSortingRepository.html
[JDBC]: https://javabydeveloper.com/spring-data-jdbc-2-0-with-spring-boot-example/
[Link 3]: https://javabydeveloper.com/lombok-data-annotation/
[J_ 5]: https://javabydeveloper.com/junit-5-test-execution-order-with-examples/
[Junit 5]: https://javabydeveloper.com/spring-boot-junit-5-test-example/