Spring + JPA + Hibernate and multiple Database connection

[Pau Ruiz]

I have a problem connecting more than one database, I have spent 2 days without succes. I have read through different forums without getting results. I do not understand how something as simple as connecting to two databases so difficult when using Spring + JPA + Hibernate.

I need two datasource and can make transactions between them. I put my settings for only one connection.

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.driverClassName}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" p:dataSource-ref="dataSource" p:persistenceXmlLocation="classpath:/resources/config/persistence/persistence.xml" p:jpaVendorAdapter-ref="jpaVendorAdapter"> <property name="jpaPropertyMap"> <map> <entry key="hibernate.generate_statistics" value="${persistence.hibernate.generate_statistics:false}" /> <entry key="hibernate.jdbc.use_scrollable_resultset" value="${persistence.hibernate.jdbc.use_scrollable_resultset:true}" /> </map> </property> </bean> <bean id="jpaVendorAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> <property name="showSql" value="${persistence.showSQL}" /> <property name="generateDdl" value="${persistence.generateDdl}" /> <property name="databasePlatform" value="${persistence.dialect}" /> </bean> <tx:advice id="txAdvice" transaction-manager="txManager"> <tx:attributes> <tx:method name="get*" read-only="true"/> <tx:method name="*"/> </tx:attributes> </tx:advice> <aop:config> <aop:pointcut id="serviceOperation" expression="execution(* setting.springjpa.service.*Service.*(..))"/> <aop:advisor advice-ref="txAdvice" pointcut-ref="serviceOperation"/> </aop:config> <bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager" p:entityManagerFactory-ref="entityManagerFactory"> </bean>

I read that you could create two LocalContainerEntityManagerFactoryBean, this always gives an exception in the application startup:

org.springframework.beans.factory.NoSuchBeanDefinitionException: No unique bean of type [javax.persistence.EntityManagerFactory] is defined: expected single bean but found 2

And I think if I have also created two EntityManagerFactory to create two JpaTransactionManager and that's not the goal.

In my search for internet or watching Spring documentation to create multiple persistence units must be used org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager, I've tried without success, how to tell the DAO datasource to use?

In other post I read said that Spring with JPA can only connect to a datasource to connect to more than one is to use JNDI, is that true?

I am new to Spring and whether the approach has to be one or it is advisable to use JNDI, I'm open to suggestions.

Whatever it takes to complete the information you just have to ask.

Thank you very much!

[Vyas Sanzgiri]

I just see one datasource in the configuration file you posted. Can you please post the complete file with 2 data sources and the exception ? It looks as if you are naming both beans entityManagerFactory. Try changing the name

[Pau Ruiz]

Vyas Sanzgiri wrote:I just see one datasource in the configuration file you posted. Can you please post the complete file with 2 data sources and the exception ? It looks as if you are naming both beans entityManagerFactory. Try changing the name

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.driverClassName}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <bean id="dataSourceMysql" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close"> <property name="driverClassName" value="${jdbc.mysql.driverClassName}"/> <property name="url" value="${jdbc.mysql.url}"/> <property name="username" value="${jdbc.mysql.username}"/> <property name="password" value="${jdbc.mysql.password}"/> </bean> <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" p:dataSource-ref="dataSource" p:persistenceXmlLocation="classpath:/resources/config/persistence/persistence.xml" p:jpaVendorAdapter-ref="jpaVendorAdapter"> <property name="jpaPropertyMap"> <map> <entry key="hibernate.generate_statistics" value="${persistence.hibernate.generate_statistics:false}" /> <entry key="hibernate.jdbc.use_scrollable_resultset" value="${persistence.hibernate.jdbc.use_scrollable_resultset:true}" /> </map> </property> </bean> <bean id="entityManagerFactoryMysql" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" p:dataSource-ref="dataSource" p:persistenceXmlLocation="classpath:/resources/config/persistence/persistence-mysql.xml" p:jpaVendorAdapter-ref="jpaVendorAdapterMysql"> <property name="jpaPropertyMapMysql"> <map> <entry key="hibernate.generate_statistics" value="${persistence.hibernate.generate_statistics:false}" /> <entry key="hibernate.jdbc.use_scrollable_resultset" value="${persistence.hibernate.jdbc.use_scrollable_resultset:true}" /> </map> </property> </bean> <bean id="jpaVendorAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> <property name="showSql" value="${persistence.showSQL}" /> <property name="generateDdl" value="${persistence.generateDdl}" /> <property name="databasePlatform" value="${persistence.dialect}" /> </bean> <tx:advice id="txAdvice" transaction-manager="txManager"> <tx:attributes> <tx:method name="get*" read-only="true"/> <tx:method name="*"/> </tx:attributes> </tx:advice> <aop:config> <aop:pointcut id="serviceOperation" expression="execution(* setting.springjpa.service.*Service.*(..))"/> <aop:advisor advice-ref="txAdvice" pointcut-ref="serviceOperation"/> </aop:config> <bean id="txManager" class="org.springframework.orm.jpa.JpaTransactionManager" p:entityManagerFactory-ref="entityManagerFactory"> </bean>

