- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- 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