java 如何设置 Hibernate 来读/写不同的数据源？

How to setup Hibernate to read/write to different datasources?

提问by Deejay

Using Spring and Hibernate, I want to write to one MySQL master database, and read from one more more replicated slaves in cloud-based Java webapp.

使用 Spring 和 Hibernate，我想写入一个 MySQL 主数据库，并在基于云的 Java webapp 中从另一个复制的从数据库中读取。

I can't find a solution that is transparent to the application code. I don't really want to have to change my DAOs to manage different SessionFactories, as that seems really messy and couples the code with a specific server architecture.

我找不到对应用程序代码透明的解决方案。我真的不想更改我的 DAO 来管理不同的 SessionFactories，因为这看起来非常混乱并且将代码与特定的服务器架构耦合。

Is there any way of telling Hibernate to automatically route CREATE/UPDATE queries to one datasource, and SELECT to another? I don't want to do any sharding or anything based on object type - just route different types of queries to different datasources.

有什么方法可以告诉 Hibernate 自动将 CREATE/UPDATE 查询路由到一个数据源，并将 SELECT 路由到另一个数据源？我不想根据对象类型进行任何分片或任何操作 - 只需将不同类型的查询路由到不同的数据源。

回答by Alex Fedulov

An example can be found here: https://github.com/afedulov/routing-data-source.

可以在此处找到示例：https: //github.com/afedulov/routing-data-source。

Spring provides a variation of DataSource, called AbstractRoutingDatasource. It can be used in place of standard DataSource implementations and enables a mechanism to determine which concrete DataSource to use for each operation at runtime. All you need to do is to extend it and to provide an implementation of an abstract determineCurrentLookupKeymethod. This is the place to implement your custom logic to determine the concrete DataSource. Returned Object serves as a lookup key. It is typically a String or en Enum, used as a qualifier in Spring configuration (details will follow).

Spring 提供了 DataSource 的一种变体，称为AbstractRoutingDatasource. 它可以用来代替标准的 DataSource 实现，并启用一种机制来确定在运行时为每个操作使用哪个具体的 DataSource。您需要做的就是扩展它并提供抽象determineCurrentLookupKey方法的实现。这是实现您的自定义逻辑以确定具体数据源的地方。返回的对象用作查找键。它通常是一个 String 或 en Enum，在 Spring 配置中用作限定符（详细信息将在后面）。

package website.fedulov.routing.RoutingDataSource

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class RoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        return DbContextHolder.getDbType();
    }
}

You might be wondering what is that DbContextHolder object and how does it know which DataSource identifier to return? Keep in mind that determineCurrentLookupKeymethod will be called whenever TransactionsManager requests a connection. It is important to remember that each transaction is "associated" with a separate thread. More precisely, TransactionsManager binds Connection to the current thread. Therefore in order to dispatch different transactions to different target DataSources we have to make sure that every thread can reliably identify which DataSource is destined for it to be used. This makes it natural to utilize ThreadLocal variables for binding specific DataSource to a Thread and hence to a Transaction. This is how it is done:

您可能想知道 DbContextHolder 对象是什么，它如何知道要返回哪个 DataSource 标识符？请记住，determineCurrentLookupKey只要 TransactionsManager 请求连接，就会调用该方法。重要的是要记住，每个事务都与一个单独的线程“关联”。更准确地说，TransactionsManager 将 Connection 绑定到当前线程。因此，为了将不同的事务分派到不同的目标数据源，我们必须确保每个线程都可以可靠地识别要使用哪个数据源。这使得利用 ThreadLocal 变量将特定数据源绑定到线程并因此绑定到事务变得很自然。这是如何完成的：

public enum DbType {
   MASTER,
   REPLICA1,
}

public class DbContextHolder {

   private static final ThreadLocal<DbType> contextHolder = new ThreadLocal<DbType>();

   public static void setDbType(DbType dbType) {
       if(dbType == null){
           throw new NullPointerException();
       }
      contextHolder.set(dbType);
   }

   public static DbType getDbType() {
      return (DbType) contextHolder.get();
   }

   public static void clearDbType() {
      contextHolder.remove();
   }
}

