This forum is now a read-only archive. All commenting, posting, registration services have been turned off. Those needing community support and/or wanting to ask questions should refer to the Tag/Forum map, and to http://spring.io/questions for a curated list of stackoverflow tags that Pivotal engineers, and the community, monitor.

Multiple datasources

Aug 30th, 2004, 03:52 PM

Hi all,

I have an app that connects to multiple datasources but the tables underneath are exactly the same. We've been running into problems using the default HibernateTemplate since it requires to work with a single session.

The datasource is determined at login (based on the user id). Thus, each user is assigned a different datasource.

What is the best approach with the spring framework. Rewrite the default Template so it can handle multiple sessions?

I'm not sure Hibernate can work with multiple datasources. You could use a custom TargetSource to conceal the selection of which DataSource to invoke based on a ThreadLocal, thus concealing the choice from Hibernate.

Comment

This is not really about supporting multiple sessions, but ultimately about supporting multiple SessionFactories, since a SessionFactory is really tied to a DataSource, and the Session is created from there. Spring aside, there is somewhat of mismatch here with how Hibernate is typically supposed to be used, as the SessionFactory is fairly expensive to create, in terms of time. With a smaller number of users logging in, I suppose it is reasonable to create multiple SessionFactories, but this is not very typical.

There are a few approaches I can see working. One is where, on a user login, the SessionFactory is created and bound into the HTTP Session. When that user comes in later via another request, a servlet filter takes the Session and binds it to the current thread as a threadlocal. All usage of the SessionFactory would be through a proxy which grabs it from the threadlocal. One thing to figure out/be careful with is the fact that normally most beans are singletons, and are created when the appcontext is first created, so if some bean tries to use the SessionFactory right away, there would be a problem. A potential solution around this is to default to one premade session factory in the case there is no thread-bound one.

Alternately, as Rod mentioned, a possibly simpler solution since in fact all the mapping information is the same, is to just wrap the datasource with a proxy, which relies on thread-bound info for the real datasource to invoke on. As far as any users of the proxy datasource are concerned, they are dealing with the same proxy all the time. One thing to keep in mind is that you should not within the same web invocation (on the same thread) mix and match operations meant for multiple users.

Comment

1. Create a DatasourceProxy and propagate the id in some thread local variable and call getConnection to retrieve the correct datasource. The datasourceproxy would be preloaded with a map of the userid with the associated datasource.

2. Inject at the dao implementation layer all the different session factories and create a hibernateTemplate for each session factory. Hence, to retrieve the template:

Instead of saying
getHibernateTemplate();

We would have:
getHibernateTemplate(id);

Dino

Comment

I'm not sure Hibernate can work with multiple datasources. You could use a custom TargetSource to conceal the selection of which DataSource to invoke based on a ThreadLocal, thus concealing the choice from Hibernate.

Hi, i'm currently implementing a similar mechanism. However i have another question: what is the best strategy to configure a transaction manager for each datasource?

Also there is a difference between my test environment and the production environment. In my test environment, i configure each datasource with as an
org.apache.commons.dbcp.BasicDataSource instance. In the production environment, the datasources are retrieved by means of <jee:jndi-lookup...

For the test environment, should i configure different transaction managers, where the selection between each is based on a the same ThreadLocal as for the datasource? And for the production environment, does it suffice to use <bean id="transactionManager" class="org.springframework.transaction.jta.JtaTran sactionManager"/>?

Comment

Resurrecting such old threads is mostly not a good idea. VERY much has changed in the last three years.

You might want to have a look at a very recent thread. Remains the question if you need more than switching between test and production environment. And what exactly. Maybe AbstractRoutingDataSource is an alternative. If you really need multiple data sources at the same time (i.e. taking part in one transaction) you need also multiple SessionFactory's as far as I know.

Comment

Thanks for the tip, Jörg! In fact, the implementation of the AbstractRoutingDataSource is very similar to my own. But perhaps i did not explain my question very well.

The application is not very complex, it's a webapp with several datasources. Each user has a property that maps to a datasource. With a servlet filter, the datasource alias is put on the thread by means of a ThreadLocal. My DataSource proxy switches between these datasources by figuring out the correct DataSource from the ThreadLocal. So, each user transaction concerns only a single datasource.

Now to be clear: consider following 2 situations.

a. The application manages the datasources (defined in the application context as org.apache.commons.dbcp.BasicDataSource instances for example)

b. the container (like Tomcat and WAS) manages the datasources (the datasources are defined in the application context by means of jee:jndi-lookup)

so, in the first situation, can i configure the Transaction manager as follows:
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSou rceTransactionManager">
<property name="dataSource" ref="dataSourceProxy"/>
</bean>

and in the second situation simply as follows:
<bean id="transactionManager" class="org.springframework.transaction.jta.JtaTran sactionManager"/>

?

i hope all this makes a bit sense..

thanks in advance.

Comment

I still miss the point why you need to switch between transaction managers. If there is only one resource involved a "simple" tm works as well. If you need to synchronize multiple resources, you need JTA and 2PC - which does not work with plain SQL DataSource. For the rest I just can hint to what I have written in the other thread.

Comment

I still miss the point why you need to switch between transaction managers. If there is only one resource involved a "simple" tm works as well.

I think this answers my question. I was not sure whether a single transaction manager would suffice for my application, where indeed no synchronization is needed between different datasources. Only one datasource is involved per "user operation", in which the single relevant datasource is resolved by means of a ThreadLocal variable.