what is the best way to implement pagination using spring MVC with mysql

Question

In my database I am having more than 100,000 records and it could be more in future. So I want to implement pagination for this.Its like posts and once in a while user can go to the oldest or too old posts. I am using spring MVC and database is mysql.

Also I am using PagedListHolder to implement pagination. I want to display 25 posts per page but if I fetch all the 100,000 posts initially then it takes some time to load all the records. So is this better if I first load 100 records and then subsequently merge the list with another 100 records and so on. And if the user want to see the oldest post then fetch the last 100 posts and display last 25 posts on last page.

So just wanted to know is this solution feasible will this work if someone can help me to give the right direction.

Answer 1

If you are using ORM , Spring Data JPA, can easily provide out-of-the box pagination feature.

DAO interface for entity that needs pagination will extend PagingAndSortingRepository interface like:-

public interface BookRepository extends PagingAndSortingRepository<Book,Long>{
      // methods other than plain CRUD can be declared here
}

The key interface for pagination operation is Pageable. Its ready to use implementation PageRequest encapsulates pagination inputs like max result size, page no, size, sort by, sort direction, offset.

PageRequest can be fetched from request attribute in a handler method and passed as an argument to the repository method like:-

Page<Book> result = bookRepository.findAll(pageRequest);

The return type Page , can be easily iterated in view layer to render details like result size, total no of results, sort order, sort direction.

I found this tutorial helpful.