Getting my webapp to be database agnostic with Hibernate...

Question

So the ultimate in scope-creep came in the other day: since we're using Hibernate, could we make our webapp run on Oracle as well as MySQL, interchangably?

I thought this would be a simple case of changing hibernate.cfg.xml so that instead of explicity stating MySQL-specific options, it would reference a JNDI datasource, allowing the application to build regardless of the database we intend to deploy to. Then changing to a different database would simply mean changing the separate datasource configuration in JBoss, Jetty, WebLogic etc. Is this realistic?

Well, I got as far as setting that up in Jetty, but What's tripping me up right now is error about the hibernate.dialect not having been set in hibernate.cfg.xml. But If I set the dialect there, then my app is still going to be built in either MySQL or Oracle flavours, which is not really what I want.

Either I'm trying to attempt the impossible or I've missed something fundamentally obvious... anyone else had a similar problem (and subsequent solution/workaround)?

Answer 1

Ah - no. The dialect is a configuration item only. Sure the app needs to be RECONFIGURED for another database.

It basically defines the SQL generator (i.e. how the outgoing SQL looks like) and triggers no change in your application. Similar to the database connection string.

Reason: Even SQL Server may have different dialects for different versions.

Answer 2

While you can (and actually should when deploying in a container) configure Hibernate to use a JNDI datasource (and thus setup the driver class name, the db URL, the db user name and password at the application server level), database still have specificities (column types, proprietary syntax and specific features like autoincrement columns in MySQL, sequence in Oracle, etc) that Hibernate must take into account. And this is exactly the purpose of SQL Dialects. So if you want to change the database (and if your mappings or annotations are portable, I'm thinking to id generation here), you'll have at least to set the right dialect.

Answer 3

You can still do this dynamically. Before initializing your configuration/session factory, open a direct connection to the data source using JNDI. Then use connection.getMetaData().getDatabaseProductName() or some other JDBC call to find out what database the connection uses, and set the dialect accordingly, e.g. hibernateConfiguration.setProperty(Environment.DIALECT, deducedDialect)

I have an app that can run in SQL Server, Oracle, MySQL, DB2, and Informix. If you can control the data model and keep things simple, the built-in dialects will be sufficient. However, there may be situations where you need to extend the dialect(s) to take advantage of more advanced features.