Streaming query refers to a query that returns an iterator instead of a collection after a successful query, and the application takes one 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, when we want to get 10 million records from the database and don't 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't execute efficient page query. Therefore, streaming query is a function that a database access framework must have.

In the process of streaming query, the database connection remains 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 itself after fetching the data.

MyBatis streaming query interface

MyBatis provides an interface class called org.apache.ibatis.cursor.Cursor for streaming query. This interface inherits the java.io.Closeable and java.lang.Iterable interfaces

1. Cursor can be closed. In fact, when cursor is closed, the database connection is also closed;
2. Cursor is ergodic.

In addition, Cursor provides three methods:

1. Ispen(): used to determine whether the cursor object is open before fetching data. Cursor can retrieve data only when it is opened;
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 simple to get data from Cursor in practical use:

try(Cursor cursor = mapper.querySomeData()) {
    cursor.forEach(rowObject -> {
        // ...
    });
}

Use the try resource method to turn off the Cursor automatically.

But the process of building Cursor is not simple

Let's give a practical example. Here is a Mapper class:

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

The method scan() is a very simple query. When we define this party, we specify the return value as the Cursor type, and MyBatis understands that this query method is a streaming query.

Then we write a SpringMVC Controller method to call Mapper (irrelevant code has been omitted):

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

Suppose fooMapper came in @ Autowired. Note 1 is to obtain the cursor object and ensure that it can be closed finally; Two places get data from cursor.

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

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

This is because we said earlier that the database connection needs to be maintained during the data retrieval process, and the Mapper method usually closes the connection after execution, so Cusor is also closed.

Therefore, the idea to solve this problem is not complex. Just keep the database connection open. We have at least three options.

Scheme 1: SqlSessionFactory

We can manually open the database connection with SqlSessionFactory, and modify the Controller method as follows:

@GetMapping("foo/scan/1/{limit}")
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 (in fact, it also represents a database connection) and ensure that it can be closed at last; At 2, we use SqlSession to obtain Mapper objects. This ensures 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:

@GetMapping("foo/scan/2/{limit}")
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) {
            e.printStackTrace();
        }
        return null;
    });
}

In the above code, we create a TransactionTemplate object in 1 place (there is no need to explain how transactionManager comes here. This article assumes that readers are familiar with the use of Spring database transactions), and execute database transactions in 2 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:

@GetMapping("foo/scan/3/{limit}")
@Transactional
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 looks the most concise, but please note the pit used by annotations in the Spring framework: it takes effect only when called externally. Calling this method in the current class will still report errors.

The above are three methods to realize MyBatis streaming query.