Tag Archives: hibernate.dialect

[Solved] com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘type=MyISAM’


This issue SyntaxErrorException for type MyISAM is occurred because of dialect selected for hibernate. Let’s consider my example and the issue occurred because of that.

Hibernate Configuration

<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

As we know in the hibernate configured dialect decide the query generation as per database.

Query Generated

create table ADDRESS (
       id integer not null auto_increment,
        street_name varchar(255),
        city_name varchar(255),
        state_name varchar(255),
        zipcode varchar(255),
        primary key (id)
    ) type=MyISAM

Exception Stack Trace

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL via JDBC Statement
 at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlString(SchemaCreatorImpl.java:440)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.applySqlStrings(SchemaCreatorImpl.java:424)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.createFromMetadata(SchemaCreatorImpl.java:315)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.performCreation(SchemaCreatorImpl.java:166)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:135)
 at org.hibernate.tool.schema.internal.SchemaCreatorImpl.doCreation(SchemaCreatorImpl.java:121)
 at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:155)
 at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:72)
 at org.hibernate.internal.SessionFactoryImpl.(SessionFactoryImpl.java:309)
 at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:452)
 at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:710)
 at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:726)
 at com.facingissuesonit.persistent.HibernateUtil.buildSessionFactory(HibernateUtil.java:13)
 at com.facingissuesonit.persistent.HibernateUtil.(HibernateUtil.java:8)
 at com.facingissuesonit.App.main(App.java:26)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'type=MyISAM' at line 8
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
 at com.mysql.jdbc.Util.getInstance(Util.java:381)

Solutions

As given above the problem is that the dialect org.hibernate.dialect.MySQLDialect is configured which is for MySQL 4.x or earlier version and we are pointing to MY SQL database with the newer version.

The query generated by dialect having to fragment TYPE=MyISAM that was deprecated in MySQL 4.0 and removed in 5.5. Earlier MySQL 4.x.x version use TYPE MyISAM engine used to store tables but in MySQL 5.x.x or later version MySQL is used ENGINE = MyISAM to store tables. e.g. thats make difference while hibernate generates queries.

As per the database configured for MySQL, we should use other dialects as per database configured. Here is the complete list of Hibernate dialects as per databases:

Hibernate: SQL Dialects List

For Example:
In MySQL <= 4.x.x use dialects as below

<property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

In MySQL>=5.x.x. use dialects as below

<property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>

[Solved] org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when ‘hibernate.dialect’ not set


Here this issue occurred while connecting to database through Hibernate and not declared dialect while mentioning all connection properties. In hibernate dialect is required properties so that hibernate known what type of queries need to generate because every database have different queries patterns.

Exception Stacktrace


Caused by: org.hibernate.HibernateException: Access to DialectResolutionInfo cannot be null when 'hibernate.dialect' not set
    at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.determineDialect(DialectFactoryImpl.java:100) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl.buildDialect(DialectFactoryImpl.java:54) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:137) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:94) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:263) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final]

Solutions

Below is example to connect to H2 database by hibernate. You can update properties values according to your database. Added hibernate.dialect to connect H2 database.

Hibernate Supported Dialect List

In hibernate.cfg.xml

<property name="dialect">
org.hibernate.dialect.H2Dialect
</property>

In application.properties with Spring boot + JPA

spring.jpa.database-platform=org.hibernate.dialect

References

https://stackoverflow.com/questions/24655684/spring-boot-default-h2-jdbc-connection-and-h2-console

Hibernate : SQL Dialects List


Hibernate supports many databases, by using dialect (hibernate.dialect) property in hibernate generates the appropriate SQL for the selected database. This idea makes us to develop database vendor independent application so that if you switch to another application just change dialect only.

Define dialect : Below is example of dialect for H2 database you can use according to your database.

In hibernate.cfg.xml

<property>
         org.hibernate.dialect.H2Dialect
</property>

In application.properties with Spring boot + JPA

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

List of Hibernate SQL Dialects

Following is the lists of Dialects provided by hibernate to connect different databases:

Note : Click on database name link to know method of hibernate dialect methods.

RDBMS DIALECT
Cache 71 org.hibernate.dialect.Cache71Dialect
DB2 org.hibernate.dialect.DB2Dialect
DB2 AS/400 org.hibernate.dialect.DB2400Dialect
DB2 OS390 org.hibernate.dialect.DB2390Dialect
Derby org.hibernate.dialect.DerbyDialect
Firebird org.hibernate.dialect.FirebirdDialect
FrontBase org.hibernate.dialect.FrontbaseDialect
H2 Database org.hibernate.dialect.H2Dialect
HypersonicSQL org.hibernate.dialect.HSQLDialect
Informix org.hibernate.dialect.InformixDialect
Interbase org.hibernate.dialect.InterbaseDialect
Ingres org.hibernate.dialect.IngresDialect
Ingres 9 org.hibernate.dialect.Ingres9Dialect
Ingres 10 org.hibernate.dialect.Ingres10Dialect
JData Store org.hibernate.dialect.JDataStoreDialect
Mckoi SQL org.hibernate.dialect.MckoiDialect
Microsoft SQL Server 2000 org.hibernate.dialect.SQLServerDialect
Microsoft SQL Server 2005 org.hibernate.dialect.SQLServer2005Dialect
Microsoft SQL Server 2008 org.hibernate.dialect.SQLServer2008Dialect
MySQL 5 org.hibernate.dialect.MySQLDialect
MySQL with InnoDB org.hibernate.dialect.MySQLInnoDBDialect
MySQL with MyISAM org.hibernate.dialect.MySQLMyISAMDialect
Mimer SQL org.hibernate.dialect.MimerSQLDialect
Oracle (any version) org.hibernate.dialect.OracleDialect
Oracle 8 org.hibernate.dialect.OracleDialect
Oracle 9i org.hibernate.dialect.Oracle9Dialect
Oracle 10g org.hibernate.dialect.Oracle10Dialect
Oracle 11g org.hibernate.dialect.Oracle10Dialect
Pointbase org.hibernate.dialect.PointbaseDialect
PostgreSQL org.hibernate.dialect.PostgreSQLDialect
PostgreSQL 8.1 org.hibernate.dialect.PostgreSQL81Dialect
PostgreSQL 8.2 org.hibernate.dialect.PostgreSQL82Dialect
PostgrePlus org.hibernate.dialect.PostgresPlusDialect
Progress org.hibernate.dialect.ProgressDialect
RDMSOD 2200 org.hibernate.dialect.RDMSOS2200Dialect
SAP DB org.hibernate.dialect.SAPDBDialect
Sybase org.hibernate.dialect.SybaseDialect
Sybase Anywhere org.hibernate.dialect.SybaseAnywhereDialect
Sybase ASE 15.5 org.hibernate.dialect.SybaseASE15Dialect
Sybase ASE 15.7 org.hibernate.dialect.SybaseASE157Dialect
Tera Data org.hibernate.dialect.TeradataDialect
Times Ten org.hibernate.dialect.TimesTenDialect

Note: This is not mandatory to be given in hibernate.cfg.xml file.

References