PostgreSQL JDBC驱动程序何时执行查询后获取行？

Question

执行查询后，PostgreSQL
JDBC驱动程序
版本9.2-1002什么时候从服务器获取行？它是在查询执行后立即（在客户端应用程序调用之后PreparedStatement.executeQuery()）还是在客户端应用程序第一次调用ResultSet.next()以从结果集中检索行之后获取行？这是否取决于语句访存大小的值？

Answer 1

如以下程序所示，PreparedStatement.executeQuery()始终从服务器检索结果集中的行。该程序还演示了语句获取大小如何影响行检索。如果该语句的默认访存大小为零，executeQuery()则从服务器检索所有行，然后ResultSet.next()从内存而不是从服务器检索并返回下一行。（程序甚至可以在执行查询后关闭连接，并且next()仍然可以遍历所有行。）在提取大小不为零的情况下，executeQuery()检索第一批行，其数量等于提取大小，并且ResultSet.next()再次从内存中返回下一行，直到消耗完当前批处理中的所有行，这时它将从服务器中检索下一批行。重复此模式，直到ResultSet.next()从服务器检索一个空批次（一个包含零行的批次）为止。

的SQL

-- Create table "test" and insert 2,000,000 integers from 1 up to 2,000,000.
WITH RECURSIVE t(n) AS
(
  VALUES (1)
  UNION ALL
  SELECT n+1
  FROM t
  WHERE n < 2000000
)
SELECT n as value
INTO test
FROM t;

爪哇

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Properties;

public class Start
{
    public static void main( String[] args ) throws InterruptedException, SQLException
    {
        try
        {
            Class.forName( "org.postgresql.Driver" );
        }
        catch ( ClassNotFoundException e )
        {
            System.out.println( "Where is your JDBC Driver?" );
            e.printStackTrace();
            return;
        }

        System.out.println( "Registered JDBC driver" );
        Connection connection = null;

        try
        {
            final String databaseUrl = "jdbc:postgresql://localhost:5483/postgres";
            final Properties properties = new Properties();
            connection = DriverManager.getConnection( databaseUrl, properties );
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();

            // Default fetch size of 0 does not create a cursor.
            // Method executeQuery will retrieve all rows in result set.
            statement.setFetchSize( 0 );

            // Fetch size of 1 creates a cursor with batch size of 1.
            // Method executeQuery will retrieve only 1 row in the result set.
            //statement.setFetchSize( 1 );

            System.out.println( new Date() + ": Before execute query" );
            ResultSet result =
                statement.executeQuery( "select * from test" );
            System.out.println( new Date() + ": After execute query" );
            System.out.println( new Date() + ": Sleep for 5 s" );
            Thread.sleep( 5000 );
            System.out.println( new Date() + ": Before process result set" );
            while ( result.next() );
            System.out.println( new Date() + ": After process result set" );
            result.close();
            statement.close();
        }
        catch ( SQLException e )
        {
            System.out.println( "Connection failed!" );
            e.printStackTrace();
            return;
        }
        finally
        {
            if ( connection != null )
                connection.close();
        }
    }
}