Hi All,

I'm facing a problem and like to share and have your feedbacks.

My problem is that I have a single application that should, basing on user login and some business rules, use a lot of different datasources ( about 6000 ). I'm going to use a lot of datasource because they reference differents mysql database and not only for configuration policies.

The application persitence is ejb3 with persitance-unit that references the datasource.
My problem is that in a single persistence unit there is a single jta-data-source, but I'd like to change it at runtime.

Solution #1. At this purpose I implemented a specific custom hibernate.connection.provider_class that, each time I get a connection, gets users info (from threadlocal), calculate and get the user jndi datasource, and provide me a new connection from the user jndi datasource.

Solution #2. Using setCatalog on every new Connection, in my application (ejb) code I change the mysql catalogue at runtime, just before using the connection.

My preferred solution is #2, that enable me to have only 1/2 pool definition, with a lot of connection, instead of a lot of pools with small connection.

I'd like to have your opinion about.
Also I cannot figure out which are the drawbacks for both solution. My primary concern is about corrupting, on long term, ejb caches or hibernate caches, or mixing up data on different threads when there is high concurrency.

Which is your opinion/advice ?
Thankyou all.