As you see, you can also use an enum as the key and Spring will take care of resolving it correctly based on the name. Associated DataSource configuration and keys might look like this:

如您所见，您还可以使用枚举作为键，Spring 会根据名称正确解析它。关联的数据源配置和键可能如下所示：

  ....
<bean id="dataSource" class="website.fedulov.routing.RoutingDataSource">
 <property name="targetDataSources">
   <map key-type="com.sabienzia.routing.DbType">
     <entry key="MASTER" value-ref="dataSourceMaster"/>
     <entry key="REPLICA1" value-ref="dataSourceReplica"/>
   </map>
 </property>
 <property name="defaultTargetDataSource" ref="dataSourceMaster"/>
</bean>

<bean id="dataSourceMaster" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  <property name="url" value="${db.master.url}"/>
  <property name="username" value="${db.username}"/>
  <property name="password" value="${db.password}"/>
</bean>
<bean id="dataSourceReplica" class="org.apache.commons.dbcp.BasicDataSource">
  <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
  <property name="url" value="${db.replica.url}"/>
  <property name="username" value="${db.username}"/>
  <property name="password" value="${db.password}"/>
</bean>

At this point you might find yourself doing something like this:

在这一点上，你可能会发现自己在做这样的事情：

@Service
public class BookService {

  private final BookRepository bookRepository;
  private final Mapper               mapper;

  @Inject
  public BookService(BookRepository bookRepository, Mapper mapper) {
    this.bookRepository = bookRepository;
    this.mapper = mapper;
  }

  @Transactional(readOnly = true)
  public Page<BookDTO> getBooks(Pageable p) {
    DbContextHolder.setDbType(DbType.REPLICA1);   // <----- set ThreadLocal DataSource lookup key
                                                  // all connection from here will go to REPLICA1
    Page<Book> booksPage = callActionRepo.findAll(p);
    List<BookDTO> pContent = CollectionMapper.map(mapper, callActionsPage.getContent(), BookDTO.class);
    DbContextHolder.clearDbType();               // <----- clear ThreadLocal setting
    return new PageImpl<BookDTO>(pContent, p, callActionsPage.getTotalElements());
  }

  ...//other methods

Now we can control which DataSource will be used and forward requests as we please. Looks good!

现在我们可以控制将使用哪个 DataSource 并根据需要转发请求。看起来挺好的！

...Or does it? First of all, those static method calls to a magical DbContextHolder really stick out. They look like they do not belong the business logic. And they don't. Not only do they not communicate the purpose, but they seem fragile and error-prone (how about forgetting to clean the dbType). And what if an exception is thrown between the setDbType and cleanDbType? We cannot just ignore it. We need to be absolutely sure that we reset the dbType, otherwise Thread returned to the ThreadPool might be in a "broken" state, trying to write to a replica in the next call. So we need this:

......或者是吗？首先，那些对神奇 DbContextHolder 的静态方法调用真的很突出。它们看起来不属于业务逻辑。他们没有。它们不仅没有传达目的，而且它们看起来很脆弱且容易出错（忘记清理 dbType 怎么样）。如果在 setDbType 和 cleanDbType 之间抛出异常怎么办？我们不能忽视它。我们需要绝对确定我们重置了 dbType，否则返回到 ThreadPool 的 Thread 可能处于“损坏”状态，尝试在下一次调用中写入副本。所以我们需要这个：

