Announcement Announcement Module
No announcement yet.
Proxied Datasource (Multiple Databases) +TheadLocal Question Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Proxied Datasource (Multiple Databases) +TheadLocal Question

    Hi all,

    I've read through this forum regarding this topic and I found some entries:

    Proxied Datasource (without Hibernate)

    Hibernate and multiple databases
    I have an app that connects to multiple datasources but the tables underneath are exactly the same.
    This is also my exact Problem, where I'm looking for a solution.

    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.
    My Questions:
    has anyone made some experience with this way?
    Can someone provide some more details howto set up this configuration?
    Or give a hint where to look for some more infos?
    Are there problems, maybe because of connection pooling, because of using only one SessionFactory?
    Maybe "user1" gets a pooled connection where he will access the "user2" - database?

    Searching in Hibernate Forums, the answer is clear:
    one SF for each Database. :-(
    So from this direction I won't get any help...

    I need this configuration to get my App (the database) multi-client ready
    so i have a "main-table" (users) and for each user a single db.

    Thank you in advance!

    best regards
    Last edited by robyn; May 14th, 2006, 08:44 PM.

  • #2
    Spending some more hours investigating forums and "internet" I got some more information concerning multiple databases.
    This thread:
    "Setting Schema for Each DataSource.getConnection() Call"
    is worth reading.

    The Solution now I use for me is simpler,
    I use the TransactionAwareDSProxy as DataSource for Hibernate 3
      <bean id="myDataSource" class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">
          <property name="targetDataSource"><ref local="appDataSourceDefault"/></property>
    and I have specified all datasources I need:
    	<bean id="appDataSourceDefault" class="org.apache.commons.dbcp.BasicDataSource">
    	<bean id="appDataSource1" class="org.apache.commons.dbcp.BasicDataSource">
    	<bean id="appDataSource..n" class="org.apache.commons.dbcp.BasicDataSource">
    within businessLogic I simply do the following:

            ds1 = (BasicDataSource)ctx.getBean("appDataSourceDefault");
            ds2 = (BasicDataSource)ctx.getBean("appDataSource2");
            dsProxy = (TransactionAwareDataSourceProxy)ctx.getBean("myDataSource");
    //change DS 
    And this works with hibernate 3 and mysql 4.x (innodb)
    I run my transaction-unit-Test over it and this works well!
    (I don't change DS within transactions, this is not needed for me, and I haven't tried it, but I think it shouldn't work)

    Is this a preferrable way?
    Or is there anything else to attend?

    Is this "threadsave", e.g. multiple users can use different DataSources
    Last edited by robyn; May 14th, 2006, 08:38 PM.


    • #3
      problem with concurrency

      Running some concurrency tests I found out,
      that my solution will not work correctly, because the DS-Proxy seems to be not thread-save.
      If I have 2 http-sessions both sessions change their DS frequently to load data, sometimes I get an error, that session one reads results from the other

      How do I have to change my setup
      (org.springframework.jdbc.datasource.TransactionAw areDataSourceProxy)
      for getting clean results?

      I read about Threadlocal-pattern, but how can I set up this correctly
      for DataSource?



      • #4
        Bad links


        The 3 forum threads referred to by Hans are unreachable :

        Proxied Datasource (without Hibernate)

        Hibernate and multiple databases

        "Setting Schema for Each DataSource.getConnection() Call"

        Dealing with multiple Hibernate datasources with Spring is a topic I am currently investigating.

        If anyone has working URLs or copied mirrors, that would be nice.

        - Alvin


        • #5
          any progress?

          any progress on the above issue?


          • #6
            I am Assuming that you are using tomcat and you may need distributed transaction

            Hope this link solves your problem

            Integrating JOTM into Tomcat 5.5.x and configuring 2PC with Spring Category: Spring Live



            • #7
              To avoid the concurrency problem use a threadlocal for storing the datasource. use a filter or an interceptor that, at the beginning of the thread (the web request or whatever triggers the thread) will decide which datasource will be used and set the value of the thread local. The target source will simply return the value of the thread local.
              Btw, Spring already provides some nice TargetSource implementations out of the box under