Explain how to read a ResultSet with an example

9.B] Explain how to read a ResultSet with an example.

Answer:-

In Java, the ResultSet interface represents the result set of a query executed against a database. It provides methods to retrieve data from the result set.

Steps to Read a ResultSet

1. Execute a Query: Use a Statement or PreparedStatement object to execute a SQL query that returns a ResultSet.
2. Iterate Through the ResultSet: Use the next() method to iterate through each row of the ResultSet.
3. Retrieve Data: Use various getter methods provided by the ResultSet to fetch data from the current row.
4. Close Resources: Always close the ResultSet, Statement, and Connection to release database resources.

Example Code

Here’s a complete example demonstrating how to read data from a ResultSet:

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

public class ResultSetExample {
    public static void main(String[] args) {
        // Database URL, username, and password
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String username = "root";
        String password = "password";

        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            // Load the JDBC driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Establish the connection
            conn = DriverManager.getConnection(url, username, password);

            // Create a statement object
            stmt = conn.createStatement();

            // Execute a query
            String sql = "SELECT id, name, age FROM employees";
            rs = stmt.executeQuery(sql);

            // Process the result set
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                int age = rs.getInt("age");

                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // Close resources
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

Explanation

1. Load the JDBC Driver: Class.forName("com.mysql.cj.jdbc.Driver"); ensures the JDBC driver is loaded.
2. Establish the Connection: DriverManager.getConnection(url, username, password); creates a connection to the database.
3. Create a Statement: conn.createStatement(); prepares a statement object for executing SQL queries.
4. Execute the Query: stmt.executeQuery(sql); executes the SQL query and returns a ResultSet.
5. Iterate Through the ResultSet:

• Use while (rs.next()) to loop through each row.
• rs.getInt("id"), rs.getString("name"), and rs.getInt("age") fetch the values of the specified columns.

1. Close Resources:

• rs.close(), stmt.close(), and conn.close() release database resources.

This approach ensures you properly read and handle the data from a ResultSet while managing resources effectively.