Transaction Management

  • When a connection is created using JDBC, by default it is in auto-commit mode.
  • This means that each SQL statement is treated as a transaction and will be automatically committed immediately after it is executed.
  • Sometimes, you want a group of statements to execute together or fail together.
  • Transactions are used to group a set of statements so that they all execute successfully, or all fail.
  • The way to allow two or more statements to be grouped into a transaction is to disable auto-commit mode.
  • The following line of code will do this:
    • conn.setAutoCommit(false);
  • Once auto-commit mode is disabled, no SQL statement will be committed until the commit method is called explicitly.
  • All statements starting from the previous call to the commit will be committed together.
  • If there is a problem, the entire set of statements can be rolled back, without committing.
  • By Default JDBC Transactions automatically committed immediately after it is executed and it is treated as a transaction. But imagine a situation where you want to execute a batch of statements, either they should commit at on go or they should get failed together.
  • For this we need to disable the auto- commit mode by using the method: con.setAutoCommit(false). After setting the auto- commit as false, no SQL statement will be committed until we call the con.commit() method.
  • If there arises any problem while committing then the set of statements will be rollback, without committing. con.rollback();

Batch Upadate

  • One of the more advanced features of JDBC 2.0 is the ability to submit multiple update statements to the database for processing as a single unit.
  • This batch updating can be significantly more efficient compared to JDBC 1.0, where each update statement has to be executed separately.
  • In batch update more than one records can be added in the database simultaneously by using the some java methods like: addBatch and executeUpdate.