JPA Config with two database

Question

I have a application pointing to two different database, I have created two JPAConfig following the example posted here (The JPA Configuration with JAVA at 2)

http://www.baeldung.com/2011/12/13/the-persistence-layer-with-spring-3-1-and-jpa/#noxml

In my persistence layer I use anotations and code is something like this persistor interface

public interface EmployeePersistor {

    public void create(EmployeeEntity employee);

}

Implementation is

 @Component
    public class EnvelopeStatusPersistorImpl implements EnvelopeStatusPersistor {

        @PersistenceContext(unitName = "db2EntityManagerFactory")
        EntityManager db2EM;

        @Override
        @Transactional("db2TransactionManager")
        public void create(EmployeeEntity employee) {

            db2EM.persist(employee);
            db2EM.flush();
        }

}

This works fine for db2 transactions , but when I try to do similar approach with db1 , the query are still running against db2

@PersistenceContext(unitName = "db1EntityManagerFactory")
        EntityManager db1EM;

        @Override
        @Transactional("db1TransactionManager")
        public void create(EmployeeEntity employee) {

            db1EM.persist(employee);
            db1EM.flush();
        }

Listed below is the JPAConfig file for just db1

@Configuration
@PropertySource("classpath:application.${target_env}.properties")
@EnableTransactionManagement
@EnableJpaRepositories(basePackages = "com.application", entityManagerFactoryRef = "db1EntityManagerFactory", transactionManagerRef = "db1TransactionManager")
public class CRMJPAConfig {

    @Value("${db1.url}")
    private String db1URL;

    @Value("${db1.username}")
    private String db1User;

    @Value("${db1.password}")
    private String db1Password;

    @Value("${db1.driver}")
    private String db1DriverClass;

    @Value("${db1.scan}")
    private String db1PackageScan;

    @Value("${db1.dialect}")
    private String db1HibernateDialect;

    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean db1EntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(dataSource());
        em.setPackagesToScan(new String[] { db1PackageScan });

        JpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        em.setJpaProperties(additionalProperties());

        return em;
    }

    @Bean
    @Primary
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(db1DriverClass);
        dataSource.setUrl(db1URL);
        dataSource.setUsername(db1User);
        dataSource.setPassword(db1Password);
        return dataSource;
    }

    @Bean
    @Primary
    public PlatformTransactionManager db1TransactionManager() {
        JpaTransactionManager transactionManager = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(db1EntityManagerFactory()
                .getObject());

        return transactionManager;
    }

    @Bean
    public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
        return new PersistenceExceptionTranslationPostProcessor();
    }

    Properties additionalProperties() {
        Properties properties = new Properties();
        properties.setProperty("hibernate.hbm2ddl.auto", "none");
        properties.setProperty("hibernate.dialect", db1HibernateDialect);
        return properties;
    }
}

There is no persistence.xml , the Configuration is done using Java as described earlier — avenirit12, May 28 '15 at 14:31
Added the JPAConfig for just db1 in the Original Question,db2 is similar to this with db1 replaced by db2 — avenirit12, May 28 '15 at 16:11

score 0 · Answer 1 · answered May 28 '15 at 14:45

this should do the trick...

