How to do Pagination with mybatis?

mysql hibernate pagination mybatis
Shailesh Pratapwar picture Shailesh Pratapwar · Jul 7, 2013 · Viewed 34.7k times · Source

I am currently working on a ecommerce application where I have to show a list of available products using search functionality.

As with every search, I have to implement Pagination here.

I am using mybatis as my ORM tool and mysql as an underlying database.

Googling around I found following ways to accomplish this task :

  1. Client Side paging : Here I will have to fetch all the results from the database matching the search criteria in one stroke and handle the pagination at my code level (Possibly frond end code ).

  2. Server Side Paging : With mysql I can use the Limit and the offset of the resultset to construct a query like : SELECT * FROM sampletable WHERE condition1>1 AND condition2>2 LIMIT 0,20

Here, I have to pass the offset and limit count everytime the user selects a new page while navigating in search results.

Can anyone tell,

  1. which will be better way to implement paging ?
  2. Do mybatis supports a better way to implement paging than just relying on above SQL queries ( like the hibernate criteria APIs).

Any inputs is highly appreaciated. Thanks .

Answer

Volodymyr Levytskyi picture Volodymyr Levytskyi · Jul 7, 2013

I myself use your second opion with LIMIT in sql query.

But there is range of methods that support pagination using RowBounds class. This is well described in mybatis documentation here

Pay attention to correct result set type to use.

Related questions

Is there a more efficient way of making pagination in Hibernate than executing select and count queries?

Usually pagination queries look like this. Is there a better way instead of making two almost equal methods, one of which executing "select *..." and the other one "count *..."? public List<Cat> findCats(String name, int offset, int limit) { …

Read More
Hibernate: Automatically creating/updating the db tables based on entity classes

I have the following entity class (in Groovy): import javax.persistence.Entity import javax.persistence.Id import javax.persistence.GeneratedValue import javax.persistence.GenerationType @Entity public class ServerNode { @Id @GeneratedValue(strategy = GenerationType.AUTO) Long id String firstName String lastName } and …

Read More
Unable to get spring boot to automatically create database schema

I'm unable to get spring boot to automatically load my database schema when I start it up. Here is my application.properties: spring.datasource.url=jdbc:mysql://localhost:3306/test spring.datasource.username=test spring.datasource.password= spring.datasource.driverClassName = com.…

Read More