Spring. Multiple data source with one persistence unit

Question

Is it possible to use the same persistence unit with multiple data source in spring?

For example. I have two databases. They are on different server. One for only writing operation(insert,update, delete and etc.) And other one is for only reading operation. Both of them use the same structure. So can I use one persistence unit for both of them?

I found this ReplicationDriver which is solve my problem. I just define my master and slave servers and on the code side I just define @Transaction(readOnly=true). So when transaction is readOnly=true manager use my slave and other case use my master according to connector/j documentation. Now when use this tomcat can not connect to my database. I mean it just wait nothing happen. This my apache tomcat context.xml

<?xml version='1.0' encoding='utf-8'?>
<Context>
    <WatchedResource>WEB-INF/web.xml</WatchedResource>

    <Resource 
        auth="Container" 
        driverClassName="com.mysql.jdbc.ReplicationDriver" 
        maxActive="250" 
        maxIdle="100"
        maxWait="30"
        validationQuery="select 1"
        name="mysql/test" 
        type="javax.sql.DataSource" 
        url="jdbc:mysql:replication://address=(host=master)(user=root)(password=mroot),address=(host=slave)(user=root)(password=sroot)/test?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF8&amp;failOverReadOnly=false&amp;maxReconnects=10" 
    />
</Context>

And this my 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:jee="http://www.springframework.org/schema/jee"
    xmlns:tx="http://www.springframework.org/schema/tx"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

<bean class="org.springframework.orm.jpa.support.PersistenceAnnotationBeanPostProcessor" />

    <tx:annotation-driven/>

    <bean id="jpaVendorAdapter" class="org.springframework.orm.jpa.vendor.EclipseLinkJpaVendorAdapter"/>

    <jee:jndi-lookup id="dataSource" jndi-name="mysql/test" expected-type="javax.sql.DataSource" />

    <bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
        <property name="persistenceUnitName" value="test_pu" />
        <property name="dataSource" ref="dataSource"/>
        <property name="jpaVendorAdapter" ref="jpaVendorAdapter" />
    </bean>

    <bean id="transactionManager" class="org.springframework.orm.jpa.JpaTransactionManager">
        <property name="entityManagerFactory" ref="entityManagerFactory"/>
    </bean> 
    <bean class="org.springframework.dao.annotation.PersistenceExceptionTranslationPostProcessor"/>
</beans>

What is the my wrong? why tomcat can not connect to db? By the way use eclipslink provider v2.4.0

UPDATE

I don't know why but when I use jdbc:mysql://address this format my program can not connect to db and gives the below exception

Caused by: java.lang.NullPointerException
    at com.mysql.jdbc.NonRegisteringDriver.parseHostPortPair(NonRegisteringDriver.java:204)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2235)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284).

But when use jdbc:mysql://host1,host2 format connection successful. If we use second version I should use same username and password for each connection, but the first version we can define different username and password. According to MySQL Connector J documentation

The following is an alternate format for JDBC URLs connecting to a MySQL server, which is mandatory for IPv6 connections, but can also be used with IPv4 (items in square brackets ([ ]) are optional):

jdbc:mysql://address=(key1=value)[(key2=value)]...[,address=(key3=value)[(key4=value)]...]...[/[database]]» [?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

It means that this works also with IP4 but does not work. Is there any thing I miss my configuration?

How on earth would that work and what good would it do? Are you talking about a master db and read replicas? — Kayaman, Nov 22 '16 at 08:03
@kayaman yes. I am talking about a master db and replicate server. — sancho, Nov 22 '16 at 08:04
Related http://stackoverflow.com/questions/25911359/read-write-splitting-hibernate — Kayaman, Nov 22 '16 at 08:16
http://www.codingpedia.org/ama/how-to-setup-multiple-data-sources-with-spring-and-jpa/ — Zia, Nov 22 '16 at 08:23
@Zia That describes multiple persistence units with multiple datasources. — Kayaman, Nov 22 '16 at 08:25
@Zia thanks but I read this post which you mentioned. In that post explains two different persistence unit. He creates two entity manager with two different persistence unit. my question is about using the same persistence unit with two different entity manager. Please see Kayaman's link. It is not answer my question but aim is same — sancho, Nov 22 '16 at 08:26

score -2 · Answer 1 · edited May 23 '17 at 12:13

-2

As per my knowledge, it's not possible For details please refer below link:Exaplained why not

edited May 23 '17 at 12:13

Community

1
1

answered Nov 22 '16 at 08:04

mayank agrawal

628
5
20

Spring. Multiple data source with one persistence unit

UPDATE

1 Answers1