The Java Developers Almanac 1.4


Order this book from Amazon.

   
Home > List of Packages > java.sql  [73 examples] > Connections  [10 examples]

e244. Setting the Number of Rows to Prefetch When Executing a SQL Query

When a SQL query is executed, the number of rows of data that a driver physically copies from the database to the client is called the fetch size. If you are performance-tuning a particular query, you might be able to improve performance by adjusting the fetch size to better match the use of the query.

The fetch size can be set on a statement, in which case, all result sets created from that statement will use that fetch size. The fetch size can also be set on a result set at any time. In this case, the next time data needs to be fetched from the database, the driver will copy over as many rows as is specified by the current fetch size.

    try {
        // Get the fetch size of a statement
        Statement stmt = connection.createStatement ();
        int fetchSize = stmt.getFetchSize();
    
        // Set the fetch size on the statement
        stmt.setFetchSize(100);
    
        // Create a result set
        ResultSet resultSet = stmt.executeQuery("SELECT * FROM my_table");
    
        // Change the fetch size on the result set
        resultSet.setFetchSize(100);
    } catch (SQLException e) {
    }

 Related Examples
e235. Connecting to an Oracle Database
e236. Connecting to a MySQL Database
e237. Connecting to a SQLServer Database
e238. Listing All Available Parameters for Creating a JDBC Connection
e239. Determining If a Database Supports Transactions
e240. Committing and Rolling Back Updates to a Database
e241. Handling a SQL Exception
e242. Determining If a SQL Warning Occurred
e243. Getting the Driver of a Connection

See also: Batching    Database Meta Data    Deleting Data    Drivers    Importing and Exporting    Inserting and Updating Data    Oracle OBJECTs    Oracle VARRAYs    Procedures and Functions    Retrieving Data    Scrollable Result Sets    Tables    Updatable Result Sets   


© 2002 Addison-Wesley.