-
//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 TransactionBy 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.
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.