Routing Read-Write transactions to Primary and Read_only transactions to Replicas using Spring and Hibernate

Question

I have an application that uses Hibernate/JPA, with Spring and Jersey. In my application context I set the data source, define an entity manager factory, set the transaction manager with that entity manger factory, and have various service methods annotated with the transactional annotation, so I also have the tx:annotation-driven definition to wire in my transaction manager where needed. This setup works great, I've been able to read and write just fine. I would like to move to a DB setup where I have a Master with multiple slaves (MySQL). So I want all the methods annotated with transactional to use a data source pointing to the master db server, and all others to use a connection pool of the slaves.

I've tried creating two different datasources, with two different entity manager factories, and two different persistent units - ugly to say the least. I tried a MySQL Proxy but we had more problems with that then we need. The connection pooling is handled in the servlet container already. Could I implement something in Tomcat that reads the transaction and directs it to the right database server, or is there a way I could get all those methods annotated with the transactional annotation to use a particular datasource?

Answer 1

Here's what I ended up doing and it worked quite well. The entity manager can only have one bean to use as the data source. So what I had to do was to create a bean that routed between the two where necessary. That one ben is the one I used for the JPA entity manager.

I setup two different data sources in tomcat. In the server.xml I created two resources (data sources).

<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" />

You could have the database tables on the same server, in which case the ip addresses or domain would be the same, just different dbs - you get the jist.

I then added a resource link in the context.xml file in tomcat that referenced these to resources.

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

These resource links are what spring reads in the application context.

In the application context I added a bean definition for each resource link and added one additional bean definition that referenced a Datasource Router bean I created that takes in a map (enum) of the two previously created beans (bean definition).

<!--
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>

The entity manager bean definition then referenced the 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>

I defined some properties in a properties file, but you can replace the ${} values with your own specific values. So now I have one bean that uses two other beans that represent my two data sources. The one bean is the one I use for JPA. It's oblivious of any routing happening.

So now the routing 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();
    }

}

The overridden method is called by the entity manager to determine the data source basically. The DatasourceProvider has a thread local (thread safe) property with a getter and setter method as well as the clear data source method for clean up.

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();
    }

}

I have a generic DAO implementation with methods I use to handle various routine JPA calls (getReference, persist, createNamedQUery & getResultList, etc.). Before it makes the call to the entityManager to do whatever it needs to do I set the DatasourceProvider's datasource to the read or write. The method can handle that value being passed in as well to make it a little more dynamic. Here is an example method.

@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();
}

The AvailableDataSources is an enum with READ or WRITE, which references the appropriate data source. You can see that in the map defined in my bean on the application context.