How to Get Size of SQL ResultSet in Java

Sheeraz Gul Feb 02, 2024
How to Get Size of SQL ResultSet in Java

Finding the size of the SQL ResultSet in Java can be tricky because SQL does not provide any method like length() or size(); it only provides the methods to scroll the database, which is also based on the database type.

This tutorial demonstrates a universal method to find the size of SQL ResultSet using any database.

Find the Size of SQL ResultSet in Java

First of all, decide the database table for which you want to find the size of the ResultSet and decide your query. We created a sample database, and we will get the size of the ResultSet by selecting all the items in the query.

The database we created is below.

Database

Now let’s try to get the size of the ResultSet using Java. We use the SqlLite database, but this program will work for other databases like Oracle and MySQL and make the connection properly.

See Java example:

package Delfstack;

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

class Get_Result_Set {
  public static void main(String[] args) {
    Connection db_con = null;
    PreparedStatement db_Statement = null;
    ResultSet result_set = null;
    try {
      String db_path = "jdbc:sqlite:path-to-db/sample.db";
      db_con = DriverManager.getConnection(db_path);
      System.out.println("The Data Based Connection is established.");

      db_Statement = db_con.prepareStatement(
          "select * from Products", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

      result_set = db_Statement.executeQuery();

      int Row_Count = 0;
      System.out.println("Display all the records in ResultSet object");
      System.out.println("Product_Id\tProduct_Name");
      while (result_set.next()) {
        System.out.println(
            result_set.getString("Product_Id") + "\t\t" + result_set.getString("Product_Name"));
        // Row count will get the length of result set
        Row_Count++;
      }
      System.out.println("Total number of rows in ResultSet object = " + Row_Count);

    } catch (SQLException e) {
      throw new Error("Problem", e);
    } finally {
      try {
        if (db_con != null) {
          db_con.close();
        }
      } catch (SQLException ex) {
        System.out.println(ex.getMessage());
      }
    }
  }
}

The code above counts the iterations of the rows, which will get us the size of the ResultSet. See output for the program:

The Data Based Connection is established.
Display all the records in ResultSet object
Product_Id	Product_Name
1           Delfstack1
2           Delftstack2
3           Delftstack3
4           Delftstack4
5           Delftstack5
6           Delftstack6
7           Delftstack7
8           Delftstack8
9           Delftstack9
10          Delftstack10
The Size of the ResultSet object according to rows = 10

The size of the ResultSet for query select * from Products is 10.

Author: Sheeraz Gul
Sheeraz Gul avatar Sheeraz Gul avatar

Sheeraz is a Doctorate fellow in Computer Science at Northwestern Polytechnical University, Xian, China. He has 7 years of Software Development experience in AI, Web, Database, and Desktop technologies. He writes tutorials in Java, PHP, Python, GoLang, R, etc., to help beginners learn the field of Computer Science.

LinkedIn Facebook

Related Article - Java SQL