Java – Transaction management with DAOs

daojavaMySQLtransactions

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?

Best Answer

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