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.
|
1 2 3 4 5 6 7 |
<persistence-unit name="sample" transaction-type="JTA"> <jta-data-source>java:jboss/datasources/sample</jta-data-source> <exclude-unlisted-classes /> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect" /> </properties> </persistence-unit> |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
import java.sql.DatabaseMetaData; import java.sql.SQLException; import java.util.HashMap; import java.util.Map; import org.hibernate.dialect.Dialect; import org.hibernate.exception.JDBCConnectionException; public class MyDialectResolver implements org.hibernate.service.jdbc.dialect.spi.DialectResolver { private static Map<String, Class<? extends Dialect>> DIALECT_BY_NAME = new HashMap<String, Class<? extends Dialect>>(); @Override public Dialect resolveDialect(DatabaseMetaData dbMetaData) throws JDBCConnectionException { String databaseName = getDatabaseName(dbMetaData); Dialect dbDialect = lookupDialect(databaseName); return dbDialect; } private String getDatabaseName(DatabaseMetaData dbMetaData) { try { return dbMetaData.getDatabaseProductName(); } catch (SQLException e) { // log something return null; } } private Dialect lookupDialect(String databaseName) { Class<? extends Dialect> dialectClass = DIALECT_BY_NAME.get(databaseName); if (dialectClass == null) { return null; } try { return dialectClass.newInstance(); } catch (InstantiationException | IllegalAccessException e) { // log something return null; } } } |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
{ registerDialect(H2Dialect.class, "H2"); registerDialect(MySQL5Dialect.class, "MySQL"); registerDialect(SQLServer2012Dialect.class, "Microsoft SQL Server"); registerDialect(HSQLDialect.class, "HSQL Database Engine"); registerDialect(DB2Dialect.class, "DB2", "DB2/NT64", "DB2/LINUXX8664"); } private static void registerDialect(Class<? extends Dialect> dialect, String... databaseNames) { for (String databaseName : databaseNames) { DIALECT_BY_NAME.put(databaseName, dialect); } } |
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.
|
1 2 3 4 5 6 7 |
<persistence-unit name="sample" transaction-type="JTA"> <jta-data-source>java:jboss/datasources/sample</jta-data-source> <exclude-unlisted-classes /> <properties> <property name="hibernate.dialect_resolvers" value="com.exxeta.blog.jee.jpa.MyDialectResolver " /> </properties> </persistence-unit> |
As you can see, the new property hibernate.dialect_resolvers replaces hibernate.dialect from listing 1.
Happy Coding,
Andreas
Leave a Comment