How to do Pagination with mybatis?

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.

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.

If you are using Spring MyBatis, you can achieve pagination manually using 2 MyBatis queries and the useful Spring Page and Pageable interfaces.

You create a higher level DAO interface e.g. UploadDao

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;

public interface UploadDao {

    Page<Upload> search(UploadSearch uploadSearch, Pageable pageable);

}

... where Upload maps to an upload table and UploadSearch is a parameter POJO e.g.

@Data  // lombok
public class UploadSearch {

    private Long userId;
    private Long projectId;
    ... 

}

An implementation of UploadDao (which injects a MyBatis UploadMapper mapper) is as follows:

public class DefaultUploadDao implements UploadDao {

    @Autowired
    private UploadMapper uploadMapper;

    public Page<Upload> searchUploads(UploadSearch uploadSearch, Pageable pageable) {
        List<Upload> content = uploadMapper.searchUploads(uploadSearch, pageable);
        Long total = uploadMapper.countUploads(uploadSearch);
        return new PageImpl<>(content, pageable, total);
    }

}

The DAO implementation calls 2 methods of UploadMapper. These are:

1. UploadMapper.searchUploads - returns a page of results based on search param (UploadSearch) and Pageable param (contains offset / limit etc).
2. UploadMapper.countUploads - returns total count, again based on search param UploadSearch. NOTE - Pageable param is not required here as we're simply determining the total rows the search parameter filters to and don't care about page number / offset etc.

The injected UploadMapper interface looks like ...

@Mapper
public interface UploadMapper {

    List<Upload> searchUploads(
        @Param("search") UploadSearch search,
        @Param("pageable") Pageable pageable);

    long countUploads(
        @Param("search") UploadSearch search);

}

... and the mapper XML file containing the dynamic SQL e.g. upload_mapper.xml contains ...

<mapper namespace="com.yourproduct.UploadMapper">

    <select id="searchUploads" resultType="com.yourproduct.Upload">
        select u.*
          from upload u
         <include refid="queryAndCountWhereStatement"/>
         <if test="pageable.sort.sorted">
             <trim prefix="order by">
                 <foreach item="order" index="i" collection="pageable.sort" separator=", ">
                     <if test="order.property == 'id'">id ${order.direction}</if>
                     <if test="order.property == 'projectId'">project_id ${order.direction}</if>
                 </foreach>
             </trim>
         </if>
        <if test="pageable.paged">
            limit #{pageable.offset}, #{pageable.pageSize}  
        </if>
        <!-- NOTE: PostgreSQL has a slightly different syntax to MySQL i.e. 
             limit #{pageable.pageSize} offset #{pageable.offset} 
        -->
    </select>

    <select id="countUploads" resultType="long">
        select count(1)
         from upload u
        <include refid="queryAndCountWhereStatement"/>
    </select>

    <sql id="queryAndCountWhereStatement">
        <where>
            <if test="search != null">
                <if test="search.userId != null"> and u.user_id = #{search.userId}</if>
                <if test="search.productId != null"> and u.product_id = #{search.productId}</if>
                ...
            </if>
        </where>
    </sql>
</mapper>

NOTE - <sql> blocks (along with <include refid=" ... " >) are very useful here to ensure your count and select queries are aligned. Also, when sorting we are using conditions e.g. <if test="order.property == 'projectId'">project_id ${order.direction}</if> to map to a column (and stop SQL injection). The ${order.direction} is safe as the Spring Direction class is an enum.

The UploadDao could then be injected and used from e.g. a Spring controller:

@RestController("/upload")
public UploadController {

    @Autowired
    private UploadDao uploadDao;  // Likely you'll have a service instead (which injects DAO) - here for brevity

    @GetMapping
    public Page<Upload>search (@RequestBody UploadSearch search, Pageable pageable) {
        return uploadDao.search(search, pageable);
    }

}

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