PostgreSQL JDBC驱动程序何时在执行查询后获取行？ [英] When does the PostgreSQL JDBC driver fetch rows after executing a query?

问题描述

PostgreSQL JDBC驱动程序版本何时 9.2-1002 在执行查询后从服务器获取行？是否在查询执行后立即获取行（在客户端应用程序调用 PreparedStatement.executeQuery（） ）或客户端应用程序首次调用 ResultSet.next（） 从结果集中检索一行？这取决于语句提取大小的值吗？

When does the PostgreSQL JDBC driver version 9.2-1002 fetch rows from the server after executing a query? Does it fetch the rows immediately after query execution (after the client application invokes PreparedStatement.executeQuery()) or after the client application first invokes ResultSet.next() to retrieve a row from the result set? Does this depend on the value of the statement fetch size?

推荐答案

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

As the following program demonstrates, PreparedStatement.executeQuery() always retrieves rows in the result set from the server. The program also demonstrates how statement fetch size impacts row retrieval. In the case where the statement has the default fetch size of zero, executeQuery() retrieves all rows from the server and ResultSet.next() retrieves and returns the next row from memory, not from the server. (The program may even close the connection after executing the query and next() can still iterate over all rows.) In the case where fetch size is non-zero, executeQuery() retrieves the first batch of rows, the number of which equals the fetch size, and ResultSet.next() again returns the next row from memory until it consumes all rows in the current batch, at which point it retrieves the next batch of rows from the server. This pattern repeats until ResultSet.next() retrieves an empty batch from the server (one that contains zero rows).

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;

Java

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

这篇关于PostgreSQL JDBC驱动程序何时在执行查询后获取行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！