Tutorialspoint

1 Answer
Vikyath Ram

When we execute certain SQL queries (SELECT query in general) they return tabular data.

The java.sql.ResultSet interface represents such tabular data returned by the SQL statements.

i.e. the ResultSet object holds the tabular data returned by the methods that execute the statements which quires the database (executeQuery() method of the Statement interface in general).

The ResultSet object has a cursor/pointer which points to the current row. Initially this cursor is positioned before first row.

The isClosed() method of the ResultSet interface is used to determine whether the current ResultSet object is closed.

rs.isclosed()

CLOSE_CURSORS_AT_COMMIT

If the holdability of the ResultSet object 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 ResultSet (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

Advertisements

We use cookies to provide and improve our services. By using our site, you consent to our Cookies Policy.