RowBounds in MyBatis

[Tashiro Taisuke]

Hello,

I am migrating my application from iBATIS to MyBATIS(3.2.8) and having problem with RowBounds.

When I execute SqlSession#selectList(String, Object, RowBounds) method and the actual row count is less than the offset count specified with RowBounds, I get SQLException indicating that the result set is closed. In iBATIS, I simply get empty list with no exception.

How do I use this method when records may be deleted by another user? Do I have to query row count ahead and lock the records?
 

[Iwao AVE!]

Hi,

I couldn't reproduce it with a simple test case.
As expected, selectList returns an empty list when the offset is
larger than the row count in the results.

Could you provide a test case?
https://github.com/mybatis/mybatis-3/wiki/Unit-Test
Or please post more information.

And, just to clarify, does it work if the offset is smaller than the
actual row count?

Regards,
Iwao

[Tashiro Taisuke]

Yes, it works when the offset is smaller than the row count.

SQLException is thrown at DefaultResultSetHandler#handleRowValuesForSimpleResultMap (line 292) when the offset exceeds the row count by one, and at DefaultResultSetHandler#skipRows (line 323) when more than one. Both calling ResultSet#next.

According to Java API Specification, whether the ResultSet#next method returns false or throws SQLException once it has reached the end of result set depends on JDBC driver implementation (I am using DB2 10.5). It may have to handle the return value of ResultSet#next.

2015年8月31日月曜日 11時24分21秒 UTC+9 Iwao AVE!:

[Iwao AVE!]

Hi Taisuke,

I am not familiar with DB2, but it seems to be a common issue and
there's a technote.
http://www-01.ibm.com/support/docview.wss?uid=swg21461670
Please try enabling the allowNextOnExhaustedResultSet option and let
us know the result.

I haven't given it much thought, but it might be difficult to fix the
problem in MyBatis side.

Regards,
Iwao

> --
> You received this message because you are subscribed to the Google Groups
> "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mybatis-user...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[Tai Tash]

Hi Iwao,

Thanks for the information. I've tried the DB2 custom property and it works.

We've been using JDBC Type 4 driver since before migration and didn't want to change the behavior of JDBC driver since there are many other applications running on the same server including those not using iBATIS nor MyBatis. But now that I know it's difficult to fix on MyBatis side, it seems that I must consider this option.

Anyway, thank you very much for your help.

2015年9月1日火曜日 2時50分22秒 UTC+9 Iwao AVE!:

[Iwao AVE!]

Thank you for the follow-up.
Glad it worked :-)

Considering your requirement, it might be better to use LIMIT/OFFSET
clause instead of RowBounds as it's usually more efficient and
unaffected by this DB2 driver behavior as well.

Hope the migration goes well,
Iwao