Basic concepts
Streaming query refers to a query that returns an iterator instead of a collection after a successful query. 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 is kept open. Therefore, it should be noted that after executing a streaming query, 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 a website called ， org apache. ibatis. cursor. The interface class of cursor ， which inherits Java io. Closeable and Java Lang. Iterable interface, from which we can see:

1. Cursor # can be closed;
2. Cursor # is ergodic.
In addition, Cursor # also provides three methods:

1. Ispen(): used to judge whether the # Cursor # object is open before fetching data. Data can be retrieved only when ， Cursor ， 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:

cursor.forEach(rowObject -> {...});
But the process of building Cursor is not simple
Let's give a practical example. The following 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. By specifying that the return value of the {Mapper} method is of type {Cursor}, MyBatis} knows that this query method is a streaming query.

Then we write a spring MVC 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)) {  
        cursor.forEach(foo -> {});                      
    }
}

In the above code, fooMapper ， comes from @ Autowired ，. In note 1, call the scan method to get the cursor object and ensure that it can be closed finally; 2 ， fetches data from ， cursor ，.

The above code looks ok, but an error will be reported when executing {scanFoo0():

java.lang.IllegalStateException: A Cursor is already closed.
This is because we said earlier that the database connection needs to be maintained in the process of fetching data, and the connection is usually closed after the Mapper method is executed, so the Cusor method is also closed.

Therefore, the idea to solve 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 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();  
        Cursor<Foo> cursor = 
              sqlSession.getMapper(FooMapper.class).scan(limit)   
    ) {
        cursor.forEach(foo -> { });
    }
}

In the above code, at 1 ， we open a ， SqlSession (which actually represents a database connection) and ensure that it can be closed at last; At 2 ， we use ， SqlSession ， to obtain the ， Mapper ， object. In this way, you can 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:

@GetMapping("foo/scan/2/{limit}")
public void scanFoo2(@PathVariable("limit") int limit) throws Exception {
    TransactionTemplate transactionTemplate = 
            new TransactionTemplate(transactionManager);  

    transactionTemplate.execute(status -> {               
        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 place 1 (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 place 2. The content of database transactions is to call the streaming 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 an @ 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 only takes effect when called externally. Calling this middle note in the current class will still report a mistake.

The above are three methods to implement MyBatis streaming query.