how to make the latest MyBatis Dynamic SQL support pagination for mysql (limit/offset)?

Question

There are some samples on http://www.mybatis.org/mybatis-dynamic-sql/docs/select.html.

I want to implement limit/offset for mysql but failed to see any document on describing how to extend this library to support additional where condition.

here is what i'd like to achieve:

SelectStatementProvider selectStatement = select(id, animalName, bodyWeight, brainWeight)
        .from(animalData)
        .where(id, isIn(1, 5, 7))
        .and(bodyWeight, isBetween(1.0).and(3.0))
        .orderBy(id.descending(), bodyWeight)
        .limit(1).offset(10)
        .build()
        .render(RenderingStrategy.MYBATIS3);

Answer 1

There are a couple of resources you can use.

This page - http://www.mybatis.org/mybatis-dynamic-sql/docs/whereClauses.html - shows an example of using standalone where clauses to build a paging query. This is not exactly what you are looking for, but it shows one way to do it.

There is a unit test showing something that is closer to what you are looking for here - https://github.com/mybatis/mybatis-dynamic-sql/tree/master/src/test/java/examples/paging. This code works for MySQL and you could use it as is.

I hope to make this a little easier in a future release.