Java & MySQL – Commit & Rollback
”;
Once you are done with your changes and you want to commit the changes then call commit() method on connection object as follows −
conn.commit( );
Otherwise, to roll back updates to the database made using the Connection named conn, use the following code −
conn.rollback( );
The following example illustrates the 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 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, none of the above INSERT statement would success and everything would be rolled back.
Following is the example, which makes use of commit and rollback described.
This sample code has been written based on the environment and database setup done in the previous chapters.
Copy and paste the following example in TestApplication.java, compile and run as follows −
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class TestApplication { static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT"; static final String USER = "guest"; static final String PASS = "guest123"; static final String QUERY = "SELECT id, first, last, age FROM Employees"; static final String INSERT_QUERY = "INSERT INTO Employees (first, last, age) values(''Rita'', ''Tez'', 20)"; static final String INSERT_QUERY_2 = "INSERT INTO Employees (first, last, age) values(''Sita'', ''Singh'', 20)"; public static void printResultSet(ResultSet rs) throws SQLException{ // Ensure we start with first row rs.beforeFirst(); while(rs.next()){ // Display values System.out.print("ID: " + rs.getInt("id")); System.out.print(", Age: " + rs.getInt("age")); System.out.print(", First: " + rs.getString("first")); System.out.println(", Last: " + rs.getString("last")); } System.out.println(); } public static void main(String[] args) { Connection conn = null; Statement stmt = null; try{ // Open a connection System.out.println("Connecting to database..."); conn = DriverManager.getConnection(DB_URL,USER,PASS); // Set auto commit as false. conn.setAutoCommit(false); // 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); // INSERT a row into Employees table System.out.println("Inserting one row...."); stmt.executeUpdate(INSERT_QUERY); // INSERT one more row into Employees table stmt.executeUpdate(INSERT_QUERY_2); // Commit data here. System.out.println("Commiting data here...."); conn.commit(); // 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...."); printResultSet(rs); // Clean-up environment rs.close(); stmt.close(); conn.close(); }catch(SQLException se){ 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(); } }catch(Exception e){ e.printStackTrace(); }finally{ // finally block used to close resources try{ if(stmt!=null) stmt.close(); }catch(SQLException se2){ se2.printStackTrace(); } try{ if(conn!=null) conn.close(); }catch(SQLException se){ se.printStackTrace(); } } } }
Now let us compile the above example as follows −
C:>javac TestApplication.java C:>
When you run TestApplication, it produces the following result −
C:>java TestApplication Connecting to database... Creating statement... Inserting one row.... Commiting data here.... List result set for reference.... ID: 1, Age: 23, First: Zara, Last: Ali ID: 2, Age: 30, First: Mahnaz, Last: Fatma ID: 3, Age: 35, First: Zaid, Last: Khan ID: 4, Age: 33, First: Sumit, Last: Mittal ID: 5, Age: 40, First: John, Last: Paul ID: 6, Age: 20, First: Rita, Last: Tez ID: 7, Age: 20, First: Sita, Last: Singh C:>
”;