Perform Transaction mgmt. in JDBC calls

Oct

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();

}

The Blog Single