Hey Mark,

the green bar would have been even better. We all speak the universal language of the green bar, don't we .

Anyhow, good article. This will be useful for many people!

Neat feature and great blog entry!

Is there a reason why you didn't define a "BRONZE" map entry in the CustomerRoutingDataSource? It seems to me that if someone uses the code later and doesn't assume that BRONZE is the default, they will cause an exception (or use the wrong datasource) by doing:

CustomerContextHolder.setCustomerType(CustomerType.BRONZE);
List bronzeItems = catalog.getItems();
assertEquals(1, bronzeItems.size());
System.out.println("bronze items: " bronzeItems);

Dave

Actually, I have another usage pattern in mind, but I am not sure it is entirely correct:

We have a system, in which in certain use cases we use a single Oracle non-XA datasource, and in other use cases we use an Oracle datasource going to the same schema but with XA driver, and another datasource together with XA.

However, the DAOs accessing the Oracle datasource should probably work in both kinds of situation.

Now, if I made two separate DAO beans, one wired up with the XA datasource and the other with the non-XA datasource, the service beans would have to contain both DAO beans, and it would have to be distinguished case by case, whether a certain service method should use this or that DAO bean. And it is actually not the responsibility of the service bean to know, if it will be called together with another service bean which goes to a different datasource, but the use case calling both.

Therefore it would be useful if it could be found out in runtime, whether the XA or the non-XA Oracle datasource should be used, and the DAO beans would not need to be duplicated.

So the AbstractRoutingDataSource could be extended to find out, if there is a current XA transaction in place, in which case the XA datasource is used, or not, in which case the non-XA datasource could be used.

The question is, are there any hidden problems with this idea?

Best regards,

Robert

Very interesting blog.

This will be very useful in an ASP model where each customer is given a separate database. Using this model, the data-source for individual customers can be switched transparently. All they will need to do is to extend AbstractRoutingDataSource that gets the logged-in user's corresponding data-source.

Thanks. Enlighten us with more like this.

–>Ganesh.

I've written very similar code to solve this problem some time ago. A missing part to a solution would be an AbstractRoutingSessionFactory for Hibernate. Is something like this planed or are you interested in a code contribution for this?

[quote comment="9759"]Stefan,

Why would you need that? I expect, you just need to specify the routing data source to the session factory.

BR,

Robert[/quote]

I mean to the LocalSessionFactoryBean.

BR,

Robert

Hi Stefan,

yes, that's right.

However, could you not divide the pk space between the different sessions?

BR,

Robert

Hi there,

I was also looking for a solution for this kind of problem and the blog entry helps a lot, so thanks

Unfortunately, as Stefan noted, when working with Hibernate, the solution of just supplying a routing data source to the LocalSessionFactoryBean is problematic, especially because of possible 2nd level cache collisions. In my specific scenario, we have perhaps dozens of different DBs and we can't afford to have a session factory for each one due to the high memory consumption of this object. It seems like we're going to have to reuse the same session factory (obviously assuming all DBs share the same tables structure).

I started thinking about having different cache regions: one region per cached class/query and per DB.

If anyone can help with a better idea, or wants to shout at me "STOP what you're trying to do, don't go there since it is very dangerous" – all comments are welcome

Thanks,
Moshe

Excellent post! Well written and fun to follow along. Was the ThreadLocal bit thrown in just for fun? There is no *requirement* to use a ThreadLocal for the discriminator, right? I've alway been steered away from using ThreadLocal — isit a common solution?

We too were hoping to use this technique with Hibernate but apparently this is problematic. Too bad.

Hi Mark,
Your post is very informative. I have a single database with multiple schemas, where users each have their own separate sets of tables through schema.
Database-> DB Schema
GoldDB -> Project1_Schema, Project2_Schema etc
Each Schema will have the same set of DB Tables. i.e
Project1_Schema -> Catalog, Product, Item
Project2_Schema -> Catalog, Product, Item
Public_Schema -> Users

Some users will have access to Product_Schema1. Some will have access to Product_Schema2. How can I create a dynamic data source connection for the webapp.

Thanks for your help.

Regards,
Srini.

what do i do if i don't know the number of datasources. Not like the three customer types.I need to create the datasources during run time.

Nice article. I'm new to Spring but that made sense, unlike so many other things I am reading. I am trying to do something similar except that my databases are defined in a table and fetched when the user logs in: the user picks one from the list (or defines a brand new one).

Do you think your approach would work for this, or do the datasource deftns really need to be known at initialisation time? I've been reading about HotSwappableTargets and Proxies but don't understand that yet.

We want to use a single SessionFactory for multiple schemas without comprimising the second-level cache. I read Moshe's post on setting up a separate cache region per cached class/query and per DB.

