how to use multiple datasources with spring-data-jpa?

Question

I read all the related questions and tried all of them but still can't make my configuration straight.

I've two databases and i want to use them as datasources in my application.

Here is my context file:

<jee:jndi-lookup id="firstDataSource" jndi-name="java:/comp/env/jdbc/firstDS"   expected-type="javax.sql.DataSource" />
<jee:jndi-lookup id="secondDataSource" jndi-name="java:/comp/env/jdbc/secondDS" expected-type="javax.sql.DataSource" />

<bean name="persistenceProvider" class="org.hibernate.jpa.HibernatePersistenceProvider"></bean>

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="thisEntityManagerFactory">
    <property name="dataSource" ref="firstDataSource"/>
    <property name="packagesToScan" value="com.a.b.first.model"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="generateDdl" value="false" />
            <property name="showSql" value="true"/>
            <property name="databasePlatform" value= "org.hibernate.dialect.Oracle10gDialect"/>
            <property name="database" value="ORACLE"/>
        </bean>
    </property>
    <property name="persistenceUnitName" value="firstPersistenceUnit" />
    <property name="persistenceProvider" ref="persistenceProvider"></property>
    <property name="jpaProperties">
        <value>
            hibernate.generate_statistics = true
            hibernate.cache.use_second_level_cache = true
            hibernate.cache.region.factory_class = org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory
            hibernate.cache.use_query_cache = true
            <!--hibernate.hbm2ddl.auto=create-->
        </value>
    </property>
</bean>

<bean class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean" id="otherEntityManagerFactory">
    <property name="dataSource" ref="secondDataSource"/>
    <property name="packagesToScan" value="com.a.b.second.model"/>
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="generateDdl" value="false" />
            <property name="showSql" value="true"/>
            <property name="databasePlatform" value= "org.hibernate.dialect.Oracle10gDialect"/>
            <property name="database" value="ORACLE"/>
        </bean>
    </property>
    <property name="persistenceUnitName" value="secondPersistenceUnit" />
    <property name="persistenceProvider" ref="persistenceProvider"></property>
    <property name="jpaProperties">
        <value>
            hibernate.generate_statistics = true
            hibernate.cache.use_second_level_cache = true
            hibernate.cache.region.factory_class = org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory
            hibernate.cache.use_query_cache = true
            <!--hibernate.hbm2ddl.auto=create-->
        </value>
    </property>
</bean>

<bean class="org.springframework.orm.jpa.JpaTransactionManager" id="thisTransactionManager">
    <property name="entityManagerFactory" ref="thisEntityManagerFactory"/>
    <property name="jpaDialect">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
    </property>
</bean>

<bean class="org.springframework.orm.jpa.JpaTransactionManager" id="otherTransactionManager">
    <property name="entityManagerFactory" ref="otherEntityManagerFactory"/>
    <property name="jpaDialect">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaDialect" />
    </property>
</bean>

<tx:annotation-driven transaction-manager="thisTransactionManager" />

<tx:annotation-driven transaction-manager="otherTransactionManager" />

<jpa:repositories base-package="com.a.b.first.intf" entity-manager-factory-ref="thisEntityManagerFactory" transaction-manager-ref="transactionManager"/>
<jpa:repositories base-package="com.a.b.second.intf" entity-manager-factory-ref="otherEntityManagerFactory" transaction-manager-ref="otherTransactionManager" />

Problem is when i try to use a repository interface which is located under package com.a.b.second.intf, it goes to firstDataSource and throws a SQLSyntaxErrorException with message "ORA-00942: table or view does not exist". Because there is no such table in the first database.

There is a line in tho logs for each EntityManagerFactory, saying

Building JPA container EntityManagerFactory for persistence unit 'default'

And in the next line it prints PersistenceUnitInfo for this EntityManagerFactory. The "Non JTA datasource" property of the PersistenceUnitInfo is same for both EntityManagerFactories. I guess that means both persistence units uses the same datasource.

What am i missing?

Thanks.

@RobertNiestroj i think my xml configuration is the same. or i'am missing some little detail. can you take a look at my configuration? — okan, Jun 22 '15 at 12:59
Turns out the problem is in my context.xml file. `` `` The second datasource's global property points the first datasource. So every time i make a jndi lookup it gets the wrong datasource. Fool copy-paste mistake. — okan, Jun 23 '15 at 06:50

score 0 · Answer 1 · answered Jun 22 '15 at 12:33

0

In your service layer you should have something like this:

@Service
@Transactional("thisTransactionManager")
public class ThisService{

   @Autowired
   private com.a.b.first.intf.Repo1 repo1;
}

@Service
@Transactional("otherEntityManagerFactory")
public class OtherService{

   @Autowired
   private com.a.b.second.intf.Repo1 repo1;
}

This way, when you call a service method, the TransactionInterceptor can load the appropriate transaction manager, associated to the EntityManagerFactory you want to operate with.

answered Jun 22 '15 at 12:33

Vlad Mihalcea

142,745
71
566
911

Vlad Mihalcea i tried that, still the same exception. I think the problem here isn't about choosing the right EntityManagerFactory. When creating the second EntityManagerFactory, it's persistence unit gets the wrong datasource. – okan Jun 22 '15 at 12:54
That's strange, because you specified the `dataSource` property of the `LocalContainerEntityManagerFactoryBean`. Try adding a break-point into LocalContainerEntityManagerFactoryBean to see if it loads the right DataSource. – Vlad Mihalcea Jun 22 '15 at 13:06
Did you find any solution? I have the same problem, I debug the second data source and everything is fine with the second configurations but when I want to read from tables it throws `ORA-00942: table or view does not exist`. – Faramarz Afzali Aug 10 '22 at 06:05

how to use multiple datasources with spring-data-jpa?

1 Answers1