How to paginate with mybatis?

Question

I am currently working on an e-commerce application where I have to show a list of available products using search functions.

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

I use mybatis as my ORM tool and mysql as the base database.

Google google, I found the following ways to solve this problem:

• Client side swapping : Here I will need to get all the results from the database that match the search criteria in one fell swoop and handle pagination at my code level (possibly the code end code).

• Server side swapping : With mysql, I can use Limit and a result set offset to build a query like: SELECT * FROM sampletable WHERE condition1>1 AND condition2>2 LIMIT 0,20

Here I need to pass the offset and limit counter every time the user selects a new page while navigating in the search results.

Can someone say

• which would be the best way to implement swap?
• Mybatis supports a better way to implement swap than simply relying on the above SQL queries (for example, hibernate APIs).

Any input is very much appreciated. Thanks.

Answer 1

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

But there are a number of methods that support pagination using the RowBounds class. This is well described in the mybatis documentation here

Note the correct type of result set to use.

Answer 2

If you use Mappers (much easier than using raw SqlSessions), the easiest way to apply the restriction is to add the RowBounds parameter to the argument list of the mapping function, for example:

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

This is referred to almost as an afterthought in a link posted in Vladimir under the heading “Using Mappers” and may use another accent:

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

Please note that support for RowBounds may vary by database. Mybatis documentation implies that Mybatis will make sure to use the appropriate query. However, for Oracle, at least this is handled by very inefficient database callbacks.

Answer 3

pagination has two types: physical and logical

• boolean means first get all the data and then sort them in memory.
• Submenu level physical database

by default, mybatis pagination is logical ... so when you select a massive database, like a 100 GB drop, for example, the rowbound method will still be very slow.

the solution is to use physical pagination

• you can make your own path through mybatis interceptor
• or using plugins made by someone else

Answer 4

If you use the MyBatis generator, you can try connecting the Row Bounds plugin from the official website: org.mybatis. generator.plugins.RowBoundsPlugin . This plugin will add a new version of selectByExample , which accepts the RowBounds parameter.