We want to do similar thing and we're thinking about adding the schema's name to the cache region name. Does anyone have any idea of how to achieve this?

Any idea is appreciated.

-E

Hello,

I have the same challenge as Paul, but I don't really know how to do it. Basically, I will have a table with a relationship between usernames and jdbc urls. How can I tell at execution time what connection should be used? that can be done using AbstractRoutingDataSource?

I spend a lot of days trying to figure out how to do this, and your example is the closest thing I found, but I don't know how to go on.

I really appreciate any help you could bring to me.

Thank you for your time,
Oskar

Hello again,

I'm really getting desperated with this… please, can someone tell me how to approach this?

Thank you!,
Oskar

Mark

Can we switch several datasources within a thread (context)? How about the transaction manager in this case?

Thanks

Hello,

I'm new to Spring and trying to use AbstractRoutingDataSource.

I'm getting the following error when application context starts to initialize:

(Error) Attribute 'key-type' used but not declared.

I have to use a specific data source based on the year and I have something like this:

……………………………………..

………………………….

Am I missing something?

Thanks,
Vio

[quote comment="17569"]Ken, it is true that you could avoid collisions through globally-unique identifiers. However,

it seems that the ideal solution would involve dynamically routing to different SessionFactory instances. I plan to post a follow-up that addresses that within the next few days.[/quote]

Did you post a follow up?

I guess this articles comments are dead. No activity for a while
Well I'll past my last question.

After implementing the AbstractRoutingDataSource I across a problem which was not in the article. I have 8 databases which my application connects to determined by the users choice. It seems like I get a "Failed to look up JNDI DataSource with name myDataSource1". This problem I solves by creating a JNDI named myDataSource01 in WebSphere(My J2EE container) for database 1. The other datasources did not need a JNDI name.

Why is this?

[quote comment="36083"][quote comment="36082"]Can anyone think of a good way to handle scenarious like this, where I can't call the equivalent of setCustomerType(), because I don't have access to a spawned thread?[/quote]

Would it be possible to store the customer type in an InheritableThreadLocal?[/quote]

Thanks Mark! Using an InheritableThreadLocal does indeed solve the problem – it works like a charm.

[quote comment="17569"]Ken, it is true that you could avoid collisions through globally-unique identifiers. However,

it seems that the ideal solution would involve dynamically routing to different SessionFactory instances. I plan to post a follow-up that addresses that within the next few days.[/quote]

Are there any plans on responding on this? I'm taking a look (as suggested by Venkat) to look into Hibernate Shards, but will be interested to see an approach using Spring.

Did you ever post the Hibernate version. Also, would this approach work with annotation driven transactions?

I saw some notes about creating data sources programmatically in your application (such as when the data sources are user configured) and still be able to create your session factories, which are dependent on the data source, in the proper way. I blogged on this awhile ago. Another way to handle this is described here using hierarchical contexts. The key thought is that by using parent context's you can control the creation of a group of beans as a creation unit. The data source layer is a parent layer to the session factories and you merely create the data source context first, configure it, then create a context with the session factory with the data source context as a parent: blog location.

I'll give the dynamic datasource routing approach a try as well.

Hi, great article.

I've posted a question regarding this method on the board:
http://forum.springframework.org/showthread.php?t=52224

would be happy if anyone can comment

thanks.

You have this comment on youre blog:
"In reality, the 'context' would likely hold more information about the customer. Also note that if you are using Acegi, then you could retrieve some information from the userDetails."

Can you show me how to do this…a small example or something.

Best regards

Hi Mark,

I have a doubt in using multiple databases depends on the users preferences, In my application the user is allowed to change database runtime. first the default database is to be loaded after user login there is a drop down list which shows different databases from which the user can choose one. my applicationContext.xml

${db.driverClassName}

${db.1.url}

${db.1.username}

${db.1.password}

I need to use a database at a time
All I need is to change the url only all other property are same …

I try few methods to change the db but all in vain

thanks in advance

Hi Mark,

I try to use your sample to switch databases but its loading the same database,

my applicationContext.xml

this is my java class which sets the new database

package com.test.view.bean;

import org.springframework.util.Assert;

public class ChangeContextHolder {
public enum DataBaseType {
DEMO1,
DEMO2,
DEMO3,

}

private static final ThreadLocal contextHolder =
new ThreadLocal();
public static void setDataBaseType(DataBaseType dataBaseType) {
Assert.notNull(dataBaseType, "DataBaseType cannot be null");
contextHolder.set(dataBaseType);
}

public static DataBaseType getDataBaseType() {

return (DataBaseType)contextHolder.get();

}

public static void clearDataBase() {
contextHolder.remove();
}

}

