How to update the contents of a ResultSet using a JDBC program?

JDBCJava 8MySQLMySQLi Database

To update the contents of the ResultSet you need to create a statement by passing the ResultSet type updatable, as:

//Creating a Statement object
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

Just like getXXX() and setXXX() methods ResultSet interface also provides methods to update the contents of a row in a result set updateXXX().

These methods accept integer values representing the index or, a String value representing the column label, of the row to be updated.

Note that if you need to update the contents of a ResultSet the table should have a primary key.

Example

Assume we have a table named Employees with 5 records as shown below:

+----+---------+--------+----------------+
| Id | Name    | Salary | Location       |
+----+---------+--------+----------------+
|  1 | Amit    | 3000   | Hyderabad      |
|  2 | Kalyan  | 4000   | Vishakhapatnam |
|  3 | Renuka  | 6000   | Delhi          |
|  4 | Archana | 9000   | Mumbai         |
|  5 | Sumith  | 11000  | Hyderabad      |
+----+---------+--------+----------------+

Following example demonstrates how to update the contents of a result set:

import java.sql.*;
public class ResultSetExample {
   public static void main(String[] args) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/TestDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating a Statement object
      Statement stmt = con.createStatement(
      ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from Employees");
      //Printing the contents of the table
      System.out.println("Contents of the table: ");
      printRs(rs);
      //Moving the pointer to the starting point in the ResultSet
      rs.beforeFirst();
      //Updating the salary of each employee by 5000
      while(rs.next()){
         //Retrieve by column name
         int newSal = rs.getInt("Salary") + 5000;
         rs.updateInt( "Salary", newSal );
         rs.updateRow();
      }
      System.out.println("Contents of the ResultSet after increasing salaries");
      printRs(rs);
      // Set position to second record first
      rs.beforeFirst();
      rs.absolute(2);
      System.out.println("Record we need to delete: ");
      System.out.print("ID: " + rs.getInt("id"));
      System.out.print(", Salary: " + rs.getInt("Salary"));
      System.out.print(", Name: " + rs.getString("Name"));
      System.out.println(", Location: " + rs.getString("Location"));
      System.out.println(" ");
      //Deleting the row
      rs.deleteRow();
      System.out.println("Contents of the ResultSet after deleting one records...");
      printRs(rs);
      System.out.println("Goodbye!");
   }
   public static void printRs(ResultSet rs) throws SQLException{
      //Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Salary: " + rs.getInt("Salary"));
         System.out.print(", Name: " + rs.getString("Name"));
         System.out.println(", Location: " + rs.getString("Location"));
      }
      System.out.println();
   }
}

Output

Connection established......
Contents of the table:
ID: 1, Salary: 3000, Name: Amit, Location: Hyderabad
ID: 2, Salary: 4000, Name: Kalyan, Location: Vishakhapatnam
ID: 3, Salary: 6000, Name: Renuka, Location: Delhi
ID: 4, Salary: 9000, Name: Archana, Location: Mumbai
ID: 5, Salary: 11000, Name: Sumith, Location: Hyderabad
Conetnets of the resultset after increaing salaries
ID: 1, Salary: 8000, Name: Amit, Location: Hyderabad
ID: 2, Salary: 9000, Name: Kalyan, Location: Vishakhapatnam
ID: 3, Salary: 11000, Name: Renuka, Location: Delhi
ID: 4, Salary: 14000, Name: Archana, Location: Mumbai
ID: 5, Salary: 16000, Name: Sumith, Location: Hyderabad
Record we need to delete:
ID: 2, Salary: 9000, Name: Kalyan, Location: Vishakhapatnam
Contents of the resultset after deleting one records...
ID: 1, Salary: 8000, Name: Amit, Location: Hyderabad
ID: 3, Salary: 11000, Name: Renuka, Location: Delhi
ID: 4, Salary: 14000, Name: Archana, Location: Mumbai
ID: 5, Salary: 16000, Name: Sumith, Location: Hyderabad
Goodbye!
raja
Published on 20-Mar-2019 16:58:30
Advertisements