Hibernate – Dialect

Dialect in Hibernate – Dialect is a class and a bridge between Java JDBC types and SQL types, which contains mapping between java language data type and database datatype. Dialect allows Hibernate to generate SQL optimized for a particular relational database. Hibernate generates queries for the specific database based on the Dialect class.

1. Hibernate Dialect Class

1. When JPA EntityManagerFactory or Hibernate SessionFactory instance created, internally the Dialect instance will be created.
2. When Dialect instance created, it registers all the SQL functions for provided Dialect in configuration, to translate Hibernate queries to SQL Queries and also register all SQL types and Java JDBC types, to map java types to database types and vice versa.
3. The SQL types not same for the all data bases, so that specific database will have specific dialect, for example, dialect for the DB2 database is org.hibernate.dialect.DB2Dialect , dialect for the MySql 5 database is org.hibernate.dialect.MySQL5Dialect.
4. When you switch from one database to another database, one primary change is changing corresponding Dialect to the database.
5. To implement custom dialect, extend Dialect class Sub classes should provide a public default constructor that register a set of type mappings and default Hibernate properties. Sub classes should be immutable.

2. Sample Dialect configurations

2.1. hibernate.properties example : hibernate dialect for db2 database

hibernate.connection.driver_class = com.ibm.db2.jcc.DB2Driver
hibernate.connection.url = jdbc:db2://<host>:<port50000>/<dbname>
hibernate.connection.username = myuser
hibernate.connection.password = secret
hibernate.dialect = org.hibernate.dialect.DB2Dialect

2.2. hibernate.cfg.xml Example : org.hibernate.dialect.MySQL8Dialect

    <property name="connection.url">jdbc:mysql://localhost:3306/jpa_jbd?serverTimezone=UTC&useSSL=false</property>
    <property name="connection.username">root</property>
    <property name="connection.password">password</property>
    <property name="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="dialect">org.hibernate.dialect.MySQL8Dialect</property>

2.3. Hibernate Configuration example : mysql dialect

Configuration cfg = new Configuration()
    .addClass(org.javabydeveloper.domain.Student.class)
    .setProperty("hibernate.dialect", "org.hibernate.dialect.MySQL5Dialect")
    .setProperty("hibernate.connection.datasource", "jdbc:mysql://localhost:3306/<dbname>")
    .setProperty("hibernate.order_updates", "true");

2.4. JPA persistence.xml example : postgresql dialect

<persistence-unit name="RESOURCE_LOCAL">
     <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
     <properties>
        <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver" />
        <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost/dbName" />
        <property name="javax.persistence.jdbc.user" value="dbuser" />
        <property name="javax.persistence.jdbc.password" value="password" />
        <property name="hibernate.show_sql" value="true" />
      
        <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
    </properties>
  </persistence-unit>

3. Why do use Dialect in Hibernate?

Hibernate generates all your entity mappings and hibernate queries to Specific database query and uses JDBC to execute them, when ever required to translate entity mappings and hibernate query to Data base Specific query, it uses Dialect. Let’s have a look in to an example, the following image illustrates clear picture about Dialect.

If you set hbm2ddl.auto to create in configuration and create session factory new Configuration().configure().buildSessionFactory() :

1. For your all of entities, Hibernate generates a native query for create tables and executes it using JDBC.
2. In order to generate native query, Hibernate uses your entity mapping and uses the provided dialect class to map java types to database data types.
3. Like wise, Hibernate uses Dialect class when ever required to create database specific queries.

4. What happens if you do not specify Dialect ?

If you do not specify Dialect class in configuration, for most of the databases Hibernate try to resolve dialect name from database connection. It’s best practice to provide dialect, so that Hibernate identifies appropriate Dialect class for specific database version.

5. List of SQL Dialects in Hibernate

All the Hibernate dialects available in org.hibernate.dialect package. Following are the popular List of SQL Dialects in Hibernate.

