Connection Pools and Multiple Databases

[dasbacgl]

We are using JBoss in an environment where we have multiple indentical databases (replicated in real time). One Database takes care of Updates and replicates changes to the slaves which handle Selects. We've been able to handle this fairly easily, but as we more more and more of our material into EJBs (instead of straight JDBC calls), we're running into the problem of choosing a Database to select information out of.

For Example:

1 Master - 2 Slaves
I want to select data from one of the slaves. The actual data in the slaves is identical. In JDBC I have to randomly (or round robin) select one of the 2 DataSources from JNDI, then get a connection and perform the select. However with EJB, I have to have 2 sets of CMP Beans running, each one mapped to one of the Databases, then randomly choose one of the EJB's.

Is there a way to configure the ManagedConnectionFactory to maintain a Pool of Database connections where half of the connections are from 1 Database and the other half are from another?

If not, do you know of any 3rd Party JCA connectors for Connection Factories that would allow this type of Clustering Connection Pool?

Thanks,

Garrick Dasbach
Software Development
MusicRebellion.com
"Cheap and Easy, Just the way we like it..."

[dudoes]

See my post at
"http://www.jboss.org/modules/bb/index.html?module=bb&op=viewtopic&t=forums/

[davidjencks]

I think if you look at the jca jdbc local wrapper you will see that this would be really easy to implement yourself by modifying that wrapper. I think I'd use a string valued attribute with maybe comma separated urls for getting the list of dbs, parse it into a list, and look up the url from it when I made a connection. Adding the property and modifying internalGetConnectionURL should be enough.