What is CONCUR_READ_ONLY ResultSet in JDBC? Explain?


In general, you will pass this as a value to the createStatement() method as a value of ResultSet Concurrency type.

Statement createStatement(int resultSetType, int resultSetConcurrency)

This type of result set is not updatable. i.e. once you get a ResultSet object you cannot update its contents.

Example

Suppose, we have a table named Employee in the database with the following contents:

+----+---------+--------+----------------+
| Id | Name    | Salary | Location       |
+----+---------+--------+----------------+
| 1  | Amit    | 3000   | Hyderabad      |
| 2  | Kalyan  | 4000   | Vishakhapatnam |
| 3  | Renuka  | 6000   | Delhi          |
| 4  | Archana | 96000  | Mumbai         |
| 5  | Sumith  | 11000  | Hyderabad      |
| 6  | Rama    | 11000  | Goa            |
| 7  | Mahesh  | 5300   | Vishakhapatnam |
| 8  | Ramesh  | 12000  | Hyderabad      |
| 9  | Suresh  | 7600   | Pune           |
| 10 | Santosh | 96000  | Mumbai         |
+----+---------+--------+----------------+

In the following example, we are trying to increase the salary values of the employees by 5000 and print the results.

import java.sql.*;
public class Updatable {
   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_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from Employees");
      //Printing the contents of the table
      System.out.println("Contents of the table: ");
      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();
      //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("Conetnets of the resultset after increaing salaries");
      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();
   }
}

Error

Since the ResultSet we have created is read-only you cannot update the contents of the ResultSet and you will get the following error.

Connection established......
Contents of the table:
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: 101000, Name: Archana, Location: Mumbai
ID: 5, Salary: 16000, Name: Sumith, Location: Hyderabad
ID: 6, Salary: 16000, Name: Rama, Location: Goa
ID: 7, Salary: 10300, Name: Mahesh, Location: Vishakhapatnam
ID: 8, Salary: 17000, Name: Ramesh, Location: Hyderabad
ID: 9, Salary: 12600, Name: Suresh, Location: Pune
ID: 10, Salary: 101000, Name: Santosh, Location: Mumbai
Exception in thread "main" com.mysql.jdbc.NotUpdatable: Result Set not
updatable.This result set must come from a statement that was created with a
result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one
table, can not use functions and must select all primary keys from that table. See
the JDBC 2.1 API Specification, section 5.6 for more details.This result set must
come from a statement that was created with a result set type of
ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use
functions and must select all primary keys from that table. See the JDBC 2.1 API
Specification, section 5.6 for more details.
at com.mysql.jdbc.ResultSetImpl.updateInt(ResultSetImpl.java:8457)
at com.mysql.jdbc.ResultSetImpl.updateInt(ResultSetImpl.java:8475)
at NewSet.Updatable.main(Updatable.java:42)
raja
Published on 25-Mar-2019 11:20:03
Advertisements