使用不同的 jdbc 驱动程序连接到多个数据库
我需要编写一个基于守护进程的java进程（不是基于web的），它将连接到Oracle 10G数据库，从中读取一些数据，然后连接到SQL Server数据库并将数据写入表中。
听起来很简单，但我对此有几个疑问。
我需要有两个 jdbc 驱动程序，即一个用于连接到 Oracle 数据库，另一个用于连接到 sql server 数据库。 sql server jdbc 驱动程序是 jtds jdbc 驱动程序 (http://jtds.sourceforge.net/) 和对于 Oracle，我将使用标准的 oracle jdbc 驱动程序。我是否可能会遇到类路径中同时提供的两个驱动程序的任何问题？
我的猜测是，我所需要的只是一个用于管理连接的 ConnectionManager 类和一个客户端 DAO 类，该类将调用相关方法来获取所需的连接，具体取决于它是从 Oracle 读取还是写入 SQL Server。这是一个合理的方法还是有更好的设计/模式？
编辑
好的，我尝试组合一个快速的设计解决方案。请参阅下图
我认为我遇到的问题是如何提交。以下是处理流程
- InvoiceBD 从工厂类获取 Oracle 连接并调用 InvoiceUploadDAO.readData 向其传递 Oracle 连接对象。
- InvoiceBD 从工厂类获取 SQL Server 连接，并调用 InvoiceUploadDAO.writeData 向其传递 SQL Server 连接对象。
- InvoiceBD 重用 Oracle 连接来调用 InvoiceUploadDAO.update status 以将 Oracle 数据库上的状态设置为“完成”。
InvoiceBD 提交 Oracle 连接。 InvoiceBD 提交 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.
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?
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 commits the Oracle connection.
InvoiceBD commits the SQL Server connection.
Or if something goes wrong both connection objects are rolled back.
Does that sound about right?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不太可能。 DriverManager.getConnection 方法实际上将连接的构建委托给所有向其注册的驱动程序。只有识别 JDBC URL 中的协议的驱动程序才会返回连接。 JDBC 规范指出：
对于 jTDS 和 Oracle（瘦）驱动程序，协议格式不同，并且因此，您永远不会遇到问题。但是，请记住不要放置同一驱动程序的多个版本。
您正在寻找
数据源。数据源在 Java EE 环境中可用，而不是在 Java SE 应用程序中可用。但是，您可以构建自己的 DataSource 或类似的类；您不需要实现 DataSource 接口本身，但您可以执行类似的操作。在您的上下文中，您的ConnectionManager类将通过可能接受区分要连接到哪个数据库的参数来承担 DataSource 的角色；如果您需要一个连接池，您可以考虑使用连接池（如果您只需要一个到数据库的连接，则不太可能）。您还可以采用 @duffymo 构建 DAO 类的方法，尽管它更适合 SQL 查询不同的情况。
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: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.
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, theConnectionManagerclass 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).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.