Introduction

During this time, at work, I was dealing with a large amount of data, and I had to review the process that never responded. Overall, it took about 4 minutes. When reviewing this process, I modified Mapper to org.apache.ibatis.cursor.Cursor instead of List. As a result, we were able to speed up the response.

Since it is a big deal, I will summarize how to use the Cursor added in MyBatis 3.4 or later.

Operating environment

Java 1.8.0_181
Spring FrameWork 4.3.16
MyBatis 3.4.5
mybatis-spring 1.3.1

Only mybatis-spring, I think that it is necessary / unnecessary depending on the project.

How to use

mapper.xml does not need to be changed from when you get it in List. Since it processes a large amount of data, it would be even better to adjust the cache size with fetchSize.

`mapper.xml`


  <select id="getColumns" fetchSize="50000" resultType="java.lang.String">
    SELECT columns FROM table1
  </select>

Mapper.java imports org.apache.ibatis.cursor.Cursor. Match the \ part with the SQL return value.

`Mapper.java`


import org.apache.ibatis.cursor.Cursor;

@Mapper
public interface Mapper {
    public Cursor<String> getColumns();
}

This is the part to call from Mapper. Similar to List, except that the call result is stored in Cursor \ type.

Also, the part that retrieves the value by loop processing can be written in the same way as List. In the sample, it is written in two types, for Each and Iterator.

`Service.java`


@Service
public class Service {
    @Autowired
    private Mapper mapper;

    public void getMapper() {
        try (Cursor<String> columnsCorsor = mapper.getColumns()) {

            //Loop processing 1
            columnsCorsor.forEach(s -> {
                System.out.println(s);
            });

            //Loop processing 2 (Note that the second loop will cause an error !!)
            Iterator<String> iterator = columnsCorsor.iterator();
            while (iterator.hasNext()) {
                System.out.println(iterator.next());
            }

        } catch (Exception e) {
            e.printstacktrace();
        }
    }
}

It should be noted that, unlike when fetching with List, **, it will be closed when all the data in Cursor is read **. Therefore, if you retrieve all the data even once and then retrieve the data again, the following error will occur.

java.lang.IllegalStateException: Cannot open more than one iterator on a Cursor

In the case of basic usage, MyBatis side will close it, so I think that there is no problem.

However, if an error occurs in the middle, there is no guarantee that it will be closed. So, let's ** close it tightly ** with a try ~ resource statement or finally clause at the caller.

Finally

Thanks to Cursor, I was able to reduce the response from 4 minutes to 2 minutes. (This is the result of reviewing the parts other than Cursor ...) When processing a large amount of data, make use of Cursor.

Miscellaneous notes

MyBatis has few reference materials, so I wonder if there are few users. What is the mainstream of DB access frameworks ...? MyBatis applies raw SQL and maps it nicely, so I personally like it ... ~~ Sometimes I get annoyed by annotations ~~

References

MyBatis – MyBatis 3 | Mapper XML File Cursor | mybatis [5.2. Database Access (MyBatis3) — TERASOLUNA Server Framework for Java (5.x) Development Guideline 5.0.1.RELEASE documentation](https://terasolunaorg.github.io/guideline/5.0.1.RELEASE/en/ ArchitectureInDetail / DataAccessMyBatis3.html)

[MyBatis] Use Cursor when mapping a large amount of data