Dynamic DataSource Routing

Posted on January 23rd, 2007 by Mark Fisher in Spring, 2.0, Data Access.

Mark Fisher

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:

package blog.datasource;

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<Item> getItems() {
      String query = "select name, price from item";
      return getSimpleJdbcTemplate().query(query, new ParameterizedRowMapper<Item>() {
            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:

package blog.datasource;

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:

public enum CustomerType {
   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:

<bean id="parentDataSource"
         class="org.springframework.jdbc.datasource.DriverManagerDataSource"
         abstract="true">
   <property name="driverClassName" value="org.hsqldb.jdbcDriver"/>
   <property name="username" value="sa"/>
</bean>
               
<bean id="goldDataSource" parent="parentDataSource">
   <property name="url" value="jdbc:hsqldb:hsql://localhost:${db.port.gold}/blog"/>
</bean>

<bean id="silverDataSource" parent="parentDataSource">
   <property name="url" value="jdbc:hsqldb:hsql://localhost:${db.port.silver}/blog"/>
</bean>

<bean id="bronzeDataSource" parent="parentDataSource">
   <property name="url" value="jdbc:hsqldb:hsql://localhost:${db.port.bronze}/blog"/>
</bean>

<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
   <property name="location" value="classpath:/blog/datasource/db.properties"/>
</bean>

Notice that I added a PropertyPlaceholderConfigurer so that I could externalize the port numbers in a "db.properties" file, like so:

db.port.gold=9001
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:

package blog.datasource;

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":

public class CustomerContextHolder {

   private static final ThreadLocal<CustomerType> contextHolder =
            new ThreadLocal<CustomerType>();
       
   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:

<bean id="catalog" class="blog.datasource.Catalog">
   <property name="dataSource" ref="dataSource"/>
</bean>

<bean id="dataSource" class="blog.datasource.CustomerRoutingDataSource">
   <property name="targetDataSources">
      <map key-type="blog.datasource.CustomerType">
         <entry key="GOLD" value-ref="goldDataSource"/>
         <entry key="SILVER" value-ref="silverDataSource"/>
      </map>
   </property>
   <property name="defaultTargetDataSource" ref="bronzeDataSource"/>
</bean>

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:

public class CatalogTests extends AbstractDependencyInjectionSpringContextTests {

   private Catalog catalog;

   public void setCatalog(Catalog catalog) {
      this.catalog = catalog;
   }

   public void testDataSourceRouting() {
      CustomerContextHolder.setCustomerType(CustomerType.GOLD);
      List<Item> goldItems = catalog.getItems();
      assertEquals(3, goldItems.size());
      System.out.println("gold items: " + goldItems);

      CustomerContextHolder.setCustomerType(CustomerType.SILVER);
      List<Item> silverItems = catalog.getItems();
      assertEquals(2, silverItems.size());
      System.out.println("silver items: " + silverItems);
       
      CustomerContextHolder.clearCustomerType();
      List<Item> 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!:

gold items: [gold item #1 (250.0), gold item #2 (325.45), gold item #3 (55.6)]
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.

 

57 responses

  1. Alef Arendsen says:

    Added on January 25th, 2007 at 1:04 pm -Quote

    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!

  2. Paul says:

    Added on January 26th, 2007 at 10:13 am -Quote

    This 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?

  3. David Kilzer says:

    Added on January 26th, 2007 at 1:16 pm -Quote

    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

  4. Mark Fisher (blog author) says:

    Added on January 26th, 2007 at 3:14 pm -Quote

    Dave,

    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.

  5. Robert Varga says:

    Added on January 27th, 2007 at 4:51 am -Quote

    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

  6. John Brinnand says:

    Added on January 27th, 2007 at 12:40 pm -Quote

    Great 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!

  7. Ganeshji Marwaha says:

    Added on January 29th, 2007 at 4:40 am -Quote

    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.

  8. Frank Groot says:

    Added on January 29th, 2007 at 7:54 am -Quote

    Mark,

    Thank you. Just what we needed to solve our problem….

    Regards,

    Frank

  9. Stefan Fleiter says:

    Added on January 29th, 2007 at 8:11 am -Quote

    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?

  10. Robert Varga says:

    Added on January 29th, 2007 at 9:52 am -Quote

    Stefan,

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

    BR,

    Robert

  11. Robert Varga says:

    Added on January 29th, 2007 at 9:54 am -Quote

    On January 29, 2007 at 9:52 am, Robert Varga said:

    Stefan,

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

    BR,

    Robert

    I mean to the LocalSessionFactoryBean.

    BR,

    Robert

  12. Stefan Fleiter says:

    Added on January 29th, 2007 at 11:56 am -Quote

    On January 29, 2007 at 9:54 am, Robert Varga said:

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

    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.

  13. Robert Varga says:

    Added on January 29th, 2007 at 12:21 pm -Quote

    Hi Stefan,

    yes, that's right.

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

    BR,

    Robert

  14. kathir says:

    Added on January 29th, 2007 at 6:51 pm -Quote

    Very good article. We were struck with the static data source in our code. Now this solves our problem..

  15. Moshe says:

    Added on January 30th, 2007 at 2:56 am -Quote

    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

  16. Dinesh says:

    Added on January 30th, 2007 at 11:32 am -Quote

    Mark, 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

  17. John Lindwall says:

    Added on February 2nd, 2007 at 4:09 am -Quote

    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.

  18. Robert Varga says:

    Added on February 2nd, 2007 at 8:01 am -Quote

    Hi 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

  19. Srini says:

    Added on February 20th, 2007 at 4:39 pm -Quote

    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.

  20. ivan says:

    Added on March 3rd, 2007 at 4:36 am -Quote

    edwedwedw

  21. G.Rajesh says:

    Added on March 21st, 2007 at 7:02 am -Quote

    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.

  22. Ken DeLong says:

    Added on April 5th, 2007 at 3:25 pm -Quote

    I'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?

  23. Mark Fisher (blog author) says:

    Added on April 5th, 2007 at 3:35 pm -Quote

    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.

  24. Ken DeLong says:

    Added on April 5th, 2007 at 4:35 pm -Quote

    Mark,

    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…

  25. paul says:

    Added on April 17th, 2007 at 11:30 am -Quote

    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.

  26. burt says:

    Added on April 23rd, 2007 at 10:14 am -Quote

    Hey 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

  27. Mark Fisher (blog author) says:

    Added on April 23rd, 2007 at 11:20 am -Quote

    That 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?

  28. burt says:

    Added on April 23rd, 2007 at 12:04 pm -Quote

    not sure how i would do that…I am a newbie to spring, Can you elabourate on this for me please. this would be appreciated. Thank you

    -B

  29. eric says:

    Added on April 24th, 2007 at 12:33 pm -Quote

    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

  30. Venkat says:

    Added on May 4th, 2007 at 12:02 pm -Quote

    For those trying to acheive this with Hibernate, Try Hibernate Shards.

  31. Oskar says:

    Added on May 11th, 2007 at 4:50 am -Quote

    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

  32. Oskar says:

    Added on May 11th, 2007 at 4:54 am -Quote

    Hello,

    I have the same challenge as Paul. I need to decide at execution time which connection should I use, and it must be populated from one table. Basically, I have username ranges and jdbc urls stored, so depending on the username who logins into the application, I should use a different connection.

    I have been researching for a lot of days, and the AbstractRoutingDataSource is the closest thing I found. But I don't know how to afford this.

    I will really appreciate any help which can point me on the right direction…

    Thank you very much for your time,
    Oskar

  33. Oskar says:

    Added on May 14th, 2007 at 7:53 pm -Quote

    Hello again,

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

    Thank you!,
    Oskar

  34. Oskar says:

    Added on May 14th, 2007 at 9:32 pm -Quote

    Oh sorry, I put the same comment twice and I don't know how to delete. Sorry for that, I was not intentionally…

  35. trung says:

    Added on May 22nd, 2007 at 8:00 am -Quote

    Mark

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

    Thanks

  36. kasim says:

    Added on May 23rd, 2007 at 9:23 am -Quote

    Hi,

    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 using Spring jdbc template.

  37. Vio says:

    Added on June 13th, 2007 at 5:42 pm -Quote

    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

  38. Vio says:

    Added on June 13th, 2007 at 5:44 pm -Quote

    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

  39. Sverre Moe says:

    Added on June 20th, 2007 at 7:43 am -Quote

    On April 5, 2007 at 3:35 pm, Mark Fisher said:

    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.

    Did you post a follow up?

  40. Sverre Moe says:

    Added on June 20th, 2007 at 8:02 am -Quote

    On April 5, 2007 at 4:35 pm, Ken DeLong said:

    Mark,

    I've just now gotten the AbstractRoutingDataSource to work….

    Overall, the RoutingDatasource seems like a winner to me.

    How did you configure it? I have a LocalSessionFactoryBean which reference the routing datasource. I am unsure if it will work.

  41. Sverre Moe says:

    Added on June 21st, 2007 at 6:40 am -Quote

    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?

  42. Berre says:

    Added on June 21st, 2007 at 8:20 am -Quote

    Loved reading about all of this :)

    I'm also trying to do something like this, but my requirement is not so specific as the crazy stuff you guys have to do!

    Enjoy The Java!

  43. Mark Fisher (blog author) says:

    Added on June 21st, 2007 at 10:39 am -Quote

    On June 21, 2007 at 6:40 am, Sverre Moe said:

    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?

    Sverre,

    The "targetDataSources" map can contain values that are actual DataSource references or Strings. In the latter case, the String will (by default) be treated as a JNDI-name.

    Hope that helps,
    Mark

  44. Ian Long says:

    Added on July 24th, 2007 at 3:19 pm -Quote

    I have been using this idea in my webapp successfully except for 1 case. I have a filter that I use to set the context holder to a value so my lookup is successful. Normally, this works nicely, except I've run across a problem using jasperreports. Basically, jasperreports is spawning a thread which ends up using my datasource to grab a connection, and the context hasn't been set, so it uses the default datasource, which in most cases isn't correct. 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?

    Any help is appreciated!

  45. Mark Fisher (blog author) says:

    Added on July 24th, 2007 at 3:34 pm -Quote

    On July 24, 2007 at 3:19 pm, Ian Long said:

    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?

    Would it be possible to store the customer type in an InheritableThreadLocal?

  46. Ian Long says:

    Added on July 24th, 2007 at 5:21 pm -Quote

    On July 24, 2007 at 3:34 pm, Mark Fisher said:

    On July 24, 2007 at 3:19 pm, Ian Long said:

    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?

    Would it be possible to store the customer type in an InheritableThreadLocal?

    I'm not familiar with InheritableThreadLocal, I'll have to do some research…thanks for the suggestion.

  47. Ian Long says:

    Added on July 24th, 2007 at 5:32 pm -Quote

    On July 24, 2007 at 3:34 pm, Mark Fisher said:

    On July 24, 2007 at 3:19 pm, Ian Long said:

    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?

    Would it be possible to store the customer type in an InheritableThreadLocal?

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

  48. Rajesh says:

    Added on November 13th, 2007 at 6:10 pm -Quote

    I am getting the "key-type" error in spring configuration xml file.

    here is the error :
    Attribute "key-type" must be declared for element type "map".

    am I missing something.

    Thanks,
    Rajesh

  49. Alberto Flores says:

    Added on December 6th, 2007 at 4:15 pm -Quote

    On April 5, 2007 at 3:35 pm, Mark Fisher said:

    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.

    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.

  50. Alberto Flores says:

    Added on December 6th, 2007 at 4:18 pm -Quote

    On April 5, 2007 at 3:35 pm, Mark Fisher said:

    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.

    Neil,

    Excellent discussion, however I'm wondering if there were any plans on following up (as quoted) on using different SessionFactory instances (although such approach would potentially consume a lot of resources). I'm currently looking into Hibernate Shards (as suggested by Venkat), but will be very interested in learning of an approach using Spring.

    Thanks,

    Alberto

  51. Chuck Canning says:

    Added on January 3rd, 2008 at 5:50 pm -Quote

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

  52. mike wu says:

    Added on January 15th, 2008 at 5:09 am -Quote

    spring is too complex now

  53. aapddeevv says:

    Added on March 2nd, 2008 at 6:29 pm -Quote

    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.

  54. Craig says:

    Added on March 18th, 2008 at 8:31 pm -Quote

    This is a useful article…

    http://affy.blogspot.com/2007/11/dynamic-datasource-via-spring-using.html

  55. Lior says:

    Added on April 7th, 2008 at 7:54 am -Quote

    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.

  56. cemil says:

    Added on May 6th, 2008 at 5:30 am -Quote

    Hi,

    Thanks for the article.

    I implemented some functionality that use HotSwappableTargetSource to switch from multiple datasources . The problem is when user1 goes into application he will use dataSource1 for finding some data .When user2 goes into application he will use dataSource2 to find some data , and I have only swappable target datasource , then when the user1 will try to made a new search he will find the datasource swap to datasource2 which is not good .
    How can I bound the datasource used from the user session?

    Or how can i prevent this from occuring by maybe thread binding the datasource switching ?

    Any suggestions ?
    From Cem

  57. Cemil says:

    Added on May 7th, 2008 at 5:19 am -Quote

    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

One trackback

Leave a Reply

Quote selected text