Adding a Layer between EJB and DAO (Data Access Objects)

Hi, virendra.

First, is it true that your DAO methods will still have to catch SQLExceptions around the stored procedure calls? I mean, you still have to do:

    CallableStatement stmt = null;
    ResultSet rs = null;
    try {
        // prepare stored procedure call
        stmt = conn.prepareCall(...);
        // bind input and output params
        stmt.setXXX(...);
        stmt.setXXX(...);
        stmt.registerOutParameter(...);
        rs = stmt.executeQuery();
        // process the ResultSet?
    } catch (SQLException e) {
        // handle
    } finally {
        // resource cleanup -- ignore exceptions
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
            }
        }
    }

Is that right? So really the only thing you avoid is obtaining and releasing a Connection. Or perhaps I have misread your example. My point is, it seems you don't really buy much by eliminating the getConnection and Connection.close within the method.

What we did on my project, to abstract obtaining a Connection and releasing the JDBC resources, was to develop a DataSourceHelper class with utility methods. Something like:

public class DataSourceHelper {
    private static DataSource ds = null;

    private DataSourceHelper() {
        // prevent instantiation
    }

    private static DataSource getDataSource() {
        if (ds == null) {
            // JNDI lookup of DataSource
            ds = ...
        }
        return ds;
    }

    public Connection getConnection() {
        return getDataSource().getConnection();
    }

    public void cleanup(Connection conn, Statment stmt, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
            }
        }
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
            }
        }
    }
}

This encapsulates obtaining a Connection from the application server defined DataSource and the proper implementation of JDBC resource cleanup. (You'd be surprised how often developers seem to get this wrong even after you've given them the code!)

As far as "Connection is created in only one class", I'm not sure of your intent here. Is it to encapsulate the procedure for obtaining a Connection so that it is done consistently? If so, then the DataSourceHelper approach does that. Is it to reduce the number of Connections that are being created during a transaction? Then your application server very well may be doing this already. I'm using WebSphere and, according to an IBM document that explains connection pooling in WAS, when a user name and password are specified that match the user name and password of a connection already allocated in the current transaction, the new request will share the allocated connection. This means that multiple requesters can share the same connection object to the database as long as they are in the same transaction. Calling close() on the connection object does not return the connection to the free pool until all requesters have called close(). In most cases, the application does not supply a user name and password when calling getConnection() on a DataSource.

What application server are you using? Do you know its behavior with respect to connection pooling?

In short, I'm saying that I don't think you really need another "layer" between EJBs and DAOs to accomplish your goals. You might be able to simply add a helper class encapsulating Connection establishment and JDBC resource cleanup and call that directly from the DAOs.

Best regards,
Jim Cakalic

Hi Jim,

I admire your thoughts. Infact we follow the same approach for creating the connection e.g. by saying like DB.getConnection(), where DB has all the DataSource relared information. We use Weblogic as an AppServer.

I read some books on Design Patterns. I was thinking to use a design pattern in this case.

I strongly feel there is some way of doing things in a better fashion.

This question is current, others are not.  I have added this to them all to bring them back to the foreground of attention so that the participating experts and you can continue the collaboration process to help you achieve your goal.

ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize these if still open in seven days.  Please post closing recommendations before that time.

Question(s) below appears to have been abandoned. Your options are:
 
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you, but may help others. You must tell the participants why you wish to do this, and allow for Expert response.  This choice will include a refund to you, and will move this question to our PAQ (Previously Asked Question) database.  If you found information outside this question thread, please add it.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question (if it has no potential value for others).
   --> Post comments for expert of your intention to delete and why
   --> YOU CANNOT DELETE A QUESTION with comments; special handling by a Moderator is required.

For special handling needs, please post a zero point question in the link below and include the URL (question QID/link) that it regards with details.
https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click this link for Help Desk, Guidelines/Member Agreement and the Question/Answer process.  https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Click you Member Profile to view your question history and please keep them updated. If you are a KnowledgePro user, use the Power Search option to find them.  

Questions which are LOCKED with a Proposed Answer but do not help you, should be rejected with comments added.  When you grade the question less than an A, please comment as to why.  This helps all involved, as well as others who may access this item in the future.  PLEASE DO NOT AWARD POINTS TO ME.