Dialect Name	Dialect class	Description
DB2	org.hibernate.dialect.DB2Dialect	An SQL dialect for DB2.
DB2 AS/400	org.hibernate.dialect.DB2400Dialect	An SQL dialect for DB2/400.
DB2 OS390	org.hibernate.dialect.DB2390Dialect	An SQL dialect for DB2/390.
DB2390V8Dialect	org.hibernate.dialect.DB2390V8Dialect	An SQL dialect for DB2/390 version 8.
DB2400V7R3Dialect	org.hibernate.dialect.DB2400V7R3Dialect	An SQL dialect for i.
DB297Dialect	org.hibernate.dialect.DB297Dialect	An SQL dialect for DB2 9.7.
H2	org.hibernate.dialect.H2Dialect	A dialect compatible with the H2 database.
HyperSQL (HSQL)	org.hibernate.dialect.HSQLDialect	An SQL dialect compatible with HSQLDB (HyperSQL).
Informix	org.hibernate.dialect.InformixDialect	Seems to work with Informix Dynamic Server Version 7.31.UD3, Informix JDBC driver version 2.21JC3.
Ingres	org.hibernate.dialect.IngresDialect	An SQL dialect for Ingres 9.2.
Ingres 9	org.hibernate.dialect.Ingres9Dialect	A SQL dialect for Ingres 9.3 and later versions.
Ingres 10	org.hibernate.dialect.Ingres10Dialect	A SQL dialect for Ingres 10 and later versions.
MariaDBDialect	org.hibernate.dialect.MariaDBDialect	which is the base class for all MariaDB dialects and it works with any MariaDB version
MariaDB53Dialect	org.hibernate.dialect.MariaDB53Dialect	which is intended to be used with MariaDB 5.3 or newer versions
MariaDB10Dialect	org.hibernate.dialect.MariaDB10Dialect	MariaDB 10 or newer versions
MariaDB103Dialect	org.hibernate.dialect.MariaDB103Dialect	An SQL dialect for MariaDB 10.3 and later, provides sequence support, lock-timeouts, etc.
MariaDB102Dialect	org.hibernate.dialect.MariaDB102Dialec	MariaDB 10.2 or newer versions
Microsoft SQL Server 2000	org.hibernate.dialect.SQLServerDialect	A dialect for Microsoft SQL Server 2000
Microsoft SQL Server 2005	org.hibernate.dialect.SQLServer2005Dialect	A dialect for Microsoft SQL 2005.
Microsoft SQL Server 2008	org.hibernate.dialect.SQLServer2008Dialect	A dialect for Microsoft SQL Server 2008 with JDBC Driver 3.0 and above
Microsoft SQL Server 2012	org.hibernate.dialect.SQLServer2012Dialect	Microsoft SQL Server 2012 Dialect
MySQL	org.hibernate.dialect.MySQLDialect	An SQL dialect for MySQL (prior to 5.x).
MySQL with InnoDB	org.hibernate.dialect.MySQLInnoDBDialect	Deprecated Use “hibernate.dialect.storage_engine=innodb” environment variable or JVM system property instead.
MySQL with MyISAM	org.hibernate.dialect.MySQLMyISAMDialect	Deprecated Use “hibernate.dialect.storage_engine=myisam” environment variable or JVM system property instead.
MySQL5	org.hibernate.dialect.MySQL5Dialect	An SQL dialect for MySQL 5.x specific features.
MySQL5 with InnoDB	org.hibernate.dialect.MySQL5InnoDBDialect	Deprecated Use “hibernate.dialect.storage_engine=innodb” environment variable or JVM system property instead.
MySQL8Dialect	org.hibernate.dialect.MySQL8Dialect	An SQL dialect for MySQL 8.x specific features.
Oracle 8i	org.hibernate.dialect.Oracle8iDialect	A dialect for Oracle 8i.
Oracle 9i	org.hibernate.dialect.Oracle9iDialect	A dialect for Oracle 9i databases.
Oracle 10g and later	org.hibernate.dialect.Oracle10gDialect	A dialect specifically for use with Oracle 10g.
Oracle12cDialect	org.hibernate.dialect.Oracle12cDialect	An SQL dialect for Oracle 12c.
OracleTypesHelper	org.hibernate.dialect.OracleTypesHelper	A Helper for dealing with the OracleTypes class
PostgreSQL 8.1	org.hibernate.dialect.PostgreSQL81Dialect	An SQL dialect for Postgres
PostgreSQL 8.2	org.hibernate.dialect.PostgreSQL82Dialect	An SQL dialect for Postgres 8.2 and later, adds support for “if exists” when dropping tables
PostgreSQL 9 and later	org.hibernate.dialect.PostgreSQL9Dialect	An SQL dialect for Postgres 9 and later.
PostgreSQL91Dialect	org.hibernate.dialect.PostgreSQL91Dialect	An SQL dialect for Postgres 9.1 and later, adds support for PARTITION BY as a keyword.
PostgreSQL92Dialect	org.hibernate.dialect.PostgreSQL92Dialect	An SQL dialect for Postgres 9.2 and later, adds support for JSON data type
PostgreSQL93Dialect	org.hibernate.dialect.PostgreSQL93Dialect	An SQL Dialect for PostgreSQL 9.3 and later.
PostgreSQL94Dialect	org.hibernate.dialect.PostgreSQL94Dialect	An SQL dialect for Postgres 9.4 and later.
PostgreSQL95Dialect	org.hibernate.dialect.PostgreSQL95Dialect	An SQL dialect for Postgres 9.5 and later.
PostgreSQLDialect	org.hibernate.dialect.PostgreSQLDialect	Deprecated use PostgreSQL82Dialect instead
PostgresPlusDialect	org.hibernate.dialect.PostgrePlusDialect	An SQL dialect for Postgres Plus
SAP DB	org.hibernate.dialect.SAPDBDialect	An SQL dialect compatible with SAP DB.
SAP HANA (column store)	org.hibernate.dialect.HANAColumnStoreDialect	An SQL dialect for the SAP HANA column store.
SAP HANA (row store)	org.hibernate.dialect.HANARowStoreDialect	An SQL dialect for the SAP HANA row store.
Sybase	org.hibernate.dialect.SybaseDialect	All Sybase dialects share an IN list size limit.
Sybase 11	org.hibernate.dialect.Sybase11Dialect	A SQL dialect suitable for use with Sybase 11.9.2 (specifically: avoids ANSI JOIN syntax)
Sybase ASE 15.5	org.hibernate.dialect.SybaseASE15Dialect	An SQL dialect targeting Sybase Adaptive Server Enterprise (ASE) 15 and higher.
Sybase ASE 15.7	org.hibernate.dialect.SybaseASE157Dialect	An SQL dialect targeting Sybase Adaptive Server Enterprise (ASE) 15.7 and higher.
Sybase Anywhere	org.hibernate.dialect.SybaseAnywhereDialect	SQL Dialect for Sybase Anywhere extending Sybase (Enterprise) Dialect (Tested on ASA 8.x)
Teradata14Dialect	org.hibernate.dialect.Teradata14Dialect	A dialect for the Teradata database
Teradata	org.hibernate.dialect.TeradataDialect	A dialect for the Teradata database created by MCR as part of the dialect certification process.

5. Conclusion

In this guide we have covered what is Dialect and why do we use it in Hibernate and the List of Popular Hibernate dialects.

References

1. Dialect class hibernatedoc
2. Hibernate user manual
3. MariaDB JbossDoc
4. JPA MySQL Example