Handling Transaction JDBC


In various application we may group the series of statements in such a way that either all of them execute successfully or all to be failed. For example if we want to execute five SQL queries at a time, which are related to each other, like
ü   Search the particular data
ü    Again search for next from second row
ü     add the data retrieved
ü     Insert into third row
ü     Update the row
To do this in the single steps, we need to do the transaction in the given ordered. When multiple statements are executed in a single transaction, all operations can be committed (made permanent in database) or rollback (that is changes to the database are undone).
When a new connection object is created, it is set to commit every time transaction automatically .so we can not rollback, to solve this problem we use the transaction scheme.
There are the following methods of connection interface which are used for transactions.
 boolean  getAutoCommit() Retrieves the current auto-commit mode for this Connection object.
 void commit() Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object.
 void  rollback(Savepoint savepoint) Undoes all changes made after the given Savepoint object was set.
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; /**  @author Hemraj   * hemraj_cse2003@yahoo.com  * Program to demonstrate Transaction in JDBC  */ public class JDBCTransaction {     public static void main(String[] args)         throws  ClassNotFoundException{         Connection dbcon=null;             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");           //Define the Datasource for the Driver             String sourceURL="jdbc:odbc:hemraj_db";           //Create the connection through drivermanager             try{             dbcon=DriverManager.getConnection(sourceURL);                 System.out.println("Connection is : \n"+dbcon);           //Set the connection not autocommite             dbcon.setAutoCommit(false);             Statement stmt=dbcon.createStatement();           //String insert="INSERT INTO INFO VALUES('HEM1','RAJ1',8973499)";             System.out.println("Inserted : "+stmt.executeUpdate("INSERT INTO INFO " +                 "VALUES('HEM1','RAJ1',8973499)"));             System.out.println("Inserted : "+stmt.executeUpdate("INSERT INTO INFO " +         "VALUES('HEM2','RAJ2',000499)"));             stmt.executeUpdate("UPDATE TABLE INFO " +                 "SET name='HEMRAJ1' WHERE name=HEM1");           //set The chenges permanent             dbcon.commit();             System.out.println("Transaction Completed");             dbcon.close();             }catch(SQLException e){                 System.out.println("Error,Doing Rollback");                try{                 dbcon.rollback();                 dbcon.close();              }catch( SQLException er){                  System.out.println("Error In rollback");                  }           }     } }

No comments:

Popular Posts