I have a problem and a proposed solution. I would greatly appreciate any feedback about the proposed solution.
Problem: ======= I'm currently using a Struts application with a connection pool (using DBCP as supplied by Tomat). When a database update is needed, the Struts actions will call the facade which will talk to my service layer (currently POJO's which handle business logic.) My service layer in turn talks to the appropriate DAO. Each of these DAO's extends from a common abstract class that provides basic functionality including obtaining a connection from the DataSource (via the pool). A key aspect of my design is that some updates are in distinct areas of the database and so I have different DAO's for each area (ex. one for "workflow" on for "document.") As currently implemented I am unable to take advantage of transactions because the two DAOs will be getting a connection indepently from the pool and they will most likely not obtain the connection each time. If I could just get the same connection each time, then I could use setAutoCommit(false). Proposed Solution: ============== I'm thinking I could set up a few chains for the various kind of updates. The chains would be called by the POJO service layer (instead of calling the DAO's directly.) The first Command in the chain would be to indentify all database updates in the chain as needing transactions. This would be done through a static method on a new object called TransactionManager. Basically I would have a hashtable that would maintain connections for the duration of the chain. The connections would be stored by the current thread (use that as the key to the table.) Then when a command down the line needs a database connection, it would first check to see if there is one already set aside for it to use. Actually the command would call the DAO and the DAO would check. The command would also be decorated by a custom wrapper so that if the DAO's try to close the connection, I'll ignore it. Then when the chain does the post processing in reverse order. So the last clean up step will be to check for my custom DAOException. If there is one, then rollback, otherwise commit. Finally, the connection is removed from the TransactionManager. I think this might be crazy enough to work. I know we could allways use EJB and get transactions but that might be overkill since the volume is very light (this is custom software for a government agency not ecommerce.) Please let me know what you think. A big question I have is about storing and retrieving the datbase connection using the current thread as the key to a hashtable. Also, I know that I will have to be careful with thread synchronization. Thanks, sean