this is my class extending the AbstractRoutingDataSource

package com.ncsts.view.bean;
import java.util.Map;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class RoutingDataSource extends AbstractRoutingDataSource{
private Map targetDataSources;
private Object defaultTargetDataSource;

@Override
protected Object determineCurrentLookupKey() {

return ChangeContextHolder.getDataBaseType();
}

}

and I set the new database from my bean class, every thing seems to be ok but its not switching the databaase its loading the same database first loaded.

thanks in advance

Hi Mark,

You code (blogged) seems promising, but I tried in my case of associating AbstractRoutingDataSource with LocalSessionFactoryBean, but still it's not swapping..

Here is a glimpse of what I have done..

//This class extends AbstractRoutingDataSource

//Gives the javax.sql.DataSource references

public class EtsAuthDataSourceSwapper extends AbstractRoutingDataSource {

@Override
public Object determineCurrentLookupKey () {
return DataSourceContextHolder.getDataSourceName();
}
}

public class DataSourceContextHolder {

private static final ThreadLocal dsNameHolder = new ThreadLocal ();

public static void setDataSourceName(DataSourceName dsName) {
Assert.notNull(dsName, "DataSourceName cannot be null");
dsNameHolder.set(dsName);
}

public static DataSourceName getDataSourceName() {
return dsNameHolder.get();
}

public static void removeDataSourceName() {
dsNameHolder.remove();
}
}

//Utility method to swap database/db at runtime (currently hardcoded)
public class GenericBO {

public void swapDataSource(String datasourceName) {
System.out.println("Calling new DS, dude..Moment of truth..");
DataSourceContextHolder.setDataSourceName(DataSourceName.ETSAUTHPRODGLOBAL);
}
}

public enum DataSourceName { ETSAUTHQAGLOBAL, ETSAUTHPRODGLOBAL;
}

Its almost on similar lines, of what you have suggested except that I have not used DriverManagerDataSource but rather have my own factory method to gimme DataSource objects.

Please suggest what am I doing wrong, as I might be very near to the destination, but still could't realise the gap..

It ate my entries again..seems it doesnt accept xml data..trying last time.. Apologies!

Hello Mark, Can you help me with the following problem:
I have two datasources configured:

and I want to switch two from asapLoggingDataSourcePrimary to asapLoggingDataSourceSecondary if asapLoggingDataSourcePrimary is not available. I thought somthing like this but I didn't get it working:

I am a new Spring user. Please help me.

I guess the only problem with this approach using DriverManagerDataSource is as the Spring API doc states "…NOTE: This class is not an actual connection pool; it does not actually pool Connections. It just serves as simple replacement for a full-blown connection pool, implementing the same standard interface, but creating new Connections on every call… Useful for test or standalone environments outside of a J2EE container… ". Since I'm currently using Apache commons DBCP I wouldn't want to lose the connection pool in a production application.

Thanks Mark for the excellent article,

I've implemented this now and as you say it works well with the Apache DBCP DataSource instead of DriverManagerDataSource too (Spring is nice and flexible). Next for me is to decide if I want to Hibernate this part or if I'll just stick with Spring…

Hi Mark,

I have 2 issues now:
1) One database two or more scheema i need to use.
2) two or more database with two or more scheema i need to use.

how can i solve this? i am totally new to Hibernate And Spring.

Hi, This is good article. Simple solution than JTA.

But this solution must be uses very carefully in multi-thread context. In SomeBOImpl, a method is exist to below.

public void printAllItems() {
printGoldItems();

printSilverItems();

printBronzeItems();
}

public void printGoldItems() {
CustomerContextHolder.setCustomerType(CustomerType.GOLD);
List goldItems = catalog.getItems();
assertEquals(3, goldItems.size());
System.out.println("gold items: " goldItems);
}

public void printSilverItems() {
CustomerContextHolder.setCustomerType(CustomerType.SILVER);
List silverItems = catalog.getItems();
assertEquals(2, silverItems.size());
System.out.println("silver items: " silverItems);
}

public void printBronzeItems() {
CustomerContextHolder.clearCustomerType();
List bronzeItems = catalog.getItems();
assertEquals(1, bronzeItems.size());
System.out.println("bronze items: " bronzeItems);
}

And two anonymous threads is runned printAllItems() after get SomeBOImpl in spring-context using BeanFactory at almost same time.

Is catalog.getItems() method run correctly as our wish?

Who is ThreadLocal's key? SomeBOImpl? or anonymous thread?

Hi,

