JDBC
Transaction let you control how and when a transaction should commit into database.
-
//transaction block start - //SQL insert statement
- //SQL update statement
- //SQLdelete statemetn
- //transaction block end
In simple, JDBC transaction make sure SQL statements within a transaction block are all executed successful, if either one of the SQL statement within transaction block is failed, abort and rollback everything within the transaction block.
See below two examples to understand how JDBC transaction works.
1. Without JDBC Transaction
By default, data will be committed into database when executeUpdate() is
called.
- String insertTableSQL="INSERT INTO DBUSER"
- +
"(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES" - +
"(?,?,?,?)"; - String updateTableSQL
= "UPDATE DBUSER SET USERNAME =? " - +
"WHERE USER_ID = ?"; - preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
- preparedStatementInsert.setInt(1, 999);
- preparedStatementInsert.setString(2,"mkyong101");
- preparedStatementInsert.setString(3,"system");
- preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
- preparedStatementInsert.executeUpdate();//data COMMITTED
into database. - preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
- preparedStatementUpdate.setString(1,"A very very long string caused DATABASE ERROR");
- preparedStatementUpdate.setInt(2, 999);
- preparedStatementUpdate.executeUpdate();//Error, value
too big, ignore this update statement,
When
this code is executed, the USER_ID = ’999′ is inserted but the username is not update.
2. With JDBC Transaction
To put this in a transaction, you can use
- dbConnection.setAutoCommit(false); to start a transaction block.
- dbConnection.commit(); to end a transaction block.
See code snippets :
- dbConnection.setAutoCommit(false);//transaction
block start - String insertTableSQL
= "INSERT INTO DBUSER" - +
"(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES" - +
"(?,?,?,?)"; - String updateTableSQL
= "UPDATE DBUSER SET USERNAME =? " - +
"WHERE USER_ID = ?"; - preparedStatementInsert = dbConnection.prepareStatement(insertTableSQL);
- preparedStatementInsert.setInt(1, 999);
- preparedStatementInsert.setString(2,"mkyong101");
- preparedStatementInsert.setString(3,"system");
- preparedStatementInsert.setTimestamp(4, getCurrentTimeStamp());
- preparedStatementInsert.executeUpdate();//data IS
NOT commit yet - preparedStatementUpdate = dbConnection.prepareStatement(updateTableSQL);
- preparedStatementUpdate.setString(1,"A very very long string caused DATABASE ERROR");
- preparedStatementUpdate.setInt(2, 999);
- preparedStatementUpdate.executeUpdate();//Error, rollback,
including the first insert statement. - dbConnection.commit();
When this code is executed, update statement is hits error, and make both insert and update statements rollback together.