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.