Home Hibernate What is Dialect in Hibernate and List of Dialects

What is Dialect in Hibernate and List of Dialects

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. 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.

hibernate dialect

4. 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 NameDialect classDescription
DB2 org.hibernate.dialect.DB2Dialect An SQL dialect for DB2.
DB2 AS/400 org.hibernate.dialect.DB2400Dialect An SQL dialect for DB2/400.
DB2 OS390org.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.DB297DialectAn 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.
Ingresorg.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 List of Popular Hibernate dialects.

References

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

Satish Varma
Satish Varmahttps://javabydeveloper.com
Satish is post graduated in master of computer applications and experienced software engineer with focus on Spring, JPA, REST, TDD and web development. Follow him on LinkedIn or Twitter or Facebook

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay in Touch

Categories