Explain how to read a ResultSet with an example

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

Answer:-

9.B] Explain How to Read a ResultSet with an Example

ResultSet Interface Overview

The ResultSet interface in Java provides methods for navigating and retrieving data from a result set obtained from executing a SQL query. Here’s a brief overview of its cursor-related methods:

• Cursor Navigation Methods:
• first(): Moves the cursor to the first row.
• last(): Moves the cursor to the last row.
• previous(): Moves the cursor to the previous row. Returns false if there is no previous row.
• next(): Moves the cursor to the next row. Returns false if there are no more rows.
• absolute(int row): Moves the cursor to the specified row number.
• relative(int row): Moves the cursor a given number of rows from its current position.
• getRow(): Returns the row number where the cursor is currently positioned.
• beforeFirst(): Moves the cursor just before the first row.
• afterLast(): Moves the cursor just after the last row.

ResultSet Attributes

Type:

• ResultSet.TYPE_FORWARD_ONLY: The result set can only be navigated forward (default type).
• ResultSet.TYPE_SCROLL_INSENSITIVE: The result set can be scrolled both forward and backward; changes in the database while the result set is open are not reflected.
• ResultSet.TYPE_SCROLL_SENSITIVE: The result set can be scrolled both forward and backward; changes in the database while the result set is open are reflected.

Concurrency:

• ResultSet.CONCUR_READ_ONLY: The result set can only be read.
• ResultSet.CONCUR_UPDATABLE: The result set can be both read and updated.

Holdability:

• ResultSet.CLOSE_CURSORS_OVER_COMMIT: Result set instances are closed when connection.commit() is called.
• ResultSet.HOLD_CURSORS_OVER_COMMIT: The result set remains open when connection.commit() is called.

Example of Reading a ResultSet

Assume we have a table named employees with the following structure:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    department VARCHAR(50)
);

Here’s how to read data from the employees table using ResultSet:

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

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

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            // Establishing a connection to the database
            connection = DriverManager.getConnection(url, username, password);

            // Creating a Statement object with ResultSet type, concurrency, and holdability
            statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY,
                ResultSet.HOLD_CURSORS_OVER_COMMIT
            );

            // Executing a query to retrieve data from the employees table
            String sql = "SELECT id, name, age, department FROM employees";
            resultSet = statement.executeQuery(sql);

            // Iterating through the ResultSet
            while (resultSet.next()) {
                // Retrieving data from the current row
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String department = resultSet.getString("department");

                // Printing the data
                System.out.println("ID: " + id);
                System.out.println("Name: " + name);
                System.out.println("Age: " + age);
                System.out.println("Department: " + department);
                System.out.println("----------------------------");
            }

            // Moving cursor to the first row and printing data
            if (resultSet.first()) {
                System.out.println("First row - ID: " + resultSet.getInt("id"));
            }

            // Moving cursor to the last row and printing data
            if (resultSet.last()) {
                System.out.println("Last row - ID: " + resultSet.getInt("id"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // Closing resources
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Explanation of the Example

1. Establish Connection: DriverManager.getConnection() is used to connect to the database.
2. Create Statement: connection.createStatement() creates a Statement object with scrollable and updatable attributes.
3. Execute Query: statement.executeQuery(sql) executes the SQL query and returns a ResultSet.
4. Iterate Through ResultSet: while (resultSet.next()) iterates through each row, retrieving and printing data.
5. Navigate Cursor: Demonstrates moving the cursor to the first and last rows.
6. Close Resources: Properly closes ResultSet, Statement, and Connection to release resources.

This example illustrates how to efficiently read and navigate through a ResultSet in Java, providing a foundation for handling query results in database applications.