How to do Pagination with mybatis?

问题:

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 .

回答1:

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.

回答2:

If you're using Mappers (much easier than using raw SqlSessions), the easiest way to apply a limit is by adding a RowBounds parameter to the mapping function's argument list, e.g:

// without limit
List<Foo> selectFooByExample(FooExample ex);

// with limit
List<Foo> selectFooByExample(FooExample ex, RowBounds rb);

This is mentioned almost as an afterthought in the link Volodymyr posted, under the Using Mappers heading, and could use some more emphasis:

You can also pass a RowBounds instance to the method to limit query results.

Note that support for RowBounds may vary by database. The Mybatis documentation implies that Mybatis will take care of using the appropriate query. However, for Oracle at least, this gets handled by very inefficient repeat calls to the database.

回答3:

pagination has two types, physical and logical

• logical means to retrieve all the data first then sort them in memory
• physical means database level subset select

the default mybatis pagination is logical... thus when you select a massive database e.g 100GB of blobs, the rowbound method will still be very slow

the solution is to use the physical pagination

• you can do your own way through the mybatis interceptor
• or using plugins pre made by someone else

回答4:

If you are using the MyBatis Generator, you may want to try the Row Bounds plugin from the official site: org.mybatis.generator.plugins.RowBoundsPlugin. This plugin will add a new version of the selectByExample method that accepts a RowBounds parameter.