Since 2.0 Spring Data JDBC supports PagingAndSortingRepository to provide additional methods to retrieve entities using the pagination and sorting abstraction. In this tutorial we will walk through an example with Spring Data JDBC to demonstrate how to implement and test basic Paging and Sorting operations.
Table of Content
[hide]
1. Dependency Configurations
1.1. Maven Dependencies
To start working with Spring Boot with Spring Data JDBC you need dependency 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. Entity to work on Paging and Sorting
2.1. This tutorial focuses only on paging and sorting operations using Spring Data 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. Sample Data used for Testing
INSERT INTO `USER` VALUES
(1,'PeterM','ABC123abc*','[email protected]','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17'),
(2,'Mike','password','[email protected]','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18'),
(3,'KingPeter','password','[email protected]','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18'),
(4,'PeterH','ABC123abc*','[email protected]','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17'),
(5,'Kelvin','password','[email protected]','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18'),
(6,'PeterLouise','password','[email protected]','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18'),
(7,'JustinB','ABC123abc*','[email protected]','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17'),
(8,'AshjaA','password','[email protected]','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18'),
(9,'JenniferH','password','[email protected]','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18'),
(10,'DonaldT','ABC123abc*','[email protected]','2020-03-17 07:13:30',NULL,'STUDENT','2020-03-17'),
(11,'HilloryK','password','[email protected]','2020-03-18 14:59:35',NULL,'EMPLOYEE','2020-03-18'),
(12,'MartinKing','password','[email protected]','2020-03-19 12:19:15',NULL,'EMPLOYEE','2020-03-18');
3. Implementing PagingAndSortingRepository
@Repository
public interface UserPaginationAnSortRepository extends PagingAndSortingRepository<User, Long>{
}
4. Testing
Implemented a API Controller and Test Cases to access User table data from DB for paging and sorting. First Let’s have look into unit tests.
5.1. Test for Paging and Sorting operations :
Get only By Sorting Order Results :
// By user name in descending order
Sort sort = Sort.by(Direction.fromString("DESC"), "userName");
// Sorted Users
List<User> sortedUsers = (List<User>) userRepository.findAll(sort);
Get only Paginated Results :
// 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);
Get both Paginated and Sorted Results :
Provide Pageable to get paged results. PageRequest is basic Java Bean implementation of Pageable.
PageRequest pageable = PageRequest.of(0, 5, Direction.fromString("DESC"), "userName");
Page<User> pagedUsers = userRepository.findAll(pageable);
5.2. API controller to provide pagination results
@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. Custom Query Results using Pageable and Sort
Since Spring Data JDBC 2.0 query methods also supports, you can implement pagination and sorting using query expressions like following.
@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);
}
Testing :
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. Conclusion
In this tutorial we walked through an example with Spring Data JDBC example to demonstrate how to implement and test basic Paging and Sorting operations.
Checkout source code at Git Hub.
Other Spring Data JDBC Examples :
- Spring Data JDBC – Embedded Entities
- Spring Data JDBC – One-to-Many
- Spring Data JDBC – Many-to-Many
- Spring Data JDBC – One-to-One
- Spring Data JDBC – Query Derivation
- Spring Boot – Loading Initial data
8. References
- Spring Java Document
- Spring Boot Data JDBC example
- Lombok Data
- JUnit 5 Test Order
- Spring Boot Junit 5 Test