in your applicationcontext-jdbc.xml or similarly purposed configuration file you should have a similar situation

            <?xml version="1.0" encoding="UTF-8"?>
            <beans xmlns="http://www.springframework.org/schema/beans"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:context="http://www.springframework.org/schema/context"
                xmlns:p="http://www.springframework.org/schema/p"
                xmlns:util="http://www.springframework.org/schema/util" 
                xmlns:jdbc="http://www.springframework.org/schema/jdbc"
                xmlns:tx="http://www.springframework.org/schema/tx"
                xmlns:jpa="http://www.springframework.org/schema/data/jpa"
                xmlns:orcl="http://www.springframework.org/schema/data/orcl"
                xmlns:cache="http://www.springframework.org/schema/cache"
                xmlns:aop="http://www.springframework.org/schema/aop"
                xsi:schemaLocation="http://www.springframework.org/schema/beans 
                                    http://www.springframework.org/schema/beans/spring-beans.xsd
                                    http://www.springframework.org/schema/context
                                    http://www.springframework.org/schema/context/spring-context.xsd
                                    http://www.springframework.org/schema/util 
                                    http://www.springframework.org/schema/util/spring-util.xsd
                                    http://www.springframework.org/schema/jdbc
                                    http://www.springframework.org/schema/jdbc/spring-jdbc.xsd
                                    http://www.springframework.org/schema/tx
                                    http://www.springframework.org/schema/tx/spring-tx.xsd
                                    http://www.springframework.org/schema/data/jpa
                                    http://www.springframework.org/schema/data/jpa/spring-jpa-1.3.xsd
                                    http://www.springframework.org/schema/data/orcl 
                                    http://www.springframework.org/schema/data/orcl/spring-data-orcl-1.0.xsd
                                    http://www.springframework.org/schema/cache
                                    http://www.springframework.org/schema/cache/spring-cache.xsd
                                    http://www.springframework.org/schema/aop
                                    http://www.springframework.org/schema/aop/spring-aop.xsd">


                <!-- Used to enable transaction annotation on the DBItaServiceImpl class -->
                <!-- <bean id="cerempService" class="eu.europa.acer.aris.ceremp.service.impl.CerempServiceImpk" /> -->

                <!-- Enable the configuration of transactional behavior based on annotations -->
                <tx:annotation-driven />

                <!-- Caching configuration -->
                <cache:annotation-driven />
                <!-- ehcache Cache Manager -->
                <bean class="org.springframework.cache.ehcache.EhCacheCacheManager" id="cacheManager" p:cacheManager-ref="ehcache"/>
                <bean id="ehcache"
                    class="org.springframework.cache.ehcache.EhCacheManagerFactoryBean"
                    p:configLocation="classpath:ehcache.xml" p:shared="true" />

                <aop:aspectj-autoproxy/>

                <!-- AOP used to debug spring-jpa -->
                <bean class="org.springframework.aop.interceptor.CustomizableTraceInterceptor"
                    id="customizableTraceInterceptor">
                    <property name="enterMessage" value="Entering $[methodName]($[arguments])" />
                    <property name="exitMessage" value="Leaving $[methodName](): $[returnValue]" />
                </bean>
                <aop:config>
                    <aop:advisor advice-ref="customizableTraceInterceptor"
                        pointcut="execution(public * org.springframework.data.jpa.repository.JpaRepository+.*(..))" />
                </aop:config>

                <!-- Activate Spring Data JPA repository support -->
                <jpa:repositories base-package="eu.europa.acer.aris.ceremp.repository" entity-manager-factory-ref="entityManagerFactory" transaction-manager-ref="transactionManager"/>
                <jpa:repositories base-package="eu.europa.acer.aris.ceremp.iamrepository" entity-manager-factory-ref="entityManagerFactory2" transaction-manager-ref="transactionManager2"/>

            <!--    <util:properties id="hibernatePropertiesProps">
                    <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
                    <prop key = "hibernate.show_sql">true </prop>

                    <prop key="org.hibernate.envers.auditTablePrefix">AUD_</prop>
                    <prop key="org.hibernate.envers.auditTableSuffix"></prop>
                    <prop key="org.hibernate.envers.store_data_at_delete">true</prop>

                </util:properties> -->

                <util:properties id="hibernatePropertiesProps">
                    <prop key="hibernate.dialect">org.hibernate.dialect.Oracle10gDialect</prop>
                    <prop key="log4j.logger.org.hibernate.SQL">ALL</prop>
                    <prop key="log4j.logger.org.hibernate.type">ALL</prop>
                    <prop key="hibernate.show_sql">true</prop>
                    <prop key="org.hibernate.envers.auditTablePrefix">AUD_</prop>
                    <prop key="org.hibernate.envers.auditTableSuffix"></prop>
                    <prop key="org.hibernate.envers.store_data_at_delete">true</prop>

                </util:properties>

                <!-- flyway part -->    
                <beans profile="ci,local,dev,qa,test,smarts,prototype,pilot,testframework,testframework-lutech">
                    <bean id="flyway" class="com.googlecode.flyway.core.Flyway" init-method="migrate" depends-on="dataSource">
                        <property name="dataSource" ref="dataSource"/>
                        <property name="disableInitCheck" value="true" />
                        <property name="encoding" value="UTF-8"></property>
                            <property name="placeholders" ref="flyway.prop"/>
                    </bean>    
                </beans>

                <beans profile="staging,preprod,prod">
                    <bean id="flyway" class="com.googlecode.flyway.core.Flyway">
                        <property name="placeholders" ref="flyway.prop"/>
                    </bean>  
                </beans>    

                <!-- Declare a JPA entityManagerFactory -->  
                <beans profile="ci,local,dev,qa,test,smarts,prototype,pilot,testframework,testframework-lutech">    
                    <bean id="entityManagerFactory" depends-on="flyway"
                      class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
                      p:persistenceProviderClass="org.hibernate.ejb.HibernatePersistence"
                      p:jpaVendorAdapter-ref="hibernateVendor" 
                      p:jpaProperties-ref="hibernatePropertiesProps"
                      p:dataSource-ref="dataSource"
                      p:persistenceUnitName="persistenceUnitDCI"
                      p:packagesToScan= "eu.europa.acer.aris.ceremp.domain,eu.europa.acer.aris.ceremp.auditing,eu.europa.acer.aris.ceremp.domain.readonly" >
                        <qualifier value ="emfDCI" />
                    </bean>
                    <bean id="entityManagerFactory2"
                      class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
                      p:persistenceProviderClass="org.hibernate.ejb.HibernatePersistence"
                      p:jpaVendorAdapter-ref="hibernateVendor" 
                      p:jpaProperties-ref="hibernatePropertiesProps"
                      p:dataSource-ref="dataAltSource"
                      p:persistenceUnitName="persistenceUnitIAM"
                      p:packagesToScan= "eu.europa.acer.aris.ceremp.domain,eu.europa.acer.aris.ceremp.auditing,eu.europa.acer.aris.ceremp.domain.readonly" >
                        <qualifier value ="emfIAM" />
                    </bean>
                </beans>

                <beans profile="staging,preprod,prod">
                    <bean id="entityManagerFactory" 
                          class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
                          p:persistenceProviderClass="org.hibernate.ejb.HibernatePersistence"
                          p:jpaVendorAdapter-ref="hibernateVendor" 
                          p:jpaProperties-ref="hibernatePropertiesProps"
                          p:dataSource-ref="dataSource"
                          p:persistenceUnitName="persistenceUnitDCI"
                          p:packagesToScan= "eu.europa.acer.aris.ceremp.domain,eu.europa.acer.aris.ceremp.auditing,eu.europa.acer.aris.ceremp.domain.readonly" >
                          <qualifier value ="emfDCI" />
                    </bean>
                    <bean id="entityManagerFactory2"
                      class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
                      p:persistenceProviderClass="org.hibernate.ejb.HibernatePersistence"
                      p:jpaVendorAdapter-ref="hibernateVendor" 
                      p:jpaProperties-ref="hibernatePropertiesProps"
                      p:dataSource-ref="dataAltSource"
                      p:persistenceUnitName="persistenceUnitIAM"
                      p:packagesToScan= "eu.europa.acer.aris.ceremp.domain,eu.europa.acer.aris.ceremp.auditing,eu.europa.acer.aris.ceremp.domain.readonly" >
                        <qualifier value ="emfIAM" />
                    </bean>
                </beans>

                <beans>
                <!-- Specify our ORM vendor -->
                <bean id="hibernateVendor" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"
                            p:showSql="false"/>

                <!-- Declare a transaction manager-->
                <bean id="transactionManager" 
                      class="org.springframework.orm.jpa.JpaTransactionManager"
                      p:entityManagerFactory-ref="entityManagerFactory"
                      p:dataSource-ref="dataSource"/>

                 <!-- Declare a transaction manager-->
                <bean id="transactionManager2" 
                      class="org.springframework.orm.jpa.JpaTransactionManager"
                      p:entityManagerFactory-ref="entityManagerFactory2"
                      p:dataSource-ref="dataAltSource"/>
                </beans>

                <!-- Define the oracle pooling datasource of spring-data which use the class oracle.jdbc.pool.OracleDataSource -->



                <beans profile="local">
                    <orcl:pooling-datasource id="dataSource"
                        url="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA" username="${db.credential.username}" password="${db.credential.password}"/>
                        <util:properties id="flyway.prop" location="classpath:config_local.properties"/>
                    <orcl:pooling-datasource id="dataAltSource"
                        url="jdbc:oracle:thin:@db00-ccea.labucs.int:1521:CCEA" username="${db2.credential.username}" password="${db2.credential.password}"/>
                        <util:properties id="flyway.prop" location="classpath:config_local.properties"/>
                </beans>
            </beans>

