Open In App

Hibernate – SQL Dialects

Improve
Improve
Like Article
Like
Save
Share
Report

Hibernate is an open-source, non-invasive, lightweight java ORM(Object-relational mapping) framework that is used to develop persistence logic that is independent of Database software. An ORM(Object-relational mapping) framework simplifies data creation, data manipulation, and data access.  It is a programming technique that maps the object to the data stored in the database. It internally uses the JDBC API to interact with the database.

SQL Dialects

Dialect is a class that acts as a bridge between Java JDBC types and SQL types, which contains the 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. A hibernate dialect gives information to the framework of how to convert hibernate queries(HQL) into native SQL queries.

Since Hibernate is database agnostic. It can work with different databases. However, databases have proprietary extensions/native SQL variations, and set/sub-set of SQL standard implementations. Due to this hibernate has to use database-specific SQL. Dialect specifies the type of database used in hibernate so that hibernate can switch to the database-specific SQL generator code. Dialects can be used in the following ways:

  • To generate Optimized SQL queries
  • To interact with a particular Database if the application works with the help of more than one Database.
  • To set default values for hibernate configuration file properties based on the database software even though they are not specified in the configuration file.

SQL Dialects Configuration

The SQL dialect converts the HQL query which we write in java or any other object-oriented program to the specific database SQL query. For connecting any hibernate application with the database, it is required to provide the configuration of SQL dialect. We can provide it in hibernate.cfg.xml (DB2 Dialect) as :

<hibernate-configuration>

        <session-factory name=”session-factory”>

                 <property name=”hibernate.dialect”>org.hibernate.dialect.DB2Dialect</property>

        </session-factory>

</hibernate-configuration>

We can also specify in the properties file as :

hibernate.dialect=org.hibernate.dialect.DB2Dialect

hibernate.dialect property makes Hibernate generate the appropriate SQL statements for the given specific database.

Hibernate Configuration: MySQL dialect

Configuration config = new Configuration() 

   .addClass(org.javabydeveloper.domain.Student.class)

   .setProperty(“hibernate.dialect”, “org.hibernate.dialect.MySQL5Dialect“)

   .setProperty(“hibernate.connection.datasource”, “jdbc:mysql://localhost:3380/<dbname>”)

   .setProperty(“hibernate.order_updates”, “true”);

Configuration in hibernate.cfg.xml (MySQL8)

    <property name=”connection.url”>jdbc:mysql://localhost:3380/jpa_jbd?serverTimezone=UTC&useSSL=false</property>

   <property name=”connection.username”>user</property>

   <property name=”connection.password”>pass</property>

   <property name=”connection.driver_class”>com.mysql.jdbc.Driver</property>

   <property name=”dialect”>org.hibernate.dialect.MySQL8Dialect</property>

List of SQL Dialects in Hibernate

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

RDBMS

Dialects

DB2 org.hibernate.dialect.DB2Dialect
DB2 AS/400 org.hibernate.dialect.DB2400Dialect
DB2 OS390 org.hibernate.dialect.DB2390Dialect
PostgreSQL org.hibernate.dialect.PostgreSQLDialect
MySQL5 org.hibernate.dialect.MySQL5Dialect
MySQL5 with InnoDB org.hibernate.dialect.MySQL5InnoDBDialect
MySQL with MyISAM org.hibernate.dialect.MySQLMyISAMDialect
Oracle (any version) org.hibernate.dialect.OracleDialect
Oracle 9i org.hibernate.dialect.Oracle9iDialect
Sybase org.hibernate.dialect.SybaseASE15Dialect
Microsoft SQL Server 2000 org.hibernate.dialect.SQLServerDialect
Microsoft SQL Server 2008  org.hibernate.dialect.SQLServer2008Dialect
SAP DB org.hibernate.dialect.SAPDBDialect
Informix org.hibernate.dialect.InformixDialect
HypersonicSQL org.hibernate.dialect.HSQLDialect
H2 Database org.hibernate.dialect.H2Dialect
Ingres org.hibernate.dialect.IngresDialect
Progress org.hibernate.dialect.ProgressDialect
Mckoi SQL org.hibernate.dialect.MckoiDialect
Interbase org.hibernate.dialect.InterbaseDialect
Pointbase org.hibernate.dialect.PointbaseDialect
FrontBase org.hibernate.dialect.FrontbaseDialect
Firebird org.hibernate.dialect.FirebirdDialect

The hibernate.dialect property should be set to the correct org.hibernate.dialect.Dialect subclass for the application database. If the Dialect class is not specified in the configuration, for most of the databases, Hibernate tries to resolve dialect names from the database connections. But It is best to provide dialect so that Hibernate identifies the appropriate Dialect class for specific database versions.


Last Updated : 20 Feb, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads