Mybatis Rowbounds Doesn't Limit Query Results

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>

oracle cache mess with mybatis query results (can't get the latest)

The problem is not related to cache clearing.

I think there's no problem except issuing a commit after your insert( a DML ) statement.

If you didn't issue a commit you could get only 3 records from another session.

But after you issue,

alter table book nocache ( a DDL )

, an implicit commit occur, and you could see all 5 records.

How to do Pagination with mybatis?

I myself use your second opion with LIMIT in sql query.

But there is range of methods that support pagination using RowBounds class.
This is well described in mybatis documentation here

Pay attention to correct result set type to use.

myBATIS foreach hitting limit of 1000

I'm not sure if this is the most elegant solution or not, but here's what I did:

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <trim suffixOverrides=" OR ID IN ()">
      <foreach item="item" index="index" collection="list"
               open="(" close=")">
          <if test="index != 0">
              <choose>
                  <when test="index % 1000 == 999">) OR ID IN (</when>
                  <otherwise>,</otherwise>
              </choose>
          </if>
          #{item}
      </foreach>
  </trim>
</select>

Explanation

Lets start with the foreach. We want to surround it in ( and ). Most elements we want commas between, except every thousand elements we want to stop the list and OR with another one. That's what the choose, when, otherwise construct handles. Except we don't want either of those before the first element, thus the if that the choose is inside of. Finally, the foreach ends with actually having the #{item} inserted.

The outer trim is just so that if we have exactly 1000 elements, for example, we don't end with OR ID IN () which would be invalid ((), specifically, is the invalid part. That's a syntax error in SQL, not an empty list like I hoped it would be.)

---

Related Topics