Are you still using pagination? Try mybatis streaming query, really powerful!


Basic concepts

Streaming query means that after a successful query, it does not return a collection, but an iterator. The application gets a query result from the iterator each time. The advantage of streaming query is that it can reduce memory usage.

If there is no streaming query, if we want to get 10 million records from the database and do not have enough memory, we have to page query, and the efficiency of page query depends on the table design. If the design is not good, we can not perform efficient page query. Therefore, streaming query is a necessary function of a database access framework.

In the process of streaming query, the database connection is kept open. Therefore, it should be noted that after a streaming query is executed, the database access framework is not responsible for closing the database connection, and the application needs to close the connection itself after getting the data.

Mybatis streaming query interface

Mybatis provides a org.apache.ibatis . cursor.Cursor This interface inherits the And java.lang.Iterable It can be seen that:
1. Cursor can be closed;
2. Cursor is ergodic.

In addition, cursor also provides three methods:
1. Isopen(): used to determine whether the cursor object is open before fetching data. Only when it is opened, cursor can retrieve data;
2. Isconsumed(): used to judge whether all query results have been retrieved.
3. Getcurrentindex(): returns how many pieces of data have been obtained

Because cursor implements the iterator interface, it is very easy to get data from cursor in practice

cursor.forEach(rowObject -> {...}); 

But the process of building cursor is not simple

Let’s take a practical example. Here is a mapper class:

public interface FooMapper {
    @Select("select * from foo limit #{limit}")
    Cursor<Foo> scan(@Param("limit") int limit);

The method scan () is a very simple query. By specifying the return value of mapper method as the cursor type, mybatis knows that this query method is a stream query.

Then we write a spring MVC controller method to call mapper (irrelevant code has been omitted)

public void scanFoo0(@PathVariable("limit") int limit) throws Exception {
    try (Cursor<Foo> cursor = fooMapper.scan(limit)) {  // 1
        cursor.forEach(foo -> {});                      // 2

In the above code, foomapper comes in @ Autowired. Note 1 calls the scan method to get the cursor object and ensure that it can be closed at last; 2 takes data from the cursor.

The above code looks ok, but an error will be reported when scanfoo0() is executed

java.lang.IllegalStateException: A Cursor is already closed. 

This is because we said that the database connection needs to be maintained in the process of fetching data, and mapper method usually closes the connection after execution, so cusor is also closed.

Therefore, the solution to this problem is not complicated, just keep the database connection open. We have at least three options. Pay attention to the official account Java technology stack and get more answers to Mybatis and interview questions.

Scheme 1: sqlsessionfactory

We can use sqlsessionfactory to manually open the database connection and modify the controller method as follows:

public void scanFoo1(@PathVariable("limit") int limit) throws Exception {
    try (
        SqlSession sqlSession = sqlSessionFactory.openSession();  // 1
        Cursor<Foo> cursor = 
              sqlSession.getMapper(FooMapper.class).scan(limit)   // 2
    ) {
        cursor.forEach(foo -> { });

In the above code, we open a sqlsession (which actually represents a database connection) and ensure that it can be closed at last; in the second place, we use sqlsession to obtain mapper object. Only in this way can we ensure that the cursor object is open.

Scheme 2: transactiontemplate

In spring, we can use transactiontemplate to execute a database transaction. In this process, the database connection is also opened. The code is as follows:

public void scanFoo2(@PathVariable("limit") int limit) throws Exception {
    TransactionTemplate transactionTemplate = 
            new TransactionTemplate(transactionManager);  // 1

    transactionTemplate.execute(status -> {               // 2
        try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
            cursor.forEach(foo -> { });
        } catch (IOException e) {
        return null;

In the above code, we create a transactiontemplate object in one place (here, we don’t need to explain how transactionmanager comes from. This article assumes that readers are familiar with the use of spring database transactions), execute database transactions in two places, and the content of database transactions is to call the flow query of mapper object. Note that the mapper object here does not need to be created through sqlsession.

Scheme 3: @ transactional annotation

This is essentially the same as scheme 2. The code is as follows:

public void scanFoo3(@PathVariable("limit") int limit) throws Exception {
    try (Cursor<Foo> cursor = fooMapper.scan(limit)) {
        cursor.forEach(foo -> { });

It just adds a @ transactional annotation to the original method. This scheme seems to be the most concise, but note the hole used by annotations in the spring framework: it only takes effect when called externally. Calling this method in the current class will still report errors.

The above are three ways to realize mybatis streaming query.

Author: fabricated beliefs
Are you still using pagination? Try mybatis streaming query, really powerful!

Welcome to my WeChat official account, “code breakout”, sharing technologies like Python, Java, big data, machine learning, AI, etc., focusing on code farm technology upgrading, workplace breakout and mental transition, and 200 thousand + code growth, the first stop to grow with you.