To view your open questions, please click the following link(s) and keep them all current with updates.
https://www.experts-exchange.com/questions/Q.20277924.html
https://www.experts-exchange.com/questions/Q.20279686.html
https://www.experts-exchange.com/questions/Q.20280880.html
https://www.experts-exchange.com/questions/Q.20288055.html
https://www.experts-exchange.com/questions/Q.20289517.html
https://www.experts-exchange.com/questions/Q.20289516.html
https://www.experts-exchange.com/questions/Q.20290055.html
https://www.experts-exchange.com/questions/Q.20290921.html
https://www.experts-exchange.com/questions/Q.20291464.html




*****  E X P E R T S    P L E A S E  ******  Leave your closing recommendations if this item remains inactive another seven (7) days.  If you are interested in the cleanup effort, please click this link https://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643 
POINTS FOR EXPERTS awaiting comments are listed here -> https://www.experts-exchange.com/commspt/Q.20277028.html
 
Moderators will finalize this question if in @7 days Asker has not responded.  This will be moved to the PAQ (Previously Asked Questions) at zero points, deleted or awarded.
 
Thank you everyone.
 
Moondancer
Moderator @ Experts Exchange

Hi, virendra. I was reviewing this question and thought I might extend my previous remarks.

In practice, the DAOs that we use on my project extend a common base class called 'Manager'. This base class contains common SQL-related methods useful in implementing DAOs. For example, there is an escapeString method for escaping quote characters embedded in a String value and a dateToString method for converting a Date object to a String representation appropriate to the target database.

The Manager class also provides implementations of getConnection and cleanup which simply delegate to the DataSourceHelper object I described previously. Since the specific DAO extends Manager, these methods are conveniently available.

A coding pattern that we have adopted for our DAOs is to provide two methods for each loader (aka finder). Both accept a list of parameters that specify the criteria and one accepts an additional parameter for Connection. For example, if I have a table that has a CHAR column as its primary key then I might have these methods in my DAO (the Core object is a simple JavaBean object that directly reflects the structure of the underlying database table):

public Core[] loadByKey(String key) throws SQLException {
    try {
        Connection conn = getConnection();
        return loadByKey(key, conn);
    } finally {
        cleanup(conn, null, null);
    }
}

public Core[] loadByKey(String key, Connection conn) throws SQLException {
    PreparedStatement stmt = null;
    try {
        stmt = conn.prepareStatement(...);
        stmt.setString(1, key);
        return loadByPreparedStatement(stmt);
    } finally {
        cleanup(null, stmt, null);
    }
}

public Core[] loadByPreparedStatement(PreparedStatement stmt) throws SQLException {
    ResultSet rs = null;
    try {
        rs = stmt.executeQuery();
        ArrayList list = new ArrayList();
        while (rs.next()) {
            list.add(decodeRow(rs));
        }
        Core[] cores = new Core[list.size()];
        list.toArray(cores);
        return cores;
    } finally {
        cleanup(null, null, rs);
    }
}

private Core decodeRow(ResultSet rs) throws SQLException {
    Core obj = new Core();
    // retrieve values from ResultSet and apply to object
    return obj;
}

We've found this factoring of the code allows for a good bit of reusability. In actuality, to give credit where it is due, we adopted this style after trying an open source tool called sql2java from 'bitmechanics' which generates DAOs from a database schema. We ended up performing some substantial modifications because that tool didn't do the mapping quite the way we liked as well as some other features that seemed to be missing. But using it, we now generate a majority of our data access code. And the 'idiom' used above provides both the client convenience of not having to supply a Connection object as well as the control of supplying one when if necessary. The DAOs assume that Connections (supplied or retrieved) will be under external transactional control. That is, if the client provides a Connection, it is responsible for managing the transaction. If a Connection is retrieved from the DataSource, either it will be in auto-commit mode or it will be enlisted in a container-managed transaction.

Do you think this coding 'idiom' is more to your intent?
Jim

I feel there is a way of doing the same problem in a better fashion.

Hi Jim,

I did not get time to review your comments, as I am busy in the work.

We are all busy, virenda, but this is YOUR QUESTION, you've received significant input here and need to finalize this.  Question asked, help given, and more than a month later, still open.

I have finalized this, as stated above.  If/when you want to continue on this collaboration effort, let Jim know.

Moondancer - EE Moderator