EDUCBA

Home Software Development Software Development Tutorials Java Technology Tutorial JDBC fetch size

JDBC fetch size

Updated March 29, 2023

JDBC fetch size

Introduction to JDBC fetch size

The JDBC fetch size is a method to perform and manage the memory of the database. The fetch size is used for the resultset to fetch the database record instantly and control the multiple calls from the JVM to the database. The fetch size is the default value for statement and prepared statement to a returns database record of the 10 sizes. The fetch size is a property that uses a statement to fetch more than one database row using a driver. The fetch size is method gives hints to the driver to maintain or manage database rows. The fetch size is JDBC property interact default database, and database rows can maximize or minimize the fetching size.

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Syntax

  • The basic fetch size syntax shows below.
setFetchSize(int row);
  • The JDBC statements fetch size syntax shows below.
statement.setFetchSize(fetch size);
  • The default fetch size sets 10 with the JDBC driver.
  • The default fetch size sets 128 with SQL server statement.
  • The fetch size syntax with one record shows below.
statement.setFetchSize(Integer.MIN_values);
  • The fetch size syntax with one record shows below.
statement.setFetchSize(Integer.MAX_VALUE);
  • The fetch size syntax with multiple records shows below.
statement.setFetchSize(fetchSize);
  • The fetch size syntax with hibernate configuration shows below.
<property name = "hibernate.jdbc.fetch_size" value = "100" />
  • The fetch size syntax with hibernate configuration shows below.
.setHint("eclipselink.jdbc_fetch_size", value = "50");
  • The fetch size syntax with query hint shows below.
@QueryHint (name = "QueryHints.jdbc_fetch_size", value = "50");

How to fetch size in JDBC?

  • Create a database table with the required column and its information.
  • Create a java file with its prefix.

JdbcFetch.java

  • Create a java class and method.
class JdbcFetch{
public static void main(String[] args) { write fetch size code here…} }
  • Use jdbc driver for interconnects with the required database management system.
driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
  • Use a connection with the database and its host using jdbc methods.
connect = "jdbc:mysql://localhost:3306/database_name", "user_name", "root_password";
connection connec = DriverManager.getConnection(connect);
  • Use resultset to display database table information.
statement stat = connec.createStatement();
  • Initialize the fetch size.
stat.setFetchSize(100);
ResultSet resultst = stat.executeQuery( "select * from table_name" );
  • Use a while loop with the result set variable.
while(resultst.next()){ write output code… }
  • Display database information as output.
System.output.println("write output variable here..");
  • Close the JDBC connections.
Connec.close();
  • Combine the working procedure of the fetch size.
class JdbcFetch{
public static void main(String[] args) {
driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
connect = "jdbc:mysql://localhost:3306/database_name", "user_name", "root_password";
connection connec = DriverManager.getConnection(connect);
statement stat = connec.createStatement();
stat.setFetchSize(100);
ResultSet resultst = stat.executeQuery( "select * from table_name" );
while(resultst.next()){
System.output.println("write output variable here..");
}
Connec.close();
} }

Examples of JDBC fetch size

Different examples are mentioned below:

Example #1

The basic fetch size example and the output is shown below.

Code:

import java.sql.*;
class JdbcFetch{
public static void main(String[] args) {
driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
connect = "jdbc:mysql://localhost:3306/tutorial", "root", "root";
connection connec = DriverManager.getConnection(connect);
statement stat = connec.createStatement();
stat.setFetchSize(10);
ResultSet resultst = stat.executeQuery( "select * from table books" );
while(resultst.next()){
System.output.println(resultst.getInt(1)+ " " +resultst.getString(2));
}
Stat.close();
resultst.close();
Connec.close();
} }

Output:

JDBC fetch size output 1

Description

  • The fetch size connects and fetches the required row of the database.
  • If the database value is small, then it fetches entire rows using fetch size.
  • The JDBC sets default value means 10 fetch size.
  • The fetch size works internally for the configuration.
  • The application output displays entire database information without any interruption.

Example #2

