I have an application that must have the capability to run with two different data sources (ORACLE and MSSQL).

I figure I can use the DAOFactory pattern to create the DAO objects I need when I need them.

However I want to incorpporate EJB into this design because it is a requirement.

Can I do this? Does this make sense? I figure I will have two separate EJB layers (one for each database). On top of this layer comes the DAO layer for each database.

The application requires both write and read-only pieces. For the read-only pieces the DAO layer will contain the SQL to get the data.

For the write pieces I would like the DAO layer to access the EJB layer.

Am I heading in the right direction? Is there a "better" way?

Thanks,

Jmv

Depends if you're using cmp, bmp or session beans. Personally, I'd use CMP here with ejb ql and let the app server deal with the specifics of the db. It's the easiest. If you have other requirements that force you to place the SQL in the app tier or you want to use bmp, use two different package structures for the actual bean you implement and specify in the deployment descriptor which one you'll use. (Also make use of the datasource from the appserver and specify the connection through a property file or dynamic params via LDAP.)

I figure I would use Stateless Session Beans that call the Data Factory to create the necessary Data Access Objects.
The Data Access Objects would create the necessary database EJBs (CMP) for write purposes otherwise they would execute prepared statements for read purposes and return Value Object(s). The read-only DAOs require a set for each database (database differences).

Yes I would put the ELBs for each database in a different packages.

I guess the problem comes because for the read-only data I don't want to go through the EJB layer due to overhead. I want to go directly to the database. So I need a database specific object(s) to do this from the Session bean. But I want to keep the Session Bean generic.

Does this make sense or is it overkill?

Jeanne,

If you stick with SQL in a read operations, why don't you do the same for the write either? If the only problem is in transactions - you always have a choice of managing them manually.

Alex