Hello,

I have a single database schema that will be replicated many times for different "users" (more like corporate entities) on the system. When a client (user) makes a request, I'd like for an EJB 3.0 session bean to determine which database instance to connect to based upon the name of the client (user).

As for my database, I have multiple database schemas defined in MySQL that can be accessed via

hostname:3306/duplicateSchema1,
hostname:3306/duplicateSchema2,
...etc.

Does anyone know the best way to encorporate all of these schemas and dynamically determine which database instance to connect to based upon the client?

One thought I had was to create a DataSource (*-ds.xml) file for each schema. However, I'm concerned about how this would scale if I have between 50 and 2000 duplicate schemas. (50 to 2000 *-ds.xml files seems to be the wrong way to do this .. but maybe it's right)

Does anyone have any ideas?

I really appreciate your help on this. Thanks in advance!