Spring JdbcTemplate – Pagination Examples

Spring JDBC don’t have any built-in operations to support Pagination and Sorting. This is a quick guide on how to implement Pagination and Sorting using JdbcTemplate with examples.

1. Dependencies

Add the spring-data-commons dependency in you pom.xml. spring-data-commons provides Paging and Sorting related classes.

		<dependency>
			<groupId>org.springframework.data</groupId>
			<artifactId>spring-data-commons</artifactId>
		</dependency>

2. JdbcTemplate Pagination and sorting examples

For sorting, we need to provide a column property and direction of the order to the query. For sorting, the query looks like :

SELECT * FROM USER ORDER BY USERNAME DESC;

1. Sort only Example

@Repository
public class UserJdbcTemplate {

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	// sort by single column name
	public List<User> findAll(Sort sort) {
		
		Order order = sort.toList().get(0);
		
		return jdbcTemplate.query("SELECT * FROM USER ORDER BY "+order.getProperty()+" "+order.getDirection().name(),
				 (rs, rowNum) -> mapUserResult(rs));
	}
	
	private User mapUserResult(final ResultSet rs) throws SQLException {
		User user = new User();
		user.setId(rs.getLong("ID"));
		user.setUserName(rs.getString("USER_NAME"));
		user.setPassword(rs.getString("PASSWORD"));
		user.setCreatedTime(rs.getDate("CREATED_TIME"));
		user.setUpdatedTime(rs.getDate("UPDATED_TIME"));
		user.setUserType(UserType.valueOf(rs.getString("USER_TYPE")));
		user.setDateofBirth(rs.getDate("DOB"));

		return user;
	}

}

Testing :

	

		// By user name in descending order
		Sort sort = Sort.by(Direction.fromString("DESC"), "USERNAME");

		// Sorted Users
		List<User> sortedUsers = userRepository.findAll(sort);

		List<User> userList = users.stream().sorted(Comparator.comparing(User::getUserName).reversed())
				.collect(Collectors.toList());

2. Paging and Sorting Example

To get pagination results the query may depends on Database. Pagination Query example for MySql:

SELECT * FROM USER ORDER BY USERNAME ASC LIMIT 10 OFFSET 0;

LIMIT indicates that the max number of results query returns. OFFSET – is divides all the results into subsets based on LIMIT. The maximum number of each set is equals to LIMIT. The OFFSET value starts from zero. For example if you have 105 records in database table and provided LIMIT as 10, the total results divided into 11 sets. To get 5th set of values have to provide 4 as OFFSET in query.

Example Oracle Query for Pagination (12C) :

SELECT * FROM USER ORDER BY USERNAME DESC OFFSET 1 ROWS FETCH NEXT 10 ROWS ONLY;

	public int count() {
		return jdbcTemplate.queryForObject("SELECT count(*) FROM USER", Integer.class);
	}

 	// defaults sorts by Id if order not provided
	public Page<User> findAll(Pageable page) {

		Order order = !page.getSort().isEmpty() ? page.getSort().toList().get(0) : Order.by("ID");

		List<User> users = jdbcTemplate.query("SELECT * FROM USER ORDER BY " + order.getProperty() + " "
				+ order.getDirection().name() + " LIMIT " + page.getPageSize() + " OFFSET " + page.getOffset(),
				(rs, rowNum) -> mapUserResult(rs));

		return new PageImpl<User>(users, page, count());
	}

Get results by Page and Size only :

	
		// 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 results by Paging and Sorting :

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

3. Conclusion

In this guide we have covered how to implement Pagination and sorting using Spring JdbcTemplate.

Checkout source code at GitHub.

Other Spring JDBC Examples :

1. Spring JdbcTemplate – Batch Update
2. Spring JdbcTemplate – Query
3. Spring JdbcTemplate – Stored Procedures
4. Spring JdbcTemplate – IN Clause
5. Spring JdbcTemplate – Auto-generated Keys
6. Spring Boot – Loading Initial Data

4. Reference

1. Spring Document
2. Spring JdbcTemplate query
3. Spring Boot Data Jdbc example
4. Spring Data Jdbc Pagination Example