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.

JDBC Updatable ResultSet:

In this tutorials, I am going to tell you how to make a ResultSet as Updatable. You can find How to make a ResultSet as Updatable ResultSet and Scrollable here.

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

  • TYPE_SCROLL_SENSITIVE
  • CONCUR_UPDATABLE

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

[box type=”shadow” align=”alignleft” class=”” width=”100%”]

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

[/box]

On the above ResultSet object, we can perform the following three operations:

  • inserting a record,
  • deleting a record and
  • updating a record.

Steps to insert a record through ResultSet object:

We can insert the record in database through ResultSet object using absolute() method but before going to insert a record, first you need to decide at which position your are inserting, since the absolute() method takes position as a parameter to where its to be insert.

Step-1 :

rs.absolute (3);

Step-2 :

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

rs.moveToInsertRow ();

Step-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.

rs.updateXXX(int colno, XXX val);

Example :

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

Step-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:

public void insertRow();

By calling the above insertRow() method, the record can be inserted into the database permanently. Here the insertRow() method throws SQLException, We need to handle the exception or you can throw.

Steps to Delete a record through ResultSet :

First you need to decide which record you need to delete, because you need to pass the position of the record to absolute() to point the resultset to a particular record.

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

To delete the record permanently from the database we must call the deleteRow() method which is present in ResultSet interface

rs.deleteRow ();

Steps for UPDATING a record through ResultSet:

First you need to decide which record you need to update, because you need to pass the position of the record to absolute() to point the resultset to a particular record.

rs.absolute (2);

And then decide which column to update.

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

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.

rs.updateRow ();

Example For JDBC Updatable ResultSet :

[sourcecode language=”java”]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Jdbc_Updatable_ResultSet {

    public static void main(String[] args) throws Exception {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/onlinetutorialspoint", "root",
                "123456");
        Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = st.executeQuery("select * from person");
        rs.next();
        rs.updateInt(1, 1001);
        rs.updateRow();
        System.out.println("1 ROW UPDATED…");
        rs.moveToInsertRow();
        rs.updateInt(1, 1002);
        rs.updateString(2, "Banglore");
        rs.updateString(3, "Vinayak");
        rs.insertRow();
        System.out.println("1 ROW INSERTED…");
        System.out.println("After Updation…");
        con.close();
    }

}
[/sourcecode]

Before going to execute the above example the data inside the table like below :
[box type=”shadow” align=”alignleft” class=”” width=”100%”]

Id 1050 Name : Venu Gopal City : Vizag
Id 1060 Name : Kamal City : Pune
Id 1070 Name : Ram Gopal City : Mumbai
Id 4104 Name : Chandra Shekhar Goka City : Vijayawada

[/box]

Output Of the Above Example :

[box type=”success” align=”alignleft” class=”” width=”100%”]

1 ROW UPDATED…
1 ROW INSERTED…
1 ROW DELETED…
After Updation…

[/box]

After the successful execution, we have data in database like below :

[box type=”shadow” align=”alignleft” class=”” width=”100%”]

Id 1001 Name : Venu Gopal City : Vizag
Id 1002 Name : Vinayak City : Banglore
Id 1060 Name : Kamal City : Pune
Id 4104 Name : Chandra Shekhar Goka City : Vijayawada

[/box]

This is the way we can make the JDBC ResultSet as updatable.

Happy Learning 🙂