JDBC — Advanced concepts

S Gokul

Managing transactions

Transaction is an important business concept in the world of database
interactions. It simply refers to a series of database operations that must
necessarily be performed together, as a single unit - and not as individual
operations. Either they are performed together, or not performed at all!

For example, let us assume that you want to transfer funds from your bank
account to another bank account. This whole business process consists of two
distinct database operations:

1. The required amount is first deducted from your bank account. (First
   database operation)
2. The same amount is then added to the other designated bank account.
   (Second database operation)

From the business point of view, these two database operations are closely
associated with one another, and together, they represent a single business task
- Funds Transfer. They should be performed together — synchronously —
so as to ensure that the business task performed is meaningful.

Now this whole operation can be termed as a single Transaction.

Click here to view image.

Technically, when a transaction is performed, the database operations are ‘committed’
together — as a whole lot — or all the operations are ‘rolled back’. (‘Commit’
is like sending a confirmation to the database — that the operations performed
so far are OK. ‘Roll back’ means cancellation of all operations performed on
the database, since the last commit!). When you are writing Java applications
that perform transactions, enough care should be taken to ensure that this basic
thumb rule is fully satisfied by the application.

Java Programs make use of the JDBC API to get connected to the database and
do the transactions. While applications that do simple database interactions
need not bother about committing and rollbacks, applications that perform
database Transactions have to ensure that the business task performed is
meaningful — at the end of the whole operation.

By default, all SQL statements that are executed using the executeQuery()
or executeUpdate() methods of the JDBC Statement class, are ‘committed’
automatically. But in operations that involve transactions, we need to turn this
auto commit mode ‘off’, and take the responsibility of ‘committing’ the
operations, to ourselves. This gives us the flexibility of ‘rolling back’
the operations performed, from the ongoing transactions, at any time.

Let us take a snippet of the code, from an example program — which does the
actual transaction:

Connection con = null;

Statement stmt = null;

synchronized(this){

try{

String Db_Url = "jdbc:oracle:thin:dbssprod/dbssprod@17.252.11.235:1521:develop";

String Username = "scott";

String Password = "tiger";

con = DriverManager.getConnection(Db_Url, Username, Password);

//Turn Off the Autocommit mode

con.setAutoCommit(false);

stmt = con.createStatement();

String querry1 = "UPDATE USER_ACCOUNTS01 SET TOTAL_ACCOUNT =
TOTAL_ACCOUNT - 1000 WHERE USER_ACCOUNT_NO = '10020001'";

String querry2 = "UPDATE USER_ACCOUNTS02 SET TOTAL_ACCOUNT =
TOTAL_ACCOUNT + 1000 WHERE USER_ACCOUNT_NO = '30201223'";

int rows_affected1 =stmt.executeUpdate(querry1);

int rows_affected2 =stmt.executeUpdate(querry2);

if ((rows_affected1 == 1) && (rows_affected2 == 1)){

con.commit();

con.setAutoCommit(true);

}

}

catch (SQLException exception){

System.out.println("\n*** SQLException caught ***\n");

//Rollback the Operations — since an exception has occurred

if (con != null) {

try {

System.err.print("Rolling back the Transaction");

con.rollback();

} catch(SQLException exception1) {

System.err.print("An exception occurred while trying to rollback the
connection"); System.err.println(exception1.getMessage());

}

}

}