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.

Dialect means "the variant of a language". Hibernate, as we know, 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. Therefore at some point hibernate has to use database specific SQL. Hibernate uses "dialect" configuration to know which database you are using so that it can switch to the database specific SQL generator code wherever/whenever necessary.

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.

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

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

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

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.

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.

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

https://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/dialect/package-summary.html">Here</a>;, is a complete list of hibernate dialects.

Note: The dialect property of hibernate is not mandatory.

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.