Powerful: MyBatis and Three Streaming Query Methods

Background

Stream query means an iterator is returned after the query is successful instead of a collection. The application obtains a query result from the iterator one at a time. The advantage of streaming query is that it can reduce memory usage.

If there is no streaming query and we want to fetch 10 million records from the database, but there is not enough memory, we have to query by Pages. The efficiency of the paging query depends on the table design. If the design is not good, you cannot perform efficient paged queries. Therefore, streaming query is an essential feature of database access frameworks.

During the streaming query process, the database connection is open. Therefore, after a streaming query is executed, the database access framework no longer closes the database connection, and the application must close itself after receiving the data.

MyBatis Streaming Query Interface

MyBatis provides a service called org.apache.ibatis.cursor. The Cursor interface class is used for streaming queries, and this interface inherits the java.io.Closeable and java.lang.Iterable interface, from which you can see:

Cursors can be closable.
Cursors can be traversed.

In addition, the Cursor provides three other methods:

isOpen(): determines whether a Cursor object is open before reading data. Data can be retrieved from the Cursor only when the function is enabled.
isConsumed(): determines whether all query results have been consumed.
getCurrentIndex(): returns the number of data records that have been obtained.

The Cursor implements the iterator interface, so reading data from the Cursor is simple in practice:

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

Creating a Cursor is Not a Simple Process

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. MyBatis knows that the query method is a streaming query by specifying the Cursor type returned by the Mapper method.

Then, write a SpringMVC Controller method to call the 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
    }
}

In the preceding code, fooMapper comes in at @ Autowired. Note 1: The scan method is called to obtain the Cursor object and ensure that it can be closed. Note 2: The scan method is called to obtain data from the cursor.

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

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

As mentioned earlier, the database connection needs to be maintained during data retrieval. However, the Mapper method usually closes the connection after execution, so Cursor is also closed.

Keep the database connection open to solve this problem. We have at least three options.

Solution 1: SqlSessionFactory

We can use SqlSessionFactory to open the database connection and modify the Controller method manually:

@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 code above, we open a SqlSession (it also represents a database connection) and ensure that it can be closed at the end. Then, we use SqlSession to get the Mapper object. This ensures that the Cursor object is open.

Solution 2: TransactionTemplate

In Spring, we can use TransactionTemplate to execute a database transaction. In this process, the database connection is also opened. The following sample code is used:

@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 code above, we created a TransactionTemplate object (how transactionManager came about will not be explained much since this article assumes that readers are familiar with the use of Spring database transactions.) The database transaction is executed where the Mapper object is called. Note: The Mapper object here does not need to be created through SqlSession.

Solution 3: @Transactional Annotation

This is essentially the same as Solution 2. The code is listed below:

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

The only requirement is to add the @Transactional annotation to the original method. This solution seems to be the most concise, but please note the pit used for annotations in the Spring Framework. It only takes effect for external calls. Calling this method in the current class will still report an error.

The preceding three methods can implement the MyBatis streaming query.

Article originally published on WeChat Account Programmer Bai Nannan

Disclaimer: The views expressed herein are for reference only and don't necessarily represent the official views of Alibaba Cloud.

Community

Powerful: MyBatis and Three Streaming Query Methods

Background

MyBatis Streaming Query Interface

Creating a Cursor is Not a Simple Process

Solution 1: SqlSessionFactory

Solution 2: TransactionTemplate

Solution 3: @Transactional Annotation

Read previous post:

Read next post:

Alibaba Clouder

You may also like

Comments

Alibaba Clouder

Related Products

Microservices Engine (MSE)

YiDA Low-code Development Platform

mPaaS

Database for FinTech Solution