What are the features of using ResultSet in JDBC and its configuration?

ResultSet represents a table of data obtained as a result of executing an SQL query.

Main features:

• Iteration: Allows moving through the result rows using methods next(), previous(), first(), last(), absolute().
• Data retrieval: Provides a set of methods get<Type>() (e.g., getString(), getInt(), getDate()) to extract data from the current row by column name or index.
• Cursor state: Initially, the cursor is positioned before the first row.
• Closing: It is important to explicitly close the ResultSet using close() to free resources. It is best to use a try-with-resources block.

The configuration of ResultSet is determined when creating a Statement or PreparedStatement and affects its behavior:

• Type: Defines the ability to move through the result.
  • ResultSet.TYPE_FORWARD_ONLY: The cursor can only move forward. The most efficient type.
  • ResultSet.TYPE_SCROLL_INSENSITIVE: The cursor can move freely, but changes in the underlying data after creating the ResultSet are not visible.
  • ResultSet.TYPE_SCROLL_SENSITIVE: The cursor can move freely, and changes in the underlying data made after creating the ResultSet may be visible (depends on the driver).
• Concurrency: Defines whether data in the ResultSet can be modified and saved to the database.
  • ResultSet.CONCUR_READ_ONLY: Read-only data.
  • ResultSet.CONCUR_UPDATABLE: Allows modifying data in the ResultSet and applying these changes to the database. Requires using methods update<Type>(), insertRow(), deleteRow(), and updateRow().
• Holdability - JDBC 3.0+: Determines whether the ResultSet remains open after a transaction commit.
  • ResultSet.HOLD_CURSORS_OVER_COMMIT: The ResultSet remains open.
  • ResultSet.CLOSE_CURSORS_AT_COMMIT: The ResultSet is closed.

Example of creating a Statement with specified type and concurrency:

// java
Statement stmt = connection.createStatement(
    ResultSet.TYPE_SCROLL_INSENSITIVE, // Type
    ResultSet.CONCUR_UPDATABLE // Concurrency
);
ResultSet rs = stmt.executeQuery("SELECT id, name FROM users");

Example of using try-with-resources for automatic closing:

// java
String sql = "SELECT * FROM products WHERE price > ?";
try (PreparedStatement pstmt = connection.prepareStatement(sql);
     ResultSet rs = pstmt.executeQuery()) {

    pstmt.setDouble(1, 100.00);

    while (rs.next()) {
        int id = rs.getInt("id");
        String name = rs.getString("name");
        double price = rs.getDouble("price");
        System.out.println("ID: " + id + ", Name: " + name + ", Price: " + price);
    }
} catch (SQLException e) {
    e.printStackTrace();
}