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();
}