[hibernate] Why do I need to configure the SQL dialect of a data source?

When we configure a data source using Hibernate, we should add the hibernate.dialect property (or eclipselink.target-database if you are using EclipseLink).

I want to know what is the meaning of dialect? I configure this property according to the documentation of Hibernate but I don't know what's the meaning of it.

Short answer

"The irony of JDBC is that, although the programming interfaces are portable, the SQL language is not. Despite the many attempts to standardize it, it is still rare to write SQL of any complexity that will run unchanged on two major database platforms. Even where the SQL dialects are similar, each database performs differently depending on the structure of the query, necessitating vendor-specific tuning in most cases."

..stolen from Pro JPA 2 Mastering the Java Persistence API, chapter 1, page 9

So, we might think of JDBC as the ultimate specification that abstracts away everything related to databases, but it isn't.

A quote from the JDBC specification, chapter 4.4, page 20:

The driver layer may mask differences between standard SQL:2003 syntax and the native dialect supported by the data source.

May is no guarantee that the driver will, and therefore we should provide the dialect in order to have a working application. In a best-case scenario, the application will work but might not run as effectively as it could if the persistence provider knew which dialect to use. In the case of Hibernate he will refuse to deploy your application unless you feed him the dialect.

What about JPQL then?

The JDBC specification does not mention the word JPQL. JDBC is a standardized way of database access. Go read this JavaDoc and you will find that once the application can access the database, what must be fed into the JDBC compliant driver is vanilla ^{= undecorated} SQL.

It is worth noting that JPQL is a query language, not a data definition language (DDL). So even if we could feed the JDBC driver with JPQL, that would be of no use for the persistence provider during the phase of parsing the persistence.xml file and setting up tables.

Closer look at the property

For your reference, here is an example for Hibernate and EclipseLink on how to specify a Java DB dialect in the persistence.xml file:

<property name="hibernate.dialect" value="org.hibernate.dialect.DerbyTenSevenDialect"/>
<property name="eclipselink.target-database" value="JavaDB"/>

Is the property mandatory?

In theory, the property has not been standardized and the JPA 2.1 specification says not a word about SQL dialects. So we're out of luck and must turn to vendor specific empirical studies and documentation thereof.

Hibernate refuse to accept a deployment archive that hasn't specified the property rendering the archive undeployable. Hibernate documentation says:

Always set the hibernate.dialect property to the correct org.hibernate.dialect.Dialect subclass for your database.

So that is pretty clear. Do note that the dialects listed in the documentation are specifically targeting one or the other vendor. There is no "generic" dialect or anything like that. Given then that the property is an absolute requirement for a successful deployment, you would expect that the documentation of the WildFly application server which bundles Hibernate should say something, but it doesn't.

EclipseLink on the other hand is a bit more forgiving. If you don't provide the property, the deployment deploys (without warning too). EclipseLink documentation says:

Use the eclipselink.target-database property to specify the database to use, controlling custom operations and SQL generation for the specified database.

The talk is about "custom operations and SQL generation", meaning it is bit vague if you ask me. But one thing is clear: They don't say that the property is mandatory. Also note that one of the available values is "Database" which represent "a generic database" target. Hmm, what "dialect" would that be? SQL 2.0?? But then again, the property is called "target-database" and not "dialect" so maybe "Database" translates to no SQL at all lol. Moving on to the GlassFish server which bundles EclipseLink. Documentation (page "6-3") says:

You can specify the optional eclipselink.target-database property to guarantee that the database type is correct.

So GlassFish argues that the property is "optional" and the value added is a "guarantee" that I am actually using Java DB - in case I didn't know.

Conclusion

Copy-paste whatever you can find on google and pray to God.

A dialect is a form of the language that is spoken by a particular group of people.

Here, in context of hibernate framework, When hibernate wants to talk(using queries) with the database it uses dialects.

The SQL dialect's are derived from the Structured Query Language which uses human-readable expressions to define query statements.
A hibernate dialect gives information to the framework of how to convert hibernate queries(HQL) into native SQL queries.

The dialect of hibernate can be configured using below property:

hibernate.dialect

Here, is a complete list of hibernate dialects.

Note: The dialect property of hibernate is not mandatory.

Hibernate uses "dialect" configuration to know which database you are using so that it can convert hibernate query to database specific query.

The dialect in the Hibernate context, will take care of database data type, like in orace it is integer however in SQL it is int, so this will by known in hibernate by this property, how to map the fields internally.

The SQL dialect converts the HQL query which we write in our java or any other object oriented program to the specific database SQL.

For example in the java suppose I write List employees = session.createQuery("FROM Employee").list();

but when my dialect is <property name="hibernate.dialect"> org.hibernate.dialect.MySQLDialect

The HQL ("FROM Employee") gets converted to "SELECT * FROM EMPLOYEE" before hitting the MySQL database

Dialect is the SQL dialect that your database uses.

List of SQL dialects for Hibernate.

Either provide it in hibernate.cfg.xml as :

<hibernate-configuration>
   <session-factory name="session-factory">
      <property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
       ...
   </session-factory>
</hibernate-configuration>

or in the properties file as :

hibernate.dialect=org.hibernate.dialect.SQLServerDialect

Short answer

hibernate.dialect property makes Hibernate to generate the appropriate SQL statements for the chosen database.

Hibernate.dialect property tells Hibernate to generate the appropriate SQL statements for the chosen database.

A list of available dialects can be found here: http://javamanikandan.blogspot.in/2014/05/sql-dialects-in-hibernate.html

RDBMS                   Dialect
DB2                     org.hibernate.dialect.DB2Dialect
DB2 AS/400              org.hibernate.dialect.DB2400Dialect
DB2 OS390               org.hibernate.dialect.DB2390Dialect
PostgreSQL              org.hibernate.dialect.PostgreSQLDialect
MySQL                   org.hibernate.dialect.MySQLDialect
MySQL with InnoDB       org.hibernate.dialect.MySQLInnoDBDialect
MySQL with MyISAM       org.hibernate.dialect.MySQLMyISAMDialect
Oracle (any version)    org.hibernate.dialect.OracleDialect
Oracle 9i/10g           org.hibernate.dialect.Oracle9Dialect
Sybase                  org.hibernate.dialect.SybaseDialect
Sybase Anywhere         org.hibernate.dialect.SybaseAnywhereDialect
Microsoft SQL Server    org.hibernate.dialect.SQLServerDialect
SAP DB                  org.hibernate.dialect.SAPDBDialect
Informix                org.hibernate.dialect.InformixDialect
HypersonicSQL           org.hibernate.dialect.HSQLDialect
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

Dialect property is used by hibernate in following ways

1. To generate Optimized SQL queries.
2. If you have more than one DB then to talk with particular DB you want.
3. To set default values for hibernate configuration file properties based on the DB software we use even though they are not specifed in configuration file.

Databases implement subtle differences in the SQL they use. Things such as data types for example vary across databases (e.g. in Oracle You might put an integer value in a number field and in SQL Server use an int field). Or database specific functionality - selecting the top n rows is different depending on the database. The dialect abstracts this so you don't have to worry about it.