What is transaction? How it can be used with JDBC? Explain with suitable Java program.
TRANSACTIONS IN JAVA
There are times when we do not want one statement to take effect unless another completes. For example, when the proprietor of The Coffee Break updates the amount of coffee one sold each week, the proprietor will also want to update the total amount sold to date. However, the amount sold per week and the total amount sold should be updated at the same time, otherwise, the data will be inconsistent. The way to be sure that either both actions occur or neither action occurs is to use a transaction. A transaction is a set of one or more statements that is executed as a unit, so either all of the statements are executed, or none of the statements is executed. The way to allow two or more statements to be grouped into a transaction is to disable the auto-commit mode. This is demonstrated in the following code, where con is an active connection
con.set AutoCommit(false);
After the auto-commit mode is disabled, no SQL statements are committed until we call the method commit explicitly. All statements executed after the previous call to the method commit are included in the current transaction and committed together as a unit.
In JDBC, Connection interface provides methods to manage transaction.
Method
void setAutoCommit(boolean status)//It is true bydefault means each transaction is committed bydefault.
void commit() //commits the transaction.
void rollback()//cancels the transaction.
Simple example of transaction management in jdbc using Statement
Let's see the simple example of transaction management using Statement.
import java.sql.*;
class FetchRecords{
public static void main(String args[])throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");
con.setAutoCommit(false);
Statement stmt=con.createStatement();
stmt.executeUpdate("insert into user420 values(190,'abhi',40000)");
stmt.executeUpdate("insert into user420 values(191,'umesh',50000)");
con.commit();
con.close();
}}
Other example
Example
import java.sql.*;
public class Transaction
{
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/bank";2308
static final String USER = "root";
static final String PASS = "arjun";
public static void main(String[] args) throws Exception
{
Connection conn = null; Statement stmt = null;
Class. for Name("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL, USER, PASS);
stmt = conn.createStatement();
conn.setAutoCommit(false);
String sql1 = "Update account set balance-balance-10000 where accno=105";
String sql2 = "Update account set balance-balance+10000 where accno=205";
}
stmt.executeUpdate (sql1);
stmt .executeUpdate(sq12);
conn.commit();
ResultSet rs. stat.executeQuery("Select* from account");
rs stmt.executeQuery("Select* from acccout"):
System.out.println("After Transaction Complete");
while(rs.next())
{
int ano rs.getInt ("accno");
String nrs.getString("accname");
float balrs.getFloat("balance");
String br rs.getString("branch");
System.out.print("Account Number: " + ano);
System.out.print(", Account Name: " + n);
System.out.print(", Balance:" + bal);
System.out.println(", Branch: + br);
}
rs.close();
stat.close();
conn.close();
}//end main
}
Output
After Transaction Complete
Account Number: 105, Account Name: Arjun, Balance:10000, Branch: Patan Account Number: 205, Account Name: Bhupi, Balance:40000, Branch: New Road
OR,
A transaction is a set of actions to be carried out as a single, atomic action. Either all of the actions are carried out, or none of them are.
The classic example of when transactions are necessary is the example of bank accounts. You need to transfer $100 from one account to the other. You do so by subtracting $100 from the first account, and adding $100 to the second account. If this process fails after you have subtracted the $100 fromt the first bank account, the $100 are never added to the second bank account. The money is lost in cyber space.
To solve this problem the subtraction and addition of the $100 are grouped into a transaction. If the subtraction succeeds, but the addition fails, you can "rollback" the fist subtraction. That way the database is left in the same state as before the subtraction was executed.
You start a transaction by this invocation:
connection.setAutoCommit(false);
Now you can continue to perform database queries and updates. All these actions are part of the transaction.
If any action attempted within the transaction fails, you should rollback the transaction. This is done like this:
connection.rollback();
If all actions succeed, you should commit the transaction. Committing the transaction makes the actions permanent in the database. Once committed, there is no going back. Committing the transaction is done like this:
connection.commit();
Of course you need a bit of try-catch-finally around these actions.
Here is a an example:
Connection connection = ...
try{
connection.setAutoCommit(false);
// create and execute statements etc.
connection.commit();
} catch(Exception e) {
connection.rollback();
} finally {
if(connection != null) {
connection.close();
}
}
Here is a full example:
Connection connection = ...
try{
connection.setAutoCommit(false):
Statement statement1 = null;
try{
statement1 = connection.createStatement();
statement1.executeUpdate(
"update people set name='John' where id=123");
} finally {
if(statement1 != null) {
statement1.close();
}
}
Statement statement2 = null;
try{
statement2 = connection.createStatement();
statement2.executeUpdate(
"update people set name='Gary' where id=456");
} finally {
if(statement2 != null) {
statement2.close();
}
}
connection.commit();
} catch(Exception e) {
connection.rollback();
} finally {
if(connection != null) {
connection.close();
}
}
Comments
Post a Comment