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();
}
}
}
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…