SpringDataJpa 使用save创建表出错：java.sql.SQLSyntaxErrorException: Table doesn‘t exist

问题描述

在使用 SpringDataJpa 时遇到一个错误
在Jpa配置中配置了 hibernate.hbm2ddl.auto

<property name="jpaProperties">
   <props>
        <prop key="hibernate.hbm2ddl.auto">create</prop>
    </props>
</property>

测试使用如下：

@Test
   @Transactional
   @Rollback(false)
   public void testTest(){
       cyt.jpa.entity.Test test = new cyt.jpa.entity.Test();
       test.setName("aaaa");

       testDao.save(test);
   }

在数据库不存在表时候，会去自动创建表然后更新一行数据
或者设置 hibernate.hbm2ddl.auto 为 update，即当没有表时去创建表
实验发现当表存在时没有问题，当表不存在时无论是create还是update都创建不出表！
出现以下错误（没有贴上完整错误 log ）

log4j:WARN No appenders could be found for logger (org.springframework.test.context.junit4.SpringJUnit4ClassRunner).
log4j:WARN Please initialize the log4j system properly.
Hibernate: create table test (test_id bigint not null auto_increment, name varchar(255), primary key (test_id)) type=MyISAM
Hibernate: insert into test (name) values (?)

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute statement; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute statement

	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:281)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)
	at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:531)
	at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
	at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:154)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:174)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
	at 
Caused by: org.hibernate.exception.SQLGrammarException: could not execute statement
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:200)
	at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
	at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:43)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3124)
	at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3728)

Caused by: java.sql.SQLSyntaxErrorException: Table 'springboot.test' doesn't exist
	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.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
	at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:105)
	at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:197)
	... 85 more


org.springframework.transaction.UnexpectedRollbackException: Transaction silently rolled back because it has been marked as rollback-only

	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:752)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)
	at org.springframework.test.context.transaction.TransactionContext.endTransaction(TransactionContext.java:131)
	at org.springframework.test.context.transaction.TransactionalTestExecutionListener.afterTestMethod(TransactionalTestExecutionListener.java:230)
	at org.springframework.test.context.TestContextManager.afterTestMethod(TestContextManager.java:441)

解决办法

重点关注到：

Hibernate: create table test (test_id bigint not null auto_increment, name varchar(255), primary key (test_id)) type=MyISAM
Hibernate: insert into test (name) values (?)

可以看到在上方sql语句中已经创建了表，但在往表去操作时，这里是插入数据，却报表不存在的错误。
网上查错误信息搞半天我注意到 创建表 sql语句后面有： type=MyISAM
google一下这才知道这是Mysql5的语法，在spring全局配置 dialect 时我配置了：
<property name="databasePlatform" value="org.hibernate.dialect.MySQLDialect" />
正确应该把后面的 MySQLDialect 改为 MySQL5Dialect:
<property name="databasePlatform" value="org.hibernate.dialect.MySQL5Dialect" />

问题得以解决！

---

附上我找到问题所在的 stackoverflow 帖子：

https://stackoverflow.com/questions/43716068/invalid-syntax-error-type-myisam-in-ddl-generated-by-hibernate