Guide language:

Sometimes we need to query a large amount of data, but the JVM memory is limited, too much data will cause memory overflow. At this time, you can use streaming query to return data one by one. After processing one data, you can take down one data. In this way, the data in memory is actually very small and will not cause memory overflow. This article will talk about JDBC streaming query and Mybatis streaming query.

JDBC streaming query:

JDBC streaming queries require three parameters to be set when generating a PreparedStatement. As follows:

PreparedStatement stmt = jdbcTemplate.getDataSource().getConnection().prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
Copy the code

The prepareStatement method of java.sql.Connection is mainly used.

PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException;
Copy the code

ResultSetType and resultSetConcurrency we want to set them to resultSet.type_forward_only and resultSet.concur_read_only respectively. FetchSize is set to integer.min_value, which is confusing at first. It turns out that the code actually has special handling for this value. This is a com. Mysql. Cj. JDBC. StatementImpl setFetchSize method.

@Override public void setFetchSize(int rows) throws SQLException { synchronized (checkClosed().getConnectionMutex()) { if (((rows < 0) && (rows ! = Integer.MIN_VALUE)) || ((this.maxRows > 0) && (rows > this.getMaxRows()))) { throw SQLError.createSQLException(Messages.getString("Statement.7"), MysqlErrorNumbers. SQL_STATE_ILLcom. Mysql. Cj. JDBC. The method of StatementImpl EGAL_ARGUMENT, getExceptionInterceptor ()); } this.query.setResultFetchSize(rows); }}Copy the code

There are three resultSetTypes

/** * The constant indicating the type for a <code>ResultSet</code> object * whose cursor may move only forward. * @since 1.2 */ int TYPE_FORWARD_ONLY = 1003; /** * The constant indicating the type for a <code>ResultSet</code> object * that is scrollable but generally not ResultSet</code>. * @since 1.2 */ int TYPE_SCROLL_INSENSITIVE = 1004; /** * The constant indicating the type for a <code>ResultSet</code> object * that is scrollable and generally sensitive To change to the data * that underlies the <code>ResultSet</code>. * @since 1.2 */ int TYPE_SCROLL_SENSITIVE = 1005;Copy the code

stmt = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);
Copy the code

ResultSetConcurrency has two types. A streaming query should be set to read-only, so the data is not updated.

/**
     * The constant indicating the concurrency mode for a
     * <code>ResultSet</code> object that may NOT be updated.
     * @since 1.2
     */
    int CONCUR_READ_ONLY = 1007;

    /**
     * The constant indicating the concurrency mode for a
     * <code>ResultSet</code> object that may be updated.
     * @since 1.2
     */
    int CONCUR_UPDATABLE = 1008;
Copy the code

Mybatis streaming query

Code in Mapper:

@Select("select * from xxx order by xx desc")
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
@ResultType(XxxObject.class)
void queryStreamResult(ResultHandler<XxxObject> handler);
Copy the code

Add annotations @options and @resultType to query methods. Setting parameters needless to say is the same as the JDBC base level above, and the parameter values are the same. It just sets @resultType to tell the program what type of object it should return. And this ResultHandler is really the Consumer’s functional interface that handles every piece of data that comes back.

The code in the concrete method:

@Override
   public Boolean dealDataList() {
       mapper.queryStreamResult(resultContext -> {
           dealSingleData(resultContext.getResultObject().getUid());
       });
       return true;
   }
Copy the code

How do you use each of the data that’s returned? Just use ResultObject in your resultContext and you can take the XxxObject that the Mapper set up above and manipulate it.