Scrollable and Updatable ResultSets

By using Scrollable ResultSet, you can traverse both forward and backward directions. By using updatable ResultSet, you are allowed to update the contents of ResultSet, which will reflect in database.

Blow constants specifies ResultSet type.

Type	Constant	Description
Forward-only	TYPE_FORWARD_ONLY	The constant indicating the type for a ResultSet object whose cursor may move only forward.
Scroll-insensitive	TYPE_SCROLL_INSENSITIVE	The constant indicating the type for a ResultSet object that is scrollable but generally not sensitive to changes to the data that underlies the ResultSet.
Scroll sensitive	TYPE_SCROLL_SENSITIVE	The constant indicating the type for a ResultSet object that is scrollable and generally sensitive to changes to the data that underlies the ResultSet.

Below constants specifies ResultSet concurrency.

Type	Constant	Description
Read-only	CONCUR_READ_ONLY	ResultSet is read-only, it doesn't allow to update contents.
Updatable	CONCUR_UPDATABLE	It is opposite to read-only type. It allows contents to update.

Connection class provides method to create a Statement/ PreparedStatement/ CallableStatement object that will generate ResultSet objects with the given type, concurrency and holdability.

/* Step 1: Import sql package */
import java.sql.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws SQLException, ClassNotFoundException{
        
        Connection conn = getConnection();

          /* Create table employee */
        String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        stmt.execute(query);     
        
        /* Insert data to employee table */
        query = "INSERT INTO employee values(1, \"Krishna\")";
        stmt.execute(query);
        query = "INSERT INTO employee values(2, \"Arjun\")";
        stmt.execute(query);        
        query = "INSERT INTO employee values(3, \"Ptr\")";
        stmt.execute(query);

        query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        System.out.println("Traversing forward");
        while(rs.next()){
            int id  = rs.getInt("ID");
            String name = rs.getString("NaME");
            System.out.println(id +" " + name);
        }
        
        System.out.println("Traversing backward");
        while(rs.previous()){
            int id  = rs.getInt("id");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
            
            /* Update the name with string default */
            rs.updateString("name", "default");
            rs.updateRow();
        }
        
        System.out.println("ResultSet after updating the name by string default");
        while(rs.next()){
            int id  = rs.getInt("ID");
            String name = rs.getString("name");
            System.out.println(id +" " + name);
        }
        
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
 rs.close();
        stmt.close();
        conn.close();
    }
}

Output

Loading/Registering driver
Connecting to database
Traversing forward
1 Krishna
2 Arjun
3 Ptr
Traversing backward
3 Ptr
2 Arjun
1 Krishna
ResultSet after updating the name by string default
1 default
2 default
3 default
Dropping table employee

Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

Above statement creates a ResultSet which is scrollable and updatable.

Prevoius                                                 Next                                                 Home