And the exception:

org.springframework.beans.factory.NoSuchBeanDefinitionException: No unique bean of type [javax.persistence.EntityManagerFactory] is defined: expected single bean but found 2

[Jagdish Gahlot]

please anyone could provide a good working example, as searching over internet i couldn't find any working approach, there are lots of post on internet might they are working few as well but as a newbie to spring i don't know whether i should declare 2 entity manager factory for both these DS? because Spring documentation says there should be only one entity manager factory (http://static.springsource.org/spring/docs/2.5.x/reference/orm.html)

below are couple of links but could anyone provide a full working helloworld kind of prototype.

http://static.springsource.org/spring/docs/2.5.x/reference/orm.html
http://forum.springsource.org/archive/index.php/t-34301.html
http://stackoverflow.com/questions/1902997/multiple-database-with-springhibernatejpa

[Christian Gossart]

I struggled a lot for this months ago, so I'll try to sum up what worked for me (I admit such a use case should be described in the Spring reference documentation):

- use a PersistenceUnitManager to manage the multiple PUs
- use multiple LocalContainerEntityManagerFactoryBean beans to build your EMF and reference the PUM in it.
- specify the PU name for each EMF
- use JTA if you want a global transaction around your work on the 2 (or more) databases and use databases and drivers that support XA transactions in that case (I had some tricky problems with MySQL for example)
- inject the EM into your DAOs via the Spring configuration (@PersistenceContext(unitName="xxx") was commented in my code, but I don't remember if it should work, and I cannot test the full project for now)

You'll find below a configuration sample (I removed anything that is business specific) for 2 databases (one local, one remote) using:
- Spring 3.0 (I believe it's also usable with Spring 2.5.6),
- EclipseLink as a JPA provider (replace the specifics with Hibernate in your case),
- MySQL (or any database)
- Atomikos for a standalone JTA transaction manager (I didn't put the whole config, please refer to http://www.atomikos.com/Documentation/SpringIntegration if needed, or use another JTA provider)

<!-- PUs XML files --> <bean id="persistenceXmlLocations" class="org.springframework.beans.factory.config.ListFactoryBean" > <property name="sourceList"> <list merge="true"> <value>classpath:META-INF/persistence-standaloneLocal.xml</value> <value>classpath:META-INF/persistence-standaloneRemote.xml</value> </list> </property> </bean> <!-- Datasources --> <bean id="dataSources" class="org.springframework.beans.factory.config.MapFactoryBean" > <property name="sourceMap"> <map merge="true"> <entry key="localDataSource" value-ref="localXaDataSource"/> <entry key="remoteDataSource" value-ref="remoteXaDataSource"/> </map> </property> </bean> <!-- Custom PU post processor (if you want a specific post-process for each PU) and don't forget to read this https://jira.springsource.org/browse/SPR-2796 --> <bean id="jtaPersistenceUnitPostProcessor" class="xxx.xxx.xxx.JtaPersistenceUnitPostProcessor" > <property name="jtaLocalDataSource" ref="localXaDataSource"/> <property name="jtaRemoteDataSource" ref="remoteXaDataSource"/> </bean> <!-- Persistence Unit Manager --> <bean id="persistenceUnitManager" class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager"> <property name="persistenceXmlLocations" ref="persistenceXmlLocations" /> <property name="dataSources" ref="dataSources" /> <property name="persistenceUnitPostProcessors"> <util:list> <!-- JTA Datasource injection to shut up EclipseLink --> <ref bean="jtaPersistenceUnitPostProcessor" /> </util:list> </property> </bean> <!-- Local XA Entity Manager --> <bean id="localEntityManager" factory-bean="localEmf" factory-method="createEntityManager" scope="prototype" /> <!-- Local XA EMF --> <bean id="localEmf" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <property name="persistenceUnitManager" ref="persistenceUnitManager"/> <property name="persistenceXmlLocation" value="classpath*:META-INF/persistence-standaloneLocal.xml" /> <property name="persistenceUnitName" value="localPersistenceUnit"/> <property name="dataSource" ref="localXaDataSource" /> <property name="jpaVendorAdapter" ref="jpaVendorAdapter" /> <property name="jpaProperties" > <props> <!-- <prop key="jdbc.exclusive-connection.mode">Transactional</prop> --> <prop key="eclipselink.target-server">xxx.xxx.xxx.tx.AtomikosTransactionSessionCustomizer</prop> <prop key="javax.persistence.jdbc.driver">com.mysql.jdbc.Driver</prop> <prop key="javax.persistence.jdbc.url">${local.connection.url}</prop> <prop key="javax.persistence.jdbc.user">${local.connection.user}</prop> <prop key="javax.persistence.jdbc.password">${local.connection.password}</prop> <prop key="eclipselink.weaving">false</prop> <prop key="eclipselink.target">MYSQL</prop> <prop key="eclipselink.persistence-context.flush-mode">AUTO</prop> <prop key="eclipselink.persistence-context.close-on-commit">true</prop> <prop key="eclipselink.jdbc.batch-writing">JDBC</prop> <prop key="eclipselink.logging.level">${local.connection.logging.level}</prop> </props> </property> </bean> <!-- Local XA Datasource --> <bean id="localXaDataSource" class="com.atomikos.jdbc.AtomikosDataSourceBean" > <property name="xaDataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" /> <property name="uniqueResourceName" value="localDB" /> <property name="xaProperties"> <props> <prop key="user">${local.connection.user}</prop> <prop key="password">${local.connection.password}</prop> <prop key="URL">${local.connection.url}</prop> <!-- http://www.atomikos.com/Documentation/KnownProblems#MySQL_XA_bug and http://bugs.mysql.com/bug.php?id=27832 --> <prop key="pinGlobalTxToPhysicalConnection">true</prop> </props> </property> <property name="minPoolSize" value="5" /> <property name="maxPoolSize" value="5" /> </bean> <!-- Remote Entity Manager --> <bean id="remoteEntityManager" factory-bean="remoteEmf" lazy-init="true" factory-method="createEntityManager" scope="prototype" /> <!-- Remote EMF --> <bean id="remoteEmf" lazy-init="true" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"> <property name="persistenceUnitManager" ref="persistenceUnitManager"/> <property name="persistenceXmlLocation" value="classpath*:META-INF/persistence-standaloneRemote.xml" /> <property name="persistenceUnitName" value="remotePersistenceUnit"/> <property name="dataSource" ref="remoteXaDataSource" /> <property name="jpaVendorAdapter" ref="jpaVendorAdapter" /> <property name="jpaProperties" > <props> <!-- <prop key="jdbc.exclusive-connection.mode">Transactional</prop> --> <!-- <prop key="eclipselink.cache-usage">ConformResultsInUnitOfWork</prop> --> <prop key="eclipselink.target-server">xxx.xxx.xxx.tx.AtomikosTransactionSessionCustomizer</prop> <prop key="javax.persistence.jdbc.driver">com.mysql.jdbc.Driver</prop> <prop key="javax.persistence.jdbc.url">${remote.connection.url}</prop> <prop key="javax.persistence.jdbc.user">${remote.connection.user}</prop> <prop key="javax.persistence.jdbc.password">${remote.connection.password}</prop> <prop key="eclipselink.weaving">false</prop> <prop key="eclipselink.target">MYSQL</prop> <prop key="eclipselink.persistence-context.flush-mode">COMMIT</prop> <prop key="eclipselink.logging.level">${remote.connection.logging.level}</prop> </props> </property> </bean> <!-- Remote Datasource --> <bean id="remoteXaDataSource" lazy-init="true" class="com.atomikos.jdbc.AtomikosDataSourceBean" > <property name="xaDataSourceClassName" value="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" /> <property name="uniqueResourceName" value="remoteDB" /> <property name="xaProperties"> <props> <prop key="user">${remote.connection.user}</prop> <prop key="password">${remote.connection.password}</prop> <prop key="URL">${remote.connection.url}</prop> <!-- http://www.atomikos.com/Documentation/KnownProblems#MySQL_XA_bug and http://bugs.mysql.com/bug.php?id=27832 --> <prop key="pinGlobalTxToPhysicalConnection">true</prop> </props> </property> <property name="minPoolSize" value="2" /> <property name="maxPoolSize" value="2" /> </bean> <!-- XA Local Database DAO --> <bean id="localDao" class="xxx.xxx.xxx.XxxxDAO" > <property name="em" ref="localEntityManager" /> </bean> <!-- Remote Database DAO --> <bean id="remoteDao" lazy-init="true" class="xxx.xxx.xxx.DefaultDAO" > <property name="em" ref="remoteEntityManager" /> </bean>

Hope this helps you, and maybe others to configure properly multiple databases access through JPA and Spring.

[Pau Ruiz]

Thank you very much, when can I try and explain the result.

But I have a question, do I have to declare all the DAO to inject EntityManager even use annotations?

[Christian Gossart]

Well, just try the injection with annotations as you already have coded it. If it's OK, no need to use the Spring configuration for it. Otherwise you can use some abstract DAO classes with a @PersistenceContext to factorize the injection and just declare these beans in the Spring conf.

[Pau Ruiz]

Ah ok, I understand. Well I try and tell you something, thank you very much again!.

[Vyas Sanzgiri]

Thanks Christian. I would suggest sending such examples to Spring so that it benefits the community. I hope they find an easier way to do this

[Pau Ruiz]

I've tried setting the example and I have not been successful.

First I created just such a connection using connection pooling with Atomikos and it worked.

But when I went to put my second connection, to put another LocalContainerEntityManagerFactoryBean have always been the exception:

org.springframework.beans.factory.NoSuchBeanDefinitionException: No unique bean of type [javax.persistence.EntityManagerFactory] is defined: expected single bean but found 2

I do not know where the problem is, can you put the code of your persistence-XXX.xml and xxx.xxx.xxx.JtaPersistenceUnitPostProcessor? To see if there is the problem.

On the other hand, I always use org.apache.commons.dbcp.BasicDataSource connection pool, do you advise using Atomik better?

[Jagdish Gahlot]

hi Christian.. thanks for your help, your example is completely working but i have one situation where i don't want to declare 2 persistence entity factory bean (LocalContainerEntityManagerFactoryBean) rather than i want to use single persistence factory dealing with multiple PUs which in turn will use multiple Datasources. i tried lots of option but couldn't get.

Is there a way that we can declare one single bean of LocalContainerEntityManagerFactoryBean which will be defining 1 PersistenceUnitManager and that PersistenceUnitManager will define a list of datasources. then how my DAO will use these Datasources (either of them at a time) ? also in persistence.xml file i will define 2 persistence unit name bt how there PU name will be linked with datasources defined in my applicationContext.xml 's datasources. i hope everyone is getting what i mean to say exactly.

please help.

[Pau Ruiz]

Jagdish Gahlot wrote:hi Christian.. thanks for your help, your example is completely working but i have one situation where i don't want to declare 2 persistence entity factory bean (LocalContainerEntityManagerFactoryBean) rather than i want to use single persistence factory dealing with multiple PUs which in turn will use multiple Datasources. i tried lots of option but couldn't get.

Is there a way that we can declare one single bean of LocalContainerEntityManagerFactoryBean which will be defining 1 PersistenceUnitManager and that PersistenceUnitManager will define a list of datasources. then how my DAO will use these Datasources (either of them at a time) ? also in persistence.xml file i will define 2 persistence unit name bt how there PU name will be linked with datasources defined in my applicationContext.xml 's datasources. i hope everyone is getting what i mean to say exactly.

please help.

If you define 2 LocalContainerEntityManagerFactoryBean, are not you the exception?

[Jagdish Gahlot]

No PAUL, i am not getting exception. i defined 2 EMF, 2 DS and 2 PUs.
But i need only 1 EMF and should be able to access 2 database/datasource.

[Jayesh A Lalwani]

You can't have one EMF going to 2 PUs. One EMF will always manage 1 PU. You need to create 2 EMFs and inject the PU that you want to use into your DAO class.

I have a similar problem where I need to be able to connect to multiple databases from the same application. A thread will always connect to one database. All databases are identical. Each client has it's own schema, and at runtime, I need to connect to the correct database.

What I ended up doing is I extended HibernatePersistenceProvider. This extended provider contains a map of HibernatePersistenceProvider; one for each client. I have a thread local variable that contains the name of the client. This is set before any DAO calls are made. My Extended Persistence Provider checks the thread local variable and delegates all calls to the correct real HibernatePersistenceProvider. I used the same pattern with DataSource too. I extended DataSource to contain a map of other DataSource; one for each client. In both cases, if my extended class sees a new client, it creates a PersistenceProvider/DataSource for that client and puts it in the map (note thread safety important here )

So, I have only one PU that uses my extended persistence provider. I configured EMF to use this PU. I provide the extended datasource to the EMF. This EM is injected into my DAO classes. At run time, the service that recieves the request, sets the thread local variable and calls the DAO. DAO calls EM, EM calls the Persistence Provider. Persistence Provider routes all the calls to the "real" Persistence Provider. Internally the real persistence provider calls my extended data source, which uses the same thread local variable to route the calls to the real data source.

[Christian Gossart]

@Jagdish: could you detail why you need only 1 EMF?
@Jayesh: your solution is interesting, but maybe a little complex. When I end up extending core classes of a framework (here HibernatePersistenceProvider and DataSource), I stop myself because it often means I'm going the wrong way: there has to be a solution using the framework as is. After all, those Hibernate/Spring/whateverframeworkyouuse guys have read and implemented the spec and my use case certainly fits in

For information, here is an extract of the JPA 2.0 specification (chapter 7.3) that deals with the multiple databases case:

Each entity manager factory provides entity manager instances that are all configured in the same man
ner (e.g., configured to connect to the same database, use the same initial settings as defined by th
implementation, etc.)
More than one entity manager factory instance may be available simultaneously in the JVM. [75]

[75] This may be the case when using multiple databases, since in a typical configuration a single entity manager only communicates
with a single database. There is only one entity manager factory per persistence unit, however.

[Jayesh A Lalwani]

Well, my application is a special case because the number of databases that we can connect to can grow at runtime.

The more usual case is that you have a fixed number of database that you connect to. JPA does support multiple PersistenceCOntexts, and Spring supports it too. However, the problem is that JPA spec assumes that you will be running in a container, and as a result it assumes that a JTA transaction manager is available. That is sometimes not true of spring apps. If you are developing a standalone spring app, you do not have a container and you have to go off the beaten track

If you have a fixed number of databases then the solution is not complex(let's say 2). The solution depends if you are in a container that provides a JTATransaction manager (and whether you want to use it)

If you want to use JTATransactionManager what you do is
a) Your persistence.xml should have 2 persistence units inside it
b) declare 2 EMs in your spring XML One for each PU. Set persistenceUnitName to the correcsonding name of the PU from persistence.xml
c) declare a bean using JtaTransactionManager. The exact configuration depends on the container
d) declare tx:annotation-driven in your spring bean
e) In your code use @PersistenceContext to inject the EM. provide unitName property of @PersistenceContext. It should match the name of your PU as defined in persistence XML
f) Use @Transactional or @TransactionAttribute to demarcate transactions

