- Trending Categories
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
- Related Articles
- 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?
- How to get column count in a ResultSet in JDBC?
- How to get the row count from ResultSet in JDBC
- What is ResultSet holdability 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?
- How to get Row and Column Count from ResultSet in JDBC
- How to move the ResultSet cursor to the next row in JDBC?
- How to move the ResultSet cursor to the previous row in JDBC?
- How to move the ResultSet cursor to the last row in JDBC?
- How to move the ResultSet cursor to the first row in JDBC?
