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

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

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");
- Advertisement -

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. 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
DB2org.hibernate.dialect.DB2DialectAn SQL dialect for DB2.
DB2 AS/400org.hibernate.dialect.DB2400DialectAn SQL dialect for DB2/400.
DB2 OS390org.hibernate.dialect.DB2390DialectAn SQL dialect for DB2/390.
DB2390V8Dialect org.hibernate.dialect.DB2390V8DialectAn SQL dialect for DB2/390 version 8.
DB2400V7R3Dialectorg.hibernate.dialect.DB2400V7R3DialectAn SQL dialect for i.
DB297Dialectorg.hibernate.dialect.DB297DialectAn SQL dialect for DB2 9.7.
H2org.hibernate.dialect.H2DialectA dialect compatible with the H2 database.
HyperSQL (HSQL)org.hibernate.dialect.HSQLDialectAn SQL dialect compatible with HSQLDB (HyperSQL).
Informixorg.hibernate.dialect.InformixDialectSeems to work with Informix Dynamic Server Version 7.31.UD3, Informix JDBC driver version 2.21JC3.
Ingresorg.hibernate.dialect.IngresDialectAn SQL dialect for Ingres 9.2.
Ingres 9org.hibernate.dialect.Ingres9DialectA SQL dialect for Ingres 9.3 and later versions.
Ingres 10org.hibernate.dialect.Ingres10DialectA SQL dialect for Ingres 10 and later versions.
MariaDBDialectorg.hibernate.dialect.MariaDBDialectwhich is the base class for all MariaDB dialects and it works with any MariaDB version
MariaDB53Dialectorg.hibernate.dialect.MariaDB53Dialectwhich is intended to be used with MariaDB 5.3 or newer versions
MariaDB10Dialectorg.hibernate.dialect.MariaDB10DialectMariaDB 10 or newer versions
MariaDB103Dialectorg.hibernate.dialect.MariaDB103DialectAn SQL dialect for MariaDB 10.3 and later, provides sequence support, lock-timeouts, etc.
MariaDB102Dialectorg.hibernate.dialect.MariaDB102Dialec MariaDB 10.2 or newer versions
Microsoft SQL Server 2000org.hibernate.dialect.SQLServerDialectA dialect for Microsoft SQL Server 2000
Microsoft SQL Server 2005org.hibernate.dialect.SQLServer2005DialectA dialect for Microsoft SQL 2005.
Microsoft SQL Server 2008org.hibernate.dialect.SQLServer2008DialectA dialect for Microsoft SQL Server 2008 with JDBC Driver 3.0 and above
Microsoft SQL Server 2012org.hibernate.dialect.SQLServer2012DialectMicrosoft SQL Server 2012 Dialect
MySQLorg.hibernate.dialect.MySQLDialectAn SQL dialect for MySQL (prior to 5.x).
MySQL with InnoDBorg.hibernate.dialect.MySQLInnoDBDialectDeprecated Use “hibernate.dialect.storage_engine=innodb” environment variable or JVM system property instead.
MySQL with MyISAMorg.hibernate.dialect.MySQLMyISAMDialectDeprecated Use “hibernate.dialect.storage_engine=myisam” environment variable or JVM system property instead.
MySQL5org.hibernate.dialect.MySQL5DialectAn SQL dialect for MySQL 5.x specific features.
MySQL5 with InnoDBorg.hibernate.dialect.MySQL5InnoDBDialectDeprecated Use “hibernate.dialect.storage_engine=innodb” environment variable or JVM system property instead.
MySQL8Dialectorg.hibernate.dialect. MySQL8DialectAn SQL dialect for MySQL 8.x specific features.
Oracle 8iorg.hibernate.dialect.Oracle8iDialectA dialect for Oracle 8i.
Oracle 9iorg.hibernate.dialect.Oracle9iDialectA dialect for Oracle 9i databases.
Oracle 10g and laterorg.hibernate.dialect.Oracle10gDialectA dialect specifically for use with Oracle 10g.
Oracle12cDialectorg.hibernate.dialect.Oracle12cDialectAn SQL dialect for Oracle 12c.
OracleTypesHelperorg.hibernate.dialect.OracleTypesHelperA Helper for dealing with the OracleTypes class
PostgreSQL 8.1org.hibernate.dialect.PostgreSQL81DialectAn SQL dialect for Postgres
PostgreSQL 8.2org.hibernate.dialect.PostgreSQL82DialectAn SQL dialect for Postgres 8.2 and later, adds support for “if exists” when dropping tables
PostgreSQL 9 and laterorg.hibernate.dialect.PostgreSQL9DialectAn SQL dialect for Postgres 9 and later.
PostgreSQL91Dialectorg.hibernate.dialect.PostgreSQL91DialectAn SQL dialect for Postgres 9.1 and later, adds support for PARTITION BY as a keyword.
PostgreSQL92Dialectorg.hibernate.dialect.PostgreSQL92DialectAn SQL dialect for Postgres 9.2 and later, adds support for JSON data type
PostgreSQL93Dialectorg.hibernate.dialect.PostgreSQL93DialectAn SQL Dialect for PostgreSQL 9.3 and later.
PostgreSQL94Dialectorg.hibernate.dialect.PostgreSQL94DialectAn SQL dialect for Postgres 9.4 and later.
PostgreSQL95Dialectorg.hibernate.dialect.PostgreSQL95DialectAn SQL dialect for Postgres 9.5 and later.
PostgreSQLDialectorg.hibernate.dialect.PostgreSQLDialectDeprecated use PostgreSQL82Dialect instead
PostgresPlusDialectorg.hibernate.dialect.PostgrePlusDialectAn SQL dialect for Postgres Plus
SAP DBorg.hibernate.dialect.SAPDBDialectAn SQL dialect compatible with SAP DB.
SAP HANA (column store)org.hibernate.dialect.HANAColumnStoreDialectAn SQL dialect for the SAP HANA column store.
SAP HANA (row store)org.hibernate.dialect.HANARowStoreDialectAn SQL dialect for the SAP HANA row store.
Sybaseorg.hibernate.dialect.SybaseDialectAll Sybase dialects share an IN list size limit.
Sybase 11org.hibernate.dialect.Sybase11DialectA SQL dialect suitable for use with Sybase 11.9.2 (specifically: avoids ANSI JOIN syntax)
Sybase ASE 15.5org.hibernate.dialect.SybaseASE15DialectAn SQL dialect targeting Sybase Adaptive Server Enterprise (ASE) 15 and higher.
Sybase ASE 15.7org.hibernate.dialect.SybaseASE157DialectAn SQL dialect targeting Sybase Adaptive Server Enterprise (ASE) 15.7 and higher.
Sybase Anywhereorg.hibernate.dialect.SybaseAnywhereDialect SQL Dialect for Sybase Anywhere extending Sybase (Enterprise) Dialect (Tested on ASA 8.x)
Teradata14Dialectorg.hibernate.dialect.Teradata14Dialect A dialect for the Teradata database
Teradataorg.hibernate.dialect.TeradataDialectA dialect for the Teradata database created by MCR as part of the dialect certification process.

4. Conclusion

In this guide we have covered what is Dialect in Hibernate and List of Popular Hibernate dialects.

References

  1. Dialect class hibernatedoc
  2. Hibernate user manual
  3. MariaDB JbossDoc
- Advertisement -
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. Also founder of javabydeveloper.com. Follow him on LinkedIn or Twitter or Facebook

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Stay in Touch

Categories

Related Articles