That's it. It will use 2 entity manager with 1 transaction manager. Since the transaction manager is an XA transaction manager, it will take care of managing the transactions for you

However, if you want to use JPA transaction manager, and you do not want XA transactions, there are multiple ways of doing this. The simpler way is to use the spring's @Transactional annotation

a) Your persistence.xml should have 2 persistence units inside it
b) declare 2 EMs in your spring XML One for each PU. Set persistenceUnitName to the correcsonding name of the PU from persistence.xml
c) Declare 2 transaction managers. Set qualifier attribute of each txManager
d) declare tx:annotation-driven in your spring bean
e) In your code use @PersistenceContext to inject the EM. provide unitName property of @PersistenceContext. It should match the name of your PU as defined in persistence XML
f) Use @Transactional annotation to demarcate transactions. You should provide the qualifier attribute on the annotation to match the qualifier defined in spring xml. If you do not provide qualifier then spring won;t know which TxManager to use and it will throw exception

However, the above works only if you can change the code to provide the qualifier for transactional annotation. If you are using someone else's code or if you want to use @TransactionAttribute, you are somewhat FUBARed. Spring does provide a solution, but it's kind of messy.

Basically, what you need to do is control how spring creates the transaction proxies. For some classes, it should use one transactionmanager, for others it should use another transaction manager.

