HomeSpring BootSpring Data JDBC - Pagination Example

Spring Data JDBC – Pagination Example

September 24, 2022

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 :

8. References

Spring Boot