Calling a rollback operation undoes all the effects or modifications that have been done by a Transaction(Ti) and terminate the Ti and all the variables get their previous values stored. Rollback is mainly called when you get one or more than one SQL exception in the statements of Transaction(Ti), then the Ti get aborted and start over from the beginning. This is the only way to know what has been committed and what hasn’t been committed.
A SQL exception just signals out that something is wrong in your statement that you have written but doesn’t mention what and where wrong has been done. So the only option left to you is calling a rollback method.
Procedure: It primarily deals with two steps. First, create a database and then dealing with transactions.
- Create a database
- Execute the transactions for rollback
- Import the database
- Load and register drivers if necessary
- Create a new connection
- Create a statement for commit/rollback
- Execute the query for commit/rollback
- Process the results
- Close the connection else previous processing may lose if any.
Step 1: We can also rollback the modifications in the database up to a particular flag or save point by just passing the needed Save points name as a parameter into this below method −
// Set the Flag or Save point
con.rollback("MysavePoint");
Step 2. To roll back a transaction: Load the JDBC driver, by using the API method forName(String className) of the Class. In this example, we are using the Oracle
- Register the required driver using the registerDriver( ) method
// To register the needed Driver DriverManager.registerDriver(new com.mysql.jdbc.Driver());
- Get the connection information using the getConnection() API method of the DriverManager:
// For getting the connection String url = "jdbc:mysql://localhost/mydatabase/icpc"; Connection conn = DriverManager.getConnection(string url, String user, String password);
- Disable the auto-commit using the API method of off connection setAutoCommit(boolean auto-commit) method as:
// Set the auto commit false. This will execute all // SQL statements as individual transactions con.setAutoCommit(false);
- Now, set the save point using the setSavepoint() or, commit the transaction using the API method commit( ) of connection as shown below−
Savepoint savePoint = con.setSavepoint("MysavePoint");
Con.commit();
- If any SQL exception is found then, in that case, invoke rollback( ) API method for the whole transaction to till the previously set savepoint:
con.rollback() Or, con. rollback(my_Savepoint);
Implementation: Java program to demonstrate both rollback() and commit() program is as follows
Java
// Importing generic java librariesimport java.io.*;// Retrieving SQL DB// javaconnector-linkage for JDBCimport java.sql.*;import java.sql.SQLException;import java.sql.DriverManager;import java.sql.Connection;import java.sql.ResultSet;import java.sql.Statement;import java.sql.PreparedStatement;import java.sql.Date;// Importing drivers(if necessarily)// GFG class only to illustrate JDBC// not illustrating connection classclass GFG { /* Step 1: Importing DB */ // Database URL so as to create/fetch data from DB static String DB_URL = "jdbc:oracle:thin:@localhost/my_database_"; // DB userID static String DB_USER = "local"; // Remember randomly self createdDB password // to deal with above DB root static String DB_PASSWORD = "test"; // Main driver method public static void main(String args[]) { // Try block to check exceptions if occurs try { /* Step 2: Loading and registering drivers*/ Class.forName( "oracle.jdbc.driver.OracleDriver"); /* Step 3: Create the new connection */ Connection conn = DriverManager.getConnection( DB_URL, DB_USER, DB_PASSWORD); // set auto commit of the connection to false conn.setAutoCommit(false); /* Step 4: Create a statement */ // Input the info into record String sql_ = "INSERT INTO Employee (empid, empname) VALUES (?, ?)"; // Create a Statement_object PreparedStatement ps = conn.prepareStatement(sql_); /* Step 5: Execute a query */ // Take user input BufferedReader br = new BufferedReader( new InputStreamReader(System.in)); while (true) { // Asking user to enter data(EmpID) System.out.print("Enter emp_Id: "); // Reading above user entered EmpID String s_1 = br.readLine(); int empid = Integer.parseInt(s_1); // Asking user to enter data(EmpName) System.out.print("Enter emp_name: "); // Reading above user entered EmpName String name = br.readLine(); // Creating entry in table // Set emp_id ps.setInt(1, empid); // Set emp_name ps.setString(2, name); // Execute the updation operation ps.executeUpdate(); /* Step 6: Process the results */ /* Displaying choice what user wants to do with updation, either Commit() or rollback() */ System.out.println("commit or rollback"); // Reading choice from user String answer = br.readLine(); /* Asking user's choice for condition * check*/ /* Checking if users want to commit or * rollback */ // If user wants to commit if (answer.equals("commit")) { conn.commit(); } // If user wants to rollback if (answer.equals("rollback")) { // Rollback the update in case if some // flaw in your record conn.rollback(); } /* Display message to user for inputing next record if user wants to add */ System.out.println( "Do you want to include more records"); /* Asking choice */ System.out.println("\n yes/no"); // Read user's choice String answ = br.readLine(); if (answ.equals("no")) { break; } } conn.commit(); // Print message System.out.println( "record is successfully saved"); /* Step 7: Close the connection */ // calling commit() before closing connection // else updation would be lost conn.close(); } // Exception handled if occurred by catch block catch (Exception exc) { // Highlighting line where exception occurred // as execution is equal exc.printStackTrace(); } }} |
Output: There are two sample outputs images covering both cases: commit and rollback or simply direct rollback as illustrated in the below outputs.
