我们可以有多个dataSources到单个数据库

I am having spring webservice application with oracle as a database. Right now i have datasource created using weblogic server. Also using eclipse linkg JPA to do both read and write transactions(insert,Read and update). Now we want to separate dataSources for read(read) and wrtie(insert or update) transactions.

我正在使用oracle作为数据库的spring webservice应用程序。现在我有使用weblogic服务器创建的数据源。还使用eclipse linkg JPA来执行读写事务(插入,读取和更新)。现在我们想要将dataSource分开用于读取(读取)和wrtie(插入或更新)事务。

My current dataSource is as followed:

我当前的dataSource如下:

JNDI NAME : jdbc/POI_DS
URL : jdbc:oracle:thin:@localhost:1521:XE

using this, I am doing both read and write transactions.

使用这个,我正在做读写事务。

What if i do the following:

如果我执行以下操作该怎么办:

JNDI NAME : jdbc/POI_DS_READ
URL : jdbc:oracle:thin:@localhost:1521:XE

JNDI NAME : jdbc/POI_DS_WRITE
URL : jdbc:oracle:thin:@localhost:1521:XE

I knew that using XA datasource we can define multiple dataSources. Can I do same thing without XA dataSource. Does any one tried this kind of approach.

我知道使用XA数据源我们可以定义多个dataSource。没有XA dataSource,我可以做同样的事情。有没有人尝试过这种方法。

::UPDATE::

Thank you all for your responses I have implemented following solution.

谢谢大家对我的解决方案的反应。

I have taken the multiple database approach. where you will define multiple transactionManagers and managerFactory. I have taken only single non xa dataSource(JNDI) that is refereed in EntityManagerFactory Bean.

我采取了多种数据库方法。您将在其中定义多个transactionManagers和managerFactory。我只采用了在EntityManagerFactory Bean中引用的单个非xa dataSource(JNDI)。

you can reefer following links here which are for multiple dataSources Multiple DataSource Approach defining @transactional value

你可以在这里提供以下链接,这些链接用于多个dataSources定义@transactional值的多数据源方法

Also explored on transaction managers org.springframework.transaction.jta.WebLogicJtaTransactionManager and org.springframework.orm.jpa.JpaTransactionManager as well.

还讨论了事务管理器org.springframework.transaction.jta.WebLogicJtaTransactionManager和org.springframework.orm.jpa.JpaTransactionManager。

4 个解决方案

#1

There is an interesting article about this in Spring docs - Dynamic DataSource Routing. There is an example there, that allows you to basically switch data sources at runtime. It should help you. I'd gladly help you more, if you have any more specific questions.

Spring文档中有一篇有趣的文章 - 动态数据源路由。这里有一个例子,它允许您在运行时基本上切换数据源。它应该对你有所帮助。如果你有更具体的问题,我很乐意帮助你。

EDIT: It tells, that the actual use is to have connection to multiple databases via one configuration, but you could manage to create different configs to one database with different params, as you'd need to.

编辑:它告诉我,实际使用是通过一个配置连接到多个数据库,但是您可以根据需要设置为具有不同参数的一个数据库创建不同的配置。

#2

I would suggest using Database "services". Each workload, read-only and read-write, would be using its own service to access the database. That way you can use AWR reports to get statistics for each service. You can also turn off read-write when you keep read-only up and running.

我建议使用数据库“服务”。每个工作负载(只读和读写)都将使用自己的服务来访问数据库。这样,您可以使用AWR报告获取每项服务的统计信息。当您保持只读并运行时,您也可以关闭读写。

Here is a pointer to the Oracle Database documentation that talks about Services: https://docs.oracle.com/database/121/ADMIN/create.htm#CIABBCAI

以下是指向服务的Oracle数据库文档的指针:https://docs.oracle.com/database/121/ADMIN/create.htm#CIABBCAI

#3

If you're using spring, you should be able to accomplish this without using 2 Datasources via spring @Transactional with the readonly property set to true. The reason why I suggest this is that you seem to be concerned about the transactionality only and this seems to be catered for in the spring framework?

如果您正在使用spring,那么您应该能够在不使用2个数据源的情况下通过spring @Transactional并将readonly属性设置为true来实现此目的。我之所以提出这个问题,是因为你似乎只关注事务性,这似乎是在spring框架中得到满足的?

I'd suggest something like this for your case:

我会根据你的情况建议这样的事情:

@Transactional(readOnly = true)
public class DefaultFooService implements FooService {

    public Foo getFoo(String fooName) {
        // do something
    }

    // these settings have precedence for this method
    @Transactional(readOnly = false, propagation = Propagation.REQUIRES_NEW)
    public void updateFoo(Foo foo) {
        // do something
    }
}

Using this style, you should be able to split read only services from their write counterparts, or even have read and write service methods combined. But both of these do not use 2 datasources.

使用此样式,您应该能够从其写入对应项中拆分只读服务,甚至可以将读取和写入服务方法组合在一起。但这两个都不使用2个数据源。

Code is from the Spring Reference

代码来自Spring Reference

#4

I am pretty sure that you need to address the problem on the database / connection url + properties layer. I would google around for something like read write replication.

我很确定你需要解决数据库/连接url +属性层的问题。我会谷歌周围的东西,如读写复制。

Related to your question with JPA and transaction. You are doomed when you are using multiple Datasources. Also XA datasources are not really a solution for that. The only thing they do for you is to ensure consistency over multi data source operations. XA Transaction do only span some sort of logical transaction over two transactions (one for each datasource). From the transaction isolation point of view (as long as your not using READ_UNCOMMITED) both datasources use their own transaction. This means the read data source would not see the changes made by the write transaction.

与您的JPA和交易问题相关。当您使用多个数据源时,您注定要失败。此外,XA数据源并不是真正的解决方案。他们为您做的唯一事情是确保多数据源操作的一致性。 XA Transaction仅跨两个事务(每个数据源一个)跨越某种逻辑事务。从事务隔离的角度来看(只要您不使用READ_UNCOMMITED)两个数据源都使用自己的事务。这意味着读取数据源不会看到写入事务所做的更改。

#1