Hibernate – SQL Dialects

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.

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.