Home Spring Boot Spring JdbcTemplate - Pagination Examples

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-boot-starter-jdbc, spring-data-commons dependencies in you pom.xml. spring-data-commons provides Paging and Sorting related classes.

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

2. JdbcTemplate Pagination and sorting examples

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 :

	// Gets all of user data - Test on small sample data
	@Test
	@DisplayName("Sorting-By-UserName-Test")
	void sortByUserName() {

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

		// All Users
		List<User> users = (List<User>) userRepository.findAll();

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

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

		Ordering<User> expectedOrder = Ordering.explicit(userList);

		assertTrue(expectedOrder.isOrdered(sortedUsers));

	}

2. Paging and Sorting Example

	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 :

	@Test
	@DisplayName("Results-By-Page-And-Size-Test")
	void getByPageAndSize() {

		// total 12 users in sample data, set 5 users per page - total 3 pages
		PageRequest pageable = PageRequest.of(0, 5);

		// All users
		List<User> users = (List<User>) userRepository.findAll();

		// paged users - each page should have 5 users
		Page<User> pagedUsers = userRepository.findAll(pageable);

		assertTrue(pagedUsers.getTotalPages() == 3);
		assertTrue(pagedUsers.getContent().equals(users.subList(0, 5)));
	}

Get results by Paging and Sorting :

	@Test
	@DisplayName("Results-By-Page-Size-And-SortBy-Test")
	void getByPageAndSizeSortByUserName() {

		PageRequest pageable = PageRequest.of(0, 5, Direction.fromString("DESC"), "USERNAME");
		System.err.println(pageable.getSort().toList().get(0));
		// All Users
		List<User> users = (List<User>) userRepository.findAll();

		Page<User> pagedUsers = userRepository.findAll(pageable);

		List<User> usersList = users.stream().sorted(Comparator.comparing(User::getUserName).reversed())
				.collect(Collectors.toList()).subList(0, 5);

		Ordering<User> expectedOrder = Ordering.explicit(usersList);

		assertTrue(expectedOrder.isOrdered(pagedUsers.getContent()));

		assertTrue(pagedUsers.getTotalPages() == 3);
		assertTrue(pagedUsers.getContent().equals(usersList));
	}

3. Conclusion

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

4. Reference

  1. Spring Document
  2. Spring JdbcTemplate query
  3. Spring Boot Data Jdbc example
  4. Spring Data Jdbc Pagination Example
Satish Varma
Satish Varmahttps://javabydeveloper.com
Satish is post graduated in master of computer applications and experienced software engineer with focus on Spring, JPA, REST, TDD and web development. Follow him on LinkedIn or Twitter or Facebook

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay in Touch

Categories