JDBC Updatable ResultSet

In this article, we will discuss about Updatable ResultSets using examples and understand how it is different from a normal ResultSet.
 

Updatable ResultSet

 
Normal read-only ResultSet allows you only to read the data. An updatable ResultSet allows you both to read the data and to modify it through the ResultSet.

We can get an Updatable ResultSet by passing the ResultSet.CONCUR_UPDATABLE constant to the createStatement() or prepareStatement() method.
 

Syntax :

 


Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);


PreparedStatement pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

 
The Scroll type must be TYPE_SCROLL_SENSITIVE to ensure that the ResultSet will be sensitive to any updates that are made.
 

Example program for Updatable ResultSet

 
We will be using following Employee table data for the Updatable ResultSet example.
 
JDBC Updatable ResultSet
 
The data some records with name “James”. We will use Updatable ResultSet to update it while reading the employee records.

 

package com.topjavatutorial.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UpdatableResultsetDemo {

  public static void main(String[] args) throws SQLException {
    String url ="jdbc:mysql://localhost:3306/TestDB";
    
    String user = "user";//add your db user id here
    String password = "password";//add your db password here
    
    Connection conn = DriverManager.getConnection(url, user, password);
    System.out.println("Successfully connected");
    getEmployeeData(conn);
  }

  private static void getEmployeeData(Connection conn) throws SQLException{
    String sql = "select id,name,age from employee";
  
    
    try(PreparedStatement pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_UPDATABLE);){
      ResultSet rs = pstmt.executeQuery();
      String name = null;
      while(rs.next()){
      name = rs.getString("name");
      if("James".equalsIgnoreCase(name))
        rs.updateString("name", "James Moriarty");
      rs.updateRow();
      System.out.println("Emp Id : " + rs.getInt("id") + ", Name : " + rs.getString("name") + ", Age : " + rs.getInt("age"));
      }
    }
  }
}


 

Output

 
Successfully connected
Emp Id : 8, Name : John Doe, Age : 21
Emp Id : 11, Name : James Moriarty, Age : 23
Emp Id : 12, Name : James Moriarty, Age : 23
Emp Id : 13, Name : James Moriarty, Age : 31
 

Difference between Read-Only and Updatable ResultSet

 
ResultSet defines get methods to read column values from current row. Similarly, it also defines update methods to update column values.

A Read-only ResultSet only allows to read data, but does not allow updating data.

An Updatable ResultSet allows both reading the data and modifying through ResultSet.

To verify if Updatable ResultSets are supported by the driver, we can use supportsResultSetConcurrency() method in DatabaseMetaData.
 

© 2016, https:. All rights reserved. On republishing this post, you must provide link to original post

Leave a Reply.. code can be added in <code> </code> tags