Updatable Resultset

Whenever we create a ResultSet object which never allows us to update the database through ResultSet object and it allows retrieving the data only in forward direction. Such type of ResultSet is known as non-updatable and non-scrollable ResultSet.

In order to make the ResultSet object as updatable and scrollable we must use the following constants which are present in ResultSet interface.

int Type	int Mode
TYPE_SCROLL_SENSITIVE	CONCUR_UPDATABLE

The above two constants must be specified while we are creating Statement object by using the following method:

For example:

Statement  st=con.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

On ResultSet we can perform the following three operations, they are inserting a record, deleting a record and updating a record.

Steps for INSERTING a record through ResultSet object:

1. Decide at which position we are inserting a record by calling absolute method.

For example:

rs.absolute (3);

2. Since we are inserting a record we must use the following method to make the ResultSet object to hold the record.


For example:

rs.moveToInsertRow ();

3. Update all columns of the database or provide the values to all columns of database by using the following generalized method which is present in ResultSet interface.


For example:

rs.updateInt (1, 5);
rs.updateString (2, "abc");
rs.updateInt (3, 80);

4. Upto step-3 the data is inserted in ResultSet object and whose data must be inserted in the database permanently by calling the following method:


It throws an exception called SQLException.

For example:

rs.insertRow ();

Steps for DELETING a record through ResultSet object:

1. Decide which record you want to delete.

For example:

rs.absolute (3); // rs pointing to 3rd record & marked for deletion

2. To delete the record permanently from the database we must call the following method which is present in ResultSet interface.


For example:

rs.deleteRow ();

Steps for UPDATING a record through ResultSet object:

1. Decide which record to update.

For example:

rs.absolute (2);

2. Decide which columns to be updated.

For example:

rs.updateString (2, "pqr");
rs.updateInt (3, 91);

3. Using step-2 we can modify the content of ResultSet object and the content of ResultSet object must be updated to the database permanently by calling the following method which is present in ResultSet interface.


For example:

rs.updateRow ();

For example:

Write a java program which illustrates the concept of updatable ResultSet?

For example:

Answer:

import java.sql.*;

class UpdateResultSet {

    public static void main(String[] args) {
        try {
            Class.forName("Sun.jdbc.odbc.JdbcOdbcDriver");
            System.out.println("DRIVERS LOADED...");
            Connection con = DriverManager.getConnection("jdbc:odbc:oradsn", "scott", "tiger");
            System.out.println("CONNECTION ESTABLISHED...");
            Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = st.executeQuery("select * from emp1");
            rs.next();
            rs.updateInt(2, 8000);
            rs.updateRow();
            System.out.println("1 ROW UPDATED...");
            rs.moveToInsertRow();
            rs.updateInt(1, 104);
            rs.updateInt(2, 2000);
            rs.insertRow();
            System.out.println("1 ROW INSERTED...");
            rs.absolute(2);
            rs.deleteRow();
            System.out.println("1 ROW DELETED...");
            con.close();
        } catch (Exception e) {

            e.printStackTrace();
        }
    }// main
};// UpdateResultSet

Note:The scrollability and updatability of a ResultSet depends on the development of the driver of the driver vendors. OracleDriver and JdbcOdbcDriver will support the concept of scrollability and updatability of a ResultSet but there may be same drivers which are available in the industry which are not supporting the concept of scrollability and updatability.