preface

Database paging is often used in production environments to control the amount of data fetched at a time, and there is often another scenario in data processing:

All data is read from a database table for processing and the results are saved in other databases or files or in NoSql databases.

At this time can also use the way of paging batch processing, but this method is not only logical complex, efficiency is also very low, so there is no similar to file stream read way to read the database? The answer is yes, the JDBC standard not only provides for streaming reading, but also supports data perception and updating during reading, but this article only deals with streaming (cursor) reading of databases. Let’s look at the cursor implementation of native JDBC:

JDBC native implementation

@Test
public void testCursor(a) throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection connection = DriverManager
            .getConnection("JDBC: mysql: / / 127.0.0.1:3306 / db? useUnicode=true&characterEncoding=utf8&useSSL=false"."root"."root");
    PreparedStatement preparedStatement =
            connection.prepareStatement("select * from table",
                    ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
    preparedStatement.setFetchSize(Integer.MIN_VALUE);

    ResultSet resultSet = preparedStatement.executeQuery();

    while (resultSet.next()) {
        System.err.println(resultSet.getString("id"));
    }
    connection.close();
}Copy the code

Pay attention to the MySql

conn.prepareStatement("select * from table",
                      ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY); 
// This is because the MySQL driver implementation uses integer.min_value to determine whether to use streams
preparedStatement.setFetchSize(Integer.MIN_VALUE);Copy the code

Both lines are indispensable.

PostgreSql does

connection.setAutoCommit(false);
// The amount of data read per stream
preparedStatement.setFetchSize(1000);Copy the code

tips:



It can be seen that different database vendors still have different implementations, and this article will only test
PostgreSql
With MySQL, other databases please test yourself.

JdbcTemplate is used in most projects. Does the JdbcTemplate support streaming reading?

JdbcTemplate use

Open the JdbcTemplate and you can see a query method signed as follows:

/**
 * Query using a prepared statement, reading the ResultSet on a per-row basis
 * with a RowCallbackHandler.
 * <p>A PreparedStatementCreator can either be implemented directly or
 * configured through a PreparedStatementCreatorFactory.
 * @param psc a callback that creates a PreparedStatement given a Connection
 * @param rch a callback that will extract results, one row at a time
 * @throws DataAccessException if there is any problem
 * @see PreparedStatementCreatorFactory
 */
void query(PreparedStatementCreator psc, RowCallbackHandler rch) throws DataAccessException;Copy the code

The key point is that the parameters PreparedStatementCreator and RowCallbackHandler, we can control the creation of PreparedStatement and the extraction of ResultSet, which is easy, the specific code implementation is as follows:

MySQL

jdbcTemplate.query(con -> {
            PreparedStatement preparedStatement =
                    con.prepareStatement("select * from table",
                                         ResultSet.TYPE_FORWARD_ONLY, 
                                                   ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setFetchSize(Integer.MIN_VALUE);
            preparedStatement.setFetchDirection(ResultSet.FETCH_FORWARD);
            return preparedStatement;
        }, rs -> {
            while (rs.next()) {
                System.err.println(resultSet.getString("id")); }});Copy the code

PostgreSql

jdbcTemplate.query(con -> {
            con.setAutoCommit(false);
            PreparedStatement preparedStatement =
                    con.prepareStatement("select * from table",
                                         ResultSet.TYPE_FORWARD_ONLY, 
                                                   ResultSet.CONCUR_READ_ONLY);
            preparedStatement.setFetchSize(1000);
            preparedStatement.setFetchDirection(ResultSet.FETCH_FORWARD);
            return preparedStatement;
        }, rs -> {
            while (rs.next()) {
                System.err.println(resultSet.getString("id")); }});Copy the code

This way, we don’t have to write ugly JDBC template code anymore.

Reference:
Docs.oracle.com/javase/tuto…