sql - MyBatis RowBounds doesn't limit query results -

sql - MyBatis RowBounds doesn't limit query results -

i developing stateless api needs back upwards pagination.

i utilize oracle database. utilize spring mybatis database access.

from documentation, understand can utilize rowbounds class limit number of rows returned query.

however, seems there's no special optimization done query back upwards pagination.

for example, if set rowbounds offset 100 50 records, i'd expect query have next added:

(original query clause...) , rownum < 150 , rownum >= 100

but there's nil there, it's query defined manually.

this terrible performance, since might have several one thousand results.

what doing wrong?

thanks.

mybatis leaves many things sql driver beingness used, , appears exact behavior surroundingrowbounds 1 of those.

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

different drivers able accomplish different levels of efficiency in regard. best performance, utilize result set types of scroll_sensitive or scroll_insensitive (in other words: not forward_only).

the default apparently unset, seek utilize scroll_sensitive resultsettype attribute in select tag , see if helps. see http://mybatis.github.io/mybatis-3/sqlmap-xml.html more info on that.

if doesn't work can work around issue ditching utilize of rowbounds , implement settingsbean class (or similar) select tag take parametertype, , contains fields offset , limit (or perhaps rowstart , rowend create more sense oracle, , can set @ runtime needed , interpolate them dynamically sql @ time select executed.

while bit more code, command behavior want through pure dynamic sql. have used approach mybatis , postgres , has worked well.

so implement settingsbean class fields , getters , setters, , select statement might like:

<select id="selectfoo" parametertype="com.foo.bar.settingsbean"> select * foo rownum >= #{rowstart} , rownum < #{rowend} </select>

sql spring oracle mybatis