That's exactly what I was going to ask.
I tried to implement the DAO
pattern :
One DBFactory + several DAOs like InsertDAO, SelectDAO, UpdateDAO and DeleteDAO. each for every CRUD operation.
I call these DAOs within the methods of a stateless EJB but I'm not sure where to get the DB connection (java.sql.Connection).
there are 2 ways :
Way 1 :
or Way 2:
In way 1 the EJB method handles the transaction (via the SQL Connection). the SQL Connection is only created once (retrieved only once from DB Connection pool) and looks more appropriate to me.
Way 2 is more readable but less portable, because every method retrieves it's own DB connection from pool. if these DAOs would be put in another environment, say a swing app with no connection pool (no JNDI at all) the code of the DAOs must be modified to work (factory.getDBConnection(); method).
another problem of Way 2 is, if the first called DAO method ( insert.insertEmployee(parameter1, parameter2); in the example above) would do it's work, but the second or third method would get a timed-out connection from pool it would raise an exception that would not have occured in Way 1.
I would like to know, is it appropriate here to use the DAO pattern at all ( esp. for CRUD operations) ?
if yes, which way should I use ? Obtaining a SQL connection in every method has a cost, too. but on the other hand I use the benefits of the DAO pattern (more readable, everything is more decoupled, etc.).