How to set fetchSize for iBatis select statement

Question

I'm using iBatis as ORM framework in Java. I have a select statement

<select id="getList" resultMap="correctMap">
    SELECT *
    FROM SOME_TABLE
</select>

And I'm using queryForList method:

List<MappedObject> list = getSqlMapClientTemplate().queryForList("getList");

But it retrieves a big amount of data and performance of this query is pretty slow.

My assumption about this issues that iBatis has default fetch size (e.g. like in JDBS is 10) so that is why it so slow. So I want to set bigger fetch size (1000 for example). How I can do so?

Or am I looking in a wrong way?

NOTE: I need all data so set max results in queryForList method is not a appropriate solution for me.

List queryForList(String id,
                  Object parameterObject,
                  int skip,
                  int max) 

Answer 1

<select id="SELECT_TABLE" parameterType="String" fetchSize="500" resultType="hashmap">
    SELECT * FROM TABLE WHERE NAME = #{value}
</select>

Answer 2

Yes you can set fetchSize at a higher level and need not worry about doing for every select.

Step 1

Create a file mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="lazyLoadingEnabled" value="false"/>
        <setting name="defaultFetchSize" value="5000"/>
    </settings>
</configuration>

You can add any value supported in mybatis http://www.mybatis.org/mybatis-3/configuration.html

Step 2

Load this as a resource in your Config file. This is Spring 4 example

@Value("classpath:mybatis-config.xml")
private Resource myBatisResource ;

Step 3 : Pass to you SqlSessionFactoryBean

sessionFactory.setConfigLocation(myBatisResource);

Note: I did this with myBatis 3.3.0. It does not work with myBatis 3.4.4(there is open defect)

This will ensure that all select statements have a fetchSize property assigned to them.