Transaction Management: The sequence of actions (SQL statements) is treated as a single unit that is known as a transaction.
Disabling Auto-Commit Mode
- When a connection is created, it is in auto-commit mode.
- This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed.
- The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode.
- conn.setAutoCommit (false);
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( );
Setting and Rolling Back to Save points:
- The method Connection.setSavepoint, sets a Savepoint object within the current transaction.
- The Connection object has two new methods that help you to manage savepoints
- The following methods are used to create and delete save points.
- setSavepoint(String savepointName)
- releaseSavepoint(Savepoint savepointName)
- TheConnection.rollback method is overloaded to take a Savepoint argument.
- rollback ( String savepointName )
| package online; import java.sql.*; import java.io.*; class TrasactionInsertion { public static void main(String args[]) { try { Class.forName(“oracle.jdbc.driver.OracleDriver”); Connection con = DriverManager.getConnection( “jdbc:oracle:thin:@localhost:1521:xe”,”system”,”oracle”); con.setAutoCommit(false); PreparedStatement ps=con.prepareStatement(“insert into user420 values(?,?,?)”); BufferedReader br=new BufferedReader(new InputStreamReader(System.in)); while(true) { System.out.println(“enter id”); String s1=br.readLine(); int id=Integer.parseInt(s1); System.out.println(“enter name”); String name=br.readLine(); System.out.println(“enter salary”); String s3=br.readLine(); int salary=Integer.parseInt(s3); ps.setInt(1,id); ps.setString(2,name); ps.setInt(3,salary); ps.executeUpdate(); System.out.println(“commit/rollback”); String answer=br.readLine(); if(answer.equals(“commit”)) { con.commit(); } if(answer.equals(“rollback”)) { con.rollback(); } System.out.println(“Want to add more records y/n”); String ans=br.readLine(); if(ans.equals(“n”)) { break; } } con.commit(); System.out.println(“record successfully saved”); con.close();//before closing connection commit() is called } catch(Exception e) { System.out.println(e); } } } |