What are the different types of ResultSet, and how are they used? — Java JDBC Interview Question #9

In Java JDBC, there are mainly three types of ResultSet that are provided by the JDBC API. They are:

1. Forward-Only ResultSet - This is the default type of ResultSet. It is used to traverse the records only in forward direction, i.e. from top to bottom. This type of ResultSet is useful when we just need to retrieve and read the records from database only once.

Example usage:

    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT * FROM employee");
    
    while(rs.next()){
        // read and process the column values
    }
    
    rs.close();
    stmt.close();

2. Scrollable ResultSet - This type of ResultSet provides the ability to move the cursor in any direction, i.e., we can traverse back and forth in the ResultSet. This ResultSet type is useful when we need to move the cursor backward and forward many times.

Example usage:

    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
    ResultSet rs = stmt.executeQuery("SELECT * FROM employee");
    
    // Move the cursor to the last row
    rs.last();
    
    // Get the total number of rows in ResultSet
    int totalRows = rs.getRow();
    
    // Move the cursor to the first row again
    rs.first();
    
    while(rs.next()){
        // read and process the column values
    }
    
    rs.close();
    stmt.close();

3. Updatable ResultSet - This type of ResultSet can be used to update the records in the database. A ResultSet object is updatable if it is created with the appropriate flag set in the Statement object used to create it.

Example usage:

    Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
    ResultSet rs = stmt.executeQuery("SELECT * FROM employee");
    
    while(rs.next()){
        // Update the column value
        rs.updateString("first_name","John");
        
        // Save the changes to database
        rs.updateRow();
    }
    
    rs.close();
    stmt.close();

Note that not all databases support all types of ResultSet. For example, some databases may not support updatable ResultSet. It is always recommended to check the database documentation to see which types of ResultSet are supported by the database.