I tried this approach and it works just fine but as we use Hibernate, the second level hibernate cache did not work. One of the possible solutions to this is to create separate session factory for each data source. But as the session factory object is quite resource demanding and the schema of the data sources were every time the same I tried to find a way around it. In the end we ended up by wrapping CacheProvider and adding our wrapper for the Cache to the system.

The main idea is to prefix cache entries by the data source identificators.

In steps:
1. Implement your AbstractRoutingDataSource and supply it to your Hibernate session factory as described in the blog entry.
2. Implement a wrapper to the CacheProvider which will contain field with CacheProvider implementation supplied from the Spring configuration for instance.
3. The methods of your CacheProvider are just delegating work to the underlying cache provider except for the buildCache method which creates the CacheWrappers:
public Cache buildCache(String name, Properties properties) throws CacheException {
Cache cache = cacheProvider.buildCache(name, properties);
if (!(cache instanceof OKbaseCache)) {
cache = new CacheWrapper(cache, dataSourceProvider);
}
return cache;
}
4. Implement CacheWrapper – it implements interface org.hibernate.cache.Cache and it prefixes the keys of elements which are put/retrieved from the cache by a context based information:
for instance:
public class CacheWrapper implements Cache {

private Cache underlyCache;

public CacheWrapper(Cache underlyCache) {
this.underlyCache = underlyCache;
}

@Override
public void clear() throws CacheException {
underlyCache.clear();
}
….

@Override
public Object get(Object key) throws CacheException {
return underlyCache.get(CustomerContextHolder.getCustomerType() key));
}

Every use of the "key" must be prefixed correspondingly. Of course it might be wise to define your own key which would work with the prefix (data source identification) and the "Object key" and would define correctly hashCode and equals…

In the end you have to inject your CacheProvider into the SessionFactory.

The Hibernate second level cache will work as everytime a user connects to a given data source, he consults the region of cache prefixed with the data source identification, thus objects with same ID from different data sources would not clash.

Note, from Hibernate 3.3 the use of Cache interface is deprecated and we should use RegionFactory instead. But as most caches still work on the base of Cache interface, I guess it can wait to rewrite it to the version using RegionFactory rather than CacheProvider…

Great post. The content here is really very informative.

This is a good is a good site so i like it.
Gold Profit

Earn online easy money at your door step for about 10$ on just one So go ahead and click here click hurry up!(smart way to make fast money).

Thank you.

Excellent article. Flexibility of Spring demonstrated yet again.

Hello,

very good article. The implementation works.
But with Hibernate I can't change the database.
Always the default database is used.

Is there an article/blog entry/howto like this one available which shows a
similar implementation which works with Hibernate?

Thanks a lot.

Regards,
Stephan

Hi,

I am trying this example but not getting the exact flow…can any body guide more….

Thanks in advance…

—————
Gyana

Im getting a null pointer exception:
============================================
//DAO
public class TestDAO extends SimpleJdbcDaoSupport {

public List<Map> getDomains() {
String query = "select 1 from dual";
return getSimpleJdbcTemplate().queryForList(query);
}
}
============================================
//Routing
public class RoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return SessionContextHolder.getDBServer();
}
}
============================================
//controller
@Controller
@RequestMapping(value="/test")
public class HandleRequest {

@RequestMapping(method=RequestMethod.GET)
public String handle(Model model){

TestDAO testDAO = new TestDAO();

SessionContextHolder.setDBServer("server1");

List domains = testDAO.getDomains();

model.addAttribute("domains", domains);
return "test1";
}
}
============================================

============================================

And this is the error

java.lang.NullPointerException
springtest.TestDAO.getDomains(TestDAO.java:15)

Hey I can access to a different databases, how can do it? thanks

Hi Mark,
Please let us know if you figure out the Hibernate SessionFactory usage with your approach. You promised long time back as quoted below.
Hope you are still actively following your invention feedback.

[Mark Fisher (blog author) says:
Added on April 5th, 2007 at 3:35 pm

Ken, it is true that you could avoid collisions through globally-unique identifiers. However, it seems that the ideal solution would involve dynamically routing to different SessionFactory instances. I plan to post a follow-up that addresses that within the next few days.]

Hi,
The article is interesting!. But it seems hard coded datasources not dynamically. May be i have misunderstood. Anyway, how will you pass values to the place of database configuration.

Hope you reply soon!

I read your blog. Very interested blog. Greeting from India.

I'm trying to implement data source routing with a org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean. It throws an BeanCreationException saying that the entityManagerFactory cannot resolve reference to 'parentDataSource' because it is abatract. Is there a way to use an entityManager with an abstract dataSource?

Is it somehow possible to share a transaction between the datasources with this approach?

So far even though I select the other datasource, everything executes on the datasource transaction was started on.