Detailed Java JDBC API transaction commit and Rollback _java

If the JDBC connection is in autocommit mode, it is by default, then each SQL statement is committed to the database when it is completed.

This may be for simple applications, but there are three reasons why you may want to turn off automatic submission and manage your own transactions:

1. To improve performance
2. To maintain the integrity of the business process
3. Using Distributed 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 statements fail, the entire transaction fails.

To enable instead of the JDBC driver by default using the AUTO-COMMIT mode manual transaction support, use the Setautocommit () method of the Connection object. If you pass a Boolean value of False to Setautocommit (), turn off autocommit. You can pass a Boolean value true to reopen it.

For example, if you have a connection object named Conn, the following code turns off autocommit:

Conn.setautocommit (FALSE);

Committing and rolling back
Once the changes have been made, commit the changes, and then call the commit (in the Connection object) method, as follows:

Conn.commit ();

Otherwise the rollback update uses the named Connection conn for the database, using the following code:

Conn.rollback ();

The following example shows 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 (Tez, ' Rita ', ')";
  Stmt.executeupdate (SQL); 
  Submit a malformed SQL statement that breaks
  String sql = "INSERTED in Employees" +
        "VALUES", ' 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 such insert statement succeeds, and everything is rolled back.

Transaction Commit and rollback examples
The following is an example of using a transaction commit and rollback description.

This sample code has been learned based on the installation of the environment and the database in the previous chapters.

Copy Jdbcexample.java in the example below, compile and run as follows:

Step 1.

Import required packages import java.sql.*; public class Jdbcexample {//JDBC driver name and database URL static final String jdbc_driver = "Com.mysql.jdbc.Driv 
  ER ";

  Static final String Db_url = "Jdbc:mysql://localhost/emp";
  The 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 (Tez, ' Rita ', ')"; 

   Stmt.executeupdate (SQL);  Step 7:insert One more row to Employees table SQL = INSERT into Employees "+" VALUES (the ' Sita ',
   ' Singh ') ';

   Stmt.executeupdate (SQL);
   Step 8:commit data.
   System.out.println ("commiting data here ...");
  
  Conn.commit ();
   Step 9:now List all the available records.
   String sql = "SELECT ID, 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 ..."); 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 a-row rs.bef
   Orefirst ();
     while (Rs.next ()) {//retrieve by column name int id = rs.getint ("id");
     int age = Rs.getint (' age ');
     String a = rs.getstring ("a");

     String last = rs.getstring ("last");
     Display values System.out.print ("ID:" + ID);
     System.out.print (", Age:" + age); System.out.print (", A:" + fiRST);
   System.out.println (", Last:" + last);
  } System.out.println ();

 }//end printrs ()}//end jdbcexample

Now let's compile the example above as follows:

C:>javac Jdbcexample.java

When you run Jdbcexample, it produces the following results:

C:>java Jdbcexample

Connecting to Database ...
Creating Statement
... Inserting one row ....
commiting data here ....
List result set for reference ....
id:100, Age:18, First:zara, Last:ali id:101, age:25,
First:mahnaz, Last:fatma id:102, age:30, First:z
Aid, Last:khan
id:103, age:28, First:sumit, Last:mittal id:106,
age:20, First:rita, Last:tez
id:1 Age:22, First:sita, Last:singh
goodbye!