  @Transactional(readOnly = true)
  public Page<BookDTO> getBooks(Pageable p) {
    try{
      DbContextHolder.setDbType(DbType.REPLICA1);   // <----- set ThreadLocal DataSource lookup key
                                                    // all connection from here will go to REPLICA1
      Page<Book> booksPage = callActionRepo.findAll(p);
      List<BookDTO> pContent = CollectionMapper.map(mapper, callActionsPage.getContent(), BookDTO.class);
       DbContextHolder.clearDbType();               // <----- clear ThreadLocal setting
    } catch (Exception e){
      throw new RuntimeException(e);
    } finally {
       DbContextHolder.clearDbType();               // <----- make sure ThreadLocal setting is cleared         
    }
    return new PageImpl<BookDTO>(pContent, p, callActionsPage.getTotalElements());
  }

Yikes >_<! This definitely does not look like something I would like to put into every read only method. Can we do better? Of course! This pattern of "do something at the beginning of a method, then do something at the end" should ring a bell. Aspects to the rescue!

哎呀 >_<！这绝对不像我想放入每个只读方法中的东西。我们能做得更好吗？当然！这种“在方法开始时做某事，然后在最后做某事”的模式应该敲响警钟。救援方面！

Unfortunately this post has already gotten too long to cover the topic of custom aspects. You can follow up on the details of using aspects using this link.

不幸的是，这篇文章已经太长了，无法涵盖自定义方面的主题。您可以使用此链接跟进使用方面的详细信息。

回答by octav

I don't think that deciding that SELECTs should go to one DB (one slave) and CREATE/UPDATES should go to a different one (master) is a very good decision. The reasons are:

我不认为决定 SELECT 应该转到一个 DB（一个从属）而 CREATE/UPDATES 应该转到另一个（主）数据库是一个非常好的决定。原因是：

• replication is not instantaneous, so you could CREATE something in the master DB and, as part of the same operation, SELECT it from the slave and notice that the data hasn't yet reached the slave.
• if one of the slaves is down, you shouldn't be prevented from writing data in the master, because as soon as the slave is back up, its state will be synchronized with master. In your case though, your write operations are dependent on both master and slave.
• How would you then define transactionality if you're in fact using 2 dbs?

• 复制不是即时的，所以你可以在主数据库中创建一些东西，作为相同操作的一部分，从从服务器选择它并注意数据还没有到达从服务器。
• 如果其中一个从站宕机，你不应该被阻止在主站写入数据，因为一旦从站恢复，它的状态将与主站同步。但是，在您的情况下，您的写操作取决于主站和从站。
• 如果您实际上使用 2 dbs，那么您将如何定义事务性？

I would advise using the master DB for all the WRITE flows, with all the instructions they might require (whether they are SELECTs, UPDATE or INSERTS). Then, the application dealing with the read-only flows can read from the slave DB.

我建议将主数据库用于所有 WRITE 流，以及它们可能需要的所有指令（无论是 SELECT、UPDATE 还是 INSERTS）。然后，处理只读流的应用程序可以从从数据库读取。

I'd also advise having separate DAOs, each with its own methods, so that you'll have a clear distinction between read-only flows and write/update flows.

我还建议使用单独的 DAO，每个 DAO 都有自己的方法，这样您就可以清楚地区分只读流和写入/更新流。

回答by Liviu T.

You could create 2 session factories and hava a BaseDao wrapping the 2 factories(or the 2 hibernateTemplates if you use them) and use the get methods with on factory and the saveOrUpdate methods with the other

您可以创建 2 个会话工厂并使用 BaseDao 包装 2 个工厂（或 2 个 hibernateTemplates，如果您使用它们），并使用 on factory 的 get 方法和其他的 saveOrUpdate 方法

回答by KwonNam

Try this way : https://github.com/kwon37xi/replication-datasource

试试这种方式：https: //github.com/kwon37xi/replication-datasource

It works nicely and very easy to implement without any extra annotation or code. It requires only @Transactional(readOnly=true|false).

它运行良好且易于实现，无需任何额外的注释或代码。它只需要@Transactional(readOnly=true|false).

I have been using this solution with Hibernate(JPA),Spring JDBC Template, iBatis.

我一直在将此解决方案与 Hibernate(JPA)、Spring JDBC 模板、iBatis 一起使用。

回答by allen

You can use DDALto implement writting master database and reading slave database in a DefaultDDRDataSource without modifying your Daos, and what's more, DDAL provided loading balance for mulit-slave databases. It doesn't rely on spring or hibernate. There is a demo project to show how to use it: https://github.com/hellojavaer/ddal-demosand the demo1 is just what you described scene.

您可以使用DDAL在DefaultDDRDataSource中实现写主库和读从库，而无需修改您的Daos，而且DDAL为多从库提供了负载均衡。它不依赖于 spring 或休眠。有一个演示项目来展示如何使用它：https: //github.com/hellojavaer/ddal-demos并且 demo1 正是您描述的场景。