Detailed explanation of Mybatis' Cursor method to avoid OOM exceptions

What is Cursor

Before studying how Cursor avoids OOM exceptions, let’s first understand what Cursor is.
In Mybatis, there is a special objectCursor, the object's comments clearly illustrate the purpose of this class.

/**
 * Cursor contract to handle fetching items lazily using an Iterator.
 * Cursors are a perfect fit to handle millions of items queries that would not normally fits in memory.
 * If you use collections in resultMaps then cursor SQL queries must be ordered (resultOrdered="true")
 * using the id columns of the resultMap.
 *
 * @author Guillaume Darmont / guillaume@
 */

Cursors are a perfect fit to handle millions of items queries that would not normally fits in memory.

It is even very suitable to make it more important in the explanation.
The purpose of this class is actually to avoid program OOM errors when the database batch querys big data.

How to use Cursor

existMybatisUsed inCursorVery simple, justReturn the method in the Mapper fileSet toCursor<T>Just do it.

@Select("SELECT * FROM log")
Cursor<Log> selectAll();

Note: If you want to use Cursor in SpringBoot, you need to choose one of the following methods, otherwise using Cursor will cause an error.

Create SqlSession manually
Mark the method that calls the Mapper method@TransactionalTransaction annotation.

The reason for the extra configuration is that in SpringBoot, Mybatis'sSqlSession life cycleOnly in the Mapper method, and when closing SqlSession, the Cursor bound to SqlSession will be closed, so it is necessary to extend the survival time of SqlSession.

Cursor Principle

Resolve the Mapper method to return the value

In Mybatis, when calling the Mapper method, theMapperProxyProxy for the method. At this time, different analysis will be performed according to the specific method.

public MethodSignature(Configuration configuration, Class&lt;?&gt; mapperInterface, Method method) {
    // The parsing method returns the value    Type resolvedReturnType = (method, mapperInterface);
    if (resolvedReturnType instanceof Class&lt;?&gt;) {
         = (Class&lt;?&gt;) resolvedReturnType;
    } else if (resolvedReturnType instanceof ParameterizedType) {
         = (Class&lt;?&gt;) ((ParameterizedType) resolvedReturnType).getRawType();
    } else {
         = ();
    }
     = ();
     = ().isCollection() || ();
    // Whether the method returns the Cursor type     = ();
     = ();
     = getMapKey(method);
     =  != null;
     = getUniqueParamIndex(method, );
     = getUniqueParamIndex(method, );
     = new ParamNameResolver(configuration, method);
}

Call selectCursor based on Cursor return value

After parsing the Mapper method to get the return value, the specific query method to be called will be determined based on the type of the return value.

public Object execute(SqlSession sqlSession, Object[] args) {
    Object result;
    switch (()) {
    // ------------------------------------------------------------------------------------------------------------------------------        case SELECT:
            if (() &amp;&amp; ()) {
                executeWithResultHandler(sqlSession, args);
                result = null;
            } else if (()) {
                result = executeForMany(sqlSession, args);
            } else if (()) {
                result = executeForMap(sqlSession, args);
            } else if (()) {
                // Cursor returns type                result = executeForCursor(sqlSession, args);
            } else {
                Object param = (args);
                result = ((), param);
                if (() &amp;&amp; (result == null || !().equals(()))) {
                    result = (result);
                }
            }
            break;
    // ------------------------------------------------------------------------------------------------------------------------------    return result;
}

Build statement

Use the Sql obtained after parsing the Mapper method above to create aPreparedStatementAnd fill in the corresponding parameter values.

private Statement prepareStatement(StatementHandler handler, Log statementLog) throws SQLException {
    Statement stmt;
    Connection connection = getConnection(statementLog);
    stmt = (connection, ());
    (stmt);
    return stmt;
}

Encapsulation Cursor

At the end of the call, theResultSetas well asMybatis internalofResultSetHandlerPackaged intoCursorObjects are for users to use.

public <E> Cursor<E> handleCursorResultSets(Statement stmt) throws SQLException {
    ().activity("handling cursor results").object(());
    
    ResultSetWrapper rsw = getFirstResultSet(stmt);
    
    List<ResultMap> resultMaps = ();
    
    int resultMapCount = ();
    validateResultMapsCount(rsw, resultMapCount);
    if (resultMapCount != 1) {
        throw new ExecutorException("Cursor results cannot be mapped to multiple resultMaps");
    }
    
    ResultMap resultMap = (0);
    return new DefaultCursor<>(this, resultMap, rsw, rowBounds);
}

Why can I avoid memory overflow

Before discussing this issue, we can take a look at the query of Cursor return value and the actual calling logic of batch queries in Mybatis.

Cursor Query

  @Override
  protected <E> Cursor<E> doQueryCursor(MappedStatement ms, Object parameter, RowBounds rowBounds, BoundSql boundSql)
      throws SQLException {
    Configuration configuration = ();
    StatementHandler handler = (wrapper, ms, parameter, rowBounds, null, boundSql);
    Statement stmt = prepareStatement(handler, ());
    Cursor<E> cursor = (stmt);
    ();
    return cursor;
  }

Batch query

  @Override
  public <E> List<E> doQuery(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler,
      BoundSql boundSql) throws SQLException {
    Statement stmt = null;
    try {
      Configuration configuration = ();
      StatementHandler handler = (wrapper, ms, parameter, rowBounds, resultHandler,
          boundSql);
      stmt = prepareStatement(handler, ());
      return (stmt, resultHandler);
    } finally {
      closeStatement(stmt);
    }
  }

You can compare the two actual execution methods. The obvious difference is that in batch search.Opened explicitly closedStatement, and in the Cursor query, the connection to the database is not closed. In the final analysis, it is because Cursor is used inOperation nativeStatement, so it cannot be closed after query.
In addition, in batch query(stmt, resultHandler)In the method,Get this queryofAll data is returned, which will lead to large batches of dataCrash memoryCaused to OOM.
However, inCursor QueryIn, it won'tReturn after obtaining all data, but to obtain data based on user operations, so naturally the memory will not be crammed.

Summarize

type	How to get data	Return value	Whether to close Statement and ResultSet
Cursor	The user obtains iterator by itself	Cursor cursor type	Don't close, you need to hold it all the time
Normal search	Mybatis internally controls the JDBC pointer, obtains all query data and returns it.	Specific entity type	Close after obtaining the data

The above is a detailed explanation of the method of Mybatis Cursor to avoid OOM exceptions. For more information about Mybatis Cursor to avoid OOM exceptions, please pay attention to my other related articles!