MyBatis RowBounds doesn't limit query results

Question

I am developing an stateless API that needs to support pagination.

I use an Oracle database. I use Spring with MyBatis for database access.

From the documentation, I understand that I can use the RowBounds class to limit the number of rows returned by the query.

However, it seems that there's no special optimization done to the query to support pagination.

For example, if I set RowBounds to offset 100 with 50 records, I'd expect the query to have the following added:

(original query with the where clause...)
and ROWNUM < 150
and ROWNUM >= 100

But there's nothing there, it's just the query that I defined manually.

This is terrible for performance, since I might have several thousand results.

What am I doing wrong?

Thanks.

Answer 1

Mybatis leaves many things up to the SQL driver that is being used, and it appears the exact behavior surroundingRowBounds is one of those.

See http://mybatis.github.io/mybatis-3/java-api.html, particularly the section that says:

Different drivers are able to achieve different levels of efficiency in this regard. For the best performance, use result set types of SCROLL_SENSITIVE or SCROLL_INSENSITIVE (in other words: not FORWARD_ONLY).

The default is apparently UNSET, but you could try to use SCROLL_SENSITIVE as the ResultSetType attribute in the select tag and see if that helps. See http://mybatis.github.io/mybatis-3/sqlmap-xml.html for more info on that.

If that doesn't work you can always work around the issue by ditching the use of RowBounds and implement a SettingsBean class (or similar) that your select tag would take as a parameterType, and which contains fields for the offset and limit (or perhaps rowStart and rowEnd make more sense for Oracle, and then you can set those at runtime as needed and interpolate them dynamically into the SQL at the time the select is executed.

While a bit more code, you get to control the behavior exactly as you want through pure dynamic SQL. I have used an approach like this with Mybatis and Postgres and it has worked well.

So you would implement your SettingsBean class with those fields and their getters and setters, and your select statement might then look something like:

<select
  id="selectFoo"
  parameterType="com.foo.bar.SettingsBean">

select *
from foo
where rownum >= #{rowStart}
  and rownum < #{rowEnd}
</select>

Answer 2

I found a simple work around to this issue. I had followed the Mybatis instructions @khampson recommended and was passing a RowBounds instance to the mapper with no limits being enforced.

RowBounds rowbounds = new RowBounds(0, resultLimit);
roster = tableMapper.selectAll(rowbounds);

mapper java

public List<Row> selectAll(RowBounds rowbounds);

mapper xml

<select id="com.TableMapper.selectAll" resultMap="row" timeout="10">
            SELECT * FROM table;
</select>

simply appending "LIMIT #{param1.offset}, #{param1.limit}" to the mapper's xml produced the behavior I wanted!

<select id="com.TableMapper.selectAll" resultMap="row" timeout="10"> 
    SELECT * FROM table LIMIT #{param1.offset}, #{param1.limit}; 
</select>