What are best ways to implement a single functionality to store data in different databases based on configuration?

Question

Hi all,

I want to implement a sample in java that reads a configuration from some config file and, based on that, when user interacts with the page the application will store some data on either MySql or Oracle according to the configuration parameters. How can we implement this sample in most efficient and smart way?

Answer 1

• Make an interface for data storage, and use that in your application
• Make an abstract class which implements this interface and implements the functionality which MySQL and Oracle have in common.
• Make two classes, one for MySQL and one for Oracle which implement the database-specific stuff.
• In your configuration, specifiy which class to use (MySQL or Oracle database class).

Answer 2

Maybe use something like Hibernate, which abstracts the database away from you.

Answer 3

Indeed using Hibernate or JPA allows you to abstract the database differences away.

With a dependency injection framework like Spring or Guice you can then create 2 service instances which differ only in the persistence manager which is injected.

In this case you can keep almost 100% of the code identical for the 2 databases which guarantees they will not get out of sync over time.

Answer 4

Following up on @Sjoerd's answer:

Any tools or open source libs that can be helpful?

Hibernate or some other JPA implementation is your best bet.

Alternatively, Spring has some JDBC support classes that do some of the work ... if you can figure out which of the various alternatives is a good match to your requirements.

Unfortunately, implementing an application that works against multiple database backends is hard work, no matter how you do it. In my experience, you usually end up with a solution that doesn't perform as well as a solution that is tailored to one and only one database back end.

If I had my way, database vendors who refuse to implement the SQL standard would be first against the wall ... come the revolution.