Ibatis queryWithRowHandler() still seems to fetch all rows

Question

I'm using Ibatis 2.3.4 and Mysql 5.1.37, with mysql-java-connector 5.1.6 with java 1.6

I have query that returns very many rows from a single table. In order to do this the manual suggests to use queryWithRowHandler(). However when I call this query it internally still seems to fetch all rows (memory goes up very fast before the first handleRow() call is done.

How can I tell Ibatis to fetch small at the time portions (I can of course use several queries returning lists of smaller results, but in my opinion this is precisely what ibatis should do for me)?

EDIT: I've tried setting a fetchSize of 100 for the statement, this doesn't seem to do anything, same for resultSetType="FORWARD_ONLY". lazyLoadingEnabled isn't set anywhere, so should be enable.

Answer 1

After doing more thorough search, I found that the mysql manual on the jbdc implementation and the mysql issue database had some useful information. This boils down to needing the following:

The JDBC driver needs a setting to use cursor (this might have some downsides too)

  <transactionManager type="JDBC" commitRequired="false">
    <dataSource type="SIMPLE">
       ...
      <property name="Driver.useCursorFetch" value="true"/>
    </dataSource>
  </transactionManager>

The statement needs the following properties (with any useful value fetchSize).

fetchSize="1000" resultSetType="FORWARD_ONLY"