CODEWITHSUNDEEP
javamysqltransactionsdao
Harsha
Harsha

Reputation: 3578

Transaction management with DAOs

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?

Upvotes: 4

Views: 7046

Answers (2)

Paul Vargas
Paul Vargas

Reputation: 42020

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

Upvotes: 3

Luiggi Mendoza
Luiggi Mendoza

Reputation: 85779

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:

Upvotes: 2

Related Questions