使用Spring和Hibernate将读写事务路由到主事务并将只读事务路由到副本 [英] Routing Read-Write transactions to Primary and Read_only transactions to Replicas using Spring and Hibernate

问题描述

我有一个使用Spring和Jersey的Hibernate / JPA应用程序。在我的应用程序上下文中，我设置了数据源，定义了一个实体管理器工厂，并使用该实体管理器工厂设置了事务管理器，并使用事务性注释对各种服务方法进行了注释，因此我还要使用tx：annotation-driven定义进行连接在我需要的交易经理中。此设置效果很好，我已经能够读写。我想转到一个数据库设置，其中有一个具有多个从属的主服务器（MySQL）。因此，我希望所有带有事务性注释的方法都使用指向主数据库服务器的数据源，而所有其他方法都使用从属服务器的连接池。

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.

我尝试创建两个不同的数据源，其中包括两个不同的实体管理器工厂和两个不同的持久性单元-至少很难说。我尝试了一个MySQL代理，但那时我们遇到了更多问题。连接池已在servlet容器中处理。我可以在Tomcat中实现一些东西来读取事务并将其定向到正确的数据库服务器，还是有办法让所有带有事务注释的方法使用特定的数据源进行注释？

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?

推荐答案

这就是我最终要做的，并且效果很好。实体管理器只能有一个bean用作数据源。所以我要做的是创建一个必要时在两者之间路由的bean。那一个便是我用于JPA实体管理器的一个。

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.

我在tomcat中设置了两个不同的数据源。在server.xml中，我创建了两个资源（数据源）。

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

您可以将数据库表放在同一服务器上，在这种情况下，IP地址或域将是

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.

然后我在tomcat的context.xml文件中添加了一个资源链接，将这些链接引用到资源。

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

这些资源链接是spring在应用程序上下文中读取的内容。

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

在应用程序上下文中，我为每个资源链接添加了一个Bean定义，并添加了一个附加的Bean定义，该定义引用了我创建的一个包含映射（枚举）的Datasource Router Bean。

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>

然后，实体管理器bean定义引用了dataSource 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>

我在属性文件中定义了一些属性，但是您可以用自己的属性替换$ {}值具体值。所以现在我有一个bean，它使用另外两个bean代表我的两个数据源。一个bean是我用于JPA的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.

现在是路由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方法以及清除数据的clear数据源方法的线程本地（线程安全）属性。

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

}

我有一个通用的DAO实现，我用来处理各种常规JPA调用的方法（getReference，persist，createNamedQUery& getResultList等）。在调用entityManager做任何需要做的事情之前，我将DatasourceProvider的数据源设置为读取或写入。该方法还可以处理传入的值，使其更具动态性。这是一个示例方法。

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

AvailableDataSources是具有READ或WRITE的枚举，它引用适当的数据源。您可以在我的bean上的应用程序上下文中定义的映射中看到这一点。

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.

这篇关于使用Spring和Hibernate将读写事务路由到主事务并将只读事务路由到副本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！