Contact Us
   
   
 
   
   
 
     
 
 
JSP Tutorial
JDBC Tutorial
 
Book Must Read
 
 

JDBC Transaction / rollback

 
 
Print   Email
 
     
 

Every Trans+action transfer of action to database server or through database server is called transaction. Transaction is started from creating connection, opening connection, insert records, update records, delete, alter schema anything SQL commands.
When connection is created, it is by default auto commit true. When SQL command execute, it commits SQL statement immediately. Commit can we see as permanent changes in database table.
Suppose on generating a bill to customer, three tables should be updated
First item should be reduced from inventory table.
Second money should update in accounts table.
Third bill information should be stored in bill information table about what when whom name contact etc.

If one table entry fails that make data inconsistent. We need all entries in three tables or not in anyone, setAutoCommit false mode can prevent from this situation.

connection.setAutoCommit(false);

If auto commit is false, no SQL statement commits to database and have to forcefully to commit it.

connection.commit();

Any case fails in execution, results in rollback to previous state. Rollback is important operation for database integrity, any abnormal condition restore starting state of database and erase all modification done on database from starting state. This gives us consistent database.
Rollback transaction can be used with savepoints or without savepoints.

A savepoint is target point where we save successful transaction at particular limits of SQL queries. This can be depended on how big our transaction and our need of transaction. Suppose an example, we are doing big shopping in that we purchased something, this should immediately commit on database to start next transaction. If next transaction suddenly fails by server crush or other error, it should rollback to this savepoint not to starting point of purchase.

A transaction can not be rolled back after commit statement execution.

Example of rollback transaction

Rollback.jsp

<%@ page language="java" import="java.sql.*" errorPage="" %>
<%
        Connection conn = null;
        
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        String jdbcURL="jdbc:mysql://localhost:3306/jsp";
    
        conn = DriverManager.getConnection(jdbcURL,"root", "");
        
        Statement stInsertRecord=null;
        String sqlInsertRecord=null;
%>
<html>
<head>
<title>Rollback transaction JDBC</title>
</head>

<body>
<%
stInsertRecord=conn.createStatement();
conn.setAutoCommit(false);
    try{
        for(int i=1;i<=50;i++)
        {
          sqlInsertRecord="insert into class_room (sStudentName, sRollNo, sCourse)" +
                                      " values('New Student','4"+i+"','10th Class')";
          stInsertRecord.executeUpdate(sqlInsertRecord);
        }
        conn.commit();
       }
    catch(Exception e)
    {
      e.printStackTrace();
      conn.rollback(); 
    }
    finally
    {
      conn.setAutoCommit(true);
    }
%>
Rollback transaction is done successfully
</body>
</html>
<%
       try{
          if(stInsertRecord!=null)
          {
            stInsertRecord.close();
          }
          
          if(conn!=null)
          {
           conn.close();
          }
        }
        catch(Exception e)
        {
          e.printStackTrace(); 
        }
%>

 

 

 

     
 
If You Like this Article, share this

 
 
     
   
     
     
   
     
     
     
 
  Privacy Policy