I wish to implement an example in java that reads a configuration from some config file and, according to that, when user interacts using the page the applying will store some data on either MySql or Oracle based on the configuration parameters. Exactly how should we implement this sample in many efficient and wise way?
Indeed using Hibernate or JPA enables you to definitely abstract the database variations away.
Having a dependency injection framework like Spring or Guice after that you can create 2 service instances which differ only within the persistence manager that is injected.
Within this situation you can preserve almost 100% from the code identical for that 2 databases which guarantees they're not going to get free from sync with time.
- Make an interface for data storage, and employ that inside your application
- Make an abstract class which implements this interface and implements the functionality which MySQL and Oracle share.
- Make two classes, one for MySQL and something for Oracle which implement the database-specific stuff.
- Inside your configuration, specifiy which class to make use of (MySQL or Oracle database class).
Maybe use something similar to Hibernate, which abstracts the database from you.
Following on @Sjoerd's answer:
Any tools or free libs that may be useful?
Hibernate as well as other JPA implementation is the best choice.
Alternatively, Spring has some JDBC support classes which do a few of the work ... if you're able to discover which of the several options is a great match for your needs.
Regrettably, applying a credit card applicatoin that actually works against multiple database backends is effort, regardless of how you're doing so. In my opinion, you typically finish track of an answer that does not perform in addition to a solution that's customized to only one database back finish.
Basically had my way, database suppliers who won't implement the SQL standard could be first from the wall ... come the revolution.