Saturday, May 31, 2008

JDBC Transaction

In a Database Server , a transaction is a sequence of operations. Those operations are considered to be a single unit of work which either finish all or nothing , that is a transaction is atomic either lives or never exists.

For example you when your customer make a payment , you insert that payment into the payment table and update customer's balance in the customer table. If your update failse after the insert, you have to delete the payment to retry the operation. To avoid this we use transctions:

begin;
insert into customer_payment values(100,now(),12.0);
update customer set balance = balance+12.0 where customer_id = 100";

commit;

or something got wrong

rollback;

These two sql statements above will take affect into tables when you commit, if you rollback there will be no effect no update and no insert.

By default , every database in JDBC commits automatically. If you want to make use of transactions , you have to turn autocommit off.

Be careful , if you forget commit or rollback you can crash your db server because of full transaction log.

Here is the example java jdbc transaction code.:

try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e2) {
e2.printStackTrace();
}

Connection con = null;
java.sql.PreparedStatement stmtInsertPayment = null;
java.sql.PreparedStatement stmtUpdateBalance = null;
ResultSet rs = null;

try {
con = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/javacream", "javacream",
"creampass");
con.setAutoCommit(false);
stmtInsertPayment = con
.prepareStatement("insert into customer_payment values(?,?,now())");
stmtInsertPayment.setInt(1, 100);
stmtInsertPayment.setDouble(2, 12.0);
stmtInsertPayment.executeUpdate();

stmtUpdateBalance = con
.prepareStatement("update customer set balance = balance + ? where customer_id = ?");

stmtUpdateBalance.setDouble(1, 12.0);
stmtUpdateBalance.setInt(2, 100);
stmtUpdateBalance.executeUpdate();
con.commit();
con.setAutoCommit(true);
} catch (SQLException e1) {
e1.printStackTrace();
} finally {
if (rs != null)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();

}

if (stmtInsertPayment != null)
try {
stmtInsertPayment.close();
} catch (SQLException e) {
e.printStackTrace();

}

if (stmtUpdateBalance != null)
try {
stmtUpdateBalance.close();
} catch (SQLException e) {
e.printStackTrace();

}

if (con != null)
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}

}


No comments: