Whenever we create a ResultSet object that never allows us to update the database through the ResultSet object, it allows retrieving the data only forward. Such type of ResultSet is known as non-updatable and non-scrollable ResultSet.
JDBC Updatable ResultSet:
In this tutorial, 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.
To make the ResultSet object updatable and scrollable we must use the following constants which are present in the 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:
Statement st=con.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
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 the database through ResultSet object using absolute() method, but before inserting a record, you need to decide at which position you are inserting, since the absolute() method takes a position as a parameter where it to be inserted.
Step-1 :
rs.absolute (3);
Step-2 :
Since we are inserting a record we must use the following method to make the ResultSet object hold the record.
rs.moveToInsertRow ();
Step-3:
Update all columns of the database or provide the values to all columns of the database by using the following generalized method which is present in the ResultSet interface.
rs.updateXXX(int colno, XXX val);
Example :
rs.updateInt (1, 5);
rs.updateString (2, “abc”);
rs.updateInt (3, 80);
Step-4 :
Up to step-3, the data is inserted in the 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 the ResultSet object and the content of the ResultSet object must be updated to the database permanently by calling the following method which is present in the ResultSet interface.
rs.updateRow ();
Example For JDBC Updatable ResultSet :
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();
}
}
Before going to execute the above example the data inside the table like below :
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
Output Of the Above Example :
1 ROW UPDATED...
1 ROW INSERTED...
1 ROW DELETED...
After Updation...
After the successful execution, we have data in the database like below :
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
This is the way we can make the JDBC ResultSet updatable.
Happy Learning 🙂