JDBC Transaction Management Code Example

0) Complete the first 6 steps in the JDBC Code example at the link here

1) Create a Java class - JdbcTransactionMgmt.java - in the src folder of the Java Project /Application

package info.icontraining.jdbc;

import java.sql.*;

public class JdbcTransactionMgmt {

   public static void main(String[] args) throws SQLException, ClassNotFoundException {

      Class.forName("oracle.jdbc.OracleDriver");
      Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","system");
      PreparedStatement pstmt = null;
  
      conn.setAutoCommit(false); // start of transaction
  
      try {
         pstmt = conn.prepareStatement("insert into jdbcdemo values (?, ?, ?, ?)");

         pstmt.setString(1, "Ramesh");
         pstmt.setString(2, "PC");
         pstmt.setInt(3, 32);

         Date d = new Date(0);
         pstmt.setDate(4, d.valueOf("1978-9-28"));

         int rowsAffected = pstmt.executeUpdate();
         System.out.println(rowsAffected + " rows affected.");

         // deliberately cause an exception to be thrown
         int i = 3/0;    // comment this line for commit to happen 

         pstmt.setString(1, "Gunjan");
         pstmt.setString(2, "C");
         pstmt.setInt(3, 26);
         pstmt.setDate(4, d.valueOf("1984-10-30"));

         rowsAffected = pstmt.executeUpdate();
         System.out.println(rowsAffected + " rows affected.");

         conn.commit();

         pstmt.close();

      } catch (Exception e) {
         conn.rollback();
         System.err.println("Caught Exception & transaction rollbacked");
      }

      conn.close(); 
   }
}

2) Run the code as a standalone Java application.
Since the code throws an exception, which is caught, and the transaction is rolled-back, no changes reflect in the database table, even though one row was, apparently, successfully inserted.

3) Comment the line that deliberately throws an exception and run the code as a standalone Java application.
Now, both rows are inserted because the transaction gets committed successfully.