Handling very large amount of data in MyBatis

user_23637 0
Viewed 24584

My goal is actually to dump all the data of a database to an XML file. The database is not terribly big, it's about 300MB. The problem is that I have a memory limitation of 256MB (in JVM) only. So obviously I cannot just read everything into memory.

I managed to solve this problem using iBatis (yes I mean iBatis, not myBatis) by calling it's getList(... int skip, int max) multiple times, with incremented skip. That does solve my memory problem, but I'm not impressed with the speed. The variable names suggests that what the method does under the hood is to read the entire result-set skip then specified record. This sounds quite redundant to me (I'm not saying that's what the method is doing, I'm just guessing base on the variable name).

Now, I switched to myBatis 3 for the next version of my application. My question is: is there any better way to handle large amount of data chunk by chunk in myBatis? Is there anyway to make myBatis process first N records, return them to the caller while keeping the result set connection open so the next time the user calls the getList(...) it will start reading from the N+1 record without doing any "skipping"?

large-data mybatis java
5 Answers

I have successfully used MyBatis streaming with the Cursor. The Cursor has been implemented on MyBatis at this PR.

From the documentation it is described as

A Cursor offers the same results as a List, except it fetches data lazily using an Iterator.

Besides, the code documentation says

Cursors are a perfect fit to handle millions of items queries that would not normally fits in memory.

Here is an example of implementation I have done and which I was able to successfully use it:

import org.mybatis.spring.SqlSessionFactoryBean;

// You have your SqlSessionFactory somehow, if using Spring you can use 
SqlSessionFactoryBean sqlSessionFactory = new SqlSessionFactoryBean();

Then you define your mapper, e.g., UserMapper with the SQL query that returns a Cursor of your target object, not a List. The whole idea is to not store all the elements in memory:

import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.cursor.Cursor;

public interface UserMapper {

    @Select(
        "SELECT * FROM users"
    )
    Cursor<User> getAll();
}

Then you write the that code that will use an open SQL session from the factory and query using your mapper:

try(SqlSession sqlSession = sqlSessionFactory.openSession()) {
    Iterator<User> iterator = sqlSession.getMapper(UserMapper.class)
                                        .getAll()
                                        .iterator();
    while (iterator.hasNext()) {
        doSomethingWithUser(iterator.next());
    }
}
user_9148740
Related