managing multiple connections from multiple databases are the domain of TransactionManagers covered by JTA(JSR 907)
since TC is decidedly J2SE take a look at the following J2SE TransactionManager vendors: Bitronix: for connection-pooling JBOSSTS for eventual migration to JBOSS AppServer Amotikos for Spring Integration (BeanFactory) with connection-pooling ..the most capable pf the 3 (If you're implementing Oracle as the backend i would suggest looking at Oracle's Transaction Manager) http://en.wikipedia.org/wiki/Java_Transaction_API Martin Gainty ______________________________________________ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Date: Thu, 26 May 2011 19:41:19 -0500 > Subject: Transactions using Tomcat 7 + Apache Commons DBCP 1.4 with two > databases? > From: brianbr...@gmail.com > To: users@tomcat.apache.org > > Hi, > > I'm using: > > - JDK 1.6 > - Tomcat 7.0.12 > - Apache Commons DBCP 1.4 > - MySQL 5.1.42 > - The latest MySQL JDBC driver > > I have an application that, so far, hasn't had transactional processing. I > mean I have the autocommit in "on" mode, so every update/delete gets > commited separately. I need to fix it so it starts grouping changes to the > DB in a transaction and implement the ACID stuff, using the commit and > rollback. I read the documentation and it is not hard, but I have two > complications: > > 1- I'm using DBCP, the connection pool: The way I have programmed my app is > that a business object asks a resultset that comes after a query, which in > turn asks for a statement, which in turn asks for a connection from the > pool. That means that the business object dosn't know which connection from > the pool will attend its requirement at the end. What should I do? Should I > start asking the pool for a connection, set the autocommit to "false", use > this connection for all the changes I need to make, then commit (or > rollback), and then at the end "close" (return to the pool) the connection? > Is that it? > > 2- My app uses two databases, even though both run inside the same MySQL > instance. I mantain two DBCP pools, one for each database. Then how do I > manage the transactions, if I need to make changes to both databases and > that implies using two different connections? Do I need to use the JTA stuff > that Java EE brings, for distributed processing, or is there an easier > approach? > > Thanks a lot in advance!