Now... you should have a Repository&RepositoryImpl class for that object... in the repositoryimpl class of reference have something like this:

            package eu.europa.acer.aris.ceremp.iamrepository.impl;


            public class UserRepositoryImpl implements UserRepositoryCustom
            {
                @Qualifier("emfIAM")
                @Autowired
                private EntityManagerFactory em;

            }

Basically you have to autowire a entitymanagerfactory and specify via the qualifier the qualifer you configured for the specific entitymanagerfactory for the connection you want to use for that specific object :) You have other alternative, but this is the easiest one, though you might consider the other as well, like defining an attribute that defines which entitymanager will be used to handled two object of the same class e.g. you have customer with customer of category gold being present in a schema and customer of category silver in a different schema alltogether...

Hope this helps :)

can you try using my notation? .@PersistenceContext to .@Qualifier it's not a bother, especially since you already have the entity manager defined in your class).. I'm quite confident that it works, since It's what is used at this very moment in our production system to handle the same issue you have. Other have had a similar issue to yours and the solution is usually injecting the em via a qualifier or a persistanceunit or going with the dynamic datasource of spring. read this too: http://stackoverflow.com/questions/860918/hibernate-spring-using-multiple-datasources — witchedwiz, May 28 '15 at 18:39
In your jpa config set the package to scan properties and the qualifier for the entity manager if you haven't. — witchedwiz, May 29 '15 at 05:03
Since I am still able to not find the working soution, I am thinking my spring configuration may not be right , I do not have a Repository and RepositoryImpl , instead I have persistor and persistorimpl. I have added them in the original question can you check that — avenirit12, Jun 04 '15 at 20:53
uhm, question: in the persistor you're using @PersistenceContext(unitName = "db2EntityManagerFactory").. perfectly fine, but, you're not defning a unitname anywhere, you just defined a ref for entityManagerFactory.. If you used xml based config you should assign a property called "persistenceUnitName" with the persistanceunit you want.. Instead nowhere in your jpaconfig a persistanceunitname for that specifi entityManager factory is ever defined.. I think that's your problem... — witchedwiz, Jun 05 '15 at 10:48

score 0 · Accepted Answer · answered Jun 05 '15 at 21:39

0

Fixed the problem changed the datasource to db1DataSource in first file and in second to db2DataSource

answered Jun 05 '15 at 21:39

avenirit12

233
2
5
17

JPA Config with two database

2 Answers2