How to do Pagination with mybatis?

Question

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 1

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);
    }

}