使用不同的jdbc驱动程序连接到多个数据库 [英] connecting to multiple databases using different jdbc drivers

问题描述

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

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.

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

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

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

• 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?

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

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?

好的我试过放在一起快速设计解决方案。见下图：

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从工厂类获取Oracle连接，并调用InvoiceUploadDAO.readData将Oracle连接对象传递给它。


• InvoiceBD从工厂类获取SQL Server连接，并调用InvoiceUploadDAO.writeData将SQL Server连接对象传递给它。


• InvoiceBD重用Oracle连接将InvoiceUploadDAO.update状态调用为Oracle数据库上的完成设置状态。

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

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

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

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

这听起来是对的吗？

谢谢

推荐答案

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

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

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

Unlikely. The DriverManager.getConnection method 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 尝试
时建立一个连接，它调用
驱动程序的connect方法并传递
驱动程序的URL。如果驱动程序
实现了解URL，则
将返回 Connection 对象;
否则返回 null 。

...

JDBC URL的格式为：

The format of a JDBC URL is :

jdbc：< subprotocol>：< subname>

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

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.

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

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

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

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 ConnectionManager class 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).

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

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.

这篇关于使用不同的jdbc驱动程序连接到多个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！