Getting started with JDBC

A transaction is a sequence of queries or operations performed as a single block of commands. These commands must exhibit four properties, called the atomicity, consistency, isolation, and durability (ACID) properties, to qualify as a transaction.

In the picture below we see there are 2 paths possible, if a transaction or a block of operation succeeds, then data change is commited to database, else database see no effect.

Auto Commit Default option mode in JDBC connection allows SQL statement to get committed to database upon completion. The three reasons by turning off auto commit will help in managing transaction:-

• Performance Increase
• Transaction distributed

Auto Commit option can be enables or disabled to connection object.

conn1.setAutoCommit(false);

Commit & Rollback

After making all changes then it can be commited into database.

conn1.commit( );

Roll back updates to database can be done as follows:

conn1.rollback( );

Example of commit and rollback object.

In the example below, Second statement will fail because USER2 table does not exist. Thus rollback will be triggered.

try{
 // connection conn1 object
cConn1.setAutoCommit(false);
 Statement stmtmnt = conn1.createStatement(); 
 String SQL1 = "INSERT INTO USER " +
 "VALUES (103, 2, 'Angad', 'S')";
 stmtmnt.executeUpdate(SQL1); 
 //Wrong SQL statement breaks
 String SQL2 = "INSERT INTO USER2 " +
 "VALUES (104, 2, 'Kamal', 'K')";
 stmtmnt.executeUpdate(SQL2);
 // if there is no error or exception thrown by previous statements then commit will happen
 conn1.commit();
}catch(SQLException se){
 // However if there are any errors then rollback will take place, i.e. none of the inserted data will actually work
 conn1.rollback();
}