I am new to Hibernate. While reading Hibernate, I came across the Dialect property. Whatever database we will use in our application, we need to set dialect related to that database and Hibernate will generate appropriate query related to that database.
My Database engine is 'MySQL'. In hibernate configuration file,if I specify the dialect as 'Oracle', what happens ?
You can force Hibernate to use a certain dialect by setting the dialect in your config. Hibernate will use the dialect, that you tell it to use. If you specify the dialect incorrectly, then Hibernate will attempt to run (most likely) invalid SQL against the target database.
So if you are connecting to an MySQL database but you said that Hibernate should use the Oracle dialect, Hibernate will attempt to run Oracle queries against MySQL. For simple things this might work, but once you start getting more complex queries, the SQL won't be syntactically correct and will fail spectacularly.
The best option is to simply not select a dialect. Hibernate will automatically choose the best dialect to use based on database metadata that is gets when setting up the connection. This is the best choice because there are differences between similar dialects (MySQLDialect and MySQL5Dialect actually require differently formatted SQL).
This can be slightly misleading however, because if your database connection fails due to invalid credentials/URL, the stacktrace message is:
org.hibernate.HibernateException: Connection cannot be null when 'hibernate.dialect' not set
Which might cause you to mistakenly believe that your dialect does need to be set, but what Hibernate is actually saying is that it can't detect what dialect to use without a valid connection.
So to re-iterate, you can force Hibernate to use an different dialect to try and communicate with your database, and it might work for simple queries, but will quickly fail when more complex queries are attempted.