问题描述
在使用 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