Transaction in JDBC

Java JDBC is a very simple and basic library to access to database data, but it is quite common and easy to setup that is why it is kind of popular. However, it has various shortages such as Object-relational mapping (ORM) and Transaction Management. The latter though can be done manually with using few simple tricks. JDBC doesn’t support transaction directly, but it supports auto commit option which allows you to implement some sort of very basic transaction manually.

For example, look at this code,

try{
connection.setAutoCommit(false);
Statement stmt = connection.createStatement();
String SQL = " INSERT INTO MyTable VALUES ( 500, 'ABC', 'XYZ' ) ";
stmt.executeUpdate(SQL);
// No error occurs in db transaction, commit the changes to db
conn.commit();
}catch(SQLException se){
// Any error happens, rollback the changes
conn.rollback();
}

As you can see in the above code a simple transaction mechanism has been implemented which does commit and rollback manually in the case of success and failure respectively. But, the real transaction manager should do all the aforementioned duties by itself and in advance ones such as JTA (Java Transaction API), distributed transactions should be enabled with facilities such as two-phase commit.

Leave a Reply