I am in the beginning stages of porting my application to J2EE and would like some advice. My current application lets users pick the database they will be using when they log on to the system. Each database has an identical schema, but it may be on different machines or used for different data. How would you design your EJBs to allow there be multiple databases in the same application?
One idea that will simplify the definition of data sources is to run one EJB application per database. This means that you (in practice) run many EJB applicaitons instead of many databases.
If you want one EJB applicaiton runing against many databases, I recommend you to define all databases with JNDI name in one (!) database or a properties file. Then the user can select at login, and you can put the selection in the session object for that user. Data in the session should be the JNDi name of the database to user, and you should look in the session of the exact name of the database at aevery lookup.