|
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.
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();
}
%>
|
