CODEWITHSUNDEEP
javasqljdbctransactions
user2854207
user2854207

Reputation:

Why we are using transactions in JDBC?

MY jdbc program code with and with out transaction is mentioned below.

WITHOUT TRANSACTION IN JDBC

stmt = conn.createStatement();

      System.out.println("creating statement....");
      String sql;
      sql = " INSERT INTO testing VALUES (3,  'Rita', 'Tez','vizag')";
      stmt.executeUpdate(sql);
      System.out.println("deleted.....");
      sql = "SELECT s_no,s_name,s_group,s_area  from testing group by s_no,s_name,s_group, s_area";
      ResultSet rs = stmt.executeQuery(sql);
      while(rs.next()){


         String s_name = rs.getString("s_name");


         System.out.println("employees names:" +s_name);
      }

WITH TRANSACTION IN JDBC

conn.setAutoCommit(false);



      System.out.println("Creating statement...");
      stmt = conn.createStatement(
                           ResultSet.TYPE_SCROLL_INSENSITIVE,
                           ResultSet.CONCUR_UPDATABLE);


      System.out.println("Inserting one row....");
      String SQL = "INSERT INTO testing " +
                    "VALUES (3,  'Rita', 'Tez','vizag')";
      stmt.executeUpdate(SQL);  


      SQL = "INSERT INTO testing " +
                    "VALUES (4,  'Sita', 'Singh','hyd')";
      stmt.executeUpdate(SQL);


      System.out.println("Commiting data here....");
      conn.commit();


      String sql = "SELECT  s_no,s_name,s_group,s_area FROM testing group by s_no,s_name,s_group,s_area ";
      ResultSet rs = stmt.executeQuery(sql);
      System.out.println("List result set for reference....");
      printRs(rs);


      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){

      se.printStackTrace();

      System.out.println("Rolling back data here....");
      try{
         if(conn!=null)
            conn.rollback();
      }catch(SQLException se2){
         se2.printStackTrace();
      }

   }catch(Exception e){

      e.printStackTrace();
   }finally{

      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }
   }

}

   public static void printRs(ResultSet rs) throws SQLException{

      rs.beforeFirst();
      while(rs.next()){

         int s_no = rs.getInt("s_no");
         String s_name = rs.getString("s_name");
         String s_group = rs.getString("s_group");
         String s_area = rs.getString("s_area");


         System.out.print("s_no: " + s_no);
         System.out.print(", s_name: " + s_name);
         System.out.print(", s_group: " + s_group);
         System.out.println(", s_area: " + s_area);
     }
     System.out.println();
   }
}

Both transactions are returning same results,then y we use TRANSACTIONS in JDBC.Please clarify my doubt.

Upvotes: 0

Views: 201

Answers (3)

Balaji Reddy
Balaji Reddy

Reputation: 5700

Transaction is not just about commit/rollback.. It is just one property in ACID Click..

Upvotes: 0

kapil das
kapil das

Reputation: 2101

JDBC Connection is in auto-commit mode, by default auto-commit mode, then every SQL statement is committed to the database upon its completion. with below reasons we want to turn off auto-commit and manage your own transactions:

  • To increase performance
  • To maintain the integrity of business processes

Transactions enable us to control if any changes are applied to the database. It treats a single SQL statement or a group of SQL statements as one logical unit. If any statement fails, the whole transaction will fails.

To enable manual- transaction instead of the auto-commit mode of JDBC driver use the Connection object's setAutoCommit() method. Once we done with our changes and want to commit the changes then call commit() method. Otherwise, to roll back updates to the database made using the Connection named conn, use

 conn.rollback( );

Refer transaction

Upvotes: 1

Kevin Bowersox
Kevin Bowersox

Reputation: 94429

In the second example transaction are being used because multiple inserts are being executed as part of the transaction. The transaction is capable of rolling back the first insert if the second insert fails. It makes both operations one atomic unit.

Upvotes: 2

Related Questions