A streaming query is a query that returns an iterator instead of a collection. The application retrives 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:

  • A Cursor can be closed. When you close the Cursor, you also close the database connection.
  • Cursor is traversable.

In addition, Cursor provides three methods:

  • isOpen()Cursor: is used to determine whether the Cursor object is open before fetching data. The Cursor can retrieve data only when it is open;
  • isConsumed(): Determines whether all query results are obtained.
  • getCurrentIndex(): Returns how many pieces of data have been retrieved.

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

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

Use the try-resource method to close the Cursor automatically.

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. When we define this square, we specify the return value as Cursor type, MyBatis knows that this query method is a streaming query.

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

Let’s say fooMapper is @autowired coming in. Note 1 is 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(int) 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.

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.

How to implement streaming query in MyBatis?

Concern technical number: Public number: Code agriculture architecture