Method of setting the SavePoint for transaction rollback in Java JDBC usage _java

The new JDBC3.0 save Point Interface provides additional transaction control. Most modern DBMS in their environment, such as Oracle's Pl/sql, support SavePoint.

Defines a logical rollback point in a transaction when a save point is set. If an error occurs and a savepoint past, you can use the Rollback method to undo either all changes or changes made only after the point is saved.

The connection object has two new methods to help manage the save points:

Setsavepoint (String savepointname): Defines a new save point. It also returns a SavePoint object.

Releasesavepoint (savepoint savepointname): Deletes a save point. Note that it requires a SavePoint object as an argument. This object typically generates a savepoint by the Setsavepoint () method.

There is a rollback (String savepointname) method to roll back the work to the specified save point.

The following example shows how to use the SavePoint object:

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 Employees" +
        "VALUES (Tez, ' Rita ', ')";
  Stmt.executeupdate (SQL); 
  Submit a malformed SQL statement that breaks
  String sql = "INSERTED in Employees" +
        "VALUES" (+, ' Sita '), ' Tez ') ";
  Stmt.executeupdate (SQL);
  If There is no error, commit the changes.
  Conn.commit ();

} catch (SQLException se) {
  //If there is any error.
  Conn.rollback (SAVEPOINT1);
}

In this case, no such insert statement succeeds, and everything is rolled back.
The following is an example of a rollback using the Setsavepoint and transaction tutorials.

This sample code is explained based on the installation of the environment and the database in the previous section.

Copy the following example Jdbcexample.java, 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 delete statment with//required arguments for RS example.
   SYSTEM.OUT.PRINTLN ("Creating statement ...");

  stmt = Conn.createstatement ();
   Step 6: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 7:delete rows has ID grater than//But save point before doing.
   SavePoint savepoint1 = Conn.setsavepoint ("Rows_deleted_1");
   System.out.println ("Deleting row ...");
   String SQL = "DELETE from Employees" + "WHERE ID = 110"; 
   Stmt.executeupdate (SQL);
   Oops. We deleted too wrong employees!
   Step 8:rollback The changes Afetr save point 2.

  Conn.rollback (SAVEPOINT1);
   Step 9:delete rows has ID grater than//But save point before doing.
   SavePoint Savepoint2 = Conn.setsavepoint ("Rows_deleted_2");
   System.out.println ("Deleting row ...");
   SQL = "DELETE from Employees" + "WHERE ID = 95"; 

  Stmt.executeupdate (SQL);
   Step 10:now List all the available records.
   sql = "SELECT ID, Employees"; 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 the-a-row RS. Beforefirst ();
     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:" + a);
   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
... List result set for reference ....
Id:95, Age:20, First:sima, Last:chug id:100, age:18, First:zara, Last:ali id:101, age:25, First:mahnaz,
Last:fatma
id:102, age:30, First:zaid, Last:khan id:103,
age:30, First:sumit, Last:mittal
id:110 , Age:20, First:sima, last:chug

deleting row ....
Deleting row ....
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:30, First:sumit, Last:mittal id:110, age:20, First:sima,
last:chug

Good bye!