MyBatis RowBounds doesn't limit query results

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>

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>