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.

Solution 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>

Solution 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>

Comments

[odedia]

Thanks. It seems that RowBounds will never use the Oracle rownum technique. Additionaly, It appears that the resultSetType setting is only relevant for stateful, web-based applications, like a web page showing paginated results. I'm using stateless EJBs with Spring & MyBatis, so I gather it will make 0 difference in my case? Seems like I will have to revent back to using rownum solution. I really think the MyBatis team should communicate the limitations of RowBounds more clearly. They describe it as a simple abstract way to do pagination, while in fact it is of very limited use.

[khampson]

Sure, no problem. In theory, even in a stateless application, it could work -- there would just need to be sequential queries with different offsets in conjunction with a limit. However it would really depend on how the Oracle driver handles it, as the number of rows to fetch at once rests with the JDBC layer, which in turn has a bunch of dependencies on the individual drivers (Oracle, Postgres, etc.). I agree it's made out to be simpler than it is. I've found that to also be the case with Mybatis when it comes to mappers for more complex types, such as String arrays.