Hibernate on Oracle AND SQLServer

Question

I'm introducing a DAO layer in our application currently working on SQL Server because I need to port it to Oracle.

I'd like to use Hibernate and write a factory (or use dependency injection) to pick the correct DAOs according to the deployment configuration. What are the best practices in this case? Should I have two packages with different hibernate.cfg.xml and *.hbm.xml files and pick them accordingly in my factory? Is there any chance that my DAOs will work correctly with both DBMS without (too much) hassle?

Answer 1

Assuming that the table names and columns are the same between the two, you should be able to use the same hbm.xml files. However you will certainly need to supply different a Hibernate Configuration value (hibernate.cfg.xml), as you will need to change Hibernate's dialect from SQLServer to Oracle.

If there are slight name differences between the two, then I would create two sets of mapping files - one per Database server - and package these up into separate JARs (such as yourproject-sqlserver-mappings.jar and yourproject-oracle-mappings.jar), and deploy the application with one JAR or the other depending on the environment.

Answer 2

I did this for a client a while back -- at deployment depending on a property set in a production.properties file I changed out the hibernate.dialect in the cfg file using Ant (you can use any xml transformer). However this would only work if the Hibernate code is seamless btw both DBs i.e. no db-specific function calls etc. HQL/JPAQL has standard function calls that help ion this regard like UPPER(s), LENGTH(s) etc.

If the db implementations must necessarily be different then you'd have to do something like what @matt suggested.

Answer 3

I've worked on an app that supports a lot of databases (Oracle, Informix, SQL Server, MySQL). We have one configuration file and one set of mappings. We use jndi for the database connection so we don't have to deal with different connection URLs in the app. When we initialize the SessionFactory we have a method that deduces the type of database from the underlying connection. For example, manually get a connection via JNDI and then use connection.getMetaData().getDatabaseProductName() to find out what the database is. You could also use a container environment variable to explicitly set it. Then set the dialect using configuration.setProperty(Environment.DIALECT, deducedDialect) and initialize the SessionFactory as normal.

Some things you have to deal with:

• Primary key generation. We use a customized version of the TableGenerator strategy so we have one key table with columns for table name and next key. This way every database can use the same strategy rather than sequence in Oracle, native for SQL Server, etc.
• Functions specific to databases. We avoid them when possible. Hibernate dialects handle the most common ones. Occasionally we'll have to add our own to our custom dialect classes, .e.g. date arithmetic is pretty non-standard, so we'll just make up a function name and map it to each database's way of doing it.
• Schema generation - we use the Hibernate schema generation class - it works with the dialects to create the correct DDL for each type of database and forces the database to match the mappings. You have to be aware of the keywords for each database, e.g. don't try to have a USER table in Oracle (USERS will work), or a TRANSLATION table in MySQL.

Answer 4

There is a table mapping the differences between Oracle and SQLServer here: http://psoug.org/reference/sqlserver.html

In my opinion the biggest pitfalls are: 1) Dates. The functions and mechanics are completely different. You will have to use different code for each DB. 2) Key generation - Oracle and SQLServer use different mechanics and if you try to avoid the "native" generation altogether by having your own keys table - well, you just completely serialized all your "inserts". Not good for performance. 3) Concurrency/locking is a bit different. Parts of the code that is performance sensitive will probably be different for each DB. 4) Oracle is case sensitive, SQLServer is not. You need to be careful with that.

There are lots more :) Writing SQL code that will run on two DBs is challenging. Making it fast can seem nearly impossible at times.