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.
"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.
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
hibernate.dialect property makes Hibernate to generate the appropriate SQL statements for the chosen 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 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 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
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:
Here, 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
- To generate Optimized SQL queries.
- If you have more than one DB then to talk with particular DB you want.
- 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.