Normally you will just use tx:annotation-driven(as described above). However, tx:annotation-driven has a big limitation It supports only one transaction manager. The way tx:annnotation-driven works is that it declares the following beans
a) DefaultAdvisorAutoProxyCreator
b) TransactionAttributeSourceAdvisor
c) TransactionInterceptor

The DefaultAdvisorAutoProxyCreator is a Bean Factory that does proxying based on advisors. All advisors declared in the spring context register themselves automatically with the ProxyCreator. After all the beans are created, spring gives the beans to the ProxyCreator, DefaultAdvisorAutoProxyCreator gives each bean to each advisor. Advisor is responsible for creating proxy for beans that it wants or else return the unproxied bean. In this case TransactionAttributeSourceAdvisor looks at the annotation metadata to check if the bean needs a proxy. If it does it uses TransactionInterceptor to create the proxy. By default TransactionAttributeSourceAdvisor checks all beans for Transaction annotations. Please note that since tx:annotation-driven has only one TransactionAttributeSourceAdvisor and one TransactionInterceptor, only one transaction manager can be used

So, to get around this we will not use tx:annotation-driven, and instead declare our own proxycreator, advisors and interceptors. Please note that this IS NOT A HACK. This is a valid way of weaving transaction proxies into services. In fact, tx:annotation-driven is a utility that was introduced late into Spring. Before tx:annotation-driven was introduced, this was how transactions were weaved. We are just using an old-school method of weaving transaction proxies.

