Java – connect to multiple databases using different JDBC drivers

I need to write a daemon based java process (not web-based), which will connect to Oracle 10g database, read some data from it, then connect to SQL Server database and write the data to the table

It sounds simple, but I have a few questions about it

>I need two JDBC drivers, one for connecting to Oracle database and the other for connecting to SQL Server database The SQL Server jdbc driver is a JTDS jdbc driver（ http://jtds.sourceforge.net/ ）For Oracle, I will use the standard Oracle jdbc driver May I encounter any problems with the two drivers available in the classpath? > My guess is that what I need is a ConnectionManager class to manage connections and a client Dao class. It will call relevant methods to obtain the connections it needs, depending on whether it reads from Oracle or writes to SQL server Is this a reasonable approach or is there a better design / pattern?

edit

Well, I'm trying to sort out a quick design solution See the figure below

I think my problem is how to submit This is the processing flow

>Invoicebd obtains the Oracle connection from the factory class and calls invoiceuploaddao ReadData passes the Oracle connection object to it. > Invoicebd obtains the SQL Server connection from the factory class and calls invoiceuploaddao Writedata, pass the SQL Server connection object to it. > Invoicebd reuses Oracle connections to invoiceuploaddao The update status call sets the status for "done" on the Oracle database

Invoicebd submits an Oracle connection Invoicebd submits the SQL Server connection

Or if there is a problem, both connection objects will be rolled back

Does that sound right?

thank you

Solution

Unlikely DriverManager. The getconnection method actually delegates the construction of the connection to all drivers registered in it Only drivers that recognize the protocol in the JDBC URL will return the connection JDBC specification statement:

For JTDS and Oracle (thin) drivers, the protocol format is different, so you will never encounter problems However, remember not to place multiple versions of the same driver

You are looking for a datasource Datasources can be used in Java EE environment instead of Java se application However, you can build your own datasource or similar classes; You do not need to implement the datasource interface itself, but you can perform similar operations In your context, your ConnectionManager class will assume the role of datasource by accepting parameters that distinguish the database to be connected; You can consider using a connection pool in case you need a connection pool (unlikely if you only need one connection to the database)

You can also use @ duffymo to build Dao classes, although it is more suitable for different situations of SQL queries