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.
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.
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:
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 :
package com.navabitsolutions.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcUpdatableResultSet {
public static void main(String[] args) throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/navabitsolutions", "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();
}
}