So, your transaction manager, advisor, interceptor spring xml will look something like this

<!--  first declare the transaction managers -->    
    <bean id="transactionManagerA" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory"
            ref="entityManagerFactoryA" />
    </bean>
    <bean id="transactionManagerB" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactoryB"
            ref="entityManagerFactoryB" />
    </bean>

    <bean class="org.springframework.aop.framework.autoproxy.DefaultAdvisorAutoProxyCreator"/>
    
    <!-- All classes inside package A should use the  transaction manager A->
    <bean class="org.springframework.transaction.interceptor.TransactionAttributeSourceAdvisor">
      <property name="transactionInterceptor" ref="transactionInterceptorA"/>
      <property name="classFilter">
        <bean class="org.springframework.aop.aspectj.TypePatternClassFilter">
            <constructor-arg value="A..*"></constructor-arg>
        </bean>
      </property>
    </bean>
    
    <bean id="transactionInterceptorA"
        class="org.springframework.transaction.interceptor.TransactionInterceptor">
      <property name="transactionManager" ref="transactionManagerA"/>
      <property name="transactionAttributeSource">
        <bean class="org.springframework.transaction.annotation.AnnotationTransactionAttributeSource"/>
      </property>
    </bean>
    
    
    <!-- All classes package B should use the transaction manager B -->
    <bean class="org.springframework.transaction.interceptor.TransactionAttributeSourceAdvisor">
      <property name="transactionInterceptor" ref="transactionInterceptorB"/>
      <property name="classFilter">
        <bean class="org.springframework.aop.aspectj.TypePatternClassFilter">
            <constructor-arg value="B.*"></constructor-arg>
        </bean>
      </property>
    </bean>
    
    <bean id="transactionInterceptorB"
        class="org.springframework.transaction.interceptor.TransactionInterceptor">
      <property name="transactionManager" ref="transactionManagerB"/>
      <property name="transactionAttributeSource">
        <bean class="org.springframework.transaction.annotation.AnnotationTransactionAttributeSource"/>
      </property>
    </bean>

