Error using OneToOne/ManyToOne with JPA and Multiple Data Sources

Question

I've managed to configure JPA with multiple datasources, but I get the following error when bringing up my server (Websphere liberty):

org.hibernate.AnnotationException: @OneToOne or @ManyToOne onxxx.AccountBalance.currency references an unknown entity: xxx.Currency
at org.hibernate.cfg.ToOneFkSecondPass.doSecondPass(T oOneFkSecondPass.java:109)
at org.hibernate.cfg.Configuration.processEndOfQueue( Configuration.java:1521)
at org.hibernate.cfg.Configuration.processFkSecondPas sInOrder(Configuration.java:1446)
at org.hibernate.cfg.Configuration.secondPassCompile( Configuration.java:1351)
at org.hibernate.cfg.Configuration.buildSessionFactor y(Configuration.java:1733)
at org.hibernate.ejb.EntityManagerFactoryImpl.<init>( EntityManagerFactoryImpl.java:94)
at org.hibernate.ejb.Ejb3Configuration.buildEntityMan agerFactory(Ejb3Configuration.java:905)

The application deploys correctly if all the DAO are declared in the same database, but it fails if I move any of them to a second database. Is it possible to used JPA bags (OneToOne, ManyToOne, ManyToMany) with multiple data sources?

Relevant parts of the configuration:

Context:

<jee:jndi-lookup id="dataSource" jndi-name="jdbc/xxxwas"
cache="true" resource-ref="true" lookup-on-startup="false"
proxy-interface="javax.sql.DataSource" />

<bean id="h2dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">
<property name="driverClassName" value="org.h2.Driver" />
<property name="url"
value="jdbc:h2:mem:test;DB_CLOSE_DELAY=-1;TRACE_LEVEL_SYSTEM_OUT=3" />
<property name="username" value="test" />
<property name="password" value="test" />
</bean>

<tx:jta-transaction-manager />

<bean id="persistenceUnitManager"
class="org.springframework.orm.jpa.persistenceunit .DefaultPersistenceUnitManager">
<property name="persistenceXmlLocations">
<list>
<value>classpath:META-INF/persistence.xml</value>
</list>
</property>
<property name="dataSources">
<map>
<entry key="h2" value-ref="h2dataSource" />
<entry key="mysql" value-ref="dataSource" />
</map>
</property>
<!-- if no datasource is specified, use this one -->
<property name="defaultDataSource" ref="dataSource" />
</bean>

<bean id="integrationEntityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerE ntityManagerFactoryBean">
<property name="persistenceUnitManager" ref="persistenceUnitManager" />
<property name="persistenceUnitName" value="integrationEntityManagerFactoryPU" />
<property name="jtaDataSource" ref="h2dataSource" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.Hibernat eJpaVendorAdapter">
<!-- <property name="showSql" value="true" /> -->
<property name="database" value="H2" />
<!-- <property name="generateDdl" value="true" /> -->
</bean>
</property>
<property name="jpaProperties">
<props>
<prop key="hibernate.hbm2ddl.auto">update</prop>
</props>
</property>
</bean>

<bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerE ntityManagerFactoryBean">
<property name="persistenceUnitManager" ref="persistenceUnitManager" />
<property name="persistenceUnitName" value="databaseEntityManagerFactoryPU" />
<property name="jtaDataSource" ref="dataSource" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.Hibernat eJpaVendorAdapter">
<!-- <property name="showSql" value="true" /> -->
<property name="database" value="MYSQL" />
</bean>
</property>
</bean>

<jpa:repositories base-package="xxx.impl.repository.integration"
query-lookup-strategy="create-if-not-found"
entity-manager-factory-ref="integrationEntityManagerFactory">
</jpa:repositories>

<!-- Configures Spring Data JPA and sets the base package of my DAOs. -->
<jpa:repositories base-package="xxx.impl.repository"
query-lookup-strategy="create-if-not-found"
entity-manager-factory-ref="entityManagerFactory">
</jpa:repositories>

Server.xml

  <dataSource id="xxxwas" jndiName="jdbc/xxxwas" supplementalJDBCTrace="true" type="javax.sql.XADataSource">
        <jdbcDriver javax.sql.ConnectionPoolDataSource="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource" javax.sql.DataSource="com.mysql.jdbc.jdbc2.optional.MysqlDataSource" javax.sql.XADataSource="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource" libraryRef="MySQLLib"/>
        <properties databaseName="xxx" password="xxx" portNumber="3306" serverName="localhost" user="root"/>
    </dataSource>

Web.xml

<resource-ref>
    <res-ref-name>jdbc/xxxwas</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

Persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
    <persistence-unit name="databaseEntityManagerFactoryPU" transaction-type="JTA">
        <class>xxx.impl.bo.AccountBalance</class>
       <!-- WORKS IF DEFINED HERE -->
        <!-- <class>xxx.impl.bo.Currency</class> -->
         <properties>
             <property name="hibernate.transaction.factory_class" value="org.hibernate.transaction.CMTTransactionFactory"/>
            <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.WebSphereExtendedJTATransactionLookup" />
            <!-- <property name="hibernate.current_session_context_class"value="thread" /> -->
            <!--<prop key="hibernate.transaction.flush_before_completion">false</prop>-->
            <!--<prop key="hibernate.transaction.auto_close_session">true</prop>-->
            <!--<prop key="hibernate.current_session_context_class">thread</prop>-->
            <!--<prop key="javax.persistence.transactionType">JTA</prop>-->
            <!--<prop key="hibernate.connection.release_mode">auto</prop>-->

            <property name="hibernate.cache.region.factory_class" value="org.hibernate.cache.ehcache.EhCacheRegionFactory"/>
            <property name="hibernate.cache.use_query_cache" value="true"/>
            <property name="hibernate.cache.use_second_level_cache" value="true"/>
            <property name="net.sf.ehcache.configurationResourceName" value="ehcache_database.xml"/>
            <property name="hibernate.cache.provider_class" value="net.sf.ehcache.hibernate.SingletonEhCacheProvider"/>

            <!-- <property name="hibernate.archive.autodetection" value="class, hbm"/> -->
             <!-- <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5Dialect"/>  -->
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.DefaultNamingStrategy"/>
            <property name="hibernate.use_sql_comments" value="true"/>
            <property name="hibernate.generate_statistics" value="true"/>
        </properties>
    </persistence-unit>
     <persistence-unit name="integrationEntityManagerFactoryPU" transaction-type="JTA">
           <!-- DOES NOT WORK IF DEFINED HERE -->
        <class>xxx.impl.bo.Currency</class>
        <properties>
             <property name="hibernate.transaction.factory_class" value="org.hibernate.transaction.CMTTransactionFactory"/>
            <property name="hibernate.transaction.manager_lookup_class" value="org.hibernate.transaction.WebSphereExtendedJTATransactionLookup" />
             <property name="hibernate.hbm2ddl.auto" value="create" />
        </properties>
    </persistence-unit>
</persistence>

No. You cannot have entity x in database x and entity y in database y and then expect entity x and y to work together. They work separately but you cannot have relations between entity x and entity y. — M. Deinum, Jan 15 '14 at 11:47
Thanks for your answer M. Deinum. Do you know the reason why this is not possible? Are the JPA relationships (one-to-one, one-to-many, many-to-many) resolved through the entity managers or through other mechanisms? It would be interesting to learn why this feature is not available and if it will ever be, I'm pretty sure it would be useful in a number of multi-tenancy scenarios. — movees, Jan 16 '14 at 09:17
Related entities are resolved through the same `EntityManager` and as such cannot live in different database. Everything has to be accessible in the same `EntityManager`. If you want to split it across different databases you will have to manage that manually. — M. Deinum, Jan 16 '14 at 09:31
I've been looking at a different solution, using the `AbtractRoutingDataSource` to create a dynamic routing data source and use it in a single entityManagerFactory [link](http://tomee.apache.org/examples-trunk/dynamic-datasource-routing/README.html). Unfortunately this approach (if it works) would be restricted to multiple databases sharing the same dialect, is that correct? Is there any other alternative to removing the relationships (one-to-one, one-to-many, many-to-many) that you can think about? — movees, Jan 16 '14 at 10:06
As mentioned doing it manually. You cannot, nor should imho, try to shoehorn something in place under the JPA spec what isn't part of the spec. Also you might want to reconsider those relationships if they are in 2 different databases. Also your domain doesn't have to be the same as the database objects! (I suggest a read of [Domain Driven Design](http://domaindrivendesign.org/) by Eric Evans. — M. Deinum, Jan 16 '14 at 10:11
Good suggestion. I hope this thread and the sample code will help other people facing similar issues. — movees, Jan 16 '14 at 16:52

score 0 · Answer 1 · answered Aug 13 '20 at 12:49

I know its too late, but I had the same probleme. I'm working on Oracle database, the same database with two schemas (users) My solution was to give the first user access to all tables in the second user schema. After that, on JPA Entity annotation, precise the schema for each entity.

This way, hibernate generate SQL queries with schema :

select field1, field2 from USER1.Table1 INNER JOIN USER2.TABLE2 ON .....

It work this way because user1 have access to user2 tables with a grant, but the two schemas must be in the same database, otherwise you have to create a dblink and a synonym.

Error using OneToOne/ManyToOne with JPA and Multiple Data Sources

1 Answers1