Software: Java J2EE Application hosted on Tomcat Server.
UPDATE
The current software is JAVA Web application containing just JSP and Servlets no frameworks or anything. Simple JSP and Servlets communicating with Oracle DB
I have no issues in re-writing the application, but since the two
copies of it will be maintained I want the code to be same and so when
applying a change I need to apply them same on both places. Instead of
writing two sets of different code I want to keep the code same while
and intermediate layer to handle the DB communication
Software with same code is being hosted and run at two different locations miles away(two separate instances).
Initially the back-end database of the application was same(viz. Oracle).
Now one of the location has stopped the support on oracle and is switching to the Open Source Postgres.
So now the (to be) scenario is something like this
Software: Java J2EE Application hosted on Tomcat Server.
Database 1: Oracle
Database 2: Postgres
At any point of time the application will be using only one database as they are running different instances.
Can an intermediate be added that can interpret the request from application and query depending on the database which needs to be used to fetch data. if yes which is the best option for that.
All this so that every time there is a change in the application, the change takes place uniquely on each copies and an overhead of maintaining two different copies can be avoided.
6
This is essentially what an Object/Relational Mapping Framework (such as Hibernate) can do for you. It provides a layer on top of the database that abstracts away the details of querying the database by providing a database-independent layer and query language. Hibernate provides (among other ways of querying the database) HQL for this:
from Cat as cat
left join cat.kittens as kitten
with kitten.bodyWeight > 10.0
While this looks a lot like regular SQL it is acutally an intermediary language that will be translated to the dialect of SQL the underlying database system uses.
1
This sounds like a good opportunity to use the Strategy design pattern. You will have a data access strategy that will check the environment and execute the proper code depending on what the back end is. “Checking the environment” could be something as simple as using an environment variable or something more high level like injecting the appropriate query handlers during application init (easily done using Spring or another DI framework).