Spring Data JDBC – 分页示例

从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 /> <!-- lookup parent from repository -->
  </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 中心查看源代码。

其他春季数据 JDBC 示例 ：

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

8. 参考资料

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