Dynamic DataSource Routing
Spring 2.0.1 introduced an AbstractRoutingDataSource. I believe that it deserves attention, since (based on frequent questions from clients) I have a hunch that there are quite a few 'home-grown' solutions to this problem floating around. That combined with the fact that it is trivial to implement yet easy to overlook, and now I have several reasons to dust off my corner of the Interface21 team blog.
The general idea is that a routing DataSource acts as an intermediary - while the 'real' DataSource can be determined dynamically at runtime based upon a lookup key. One potential use-case is for ensuring transaction-specific isolation levels which are not supported by standard JTA. For that, Spring provides an implementation: IsolationLevelDataSourceRouter. Consult its JavaDoc for a detailed description including configuration examples. Another interesting use-case is determination of the DataSource based on some attribute of the current user's context. What follows is a rather contrived example to demonstrate this idea.
First, I created a Catalog that extends Spring 2.0's SimpleJdbcDaoSupport. That base class only requires an instance of any implementation of javax.sql.DataSource, and then it creates a SimpleJdbcTemplate for you. Since it extends JdbcDaoSupport, the JdbcTemplate is also available. However, the "simple" version provides many nice Java 5 conveniences. You can read more detail about that in this blog by Ben Hale.
Anyways, here's the code for my Catalog:
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
public class Catalog extends SimpleJdbcDaoSupport {
public List getItems() {
String query = "select name, price from item";
return getSimpleJdbcTemplate().query(query, new ParameterizedRowMapper() {
public Item mapRow(ResultSet rs, int row) throws SQLException {
String name = rs.getString(1);
double price = rs.getDouble(2);
return new Item(name, price);
}
});
}
}
As you can see, the Catalog simply returns a list of Item objects. The Item just contains name and price properties:
public class Item {
private String name;
private double price;
public Item(String name, double price) {
this.name = name;
this.price = price;
}
public String getName() {
return name;
}
public double getPrice() {
return price;
}
public String toString() {
return name + " (" + price + ")";
}
}
Now, in order to demonstrate multiple DataSources, I created an enum for different Customer types (representing "levels" of membership I guess), and I created three different databases - so that each type of customer would get a distinct item list (I did mention that this would be a contrived example didn't I?). The important thing is that each of the databases are equivalent in terms of the schema. That way the Catalog's query will work against any of them - just returning different results. In this case, it's just the "item" table with 2 columns: name and price. And… here is the enum:
BRONZE,
SILVER,
GOLD
}
It's time to create some bean definitions. Since I have 3 datasources where everything is the same except for the port number, I created a parent bean so that the shared properties can be inherited. Then, I added the 3 bean definitions to represent the per-CustomerType DataSources:
class="org.springframework.jdbc.datasource.DriverManagerDataSource"
abstract="true">
name="driverClassName" value="org.hsqldb.jdbcDriver"/>
name="username" value="sa"/>
>
id="goldDataSource" parent="parentDataSource">
name="url" value="jdbc:hsqldb:hsql://localhost:${db.port.gold}/blog"/>
>
id="silverDataSource" parent="parentDataSource">
name="url" value="jdbc:hsqldb:hsql://localhost:${db.port.silver}/blog"/>
>
id="bronzeDataSource" parent="parentDataSource">
name="url" value="jdbc:hsqldb:hsql://localhost:${db.port.bronze}/blog"/>
>
class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
name="location" value="classpath:/blog/datasource/db.properties"/>
>
Notice that I added a PropertyPlaceholderConfigurer so that I could externalize the port numbers in a "db.properties" file, like so:
db. port. silver= 9002
db. port. bronze= 9003
Now things start to get interesting. I need to supply the "routing" DataSource to my Catalog so that it can dynamically get connections from the 3 different databases at runtime based on the current customer's type. As I mentioned, the AbstractRoutingDataSource can be rather simple to implement. Here is my implementation:
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;
public class CustomerRoutingDataSource extends AbstractRoutingDataSource {
@Override
protected Object determineCurrentLookupKey() {
return CustomerContextHolder.getCustomerType();
}
}
…and the CustomerContextHolder simply provides access to a thread-bound CustomerType. 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. For this example, it's just the customer "type":
private static final ThreadLocal contextHolder =
new ThreadLocal();
public static void setCustomerType(CustomerType customerType) {
Assert.notNull(customerType, "customerType cannot be null");
contextHolder.set(customerType);
}
public static CustomerType getCustomerType() {
return (CustomerType) contextHolder.get();
}
public static void clearCustomerType() {
contextHolder.remove();
}
}
Finally, I just need to configure the catalog and routing DataSource beans. As you can see, the "real" DataSource references are provided in a Map. If you provide Strings, they can be resolved as JNDI names (or any custom resolution strategy can be provided - see the JavaDoc). Also, I've simply set the 'bronzeDataSource' as the default:
name="dataSource" ref="dataSource"/>
>
id="dataSource" class="blog.datasource.CustomerRoutingDataSource">
name="targetDataSources">
key-type="blog.datasource.CustomerType">
key="GOLD" value-ref="goldDataSource"/>
key="SILVER" value-ref="silverDataSource"/>
>
>
name="defaultTargetDataSource" ref="bronzeDataSource"/>
>
Of course I'd like to see this working, so I've created a simple test (extending one of Spring's integration test support classes). I added 3 items to the "gold" database, 2 items to the "silver" database, and only 1 item to the "bronze" database. This is the test:
private Catalog catalog;
public void setCatalog(Catalog catalog) {
this.catalog = catalog;
}
public void testDataSourceRouting() {
CustomerContextHolder.setCustomerType(CustomerType.GOLD);
List goldItems = catalog.getItems();
assertEquals(3, goldItems.size());
System.out.println("gold items: " + goldItems);
CustomerContextHolder.setCustomerType(CustomerType.SILVER);
List silverItems = catalog.getItems();
assertEquals(2, silverItems.size());
System.out.println("silver items: " + silverItems);
CustomerContextHolder.clearCustomerType();
List bronzeItems = catalog.getItems();
assertEquals(1, bronzeItems.size());
System.out.println("bronze items: " + bronzeItems);
}
protected String[] getConfigLocations() {
return new String[] {"/blog/datasource/beans.xml"};
}
}
…and rather than simply taking a screenshot of the green bar, you'll notice I've provided some console output - the results!:
silver items: [silver item #1 ( 25. 0 ), silver item #2 ( 15. 3 ) ]
bronze items: [bronze item #1 ( 23. 75 ) ]
As you can see, the configuration is simple. Better still, the data-access code is not concerned with looking up different DataSources. For more information, consult the JavaDoc for AbstractRoutingDataSource.
扫一扫
625
到【灌水乐园】发言
Alef Arendsen says:
Added on January 25th, 2007 at 1:04 pm -QuoteHey 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!
Paul says:
Added on January 26th, 2007 at 10:13 am -QuoteThis is great, but what about enterprise connectors? Can we see an example how to retrieve different javax.resource.cci.Connection(Factory) instances by a key?
David Kilzer says:
Added on January 26th, 2007 at 1:16 pm -QuoteNeat 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
Mark Fisher (blog author) says:
Added on January 26th, 2007 at 3:14 pm -QuoteDave,
Thanks for pointing that out. Since the 'bronzeDataSource' is the default, it will use the correct database for the example you provided. In fact, it would use 'bronzeDataSource' for any un-mapped CustomerType (or null as in the example in the blog). That said, it would have probably been clearer if I had mapped BRONZE and then created a fourth dataSource called "guestDataSource". The intent was to demonstrate the usage of a default.
Robert Varga says:
Added on January 27th, 2007 at 4:51 am -QuoteActually, 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
John Brinnand says:
Added on January 27th, 2007 at 12:40 pm -QuoteGreat entry - it will be very useful for us. Since our db schema goes through changes, we are faced with the perennial problem of migrating data from one schema to another.
To solve this problem we developed a db migration tool, but it naturally, it requires constant re-configuration to point to our various datasources. Of course there are many ways to solve this, but after reading this entry, I suspect that using dynamic datasource routing is the best solution so far.
Thanks!
Thanks!
Ganeshji Marwaha says:
Added on January 29th, 2007 at 4:40 am -QuoteVery 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.
Frank Groot says:
Added on January 29th, 2007 at 7:54 am -QuoteMark,
Thank you. Just what we needed to solve our problem….
Regards,
Frank
Stefan Fleiter says:
Added on January 29th, 2007 at 8:11 am -QuoteI'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?
Robert Varga says:
Added on January 29th, 2007 at 9:52 am -QuoteStefan,
Why would you need that? I expect, you just need to specify the routing data source to the session factory.
BR,
Robert
Robert Varga says:
Added on January 29th, 2007 at 9:54 am -QuoteOn January 29, 2007 at 9:52 am, Robert Varga said:
I mean to the LocalSessionFactoryBean.
BR,
Robert
Stefan Fleiter says:
Added on January 29th, 2007 at 11:56 am -QuoteOn January 29, 2007 at 9:54 am, Robert Varga said:
Robert,
many thanks for asking the question, I did forget to mention this.
You need different session factories for hibernate if the database instance is part of the primary key. In this case having a single session factory would defeat hibernates' view of object identity and would deliver wrong results if you activate second level caching.
Robert Varga says:
Added on January 29th, 2007 at 12:21 pm -QuoteHi Stefan,
yes, that's right.
However, could you not divide the pk space between the different sessions?
BR,
Robert
kathir says:
Added on January 29th, 2007 at 6:51 pm -QuoteVery good article. We were struck with the static data source in our code. Now this solves our problem..
Moshe says:
Added on January 30th, 2007 at 2:56 am -QuoteHi 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
Dinesh says:
Added on January 30th, 2007 at 11:32 am -QuoteMark, Thanks for the blog…we are using the same approach to support multiple data sources.
we also want to support multiple schema for hibernate session factory implementation.
Is there anything new in Spring 2.0.1 ?
Thank you,
Dinesh
John Lindwall says:
Added on February 2nd, 2007 at 4:09 am -QuoteExcellent 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.
Robert Varga says:
Added on February 2nd, 2007 at 8:01 am -QuoteHi John,
ThreadLocal is not strictly required, but I don't really see another way of passing occurence-related information to the AbstractRoutingDataSource.getConnection() method in a thread-safe manner which does not involve ThreadLocal behind the scenes.
A ThreadLocal is a perfectly correct tool to use, only you have to ensure that you clean it up, once you are done with it, otherwise you can leak memory and classloaders.
Best regards,
Robert
Srini says:
Added on February 20th, 2007 at 4:39 pm -QuoteHi 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.
ivan says:
Added on March 3rd, 2007 at 4:36 am -Quoteedwedwedw
G.Rajesh says:
Added on March 21st, 2007 at 7:02 am -Quotewhat 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.
Ken DeLong says:
Added on April 5th, 2007 at 3:25 pm -QuoteI'm trying to understand if this is irretrievably broken for Hibernate. If I have multiple partitioned databases, with the exact same schema in each one, and if the id's of the objects are kept unique across all the databases (a good idea if you ever plan on migrating data from one partition to another) would this solution not work? I'm not understanding the second-level cache collision; if the ids of the objects are unique wouldn't this be ok?
Mark Fisher (blog author) says:
Added on April 5th, 2007 at 3:35 pm -QuoteKen, 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.
Ken DeLong says:
Added on April 5th, 2007 at 4:35 pm -QuoteMark,
Thanks for the quick answer! I've just now gotten the AbstractRoutingDataSource to work with Hibernate in our partitioned database scheme. I've also tested out the solution that you proposed above: a different SessionFactory for each database, with duplicate service configs (dao1, dao2, etc) and a "routing" interceptor that wraps the service and picks the correct dao to route to.
This was my first design choice, but there are several problems: many SessionFactories is expensive in terms of resources and startup time. It's also a configuration mess, with bunches of SessionFactories, dao defns, and txn managers running around. (We're not [yet] using JTA; maybe we should). Also, I've implemented my routing interceptor with @AspectJ style (which is *very* nice), but I cannot force it to allow the txn interceptor to bind *first* - it insists on wrapping the dao directly, which means that the Session/txn starts up *before* I can select a datasource, and that's bad.
Overall, the RoutingDatasource seems like a winner to me. What makes you think that the "multiple SessionFactory" style is superior? Perhaps I'm missing something crucial…
paul says:
Added on April 17th, 2007 at 11:30 am -QuoteNice 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.
burt says:
Added on April 23rd, 2007 at 10:14 am -QuoteHey I ran into some problems using this method. I have 3 datasources, devDS, QADS, prodDS. My app points to devDS by default. Now using this method i was able to change DS's on the fly. However, I recognized a problem. I would switch to a DS say QADS, and then navigate around and realize that i am back at the default devDS. I can't seem to figure out how to fix this. I think what is happening is that a new thread is being started that uses automatically uses the default DS. How would i go about fixing this?
-B
Mark Fisher (blog author) says:
Added on April 23rd, 2007 at 11:20 am -QuoteThat sounds like you are losing the thread-bound key info. Depending on your situation, you may be able to resolve it by using an InheritableThreadLocal?
burt says:
Added on来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7845854/viewspace-1007261/，如需转载，请注明出处，否则将追究法律责任。