Spring JPA Read Write split - use a transaction for a write data source

Question

I have an application using Hibernate / JPA with Spring and Jersey. In my application context, I installed a data source, defined a factory entity manager, installed a transaction manager with this factory entity box, and had various maintenance methods annotated with transaction annotation, so I also have tx: annotation- to configure posting in my transaction manager, where it's necessary. This setting works great, I can read and write well. I would like to move on to setting up a database where I have a wizard with several subordinates (MySQL). Therefore, I want all methods annotated by the transaction to use a data source pointing to the master db server, and all the rest, to use the connection pool for the slaves.

I tried creating two different data sources, with two different entity management factories and two different constant units - ugly, to say the least. I tried MySQL Proxy, but we had more problems with what we needed. The connection pool is already being processed in the servlet container. Can I implement something in Tomcat that reads a transaction and routes it to the correct database server, or is there a way I could get all of these methods annotated using transactional annotation to use a specific data source?

Answer 1

, . bean . bean, , . ben , JPA.

tomcat. server.xml ( ).

<Resource name="readConnection" auth="Container" type="javax.sql.DataSource"
          username="readuser" password="readpass"
          url="jdbc:mysql://readipaddress:3306/readdbname"
          driverClassName="com.mysql.jdbc.Driver"
          initialSize="5" maxWait="5000"
          maxActive="120" maxIdle="5"
          validationQuery="select 1"
          poolPreparedStatements="true"
          removeAbandoned="true" />
<Resource name="writeConnection" auth="Container" type="javax.sql.DataSource"
          username="writeuser" password="writepass"
          url="jdbc:mysql://writeipaddress:3306/writedbname"
          driverClassName="com.mysql.jdbc.Driver"
          initialSize="5" maxWait="5000"
          maxActive="120" maxIdle="5"
          validationQuery="select 1"
          poolPreparedStatements="true"
          removeAbandoned="true" />

, IP- , dbs - jist.

context.xml tomcat, .

<ResourceLink name="readConnection" global="readConnection" type="javax.sql.DataSource"/>
<ResourceLink name="writeConnection" global="writeConnection" type="javax.sql.DataSource"/>

- , spring .

bean bean, Datasource Router bean, , () beans (bean).

<!--
Data sources representing master (write) and slaves (read).
-->
<bean id="readDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="readConnection" /> 
    <property name="resourceRef" value="true" />
    <property name="lookupOnStartup" value="true" />
    <property name="cache" value="true" />
    <property name="proxyInterface" value="javax.sql.DataSource" />  
</bean>

<bean id="writeDataSource" class="org.springframework.jndi.JndiObjectFactoryBean">
    <property name="jndiName" value="writeConnection" />
    <property name="resourceRef" value="true" />
    <property name="lookupOnStartup" value="true" />
    <property name="cache" value="true" />
    <property name="proxyInterface" value="javax.sql.DataSource" />
</bean>

<!--
Provider of available (master and slave) data sources.
-->
<bean id="dataSource" class="com.myapp.dao.DatasourceRouter">
    <property name="targetDataSources">
      <map key-type="com.myapp.api.util.AvailableDataSources">
         <entry key="READ" value-ref="readDataSource"/>
         <entry key="WRITE" value-ref="writeDataSource"/>
      </map>
   </property>
   <property name="defaultTargetDataSource" ref="writeDataSource"/>
</bean>

bean dataSource bean.

<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="persistenceUnitName" value="${jpa.persistenceUnitName}" />
    <property name="jpaVendorAdapter"> 
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter"> 
            <property name="databasePlatform" value="${jpa.dialect}"/>
            <property name="showSql" value="${jpa.showSQL}" />
        </bean>
    </property>
</bean>

, ${} . , bean, beans, . bean - , JPA. .

, bean.

public class DatasourceRouter extends AbstractRoutingDataSource{

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException{
    // TODO Auto-generated method stub
    return null;
    }

    @Override
    protected Object determineCurrentLookupKey(){
    return DatasourceProvider.getDatasource();
    }

}

. DatasourceProvider () getter setter, .

public class DatasourceProvider{
    private static final ThreadLocal<AvailableDataSources> datasourceHolder = new ThreadLocal<AvailableDataSources>();

