Mybatis return large result with xml configuration in spring

Question

I need to dump data from a table in oracle to elasticsearch(100 Million record), My memory limit of JVM is 256M, I use the following code and config to get the data from oracle (mybatis + spring): the interface:

package com.fudy.mapper;
import java.util.List;
import com.fudy.domain.Person;
public interface PersonMapper {
    List<Person> selectAllPerson();
}

the xml config:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.fudy.mapper.PersonMapper">
    <resultMap type="com.fudy.domain.Person" id="PersonAlias">
        <id column="ID" property="id" />
        <result column="NAME" property="name" />
    </resultMap>
    <select id="selectAllPerson" fetchSize="10000" resultMap="PersonAlias">
        SELECT * FROM person
    </select>
</mapper>

ApplicationContext.xml :

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
    xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="
        http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd">
    <context:annotation-config />
    <tx:annotation-driven transaction-manager="transactionManager" />

    <context:property-placeholder location="classpath:db.properties"/>

    <bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource" destroy-method="close">
        <property name="URL" value="${jdbc.url}" />
        <property name="user" value="${jdbc.user}"/>
        <property name="password" value="${jdbc.password}"/>
        <property name="connectionCachingEnabled" value="true"/>
    </bean>



    <!-- define the SqlSessionFactory, notice that configLocation is not needed 
        when you use MapperFactoryBean -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource" />

        <property name="configLocation" value="classpath:Configuration.xml" />  

    </bean>

    <!-- scan for mappers and let them be autowired -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="basePackage" value="com.fudy.mapper" />
        <!-- optional unless there are multiple session factories defined -->
        <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
    </bean>

    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean> 

</beans>

My junit test:

package com.fudy.mapper;

import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.fudy.domain.Person;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration({"/ApplicationContext.xml"})
public class PersonMapperTest {
    @Autowired
    private PersonMapper mapper;
    @Test
    public void testSelectAllPerson() {
        List<Person> list = mapper.selectAllPerson();
        for ( Person person : list) {
            System.out.println(person.getId());
            System.out.println(person.getName());
            //TODO insert into elasticsearch 
        }
    }
}

you can see from the junit test, Mybatis will return the whole list of the result and this will cause out of memory issue. After google it, I find the ResultHandler may solve the problem, But I can't find a working demo.

Answer 1

There are two options:

1. use ResultHandler
2. since 3.4.1 use Cursor

ResultHandler

That is how you can use custom ResultHandler:

PersonMapper.xml

<mapper namespace="com.fudy.mapper.PersonMapper">
  <resultMap type="com.fudy.domain.Person" id="PersonAlias">
    <id column="ID" property="id" />
    <result column="NAME" property="name" />
  </resultMap>
  <select id="selectAllPerson" resultMap="PersonAlias">
     SELECT * FROM person
  </select>
</mapper>

PersonMapper.java

public interface PersonMapper {
     void selectAllPersons(ResultHandler handler);
}

MyService.java

class PersonResultHandler implements ResultHandler {
    @Override
    public void handleResult(ResultContext context) { 
        Person person = (Person)context.getResultObject(); 
        // process person here
    }
};
PersonResultHandler handler = new PersonResultHandler();
PersonMapper personMapper = ...;
personMapper.selectAllPersons(handler);

Cursor

Starting from mybatis 3.4.1 you can return Cursor which is Iterable and can be used like this (under condition that result is ordered, see above Cursor API java doc for details):

PersonMapper.java

public interface PersonMapper {
     Cursor<Person> selectAllPersons();
}

MyService.java

PersonMapper personMapper = ...;
try (Cursor<Person> persons = personMapper.selectAllPersons()) {
   for (Person person : persons) {
      // process one person
   }
}