03.23.2016 | Andreas Günzel | comment icon 2 Comments

Dynamically resolve Hibernate database dialect

When using Hibernate as JPA provider you need to tell the JEE application which database vendor you are using. The classic approach is to set the property hibernate.dialect as shown in the snippet of a persistence.xml in listing 1.

This works in most cases. However, I often have to cope with different database vendors serving the same datasource – depending on the current environment. So, for example in production we are running on Oracle or DB2 and for local tests we are limited to a MySQL or H2 installation. This forces us to either ship a separate persistence.xml per stage or to make the persistence.xml somehow configurable.

In these situations I prefer to implement a so called DialectResolver. As the name indicates this interface helps to dynamically detect the needed Hibernate dialect to talk to the database. The resolver is called at the application start-up. The DialectResolver is a non JPA, Hibernate specific extension and comes within the hibernate-core library. You may need to add a dependency to this library for your project.

Listing 2 shows a basic implementation that worked in all of my projects.

We need to implement the interface DialectResolver und thus the method resolveDialect (DatabaseMetaData). This permits access to the database meta-data of a certain JDBC connection. Now we can simply ask for the database’s product name. This might be “H2” for H2 or “MySQL” for MySQL – maybe you recognize a pattern  Using this unique name, I just create a mapping from product name to dialect implementation in the map DIALECT_BY_NAME. To add some mappings, I prefer to use a static initializer block, as shown in listing 3.

The example is reduced to 5 database vendors. Indeed, there are more than 50 dialects supported by Hibernate. In practice, I limit the registered databases to those we need to address in the current project. You should be aware, that there are different dialects for different versions of a specific database. In addition, there might be vendors naming their products in dependence of the underlying architecture or operation system (just like IBM does for its DB2, as shown in the example).

Finally, we need to enable our DialectResolver in the persistence.xml, shown in listing 4.

As you can see, the new property hibernate.dialect_resolvers replaces hibernate.dialect from listing 1.

Happy Coding,
Andreas

hibernate java ee jpa
  1. Williamlip
    Posted on
    I value the post.Thanks Again. Cool. Mccluney
  2. markiewb
    Posted on
    Nice idea. The official documentation the article is based on is https://docs.jboss.org/hibernate/orm/5.0/devguide/en-US/html/ch01.html#d5e428 In our spring-based applications we configure the dialect with a sessionfactory within springbeans.xml. Thus you can inject the dialect via property placeholders.

Leave a Comment