从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 示例 :