JDBC Transactions

JDBC - Transactions

In this tutorial, we will learn about the JDBC Transactions that plays a vital role in the subject. Every SQL statement is basically gets committed to the database upon its completion, when the JDBC Connection gets in auto-commit mode that is basically by default.

However, this condition may work well with simple applications, but there are generally three reasons that will insist the programmer to turn off the auto-commit and manage their own transactions that are depicted below:

With the help of transaction the user stays in control whenever any changes are made or been applied to the database. The transactions in JDBC generally treat a single SQL statement or a group of SQL statements as a one logical unit and in case of the statement failure the whole transactions will fail.

In order to enable manual- transaction support rather than the auto-commit mode that the JDBC driver generally uses by default the programmer is supposed to use the Connection object's setAutoCommit() method. In Case the user pass a boolean false to setAutoCommit( ), then the user have to turn off auto-commit. In order to turn that back on the user have the option to pass a boolean.

conn.setAutoCommit(false);

Commit & Rollback

After the programmer is done with the changes that are to be made then in order to commit the changes the user have to call commit() method on connection object. An example of this has been depicted below:

conn.commit( );

Or else, in order to roll back the updates that have been made to the database just by the use of the Connection named conn and should use the following code that has been displayed below:

conn.rollback( );

Here is an example that has been mentioned below and that will explain you the concept of use of a commit and rollback object:

try{
   //Assume a valid connection object conn
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   String SQL = "INSERT INTO students  " +
                "VALUES (101, 10, 'Abhi', 'Kumar')";
   stmt.executeUpdate(SQL);  
   //Submit a malformed SQL statement that breaks
   String SQL = "INSERTED IN students  " +
                "VALUES (102, 11, 'Rexx', 'Singh')";
   stmt.executeUpdate(SQL);
   // If there is no error.
   conn.commit();
}
catch(SQLException se)
{
   // If there is any error.
   conn.rollback();
}

Using Savepoints

The additional transactional control has been provided or we can say has been delivered to the programmers by the new version that is JBDC 3.0 Savepoint interface. Savepoint is generally supported by the various modern DBMS within their environments like Oracle’s PL/SQL.

Whenever the programmer wants to set a savepoint then it is mandatory that the user will define a logical rollback point within a transaction. In case any error occurs past a savepoint, then the programmer can use the rollback method in order to undo either all the changes or only the changes made after the savepoint.

There are generally two new methods of the connection object that will help the users in managing savepoints, these methods are displayed below:

• setSavepoint(String savepointName) : This method is known to define a new savepoint and also to return a Savepoint object.
• releaseSavepoint(Savepoint savepointName) : This method is generally used in order to delete a savepoint. Please note that this method also requires a Savepoint object as a parameter. This object is generally a savepoint that is generated by the setSavepoint() method.

There is also one rollback (String savepointName) method in the JDBC that is generally used to rolls back work to the specified savepoint.

Here is an example depicted below that will explain the use of a savepoint object in the JDBC:

try{
   //Assume a valid connection object conn
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   //set a Savepoint
   Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
   String SQL = "INSERT INTO Students " +
                "VALUES (101, 10, 'Abhi', 'Kumar')";
   stmt.executeUpdate(SQL);  
   //Submit a malformed SQL statement that breaks
   String SQL = "INSERTED IN Students " +
                "VALUES (102, 11, 'Rexx', 'Singh')";
   stmt.executeUpdate(SQL);
   // If there is no error, commit the changes.
   conn.commit();
}
catch(SQLException se)
{
   // If there is any error.
   conn.rollback(savepoint1);
}