JDBC流式读取MySQL表

一张MySQL大表，我们希望遍历表中的数据，一般情况下都是一次性读进内存中，这样就会给服务器增大很大的压力，那我们可以不可以流式读取MySQL表呢，就像ElasticSearch的scrollSearch一样的效果。
答案是可以的。

经过看Statement的源码找到了如下片段：

/*** We only stream result sets when they are forward-only, read-only, and the* fetch size has been set to Integer.MIN_VALUE* * @return true if this result set should be streamed row at-a-time, rather*         than read all at once.*/protected boolean createStreamingResultSet() {return ((this.resultSetType == java.sql.ResultSet.TYPE_FORWARD_ONLY) && (this.resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY)&& (this.fetchSize == Integer.MIN_VALUE));}

也就是说流式读mysql表可以通过设置三个参数实现：

• resultSetType = forward-only(默认)
• resultSetConcurrency = read-only（默认）
• fetchSize = Integer.MIN_VALUE

完整测试代码如下。

try {Class.forName("com.mysql.cj.jdbc.Driver");Connection connection = DriverManager.getConnection("jdbc:mysql://test01:3306/test_db?characterEncoding=utf8&useSSL=true", "xxxx","xxxxxx");PreparedStatement pstm = connection.prepareStatement("select * from test_table",ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);pstm.setFetchSize(Integer.MIN_VALUE);ResultSet resultSet = pstm.executeQuery();int columnCount;JSONObject jsonObject;while (resultSet.next()) {ResultSetMetaData metaData = resultSet.getMetaData();jsonObject = new JSONObject();columnCount = metaData.getColumnCount();for (int m = 1; m <= columnCount; m++) {jsonObject.put(metaData.getColumnName(m), resultSet.getString(m));}System.out.println(jsonObject);}
} catch (Exception e) {e.printStackTrace();
}