Pattern for connecting to different databases using JDBC

Question

I'm writing an application which has to be configurable to connect to Oracle, SQL Server and MySQL depending on client whim.

Up till now I'd been planning on using the JDBC-ODBC bridge and just connecting to the databases using different connection strings.

I'm told this is not very efficient.

1. Is there a pattern or best practice for connecting to multiple database systems? Or for selecting which driver to use?

2. Should I have it configurable? but include all three drivers or build three separate clients?

I'm not doing anything complex just pumping (inserting) data into the database from an event stream.

Answer 1

If you need anything complex, Hibernate is a good choice.

otherwise, what I would do is store your connection details in a properties file (or some other form of configuration) - namely: driver classname, JDBC url, username and password.

Then, all you need to do is load up the connection details from your properties file and include the correct JAR file on your classpath and you're done.

You could use a library such as Commons-DBCP if you wanted it to be a little easier to configure but other than that it's all you need to do (provided your SQL statements work on every database, of course).

Answer 2

I would suggest that you make it configurable and include the three drivers. You can use a pattern like this: Create a super class (lets call it DAO) that provides the functionality of connecting to the database. This could be abstract.

Create a concrete sub class for each type of database that you wish to connect to. So you may end up with MySQLDAO, MSSQLDAO, and OracleDAO. each one will load the respective driver and use its respective connection string.

Create another class (lets call it DAOFactory) with a method getDAO(DB) that will create an instance of the DAO depending on the value of DB.

So for instance(in Pseudocode):

 if(DB.equals("MySQL")){
    DAO = new MySQLDAO();
}
return DAO;

So any code that needs to connect to the database will call the DAOFactory and ask for a DAO instance. You may store the DB value in an external file (like a properties file) so that you do not have to modify code to change the type of database.

this way your code does not need to know which type of database it is connecting to, and if you decide to support a fourth type of database later you will have to add one more class and modify the DAOFactory, not the rest of your code.

Answer 3

Take a look at Datasource. This is the preferred mechanism for obtaining a database connection.

IMO this provides an adminstrator the greatest flexibility for choosing database, connection pooling, and transaction strategies.

If you're using tomcat, then see here for how to register a Datasource with tomcat's JNDI.

If you're using Spring, then you can obtain a Datasource using jee:jndi-lookup.

If you're using Spring, but don't want to use JNDI, take a look at DriverManagerDataSource for a discussion of how to obtain a pooled Datasource (DBCP or C3P0).

Answer 4

If you're careful (and you test), you can do this with straight JDBC and just vary the driver class and connection information. You definitely want to stay away from the JDBC-ODBC bridge as it's generally slow and unreliable. The bridge is more likely to behave differently across dbs than JDBC is.

I think the DAO path is overkill if your requirements are as simple as listed.

If you're doing a lot of inserts, you might want to investigate prepared statements and batched updates as they are far more efficient. This might end up being less portable - hard to say without testing.