The basic concept

A streaming query is a query that returns not a collection but an iterator, one result at a time from the iterator. Streaming queries have the advantage of reducing memory usage.

Without streaming queries, when we want to fetch 10 million records from the database and do not have enough memory, we have to paging queries, and the efficiency of paging queries depends on the design of the table, and if the design is not good, it will not be able to perform efficient paging queries. Streaming query is therefore an essential feature of a database access framework.

The database connection is kept open during a streaming query, so it is important to note that the DATABASE access framework does not close the database connection after executing a streaming query. The application must close the database connection after fetching data.

MyBatis streaming query interface

MyBatis provides a call org. Apache. Ibatis. Cursor. The cursor interface classes for streaming, this interface inherits the Java IO. Closeable and Java. Lang. The Iterable interface, thus: 1.Cursor can be closed; 2.Cursor is traversable.

IsOpen () : is used to determine whether the Cursor object isOpen before fetching data. The Cursor can retrieve data only when it is open; 2. IsConsumed () : Used to check whether all query results are consumed. 3. GetCurrentIndex () : Returns how many entries have been retrieved

Because Cursor implements the iterator interface, fetching data from Cursor is very simple in practice:

cursor.forEach(rowObject -> {... });Copy the code

But building a Cursor is not an easy process

Let’s take a real example. Here is a Mapper class:

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

The scan() method is a very simple query. MyBatis knows that the query method is a streaming query by specifying that the return value of the Mapper method is of type Cursor.

Then we’ll 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}}Copy the code

In the code above, fooMapper is @autowired coming in. The scan method is called at comment 1 to get the Cursor object and ensure that it closes last. At 2, we get the data from the cursor.

The above code looks fine, but an error occurs when scanFoo0() is executed:

java.lang.IllegalStateException: A Cursor is already closed.
Copy the code

This is because we talked about the need to keep the database connection while fetching data, and the 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 to choose from. Follow the public account of Java technology stack for Mybatis and more interview questions with answers.

Solution 1: SqlSessionFactory

We can manually open the database connection with SqlSessionFactory and change 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 -> { }); }}Copy the code

In the above code, at one point we opened an SqlSession (which actually represents a database connection) and ensured that it would eventually close; 2. We use SqlSession to get Mapper objects. This ensures that the Cursor object is open.

Solution 2: TransactionTemplate

In Spring, we can use the TransactionTemplate to perform a database transaction with the database connection still open. 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;
    });
}
Copy the code

In the code above, we create a TransactionTemplate object in one place and execute a database transaction in two places. The content of a database transaction is a streaming query that invokes a Mapper object. Note that Mapper objects are not created using SqlSession.

The @Transactional annotation

This is essentially the same as plan 2, with the following code:

@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 -> { });
    }
}
Copy the code

It simply adds the @Transactional annotation to the original method. This seems to be the simplest solution, but note the hole annotations use in the Spring framework: they only work when called externally. If you call this method in the current class, you will still get an error.

The above are three ways to implement MyBatis streaming query.

Author: fake faith source: segmentfault.com/a/119000002…