How to Get Row Count Using ResultSet in Java

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

In this tutorial, we'll discuss how to obtain the row count from a ResultSet in Java. The ResultSet object represents the result set of a database query. While ResultSet itself does not provide a direct method to get the row count, we can achieve this in a few ways.

Introduction
Methods to Get Row Count
- Using ResultSet Navigation
- Using SQL COUNT Function
Example: Using ResultSet Navigation
Example: Using SQL COUNT Function
Conclusion

1. Introduction

When working with databases, it's often necessary to determine the number of rows returned by a query. While JDBC's ResultSet does not have a direct method to count rows, you can achieve this by navigating through the ResultSet or by using an SQL COUNT query.

2. Methods to Get Row Count

Using `ResultSet` Navigation

This method involves moving the cursor to the end of the ResultSet to determine the number of rows.

Using SQL `COUNT` Function

This method involves executing a separate SQL query that uses the COUNT function to get the number of rows.

3. Example: Using `ResultSet` Navigation

Here, we move the cursor to the last row of the ResultSet, get the row number, and then move it back to the original position.

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

public class ResultSetRowCountExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";
    private static final String QUERY = "SELECT * FROM your_table";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(QUERY)) {

            int rowCount = getRowCount(resultSet);
            System.out.println("Row count: " + rowCount);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static int getRowCount(ResultSet resultSet) throws SQLException {
        int rowCount = 0;
        if (resultSet.last()) {
            rowCount = resultSet.getRow();
            resultSet.beforeFirst(); // Move cursor back to the beginning
        }
        return rowCount;
    }
}

Explanation

Establishing a Connection: We connect to the MySQL database using JDBC.
Executing a Query: We execute a query to get the ResultSet.
Getting Row Count: We move the cursor to the last row using resultSet.last(), get the row number using resultSet.getRow(), and then move the cursor back to the beginning using resultSet.beforeFirst().

4. Example: Using SQL `COUNT` Function

This method involves executing a separate SQL query to get the row count.

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

public class CountFunctionExample {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/your_database";
    private static final String DB_USER = "your_username";
    private static final String DB_PASSWORD = "your_password";
    private static final String COUNT_QUERY = "SELECT COUNT(*) FROM your_table";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(COUNT_QUERY)) {

            if (resultSet.next()) {
                int rowCount = resultSet.getInt(1);
                System.out.println("Row count: " + rowCount);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

Establishing a Connection: We connect to the MySQL database using JDBC.
Executing a COUNT Query: We execute a SELECT COUNT(*) query to get the number of rows.
Getting Row Count: We retrieve the count from the ResultSet.

5. Conclusion

Both methods allow you to get the row count from a ResultSet. The ResultSet navigation method is straightforward but may not be as efficient for large result sets. Using the SQL COUNT function is more efficient and should be preferred when dealing with large data sets or when performance is a concern.

By using these techniques, you can effectively manage and retrieve the number of rows in your database queries using JDBC.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Spring Boot + RabbitMQ Course - The Practical Guide

Java Guides

Search This Blog