Targeting a Java app at Oracle AND Postgres

Question

Does anyone have any experience with creating database agnostic apps in Java, particularly with Hibernate, and simultaneously targeting Oracle and Postgres databases?

In particular I am looking at Oracle Spatial and PostGIS. We want to create a Java based SOA which can be used with both Oracle Spatial and PostGIS back ends. I've used Hibernate with both these databases but never with the intention of targeting both. I can create scenarios where the same code can generate different results depending on which database is used.

It maybe that hibernate can handle this but it would be nice to hear if there are any known problems.

Ken

Answer 1

Hibernate handles targetting different databases using its Dialect abstraction. You don't need to make any changes to your application itself only the hibernate configuration for each database.

You can either specify in your configuration the dialect to use with your database or allow Hibernate to use the JDBC driver settings to determine the appropriate dialect.

Following the comment received, have you looked at Hibernate Spacial? It is an extension to Hibernate to support geographic data by providing the necessary Hibernate types and dialects.

Answer 2

In addition to using Hibernates dialects you will want a database creation management tool like Liquibase which will allow the creation code to be abstracted away from the specific syntax of the different databases.

Answer 3

along with hibernate i can recommend Hibernate Spatial , an extension which supports Mysql, Oracle and Postgre, with their respective GIS extensions.

some pitfalls i encountered:

be aware, the configuration of the dialects was not trivial to do correctly. make sure the dialects are not reconfigured for every statement, as it happened to me.

depending on the features from hibernatespatial you use you might get locked in on a specific version number of hibernate

you can use the criteria api ONLY, hql is not directly supported.

my code using hibernatespatial looks like this:

  if (query.getMaxDistance() != null && query.getCenter() != null) {
        basicCriteria.add(SpatialRestrictions.within("coordinate", GeoidCircleFactory.circle(query.getCenter(), query.getMaxDistance())));
    }

you will suffer from some of the quite dire constraints postgis and others are under. i would recommend to relax some of your application needs to better fit the possibilities of your DB. for example, queries in "angle space" are much easier to do than in "euclidean space".

the code contained in GeoidCircleFactory looks quite scary... :)