Menu

The JDBC ResultSet interface

The JDBC ResultSet interface

The ResultSet interface in JDBC is used to represent the result set of a database query. It provides methods to retrieve, navigate, and manipulate the data returned by a SELECT query. A ResultSet object maintains a cursor pointing to its current row of data. Initially, the cursor is positioned before the first row. The ResultSet interface provides methods to.

ResultSet interface

1. Navigate through the data:

  1.   next(): Moves the cursor forward one row.
  2.   previous(): Moves the cursor backward one row.
  3.   first(): Moves the cursor to the first row.
  4.   last(): Moves the cursor to the last row.
  5.   absolute(int row): Moves the cursor to the specified row.
  6.   relative(int rows): Moves the cursor a relative number of rows from the current row.

2. Retrieve data from the current row:

  •  getString(String columnLabel): Retrieves the value of the specified column as a String.
  •  getInt(String columnLabel): Retrieves the value of the specified column as an int.
  •  getDouble(String columnLabel): Retrieves the value of the specified column as a double.
  •  Various other getXXX methods to retrieve different data types.

3. Update data in the current row (if the ResultSet is updatable):

  • updateString(String columnLabel, String value): Updates the value of the specified column with a String.
  • updateInt(String columnLabel, int value): Updates the value of the specified column with an int.
  • updateDouble(String columnLabel, double value): Updates the value of the specified column with a double.
  • Various other updateXXX methods to update different data types.

4. Insert and delete rows (if the ResultSet is updatable):

  •  insertRow(): Inserts the contents of the insert row into the result set.
  •  deleteRow(): Deletes the current row from the result set.

Example

Let’s illustrate the usage of the ResultSet interface with a Java example where we retrieve and navigate data from a database.

Database Table Creation
Database Table Creation
Before running the Java code, create a table in your database.

sql
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    salary DOUBLE
);


Java Program Using ResultSet

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

public class ResultSetExample {
    public static void main(String[] args) {
        // JDBC URL, username, and password of MySQL server
        String jdbcURL = "jdbc:mysql://localhost:3306/your_database";
        String username = "root";
        String password = "password";

        // JDBC variables for opening and managing connection
        Connection connection = null;

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

            // Establish a connection
            connection = DriverManager.getConnection(jdbcURL, username, password);
            System.out.println("Connected to the database!");

            // Create a Statement object
            Statement statement = connection.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            // Execute a SELECT query and obtain a ResultSet
            ResultSet resultSet = statement.executeQuery("SELECT * FROM employees");

            // Navigate and retrieve data from the ResultSet
            System.out.println("Navigating and retrieving data:");

            // Move to the first row and print data
            if (resultSet.first()) {
                printRowData(resultSet);
            }

            // Move to the next row and print data
            while (resultSet.next()) {
                printRowData(resultSet);
            }

            // Move to the previous row and print data
            if (resultSet.previous()) {
                printRowData(resultSet);
            }

            // Move to the last row and print data
            if (resultSet.last()) {
                printRowData(resultSet);
            }

            // Close the ResultSet and Statement
            resultSet.close();
            statement.close();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                    System.out.println("Disconnected from the database.");
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    private static void printRowData(ResultSet resultSet) throws SQLException {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        double salary = resultSet.getDouble("salary");
        System.out.println("ID: " + id + ", Name: " + name + ", Salary: " + salary);
    }
}

Explanation of the ResultSet interface

  • Connection Setup:
    • The JDBC URL, username, and password are specified to connect to the MySQL database.
    • The Connection object is created using DriverManager.getConnection().

  • Creating Statement and Executing Query:
    • Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); creates a Statement object with a TYPE_SCROLL_INSENSITIVE and CONCUR_READ_ONLY ResultSet.
    • ResultSet resultSet = statement.executeQuery(“SELECT * FROM employees”); executes the SELECT query and returns a ResultSet.

  • Navigating and Retrieving Data:
    • The ResultSet cursor is navigated using first(), next(), previous(), and last() methods.
    • The data from the current row is retrieved using getInt(), getString(), and getDouble() methods.

  • Closing Resources:
    • The ResultSet and Statement are closed using resultSet.close() and statement.close().
    • The Connection is closed in the finally block.

Homepage

Readmore