If the JDBC connection is in autocommit mode, it is by default, then every SQL statement is committed to the database when it is completed.
This may be for simple applications, but for three reasons you might want to turn off automatic commit and manage your own transactions:
To control transactions and when changes are applied to the database. It takes a single SQL statement or a set of SQL statements as a logical unit, and if any statement fails, the entire transaction fails.
To enable, instead of JDBC drivers using the auto-commit mode manual transaction support by default, use the SetAutoCommit() method of the Connection object. If a boolean value is passed to setAutoCommit(), turn off automatic commit. A boolean value can be passed to true to reopen it.
For example, if there is an object named conn Connection, the following code will turn off automatic commit:
conn.setAutoCommit(false);
Submit and rollback
Once the changes have been completed, you want to commit the changes and then call the commit (on the connection object) method as follows:
conn.commit( );
Otherwise, the rollback updates that are made to the database use named connection conn, using the following code:
conn.rollback( );
The following example demonstrates how to use a commit and rollback object:
try{ //Assume a valid connection object conn conn.setAutoCommit(false); Statement stmt = conn.createStatement(); String SQL = "INSERT INTO Employees " + "VALUES (106, 20, 'Rita', 'Tez')"; stmt.executeUpdate(SQL); //Submit a malformed SQL statement that breaks String SQL = "INSERTED IN Employees " + "VALUES (107, 22, 'Sita', 'Singh')"; stmt.executeUpdate(SQL); // If there is no error. conn.commit();}catch(SQLException se){ // If there is any error. conn.rollback();}In this case, no INSERT statement above will succeed and everything will be rolled back.
Transaction commit and rollback examples
The following is an example of using transaction commit and rollback description.
This sample code has been learned based on the environment and database installation in the previous chapter.
Copy the JDBCExample.java from the following example in the past, compile and run, as shown below:
//STEP 1. Import required packagesimport java.sql.*;public class JDBCExample { // JDBC driver name and database URL static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; static final String DB_URL = "jdbc:mysql://localhost/EMP"; // Database credentials static final String USER = "username"; static final String PASS = "password"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ //STEP 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); //STEP 3: Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); //STEP 4: Set auto commit as false. conn.setAutoCommit(false); //STEP 5: Execute a query to create statment with // required arguments for RS example. System.out.println("Creating statement..."); stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); //STEP 6: INSERT a row into Employees table System.out.println("Inserting one row...."); String SQL = "INSERT INTO Employees " + "VALUES (106, 20, 'Rita', 'Tez')"; stmt.executeUpdate(SQL); //STEP 7: INSERT one more row into Employees table SQL = "INSERT INTO Employees " + "VALUES (107, 22, 'Sita', 'Singh')"; stmt.executeUpdate(SQL); //STEP 8: Commit data here. System.out.println("Commiting data here...."); conn.commit(); //STEP 9: Now list all the available records. String sql = "SELECT id, first, last, age FROM Employees"; ResultSet rs = stmt.executeQuery(sql); System.out.println("List result set for reference..."); printRs(rs); //STEP 10: Clean-up environment rs.close(); stmt.close(); conn.close(); }catch(SQLException se){ //Handle errors for JDBC se.printStackTrace(); // If there is an error then rollback the changes. System.out.println("Rolling back data here..."); try{ if(conn!=null) conn.rollback(); }catch(SQLException se2){ se2.printStackTrace(); }//end try }catch(Exception e){ //Handle errors for Class.forName e.printStackTrace(); } finally{ //finally block used to close resources try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ }// nothing we can do try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); }//end finally try }//end try System.out.println("Goodbye!");}//end main public static void printRs(ResultSet rs) throws SQLException{ //Ensure we start with first row rs.beforeFirst(); while(rs.next()){ //Retrieve by column name int id = rs.getInt("id"); int age = rs.getInt("age"); String first = rs.getString("first"); String last = rs.getString("last"); //Display values System.out.print("ID: " + id); System.out.print(", Age: " + age); System.out.print(", First: " + first); System.out.println(", Last: " + last); } System.out.println(); }//end printRs()}//end JDBCExampleNow let's compile the above example as follows:
C:>javac JDBCExample.java
When running JDBCExample, it produces the following results:
C:>java JDBCExample
Connecting to database...Creating statement...Inserting one row....Committing data here....List result set for reference....ID: 100, Age: 18, First: Zara, Last: AliID: 101, Age: 25, First: Mahnaz, Last: FatmaID: 102, Age: 30, First: Zaid, Last: KhanID: 103, Age: 28, First: Sumit, Last: MittalID: 106, Age: 20, First: Rita, Last: TezID: 107, Age: 22, First: Sita, Last: SinghGoodbye!