

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Maintain an open ResultSet after commit in JDBC?
ResultSet holdability determines whether the ResultSet objects (cursors) should be closed or held open when a transaction (that contains the said cursor/ ResultSet object) is committed using the commit() method of the Connection interface.
ResultSet interface provides two values to specify the holdability namely CLOSE_CURSORS_AT_COMMIT and HOLD_CURSORS_OVER_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 held open.
Therefore, if you need to hold the ResultSet cursor open after the commit automatically, set the ResultSet holdability to HOLD_CURSORS_OVER_COMMIT using the setHoldability() method of the Connection interface.
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 demonstrates how to hold the ResultSet cursor open after commit.
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_Hold_Cursors_Over_Commit{ 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 HOLD_CURSORS_OVER_COMMIT con.setHoldability(ResultSet.HOLD_CURSORS_OVER_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
- Related Questions & Answers
- How to close the ResultSet cursor automatically, after commit in JDBC?
- How will you keep the CURSOR open after firing COMMIT in a COBOL-DB2 program?
- How to insert rows into a ResultSet in JDBC?
- What is Type_FORWARD_ONLY ResultSet in JDBC?
- What is TYPE_SCROLL_INSENSITIVE ResultSet in JDBC?
- What is TYPE_SCROLL_SENSITIVE ResultSet in JDBC?
- What is ResultSet Concurrency in JDBC?
- What is ResultSet holdability in JDBC?
- How to get column count in a ResultSet in JDBC?
- How to get the row count from ResultSet in JDBC
- How to open an activity after receiving a PUSH notification in Android?
- What is CONCUR_UPDATABLE ResultSet in JDBC? Explain?
- What is CONCUR_READ_ONLY ResultSet in JDBC? Explain?
- How to Navigate through a ResultSet using a JDBC program?
- How to delete a row from ResultSet object using JDBC?