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:

Hibernate Dialect Resolution

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 VARBINARYcolumn 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:

Hibernate 6 MySQL Dialect

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.

Transactions and Concurrency Control eBook

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.