MySQL jdbc/mybatis Stream Way Read Select Super Large result set

scenario : Traverse and process all the data in a large table, the data in this table may be thousands or hundreds of billions of dollars, many people may say with paging limit ... However, the requirement itself is more convenient for one-time traversal, and ORACLE/DB2 has a convenient cursor mechanism.

For DB, the stream is actually what we call the cursor, and there are 2 ways to stream MySQL, the Client Side cursor and the server Side cursor. JDBC Default mode client Side Cursor, without any settings the JDBC driver will read all the results of a select to client Side and then process it. In this case, when the result set returned by select is very large, it will burst the client's memory, and JDBC is the normal oom; Of course, using an ORM like MyBatis has the same problem, because these things are architected on top of JDBC.

Solutions:
1. Using the client Side Cursor
The Setfetchsize method of the preparedstatement/statement is set toInteger.min_valueor use the methodstatement.enablestreamingresults (), in fact, this method and set Integer.min_value, the source code is as follows:

public void Enablestreamingresults () throws SQLException {
Synchronized (checkclosed (). Getconnectionmutex ()) {
This.originalresultsettype = This.resultsettype;
This.originalfetchsize = this.fetchsize;

Setfetchsize (Integer.min_value);
Setresultsettype (resultset.type_forward_only);
}
}

In the online search for this client Side cursor of the approximate implementation, in fact, MySQL itself does not have fetchsize method, it is through the use of CS blocking mode of network flow control to implement the service side will not send a large amount of data to the client burst client memory, I think this way is very low!. is a very obvious "patch" strategy; This creates the inevitable problem that if not all of the results of ResultSet are read and then other SQL is executed, then the cache of the connection is affected, soThis approach requires either reading all the data in the resultset or calling the Resultset.close () method yourself, which means to use try {} finally{rs.close ();} Or the try-with-resources syntax under JDK7, for example:
Try (ResultSet rs = pstmt.executequery ()){
Role role = new role ();
int i = 0;
while (Rs.next ()) {
try {
Role.setroleid (rs.getstring ("Roleid"));
Role.setstate (Rs.getint ("state"));
Role.setmiscdata (rs.getstring ("Miscdata"));

Selecthandler.action (role);
} catch (Exception ex) {
Logger.error ("Selectallroles error!", ex);
}
}
}

Commonly used ORM MyBatis, the result of the default select is a list<xxxobject&gt, so the problem is more obvious, to put a select all the results into a set of processing, then the result set a large oom is inevitable; After querying MyBatis data found that there is resulthandler mechanism, this is handler:
Sqlsession.select ("Chenlong.mybatislearn.db.mapper.RoleMapper.findAllRoles", Handler);
But like the JDBC approach, mybatis even used Resulthandler to read all the results to the client Side, the memory exploded, finally found that XML mapper can configure the fetchsize of Select, Configure it as Integer.min_value in the previous JDBC mode--2147483648 is normal, as follows:
<select id= "Findallroles"fetchsize= " -2147483648"Resulttype= "Chenlong.mybatislearn.db.struct.Role" >
SELECT * from role
</select>
But there is another problem is that this way must own resultset.close (), through the grilled MyBatis code found that it has done for us, as follows

In this way, you can safely use the client Side cursor under MyBatis.

2. Using the server Side Cursor
A description of this parameter is available in the MySQL JDBC driver Documentation:

Usecursorfetch

If connected to MySQL > 5.0.2, and Setfetchsize () > 0 on a statement, should this statement use cursor-based Fetchin G to retrieve rows?

Default:false

Since version:5.0.0

The position in MyBatis is:
<property name= "url" value= "jdbc:mysql://localhost:3008/mybatislearn?autoreconnect=true&amp; Usecursorfetch=true "/>

This server Side cursor executes SQL after a long wait to start returning results, and the client Side cursor is almost instantaneous to start returning results; The result of the online query is that the server Side cursor uses the MySQL server side of the resource (memory/cpu ...) Dealing with the cursor, this may be the cause, but it's not a big difference when it starts to return the results visually.

Both have advantages and disadvantages, especially the client Side cursor must remember Resultset.close () otherwise the entire connection will no longer be available, this is the case of a large pit, especially with connection pooling.

Again, MySQL compared to other large RDBMS weaknesses, this query cursor traversal should be standard! and MySQL with so low realization, also need the user to master so many black magic ... f***

　　The reference code is as follows :

Http://files.cnblogs.com/files/logicbaby/MyBatisLearn.zip

MySQL jdbc/mybatis Stream Way Read Select Super Large result set