Transaction management with DAOs

dao, java, mysql, transactions

Solution

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

Problem

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?

Original source