In my Java application's DAO layer I have two DAO classes EmployeeDAO and BankDAO. I need to control/handle their database transactions. I use connection pooling to get database connections.
EmployeeDAO class:
public class EmployeeDAO {
String name;
String empCode;
int age;
// Getters & Setters
}
BankDAO class:
public class BankDAO {
String bankName;
String acNo;
String empCode;
// Getters & Setters
}
Let's say I am going to store an Employee and Bank account details related to that employee in two database tables. First I save employee and second I save bank details and if an error occurs when storing bank details I need to rollback complete transaction.
How to manage this sort of transaction while using DAOs?
If you are using plain JDBC, what you could do is share the same instance of Connection in the two instances of the DAO classes.
public class EmployeeDAO {
private Connection conn;
public void setConnection(Connection conn) {
this.conn = conn;
}
...
}
public class BankDAO {
private Connection conn;
public void setConnection(Connection conn) {
this.conn = conn;
}
...
}
In the client code, first you need to create a Connection object instance. Next, you need start the transaction, with conn.setAutoCommit(false);. Pass the Connection object instance to the both DAO classes. If no errors occurs in any operation, conn.commit();, otherwise, conn.rollback();
e.g.:
Connection conn = null;
try {
// getConnection from pool
conn.setAutoCommit(false);
EmployeeDAO employeeDAO = new EmployeeDAO();
employeeDAO.setConnection(conn);
BankDAO bankDAO = new BankDAO();
bankDAO.setConnection(conn);
// save employee
// save bank details
conn.commit();
catch(Exception e) {
if (conn != null) {
conn.rollback();
}
} finally {
if (conn != null) {
conn.close();
}
}
When you open a connection from the database, you can start a new transaction using the method [Connection#setAutoCommit][1](false), do all your insert/update/delete operations and execute commit to save all these changes, in case of an error you can rollback all the actions or to a savepoint. Here is an exampleof what I'm saying:
public void saveSomeData(DAOClass daoObject) {
Connection con = null;
try {
con = getConnectionFromDBPool(); //get the connection from the connection pool
con.setAutoCommit(false);
//start your transaction
PreparedStatement ps = con.prepareCall("insert into tablex values(?, ?)");
ps.setInt(1, daoObject.getAttribute1());
ps.setString(2, daoObject.getAttribute2());
ps.execute();
//add another insert/update/delete operations...
//at the end, you commit the transaction
con.commit();
} catch (Exception e) {
//start a rollback
if (con != null) {
try {
con.rollback();
} catch (Exception ex) {
}
}
//handle the exception
e.printStackTrace();
} finally {
if (con != null) {
try {
con.close();
} catch (Exception e) {
}
}
}
}
Another hint: you should close all the resources manually before closing the connection. This code is just explanatory, but I have should close the prepared statement after using it.
More info about handling transactions:
