How to Get Row Count Using ResultSet in Java

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.

Table of Contents

1. Introduction
2. Methods to Get Row Count
   • Using ResultSet Navigation
   • Using SQL COUNT Function
3. Example: Using ResultSet Navigation
4. Example: Using SQL COUNT Function
5. 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

1. Establishing a Connection: We connect to the MySQL database using JDBC.
2. Executing a Query: We execute a query to get the ResultSet.
3. 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

1. Establishing a Connection: We connect to the MySQL database using JDBC.
2. Executing a COUNT Query: We execute a SELECT COUNT(*) query to get the number of rows.
3. 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.