Java – mybatis uses XML configuration in spring to return large results

I need to dump data from tables in Oracle to elastic search (100 million records),

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

XML configuration:

<?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 JUnit test that mybatis will return the whole list of results, which will lead to out of memory problem After Google, I found that resulthandler can solve the problem, but I can't find a job demo

Solution

There are two options:

>Use resulthandler > from 3.4 Use cursor from 1

ResultHandler

This 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

From mybatis 3.4 1, you can return cursor, which is Iterable and can be used like this (in the case of orderly results, see cursor API Java doc above 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
   }
}