sql

Set prefetch size of SQL query example

In this example we shall show you how to set the fetch size of an SQL query. When changing the fetch size of a database, every time data is fetched from the database, the number of rows fetched will be equal to the size specified. To set the fetch size of an SQL query one should perform the following steps:

  • Load the JDBC driver, using the forName(String className) API method of the Class. In this example we use the MySQL JDBC driver.
  • Create a Connection to the database. Invoke the getConnection(String url, String user, String password) API method of the DriverManager to create the connection.
  • Create a Statement, with the createStatement() API method of the Connection.
  • Get the statement fetch size, with the getFetchSize() API method of the Statement. In order to set the fetch size of the statement invoke the setFetchSize(int rows) API method of the Statement. Now all ResultSets created from that statement will use that fetch size.
  • Change the fetch size of the ResultSet, with the setFetchSize(int rows) API method of the ResultSet. It will override the Statement threshold and the rows fetched from the database will be specified by the resultSet fetch size,

as described in the code snippet below.

package com.javacodegeeks.snippets.core;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLPrefetchSize {
 
  public static void main(String[] args) {

    Connection connection = null;
    try {

  // Load the MySQL JDBC driver

  String driverName = "com.mysql.jdbc.Driver";

  Class.forName(driverName);


  // Create a connection to the database

  String serverName = "localhost";

  String schema = "test";

  String url = "jdbc:mysql://" + serverName +  "/" + schema;

  String username = "username";

  String password = "password";

  connection = DriverManager.getConnection(url, username, password);

  

  System.out.println("Successfully Connected to the database!");

  
    } catch (ClassNotFoundException e) {

  System.out.println("Could not find the database driver " + e.getMessage());
    } catch (SQLException e) {

  System.out.println("Could not connect to the database " + e.getMessage());
    }

    try {


  // Get the fetch size of a statement

  Statement statement = connection.createStatement();

  int fetchSize = statement.getFetchSize();

  

  System.out.println("Statement fetch size : " + fetchSize);


  // Set the fetch size on the statement; all result sets created from that statement will use that fetch size

  statement.setFetchSize(100);


  // Create a result set

  ResultSet resultSet = statement.executeQuery("SELECT * FROM test_table");


  // Change the fetch size on the result set; 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 (overrides the statement threshold)

  resultSet.setFetchSize(50);

  
    } catch (SQLException e) {
    }

  }
}

Example Output:

Successfully Connected to the database!
Statement fetch size : 0

 
This was an example of how to set the fetch size of an SQL query in Java.

Byron Kiourtzoglou

Byron is a master software engineer working in the IT and Telecom domains. He is an applications developer in a wide variety of applications/services. He is currently acting as the team leader and technical architect for a proprietary service creation and integration platform for both the IT and Telecom industries in addition to a in-house big data real-time analytics solution. He is always fascinated by SOA, middleware services and mobile development. Byron is co-founder and Executive Editor at Java Code Geeks.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Back to top button