1

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.

Improve this question
6
  • @DocBrown Not sure if this question can satisfy the requirements I have.As I don't fetch data based on URL request and my user-end code is fixed its in JAVA. My requirement is for something that can interpret a request and run it on the different database.
    – S.M.09
    Commented Mar 12, 2015 at 7:14
  • 1
    Are you sure an intermediate level is the correct solution? It's a common solution (and you already got a good answer for that) but there's tradeoffs. Mostly those tradeoff are performance wise. In mainly two ways. The layer will add some perfomance penalty and you won't be able to fully adjust you queries for the perfect performance for that specific database. Make sure you're fine with this before taking this path.
    – iveqy
    Commented Mar 12, 2015 at 9:10
  • So your requirement is not to change your already existing Java application? I do not know any solution for making this possible afterwards, when your application was not designed to be DB agnostic from the ground. If I got you wrong, and you are looking for solutions which include changing the application, you need to give more details about the current state of the implemenation, does it make use of an ORM, did you use a lot of Oracle specfic stuff like stored procedures, how big is your db access layer, which DB API are you using currently, and so on.
    – Doc Brown
    Commented Mar 12, 2015 at 11:39
  • See wiki.openbravo.com/wiki/… for more information what you are asking for. And note the cite in the article: "Compatibility and compatibility layers are an illusion".
    – Doc Brown
    Commented Mar 12, 2015 at 11:48
  • @DocBrown The link is helpful, But I need not worry about the migration, migration is out of my scope of work.But will keep it handy. My issue is I need to have the software compatible for both DB. I have updated the question, hope it helps.
    – S.M.09
    Commented Mar 13, 2015 at 5:15

2 Answers 2

Reset to default
5

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.

Improve this answer
1
  • I can attest that this can be accomplished using Hibernate. To switch data sources, switch your Hibernate data source configuration. Using JPA w/ Hibernate works the same way.
    – Mike Partridge
    Commented Mar 12, 2015 at 13:07
0

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

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.