oracle 使用不同的 jdbc 驱动程序连接到多个数据库

connecting to multiple databases using different jdbc drivers

提问by ziggy

I have a requirement to write a daemon based java process (Not web based) that will connect to an Oracle 10G database, read some data from it and then connect to a SQL Server database and write the data to a table.

我需要编写一个基于守护进程的 java 进程（不是基于 web 的），该进程将连接到 Oracle 10G 数据库，从中读取一些数据，然后连接到 SQL Server 数据库并将数据写入表。

Sounds simple enough but i have a couple of queries about this.

听起来很简单，但我对此有几个疑问。

• i will need to have two jdbc drivers, i.e. one for connecting to the Oracle database and the other for connecting to the sql server database. The sql server jdbc driver is the jtds jdbc driver (http://jtds.sourceforge.net/) and for Oracle i will be using the standard oracle jdbc driver. Am i likely to come across any problems with both drivers available in the classpath together?

• My guess is that all i need is a ConnectionManager class to manage the connections and a client DAO class that would call the relevant method to get the connection it needs depending on whether it is reading from Oracle or writing to SQL Server. Is this a reasonable approach or is there a better design/pattern for this?

• 我将需要有两个 jdbc 驱动程序，即一个用于连接到 Oracle 数据库，另一个用于连接到 sql server 数据库。sql server jdbc 驱动程序是 jtds jdbc 驱动程序（http://jtds.sourceforge.net/），对于 Oracle，我将使用标准的 oracle jdbc 驱动程序。我可能会遇到类路径中可用的两个驱动程序的任何问题吗？

• 我的猜测是，我只需要一个 ConnectionManager 类来管理连接和一个客户端 DAO 类，该类将调用相关方法来获取所需的连接，具体取决于它是从 Oracle 读取还是写入 SQL Server。这是一种合理的方法还是有更好的设计/模式？

EDIT

编辑

Ok i have tried to put together a quick design solution. See image below

好的，我已经尝试组合一个快速的设计解决方案。见下图

The problem i think i am having is how to commit. Here is the flow of processing

我认为我遇到的问题是如何提交。这是处理流程

• InvoiceBD gets an Oracle connection from the factory class and calls InvoiceUploadDAO.readData passing it the Oracle connection object.
• InvoiceBD get a SQL Server connection from the factory class and calls InvoiceUploadDAO.writeData passing it the SQL Server connection object.
• InvoiceBD reuses the Oracle connection to call InvoiceUploadDAO.update status to 'Complete' set status on the Oracle database.

• InvoiceBD 从工厂类获取 Oracle 连接并调用 InvoiceUploadDAO.readData 将 Oracle 连接对象传递给它。
• InvoiceBD 从工厂类获取 SQL Server 连接并调用 InvoiceUploadDAO.writeData 将 SQL Server 连接对象传递给它。
• InvoiceBD 重用 Oracle 连接调用 InvoiceUploadDAO.update status 到 Oracle 数据库上的“完成”设置状态。

InvoiceBD commits the Oracle connection. InvoiceBD commits the SQL Server connection.

InvoiceBD 提交 Oracle 连接。InvoiceBD 提交 SQL Server 连接。

Or if something goes wrong both connection objects are rolled back.

或者如果出现问题，两个连接对象都会回滚。

Does that sound about right?

那个听起来是对的吗？

Thanks

谢谢

回答by Vineet Reynolds

Am i likely to come across any problems with both drivers available in the classpath together?

我可能会遇到类路径中可用的两个驱动程序的任何问题吗？

Unlikely. The DriverManager.getConnectionmethod actually delegates the construction of the connection to all drivers registered with it. Only drivers that recognize the protocols in the JDBC URL will return the connection. The JDBC specification states:

不太可能。该DriverManager.getConnection方法实际上将连接的构造委托给所有注册的驱动程序。只有识别 JDBC URL 中的协议的驱动程序才会返回连接。JDBC 规范指出：

When the DriverManageris trying to establish a connection, it calls that driver's connect method and passes the driver the URL. If the Driverimplementation understands the URL, it will return a Connectionobject; otherwise it returns null.

...

The format of a JDBC URL is :

jdbc:<subprotocol>:<subname>

当DriverManager试图建立连接时，它会调用该驱动程序的 connect 方法并将 URL 传递给驱动程序。如果Driver实现理解 URL，它将返回一个Connection对象；否则返回null。

...

JDBC URL 的格式为：

jdbc:<subprotocol>:<subname>

In the case of both jTDS and the Oracle (thin) driver, the protocol formats are different, and hence, you would never experience a problem. However, remember not to place more than one version of the same driver.

对于 jTDS 和 Oracle（瘦）驱动程序，协议格式不同，因此您永远不会遇到问题。但是，请记住不要放置多个版本的同一驱动程序。

Is this a reasonable approach or is there a better design/pattern for this?

这是一种合理的方法还是有更好的设计/模式？

You are looking for a DataSource. DataSources would have been availble in a Java EE environment, and not in Java SE applications. You can however, build your own DataSource or a similar class; you don't need to implement the DataSource interface itself, but you could do something similar. In your context, the ConnectionManagerclass of yours will assume the role of the DataSource by possibly accepting a parameter that distinguishes which database to connect to; you could think about using a connection pool in case you need one (unlikely if you need only one connection to the database).

您正在寻找一个DataSource. DataSources 在 Java EE 环境中可用，而不是在 Java SE 应用程序中。但是，您可以构建自己的 DataSource 或类似的类；您不需要实现 DataSource 接口本身，但您可以执行类似的操作。在您的上下文中，您的ConnectionManager类将通过可能接受区分要连接到哪个数据库的参数来承担 DataSource 的角色；如果您需要一个连接池，您可以考虑使用一个连接池（如果您只需要一个到数据库的连接，则不太可能）。

You could also adopt @duffymo's approach of building DAO classes, although it is better suited for a situation where the SQL queries are different.

您也可以采用 @duffymo 的构建 DAO 类的方法，尽管它更适合 SQL 查询不同的情况。

回答by duffymo

1. No problems with both drivers in the classpath. You might want to think about using XA drivers for both if you need the read and write to be a single transaction. If you ever need two phase commit, you'll want XA drivers for both.
2. You'll want two DAO instances, one for Oracle reads and another for SQL Server writes.

1. 类路径中的两个驱动程序都没有问题。如果您需要将读取和写入作为单个事务，您可能需要考虑将 XA 驱动程序用于两者。如果您需要两阶段提交，您将需要 XA 驱动程序。
2. 您需要两个 DAO 实例，一个用于 Oracle 读取，另一个用于 SQL Server 写入。