    public static void setDatasource(final AvailableDataSources customerType){
    datasourceHolder.set(customerType);
    }

    public static AvailableDataSources getDatasource(){
    return (AvailableDataSources) datasourceHolder.get();
    }

    public static void clearDatasource(){
    datasourceHolder.remove();
    }

}

DAO , JPA (getReference, persist, createNamedQUery getResultList ..). entityManager, , , DatasourceProvider . , . .

@Override
public List<T> findByNamedQuery(final String queryName, final Map<String, Object> properties, final int... rowStartIdxAndCount)
{
DatasourceProvider.setDatasource(AvailableDataSources.READ);
final TypedQuery<T> query = entityManager.createNamedQuery(queryName, persistentClass);
if (!properties.isEmpty())
{
    bindNamedQueryParameters(query, properties);
}
appyRowLimits(query, rowStartIdxAndCount);

return query.getResultList();
}

AvailableDataSources - READ WRITE, . , bean .

Answer 2

: readonly writeonly, MASTER/SLAVE .

, AbstractRoutingDataSource spring. , , .

<bean id="commentsDataSource" class="com.nextep.proto.spring.ReadWriteDataSourceRouter">
    <property name="targetDataSources">
        <map key-type="java.lang.String">
            <entry key="READ" value="java:comp/env/jdbc/readdb"/>
            <entry key="WRITE" value="java:comp/env/jdbc/writedb"/>
        </map>
    </property>
    <property name="defaultTargetDataSource" value="java:comp/env/jdbc/readdb"/>
</bean>

:

public class ReadWriteDataSourceRouter extends AbstractRoutingDataSource {

@Override
protected Object determineCurrentLookupKey() {
    return TransactionSynchronizationManager.isCurrentTransactionReadOnly() ? "READ"
            : "WRITE";
}
}

, , Spring, , , . - TransactionSynchronizationManager.isCurrentTransactionReadOnly() readonly ( ) defineCurrentLookupKey(), .

... , TransactionSynchronizationManager, .

, , Christophe

Answer 3

<bean id="entityManagerFactory" 
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="persistenceUnitName" value="filerp-pcflows" />
    <property name="dataSource" ref="pooledDS" />
    <property name="persistenceXmlLocation" value="classpath:powercenterCPCPersistence.xml" />
    <property name="jpaVendorAdapter">
        <bean class="org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter">
            <property name="showSql" value="true" />
            <!--<property name="formatSql" value="true" />
            --><property name="generateDdl" value="false" />
            <property name="database" value="DB2" />
        </bean>
    </property>
</bean>

                                      - >

           

<bean id="pool" autowire-candidate="false" class="org.apache.commons.pool.impl.GenericObjectPool" destroy-method="close">
    <property name="minEvictableIdleTimeMillis" value="300000"/>
    <property name="timeBetweenEvictionRunsMillis" value="60000"/>
    <property name="maxIdle" value="2"/>
    <property name="minIdle" value="0"/>
    <property name="maxActive" value="8"/>
    <property name="testOnBorrow" value="true"/>
</bean>

<bean id="dsConnectionFactory" class="org.apache.commons.dbcp.DataSourceConnectionFactory">
    <constructor-arg><ref bean="dataSource" /></constructor-arg>
</bean> 
<bean id="poolableConnectionFactory" class="org.apache.commons.dbcp.PoolableConnectionFactory">
    <constructor-arg index="0"><ref bean="dsConnectionFactory" /></constructor-arg>
    <constructor-arg index="1"><ref bean="pool" /></constructor-arg>
    <constructor-arg index="2"><null /></constructor-arg>
    <constructor-arg index="3"><value>select 1 from ${cnx.db2.database.creator}.TPROFILE</value></constructor-arg>
    <constructor-arg index="4"><value>false</value></constructor-arg>
    <constructor-arg index="5"><value>true</value></constructor-arg>
</bean>

<bean id="pooledDS" class="org.apache.commons.dbcp.PoolingDataSource"
    depends-on="poolableConnectionFactory">
    <constructor-arg>
        <ref bean="pool" />
    </constructor-arg>
</bean> 
<import resource="powercenterCPCBeans.xml"/>