Boom! Now you can either use @Transactional (without the qualifier) or @TransactionAttribute and spring will take care of weaving the transaction interceptor that uses the correct transactionmanager

[Christian Gossart]

Special use case, indeed

Mine was a standalone application too, with only two databases, but I had to persist data in both within the same transaction, so I ended up using Atomikos to get a JTA transaction manager and XA transactions. In that case, the persistence unit are declared with transaction-type="JTA" (the "resource-local" type can only be used when the transaction is managed explicitly by the application).

I've also read on the Spring jira that some people tried to solve this kind of problem using custom annotation with @Transactional as meta-annotation, and 2 tx:annotation-driven declaration, one with a specific transaction manager, the other without the attribute, letting Spring fallback to the default one. But as you said, this can only be used if you have access to the source code of the classes to annotate them.

[Rakhs Nair]

Jayesh A Lalwani wrote:You can't have one EMF going to 2 PUs. One EMF will always manage 1 PU. You need to create 2 EMFs and inject the PU that you want to use into your DAO class.

I have a similar problem where I need to be able to connect to multiple databases from the same application. A thread will always connect to one database. All databases are identical. Each client has it's own schema, and at runtime, I need to connect to the correct database.

What I ended up doing is I extended HibernatePersistenceProvider. This extended provider contains a map of HibernatePersistenceProvider; one for each client. I have a thread local variable that contains the name of the client. This is set before any DAO calls are made. My Extended Persistence Provider checks the thread local variable and delegates all calls to the correct real HibernatePersistenceProvider. I used the same pattern with DataSource too. I extended DataSource to contain a map of other DataSource; one for each client. In both cases, if my extended class sees a new client, it creates a PersistenceProvider/DataSource for that client and puts it in the map (note thread safety important here )

So, I have only one PU that uses my extended persistence provider. I configured EMF to use this PU. I provide the extended datasource to the EMF. This EM is injected into my DAO classes. At run time, the service that recieves the request, sets the thread local variable and calls the DAO. DAO calls EM, EM calls the Persistence Provider. Persistence Provider routes all the calls to the "real" Persistence Provider. Internally the real persistence provider calls my extended data source, which uses the same thread local variable to route the calls to the real data source.

I have a similar scenario.Can you please give more detail?