Perform Transaction mgmt. in JDBC calls
Client Session plays an important factor which determines if changes made via Selenium scripts needs to be committed to database or not.
For Selenium scripts to support session based updates to databases, JDBC provides “Transactions” which are executed in all or none condition based on validity of session.
In databases, a Transaction is a set of SQL Statements that are executed as a unit. Either all statements will be executed successfully or none of them.
#. The following example demonstrates transaction management in JDBC calls.
import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; /** * @author SR */ public class JDBCTransactionExample { private static final String INSERT_SQL = "INSERT INTO employee " + "(EMP_ID, NAME, DOB) VALUES (?,?,?)"; private static final String UPDATE_SQL = "UPDATE employee SET EMAIL=?, DEPT=? " + " WHERE EMP_ID=?"; public static void main(String[] args) { String jdbcUrl = "jdbc:mysql://10.9.12.16:3306/Database"; String username = "username"; String password = "pwd"; DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) { // STEP 1 - Disable auto commit mode conn.setAutoCommit(false); try (PreparedStatement insertStmt = conn.prepareStatement(INSERT_SQL); PreparedStatement updateStmt = conn.prepareStatement(UPDATE_SQL);) { // Create insert statement insertStmt.setInt(1, 1); insertStmt.setString(2, "Michael"); insertStmt.setDate(3, new Date(dateFormat.parse("1995-07-01").getTime())); insertStmt.executeUpdate(); // Create update statement updateStmt.setString(1, "michael@boraji.com"); updateStmt.setString(2, "HR Department"); updateStmt.setInt(3, 1); updateStmt.executeUpdate(); // STEP 2 - Commit insert and update statement conn.commit(); System.out.println("Transaction is commited successfully."); } catch (SQLException | ParseException e) { e.printStackTrace(); if (conn != null) { try { //STEP 3 - Roll back transaction System.out.println("Transaction is being rolled back."); conn.rollback(); } catch (Exception ex) { ex.printStackTrace(); } } } } catch (SQLException e) { e.printStackTrace(); } } } |
Output:
Transaction is commited successfully.
In JDBC, the Connection interface provides the following methods to perform Transactions management via JDBC Calls:
setAutoCommit()
commit() and
rollback()
#. Following example demonstrates commit / rollback of transactions:
try{
String Connectionurl = “jdbc:sqlserver://a.b.c.d:1433;Database=DBName”;
con = DriverManager.getConnection(Connectionurl, “qcrw”, “qcrw”);
con.setAutoCommit(false);
Statement stmt = con.createStatement();
rs = stmt.executeUpdate(SQLQuery);
// If there is no error.
conn.commit();
} catch(Exception e)
{
// on error rollback
conn.rollback();
}
#. Following example demonstrates “savepoints” in transactions:
import java.sql.Connection; import java.sql.Date; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.SQLException; import java.sql.Savepoint; import java.text.DateFormat; import java.text.ParseException; import java.text.SimpleDateFormat; /** * @author SR */ public class JDBCSavepointExample { private static final String INSERT_SQL = “INSERT INTO employee “ + “(EMP_ID, NAME, DOB) VALUES (?,?,?)”; public static void main(String[] args) { String jdbcUrl = “jdbc:mysql://10.9.121.6:3306/DBName”; String username = “username”; String password = “pwd”; DateFormat dateFormat = new SimpleDateFormat(“yyyy-MM-dd”); try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);) { // Disable auto commit mode conn.setAutoCommit(false); try (PreparedStatement insertStmt = conn.prepareStatement(INSERT_SQL);) { // Insert 1st record insertStmt.setInt(1, 1); insertStmt.setString(2, “Michael”); insertStmt.setDate(3, new Date(dateFormat.parse(“1995-07-01”).getTime())); insertStmt.executeUpdate(); // Insert 2st record insertStmt.setInt(1, 2); insertStmt.setString(2, “Sunil”); insertStmt.setDate(3, new Date(dateFormat.parse(“1988-03-22”).getTime())); insertStmt.executeUpdate(); // Insert 3st record insertStmt.setInt(1, 3); insertStmt.setString(2, “Mike”); insertStmt.setDate(3, new Date(dateFormat.parse(“1980-05-12”).getTime())); insertStmt.executeUpdate(); // Create Savepoint Savepoint savepoint = conn.setSavepoint(); // Insert 4st record insertStmt.setInt(1, 4); insertStmt.setString(2, “Manish”); insertStmt.setDate(3, new Date(dateFormat.parse(“1992-01-21”).getTime())); insertStmt.executeUpdate(); // Insert 5st record insertStmt.setInt(1, 5); insertStmt.setString(2, “Albert”); insertStmt.setDate(3, new Date(dateFormat.parse(“1972-07-05”).getTime())); insertStmt.executeUpdate(); // Rollback to savepoint conn.rollback(savepoint); // Commit statement conn.commit(); System.out.println(“Transaction is commited successfully.”); } catch (SQLException | ParseException e) { e.printStackTrace(); if (conn != null) { try { // Roll back transaction System.out.println(“Transaction is being rolled back.”); conn.rollback(); } catch (Exception ex) { ex.printStackTrace(); } } } } catch (SQLException e) { e.printStackTrace(); } } } |
0 comments