3

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

Community
  • 1
  • 1
ziggy
  • 15,677
  • 67
  • 194
  • 287
  • No problem with having the 2 jdbc drivers in the classpath together. About the design/pattern just construct it as simple/complex as you need. Two Connection variables could be enough for what you need to do. – Marcelo Jun 18 '11 at 16:32
  • the link to the image is broken, at least for viewing from StackOverflow. By the way, why did you update the question? Is it to validate the design? – Vineet Reynolds Jun 18 '11 at 21:24
  • Yes just wanted to make sure that this was the correct way of doing it. The direct link to the image is http://i279.photobucket.com/albums/kk128/ziggy_76/BlankPackage_Main-1.jpg – ziggy Jun 18 '11 at 21:31

2 Answers2

4

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

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:

When the DriverManager is trying to establish a connection, it calls that driver’s connect method and passes the driver the URL. If the Driver implementation understands the URL, it will return a Connection object; otherwise it returns null.

...

The format of a JDBC URL is :

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.

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

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.

Vineet Reynolds
  • 76,006
  • 17
  • 150
  • 174
  • @ziggy, if you are connecting to two database in the same method, you are most likely going to need a Transaction Coordinator, and XAConnections instead of plain Connections. In your current design, a failure to rollback on the second connection, might not affect the first connection at all, if work has already been committed. – Vineet Reynolds Jun 18 '11 at 21:29
  • What about if i only plan to commit at the end. I.e. only commit from InvoiceBD and only if both writes to both databases have completed? – ziggy Jun 18 '11 at 21:33
  • That's the process I was referring to. The entire commit operation is not atomic. If the first succeeds and the second fails, then you would have to do janitorial work in the first. That's why XA transactions or global transactions that span multiple datasources were conceived. – Vineet Reynolds Jun 18 '11 at 21:37
  • I am not very familiar with XA transactions. I will have to read up on that. Do you know if both the SQL Server (jtds) and Oracle jdbc driver(ojdbc14) support XA transactions. – ziggy Jun 18 '11 at 21:45
  • Yes, they both do. But you'll also need to get a Java Transaction Manager to perform the actual coordination between these two connections. – Vineet Reynolds Jun 18 '11 at 21:52
  • Ok thanks for your help Vineet. I'll do some more research on XA transactions. – ziggy Jun 18 '11 at 22:57
  • @ziggy, you're welcome. I would suggest looking at [Commons DBCP](http://commons.apache.org/dbcp/) and [a related post that demonstrates a few details](http://lafernando.com/2011/01/05/xa-transactions-with-apache-dbcp/) of what you would be dealing with eventually. You would have found this easier in an application server, but it is still doable in a Java SE application. Good luck! – Vineet Reynolds Jun 18 '11 at 23:24
  • Is it not overkill to use an application server? – ziggy Jun 19 '11 at 12:20
  • Short answer - it depends on what your code does; based on what you're presented it is likely to be an overkill for an app with 6 classes. I think you should ask a separate question on this topic, where you can discuss the context in which you've written the daemon. – Vineet Reynolds Jun 19 '11 at 12:57
  • @VineetReynolds, jTDS does not really support XA, but everyone seems to think it does. See: http://jtds.svn.sourceforge.net/viewvc/jtds/branches/jTDS%201.2%20%28stable%29/README.XA?revision=1118&view=markup – NickTee May 07 '12 at 23:09
3
  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.
duffymo
  • 305,152
  • 44
  • 369
  • 561
  • Thanks @duffymo. I dont quite understand why two DAO instances. Is it not just the URL that will be different? I added a DatabaseImpl which has all the common jdbc methods and put database specific methods in the "*Impl" classes. See image above. – ziggy Jun 18 '11 at 21:36