Hibernate5 配置 MySQL8 方言问题

程序员文章站 2022-04-13 15:59:47

...

一、问题再现

1.1环境介绍

开发工具：MyEclipse 2017 CI
Hibernate：5.4.18
Mysql Server：8.0.20

1.2问题介绍

数据库方言 dialect：org.hibernate.dialect.MySQLDialect
自动生成模式 hbm2ddl.auto：update
Hibernate5 配置 MySQL8 方言问题
测试，根据映射表自动创建数据库表，主要报错信息：

Hibernate: create table user (id integer not null auto_increment, name varchar(255), primary key (id)) type=MyISAM
七月 06, 2020 6:07:17 下午 org.hibernate.tool.schema.internal.ExceptionHandlerLoggedImpl handleException
WARN: GenerationTarget encountered exception accepting command : Error executing DDL "create table user (id integer not null auto_increment, name varchar(255), primary key (id)) type=MyISAM" via JDBC Statement
org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table user (id integer not null auto_increment, name varchar(255), primary key (id)) type=MyISAM" via JDBC Statement
	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:67)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:559)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:504)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:277)
	at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:71)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:207)
	at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:114)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:184)
	at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:73)
	at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:316)
	at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:469)
	at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:708)
	at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:724)
	at cn.nanu.e_hbm_collection.UserTest.<clinit>(UserTest.java:16)
	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:423)
	at org.junit.runners.BlockJUnit4ClassRunner.createTest(BlockJUnit4ClassRunner.java:217)
	at org.junit.runners.BlockJUnit4ClassRunner$1.runReflectiveCall(BlockJUnit4ClassRunner.java:266)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.BlockJUnit4ClassRunner.methodBlock(BlockJUnit4ClassRunner.java:263)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:86)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:678)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: java.sql.SQLSyntaxErrorException: 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 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:764)
	at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
	at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:54)
	... 35 more

二、问题解决

主要错误信息：
Error executing DDL：错误执行了DDL。
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’：你的SQL语法有错误;查看对应于MySQL服务器版本的手册，了解使用’type=MyISAM’附近的正确语法。

主要原因：
因为 Hibernate 设置的方言为 org.hibernate.dialect.MySQLDialect ，该方言建表使用的是 MySQL5.0 之前的 type=InnoDB SQL语句，但是使用的 MySQL 版本为5.0之后的，type=InnoDB 不再生效，变为了engine=InnoDB 。这里需要设置方言为：org.hibernate.dialect.MySQL5Dialect 。

在下载的 Hibernate5.4.18 资源包中 hibernate.properties 文件中 MySQL 的方言中并没有：
Hibernate5 配置 MySQL8 方言问题
将方言修改为：org.hibernate.dialect.MySQL5Dialec