Mybatis pagination does not scale on MySQL & H2

On MySQL and H2, mybatis pagination does not scale: if you do a

taskService.createTaskQuery().listPage(0,1)

Mybatis issues a SELECT * FROM ACT_RU_TASK_ (without something like "LIMIT 0,1").

Under the hood, Mybatis tries to use Scrollable JDBC ResultSets which is only partially supported on MySQL and H2.

This causes the database to fetch ALL records and cache them in memory where we then only use a certain page.