What is CLOSE_CURSORS_AT_COMMIT in JDBC?

CLOSE_CURSORS_AT_COMMIT is the constant value of the ResultSet interface representing the holdability value. If the ResultSet holdability is set to this value whenever you commit/save a transaction using the commit() method of the Connection interface, the ResultSet objects created in the current transaction (that are already opened) will be closed.

Let us create a table with name MyPlayers in MySQL database using CREATE statement as shown below −

CREATE TABLE MyPlayers(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 7 records in MyPlayers table using INSERT statements −

insert into MyPlayers values(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
insert into MyPlayers values(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica');
insert into MyPlayers values(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');
insert into MyPlayers values(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
insert into MyPlayers values(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
insert into MyPlayers values(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
insert into MyPlayers values(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

Following JDBC program sets the holdability value to CLOSE_CURSORS_AT_COMMIT starts a transaction by disabling the auto-commit, retrieves the contents of a table named MyPlayers to a ResultSet object, inserts a new row into the result set (as well as the table) and commits the transaction.

After committing the transaction, you can verify whether the ResultSet object retrieved during the transaction is closed or, held open using the isClosed() method.

If you do so since we have set the ResultSet holdability to CLOSE_CURSORS_AT_COMMIT, you can find that the ResultSet object rs is closed after the transaction is saved/committed.

Example

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ResultSetHoldability_CloseCursorsAtCommit {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String url = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(url, "root", "password");
      System.out.println("Connection established......");
      //Setting the auto commit false
      con.setAutoCommit(false);
      //Setting the holdability to CLOSE_CURSORS_AT_COMMIT
      con.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT);
      //Creating a Statement object
      Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select * from MyPlayers");
      System.out.println("Contents of the table");
      while(rs.next()) {
         System.out.print("ID: "+rs.getString("ID")+", ");
         System.out.print("First_Name: "+rs.getString("First_Name")+", ");
         System.out.print("Last_Name: "+rs.getString("Last_Name"));
         System.out.print("Date_Of_Birth: "+rs.getString("Date_Of_Birth")+", ");
         System.out.print("Place_Of_Birth: "+rs.getString("Place_Of_Birth"));
         System.out.print("Country: "+rs.getString("Country"));
         System.out.println("");
      }
      //Inserting a new row
      rs.moveToInsertRow();
      rs.updateInt(1, 8);
      rs.updateString(2, "Ishant");
      rs.updateString(3, "Sharma");
      rs.updateDate(4, new Date(904694400000L));
      rs.updateString(5, "Delhi");
      rs.updateString(6, "India");
      rs.insertRow();
      //Committing the transaction
      con.commit();
      boolean bool = rs.isClosed();
      if(bool) {
         System.out.println("ResultSet object is closed");
      } else {
         System.out.println("ResultSet object is open");
      }
   }
}

Output

Connection established......
Contents of the table
ID: 1, First_Name: Shikhar, Last_Name: DhawanDate_Of_Birth: 1981-12-05, Place_Of_Birth: DelhiCountry: India
ID: 2, First_Name: Jonathan, Last_Name: TrottDate_Of_Birth: 1981-04-22, Place_Of_Birth: CapeTownCountry: SouthAfrica
ID: 3, First_Name: Kumara, Last_Name: SangakkaraDate_Of_Birth: 1977-10-27, Place_Of_Birth: MataleCountry: Srilanka
ID: 4, First_Name: Virat, Last_Name: KohliDate_Of_Birth: 1988-11-05, Place_Of_Birth: MumbaiCountry: India
ID: 5, First_Name: Rohit, Last_Name: SharmaDate_Of_Birth: 1987-04-30, Place_Of_Birth: NagpurCountry: India
ID: 6, First_Name: Ravindra, Last_Name: JadejaDate_Of_Birth: 1988-12-06, Place_Of_Birth: NagpurCountry: India
ID: 7, First_Name: James, Last_Name: AndersonDate_Of_Birth: 1982-06-30, Place_Of_Birth: Burnley Country: England
ResultSet object is closed