The best way to configure the Hibernate Dialect
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, I’m going to explain what is the best way to configure the Hibernate Dialect so that you get the most out of your database.
Manual Hibernate Dialect resolution
Traditionally, the user had to supply the Hibernate Dialect
via the hibernate.dialect
setting.
So, if you used MySQL 8, you would supply the following setting in the application.properties
Spring Boot configuration file:
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL8Dialect
Or, if you used Aurora MySQL version 2, which is compatible with MySQL 5.7, then you’d have to set the MySQL57Dialect
instead, as explained in this article:
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL57Dialect
The downside of this approach is that when migrating to Amazon Aurora MySQL 3, which is compatible with MySQL 8, we’d have to remember to update the hibernate.dialect
setting.
Automatic Hibernate Dialect resolution
So, a much better approach that has been significantly improved in Hibernate 6 is the automatic Dialect resolution, which looks as follows:
When bootstrapping, Hibernate can extract the DatabaseMetaData
from the JDBC Driver and use it to create the DialectResolutionInfo
that encapsulates both the database server version and the JDBC Driver client version.
With the DialectResolutionInfo
in place, Hibernate can pick up the proper Dialect and configure its behavior based on the underlying database server and client capabilities.
For instance, in Hibernate 6, the MySQLDialect
configures the maximum VARCHAR
and VARBINARY
column lengths based on the underlying MySQL database server version:
public MySQLDialect( DialectResolutionInfo info) { super( info ); int bytesPerCharacter = getCharacterSetBytesPerCharacter( info.getDatabaseMetadata() ); maxVarcharLength = maxVarcharLength( getMySQLVersion(), bytesPerCharacter ); maxVarbinaryLength = maxVarbinaryLength( getMySQLVersion() ); } private static int maxVarcharLength( DatabaseVersion version, int bytesPerCharacter) { // max length for VARCHAR changed in 5.0.3 if (version.isBefore(5)) { return 255; } else { switch (bytesPerCharacter) { case 1: return 65_535; case 2: return 32_767; case 3: return 21_844; case 4: default: return 16_383; } } } private static int maxVarbinaryLength( DatabaseVersion version) { return version.isBefore(5) ? 255 : 65_535; }
Hibernate 6 Dialect options
Prior to Hibernate 6, The Hibernate Dialect
options had proliferated out of control, and the fact that the Hibernate Spatial project has merged into Hibernate made things even worse, as illustrated by the following diagram:
Luckily, in Hibernate 6, you no longer have to reason whether you should use MySQL57Dialect
or the MySQL56InnoDBSpatialDialect
in case you are also employing spatial columns because the MySQLDialect
alone can handle all the flavors of various MySQL versions.
Cool, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
While prior to Hibernate 6, it was common to provide the Dialect
version via the hibernate.dialect
setting, this is no longer the recommended strategy.
Because Hibernate 6 has greatly simplified the Dialect
handlers, it’s best to let Hibernate figure out what Dialect
instance to use based on the underlying database server and client capabilities.
