How use PagingAndSorting with MyBatis?

Question

I use mybatis for retriving data from the DB. But I would use the Pageable object (from spring) to have the pagination functionalities. Is this possible? It is still enough to extend myMapper with a PaginationAndSortRepository?

Example:

@Mapper
public interface MyObjetcMapper extends PagingAndSortingRepository {
    List<MyObjetc> findAllMyObjetcs(Pageable pageable);
}

and then I use it from my service:

List<MyObjetc> myObjetc= myObjetcMapper.findAllCharacteristics(pageable);
return new PageImpl<>(characteristics, new PageRequest(pageable.getPageNumber(), pageable.getPageSize(), pageable.getSort()), MyObjetc.size());

The problem is that so I will return all MyObjects and not only the requested set.... I have to extract the interested list every time?

Possible duplicate of [How to do Pagination with mybatis?](https://stackoverflow.com/questions/17511313/how-to-do-pagination-with-mybatis) — Shihe Zhang, Aug 13 '18 at 08:28

score 2 · Accepted Answer · edited May 23 '17 at 12:17

I was searching the same thing some weeks ago and seems this is not possible. For what i found you have to implement your own paginator using RowBound parameter

(The RowBounds parameter causes MyBatis to skip the number of records specified, as well as limit the number of results returned to some number)

as explained in this answer that i'm sure you have already red How to do Pagination with mybatis?

Extending your mapper with PaginationAndSorting will not doing the job.

This project on github seems do what you are searching but i haven't try it and it has no comments, so i don't know if it's reliable and can be used as solution.

Txs very much. I hoped in the solution. but It's ok. I will resolve it with extend my mapper manually. — code4fun, Sep 28 '16 at 05:29

score 0 · Answer 2 · answered Oct 07 '16 at 05:18

I have been using Mybatis-PageHelper plugin to do the pagination against Sql Server

Databases supported:

Oracle
Mysql/MariaDB
SQLite
Hsqldb
PostgreSQL
DB2
SqlServer (2005 to 2016)
Informix
H2

Declaration in maven

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>4.1.6</version>
</dependency>

Specify it as a plugin in mybatis-config.xml

<plugins>
    <plugin interceptor="com.github.pagehelper.PageHelper">
    </plugin>
</plugins>

Usage example:

// Get the results for page 1 where every page has 10 rows
PageHelper.startPage(1, 10);

List<MyObjetc> myObjetc= myObjetcMapper.findAllCharacteristics();

assertEquals(10, list.size());

Txs. I don't have a mybatis-config.xml. How can I configure this? I only have a entry in my application properties: mybatis.mapperLocations=classpath*:**/mybatis/mappers/*.xml where points to the package that have the mappers. — code4fun, Oct 10 '16 at 05:51

How use PagingAndSorting with MyBatis?

2 Answers2