Configuring multiple databases with multiple entitymanagerfactory in spring data jpa

Question

I want to configure 2 entity manager factories for 2 different databases(postgres & sql server) in spring 4.

persistence.xml contains 2 persistence units for 2 databases:

 <?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence">
    <persistence-unit name="entityManager">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL9Dialect"/>
            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
            <property name="hibernate.temp.use_jdbc_metadata_defaults" value="false"/>
        </properties>
    </persistence-unit>

    <persistence-unit name="sqlEntityManager">
        <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.SQLServerDialect"/>
            <property name="hibernate.show_sql" value="false"/>
            <property name="hibernate.hbm2ddl.auto" value="validate"/>
            <property name="hibernate.temp.use_jdbc_metadata_defaults" value="false"/>
        </properties>
    </persistence-unit>
</persistence>

spring-context.xml contains 2 datasources and 2 entitymanager factories :

    <bean id="postgresDataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource"
          p:driverClassName="org.postgresql.Driver"
          p:url="jdbc:postgresql://localhost:5432/test?createDatabaseIfNotExist=true"
          p:username="test"
          p:password="test"/>

    <bean id="sqlDataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource"
          p:driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver"
          p:url="jdbc:sqlserver://localhost:1433;databaseName=test"
          p:username="test"
          p:password="test"/>


    <bean id="persistenceUnit" class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
        <property name="persistenceXmlLocations">
            <list>
                <value>classpath*:META-INF/persistence.xml</value>
            </list>
        </property>
        <property name="defaultDataSource" ref="postgresDataSource"/>
    </bean>

    <bean id="sqlPersistenceUnit" class="org.springframework.orm.jpa.persistenceunit.DefaultPersistenceUnitManager">
        <property name="persistenceXmlLocations">
            <list>
                <value>classpath*:META-INF/persistence.xml</value>
            </list>
        </property>
        <property name="defaultDataSource" ref="sqlDataSource"/>
    </bean>



    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="persistenceUnitManager" ref="persistenceUnit"/>
        <property name="persistenceUnitName" value="entityManager"/>
<property name="packagesToScan" value="com.test.entities.postgres"/>
    </bean>

    <bean id="sqlEntityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="persistenceUnitManager" ref="sqlPersistenceUnit"/>
        <property name="persistenceUnitName" value="sqlEntityManager"/>
<property name="packagesToScan" value="com.test.entities.sql"/>
    </bean>

When i run the application it could not create entitymangerfactory as it is checking for tables in postgres which are in sql server.

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'entityManagerFactory' defined in ServletContext resource [/WEB-INF/spring-context.xml]: Invocation of init method failed; nested exception is org.hibernate.HibernateException: Missing table:<SqlServerTableName>

Answer 1

I am able to solve the issue by removing the persistence.xml and creating entitymanagerfactory and pchentitymanagerfactory in context.xml as below:

<bean id="entityManagerFactory"
      class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean"
      p:dataSource-ref="phoenixDataSource">
    <property name="packagesToScan">
        <array>
            <value>com.test.entities.postgres</value>
        </array>
    </property>

    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"/>
    </property>

    <property name="jpaProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQL9Dialect</prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
        </props>
    </property>

    </bean>

because packagesToScan does not work if we use persistence.xml.

Answer 2

Please update your hibernate configuration by this. I think this would help you

<property name="hibernate.hbm2ddl.auto">create</property>
or 
<property name="hibernate.hbm2ddl.auto">update</property>