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>

user1188867 · Accepted Answer · 2018-04-14 14:47:39Z

9

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.

answered Apr 14, 2018 at 14:47

user1188867

3,9985 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Stephan Over a year ago

Annotation based configuration makes things a lot simpler with the @Configuration @EnableJpaRepositories(entityManagerFactoryRef="factory1", basePackages={"my.repository.package"}) annotations. So long as the base package and the ref are unique, you can spool up as many different datasources as you wish.

user1188867 Over a year ago

It is an old application with lot of configurations so I don't want to change all the xml based configurations to annotations based configurations

Stephan Over a year ago

Ah ok yeah that'd be a dangerous refactor

Devendra Singh · Accepted Answer · 2018-04-11 05:26:47Z

1

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>

answered Apr 11, 2018 at 5:26

Devendra Singh

6401 gold badge6 silver badges12 bronze badges

5 Comments

user1188867 Over a year ago

I changed them it is creating sql server tables in postgres

Devendra Singh Over a year ago

so just use create with sql and validate with postgres

user1188867 Over a year ago

I do not want to create/update any tables either in postgres or sql server as those are production databases

Devendra Singh Over a year ago

then make sure all tables are available in the database already, which you are going to use.

user1188867 Over a year ago

All the tables are already available postgres tables are mapped to entities in com.test.entities.postgres package and sqlserver tables are mapped to entities in com.test.entities.sql package that's why I have used packagesToScan property in entitymanagerfactory configuration

Alberto · Accepted Answer · 2018-04-18 14:49:00Z

I have an application with two entitymanager. This is my config:

persistence.xml:

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.1"
    xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">

    <persistence-unit name="myapp" transaction-type="RESOURCE_LOCAL">
        <class>...</class>
        <class>...</class>
    </persistence-unit>

    <persistence-unit name="proxies" transaction-type="RESOURCE_LOCAL">
        <class>...</class>
        <class>...</class>
    </persistence-unit>

</persistence>

spring-context.xml:

<?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: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:task="http://www.springframework.org/schema/task"
    xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.0.xsd
        http://www.springframework.org/schema/task http://www.springframework.org/schema/task/spring-task-4.0.xsd
        http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.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/tx http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">

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

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="dataSource" ref="myappDataSource" />
        <property name="entityManagerFactory" ref="entityManagerFactory" />
    </bean>

    <bean id="transactionManagerProxy" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="dataSource" ref="proxyDataSource" />
        <property name="entityManagerFactory" ref="entityManagerFactoryProxy" />
    </bean>

    <jpa:repositories base-package="com.example.myapp.repository" entity-manager-factory-ref="entityManagerFactory" />
    <jpa:repositories base-package="com.example.myapp.repository.proxy" entity-manager-factory-ref="entityManagerFactoryProxy" />

    <task:annotation-driven />

    <!-- holding properties for database connectivity /-->
   <context:property-placeholder location="classpath:database.properties"/>

    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="packagesToScan" value="com.example.myapp.model" />
        <property name="persistenceUnitName" value="myapp" />
        <property name="dataSource" ref="myappDataSource" />
        <property name="jpaVendorAdapter" ref="jpaVendorAdapter"/>
    </bean>

    <bean id="entityManagerFactoryProxy" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="packagesToScan" value="com.example.myapp.model.proxy" />
        <property name="persistenceUnitName" value="proxy" />
        <property name="dataSource" ref="proxyDataSource" />
        <property name="jpaVendorAdapter" ref="jpaVendorAdapter"/>
    </bean>

    <bean id="jpaVendorAdapter" class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
        <property name="database" value="MYSQL"/>
        <property name="showSql" value="false"/>
        <property name="generateDdl" value="false"/>
        <property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect"/>
    </bean>

    <bean id="dataSource" abstract="true" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
        <property name="driverClass" value="${jdbc.driverClassName}" />
        <property name="maxPoolSize" value="${jdbc.maxPoolSize}" />
        <property name="minPoolSize" value="${jdbc.minPoolSize}" />
        <property name="maxStatements" value="${jdbc.maxStatements}" />
        <property name="testConnectionOnCheckout" value="${jdbc.testConnectionOnCheckout}" />
        <property name="checkoutTimeout" value="${jdbc.checkoutTimeout}"/>
        <property name="acquireIncrement" value="${jdbc.acquireIncrement}"/>
        <property name="idleConnectionTestPeriod" value="${jdbc.idleConnectionTestPeriod}"/>
        <property name="preferredTestQuery" value="${jdbc.preferredTestQuery}"/>
    </bean>

    <bean id="myappDataSource" parent="dataSource">
        <property name="jdbcUrl" value="${jdbc.url}" />
        <property name="user" value="${jdbc.username}" />
        <property name="password" value="${jdbc.password}" />
    </bean>

    <bean id="proxyDataSource" parent="dataSource">
        <property name="jdbcUrl" value="${jdbc.proxy.url}" />
        <property name="user" value="${jdbc.proxy.username}" />
        <property name="password" value="${jdbc.proxy.password}" />
    </bean>

</beans>

I have 2 connections to MySql, but it should be the same for two different databases.

In peristence.xml we have to enter every new entity that is added to application as a <class> or jar which is tedious. So I avoided that and simply used packagesToScan property to do that work for me.

Collectives™ on Stack Overflow

Configuring multiple databases with multiple entitymanagerfactory in spring data jpa

3 Answers 3

3 Comments

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related