Is managing the mappings within a database a major requirement of yours?
If not have you had a look at using the resource-ref element within the ejb-jar.xml deployment descriptor?
This allows you to access the datasource within the bean code using a symbolic name in the java:comp/env namespace each bean can then use this name to perform the lookup which according to the deployment descriptor will return the corrosponding datasource mapped for that bean.
Yes, this database mapping has been there for years and the corresponding code is also there for years. We have a peculiar problem here. The following is what we do currently:
1. During the server startup, create the required connection pools. For example CP1, CP2 and CP3. Each of them has the required temp tables that are to be created, specified in the init-sql element.
2. Let's say the applications know the connection pools name as ACP1, ACP2 and CP3 (note the difference here, this CP3 name is the same as CP3 name of the connection pool created in step 1).
3. Let's say the connection pools ACP1, ACP2 use the same pool CP1, and CP3 uses the pool CP3.
4. We create DataSourceWrapper (our class that implements DataSource) object for each of the application specific connection pools, and bind them to the JNDI tree.
5. In the getConnection method of the DataSourceWrapper object we obtain the appropriate connection pool and then create a connection on it. Let's say, getConnection is invoked on ACP1, this method would obtain the corresponding real connection pool name, which is CP1 and then lookups the datasource and then returns the getConnection on this CP1.
The problem we are having is that, whenever we try to execute a query on this ACP1, we get a 'temp table not found' exception. But whenever we execute query on CP3, it works fine. The *-ds.xml file has the init-sql that is required to create the temp tables. But still we are getting this exception.
The getConnection method that we are invoking on the JBoss created datasource (an instance of WrappedDataSource) gives a connection that looks to be not initialized with the init-sql that's specified in the *-ds.xml.