The fetch size with database example and the output is shown below.

Code:

import java.sql.*;
class JdbcFetch{
public static void main(String[] args) {
driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
connect = "jdbc:mysql://localhost:3306/tutorial", "root", "root";
connection connec = DriverManager.getConnection(connect);
statement stat = connec.createStatement();
stat.setFetchSize(50);
ResultSet resultst = stat.executeQuery( "select id, name, marks from table books" );
while(resultst.next()){
System.output.println(resultst.getInt(1)+ " " +resultst.getString(2)+ " "" +resultst.getInt(3));
}
Stat.close();
resultst.close();
Connec.close();
} }

Output:

JDBC fetch size output 2

Description

  • The fetch size connects and fetches 50 rows of the database immediately.
  • If the database value is small, then it fetches entire rows using fetch size.
  • The JDBC sets maximum means 50 fetch size.
  • The fetch size works internally for the configuration.
  • The application output displays entire database information without any interruption.

Example #3

The fetch size example and the output is shown below.

Code:

import java.sql.*;
class JdbcFetch{
public static void main(String[] args) {
driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
connect = "jdbc:mysql://localhost:3306/tutorial", "root", "root";
connection connec = DriverManager.getConnection(connect);
statement stat = connec.createStatement();
stat.setFetchSize(5);
ResultSet resultst = stat.executeQuery( "select id, book from table books" );
while(resultst.next()){
System.output.println(resultst.getInt(1)+ " " +resultst.getString(2));
}
Stat.close();
resultst.close();
Connec.close();
} }

Output:

JDBC fetch size output 3

Description

  • The fetch size connects and fetches 5 rows of the database immediately.
  • If the database value is small, then it fetches entire rows using fetch size.
  • The JDBC sets maximum means 5 fetch size.

Example #4

The fetch size example and the output is shown below.

Code:

import java.sql.*;
class JdbcFetch{
public static void main(String[] args) {
driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
connect = "jdbc:mysql://localhost:3306/tutorial", "root", "root";
connection connec = DriverManager.getConnection(connect);
statement stat = connec.createStatement();
stat.setFetchSize(integer.MIN_VALUE);
ResultSet resultst = stat.executeQuery( "select * from table exam" );
while(resultst.next()){
System.output.println(resultst.getInt(1)+ " " +resultst.getString(2) )+ " "" +resultst.getInt(3));
}
Stat.close();
resultst.close();
Connec.close();
} }

Output:

output 4

Description

  • The fetch size connects and fetches the minimum row of the database.
  • If database size does not affect the Integer.MIN_VALUE.
  • It fetches only one row of the table using fetch size.

Example #5

The fetch size example and the output is shown below.

Code:

import java.sql.*;
class JdbcFetch{
public static void main(String[] args) {
driver = "com.mysql.jdbc.Driver";
Class.forName(driver);
connect = "jdbc:mysql://localhost:3306/tutorial", "root", "root";
connection connec = DriverManager.getConnection(connect);
statement stat = connec.createStatement();
stat.setFetchSize(integer.MAX_VALUE);
ResultSet resultst = stat.executeQuery( "select * from table exam" );
while(resultst.next()){
System.output.println(resultst.getInt(1)+ " " +resultst.getString(2) )+ " "" +resultst.getInt(3));
}
Stat.close();
resultst.close();
Connec.close();
} }

Output:

output 5

Description

  • The fetch size connects and fetches the maximum row of the database.
  • If the database value is small, then it fetches entire rows using fetch size.

Conclusion

The fetch size is partitioning and maintaining the database information; The different fetch size does not affect on the output of the database information. The fetch size creates sustainable, easy, and user-friendly applications with large database information. The fetch size minimizes application works and maintenance of the developer and database manager.

 Recommended Articles

This is a guide to JDBC fetch size. Here we discuss How to fetch size in JDBC along with the examples and outputs in detail. You may also have a look at the following articles to learn more –

  1. JDBC Driver
  2. What is JDBC?
  3. JDBC Architecture
  4. JDBC Driver for Oracle
quiz