环境：
MS SQL Server环境
JDK 8
使用H2将Spring Boot 2转换为JPA进行集成测试。
application-test.properties
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled: false
spring.jpa.properties.hibernate.jdbc.time_zone=UTC
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStategyStandardImpl
hibernate.dialect=org.hibernate.dialect.H2Dialect
实体：
@Entity
@Table(name = "ref_application")
@EntityListeners(AuditingEntityListener.class)
public class Application {
@Id
@GenericGenerator(name = "generator", strategy = "uuid2", parameters = {})
@GeneratedValue(generator = "generator")
@Column(name = "app_id", columnDefinition = "uniqueidentifier")
private String appId;
@Column(name = "app")
@NotNull
private String appName;
@Column(name = "description")
@NotNull
private String description;
@Column(name="sys_create_tm")
@NotNull
@CreatedDate
private LocalDateTime createTm;
@Column(name="created_by", columnDefinition="varhcar(150) default 'ORION'")
@NotNull
@CreatedBy
private String createdBy;
@Column(name="updated_at")
@Null
@LastModifiedDate
private LocalDateTime updatedAt;
@Column(name="updated_by")
@Null
@LastModifiedBy
private String updatedBy;
仓库：
@Repository
@EnableJpaAuditing
public interface ApplicationRepository extends JpaRepository<Application, String> {
//JPQL syntax
@Query("SELECT ra from Application ra where ra.appName = :appName")
Application findByAppName(@Param("appName") String appName);
}
测试：
@ActiveProfiles("test")
@RunWith(SpringRunner.class)
@DataJpaTest //Autoconfigures H2 by default and runs app context
public class ApplicationRepositoryTests {
private static final Logger LOG = LoggerFactory.getLogger(ApplicationRepositoryTests.class);
@Autowired
private ApplicationRepository appRepository;
@Autowired
private EntityManager entityManager;
@Autowired
private JdbcTemplate jdbcTemplate;
@Test
//@Sql({"/import_applications.sql"})
public void whenFindByAppName_thenReturnApp() {
try {
Application app = new Application();
app.setAppName("TVP");
app.setDescription("TEST Description");
app.setCreateTm(LocalDateTime.now());
app.setCreatedBy("whenFindByAppName_thenReturnApp");
appRepository.save(app);
LOG.debug("*************** flush()");
appRepository.flush();
LOG.debug("*************** findByAppName()");
Application dbApp = appRepository.findByAppName("TVP");
LOG.info("dbAPP:: " + dbApp);
assertThat(dbApp).isNotNull();
} catch(Exception e) {
LOG.error(e.getMessage(), e);
fail(e.getMessage());
}
}
结果：
Hibernate: drop table ref_application if exists
13:41:11.180 [main] DEBUG org.hibernate.SQL - drop table ref_user_group if exists
Hibernate: drop table ref_user_group if exists
13:41:11.210 [main] DEBUG org.hibernate.SQL - create table ref_application (app_id uniqueidentifier not null, app varchar(255) not null, sys_create_tm timestamp not null, created_by varhcar(150) default 'ORION' not null, description varchar(255) not null, updated_at timestamp, updated_by varchar(255), primary key (app_id))
Hibernate: create table ref_application (app_id uniqueidentifier not null, app varchar(255) not null, sys_create_tm timestamp not null, created_by varhcar(150) default 'ORION' not null, description varchar(255) not null, updated_at timestamp, updated_by varchar(255), primary key (app_id))
13:41:11.220 [main] DEBUG org.hibernate.type.EnumType - Using ORDINAL-based conversion for Enum gov.dhs.tsa.tvu.bo.TvuStatus
13:41:11.221 [main] DEBUG org.hibernate.SQL - create table ref_user_group (ref_user_group_id uniqueidentifier not null, address varchar(255), city varchar(255), sys_create_dt timestamp not null, created_by varchar(255) not null, description varchar(255) not null, email varchar(255) not null, group_code varchar(255) not null, phone varchar(255) not null, poc varchar(255) not null, state varchar(255), status integer, display_ui boolean not null, updated_at timestamp, updated_by varchar(255), zip varchar(255), app_id uniqueidentifier, primary key (ref_user_group_id))
Hibernate: create table ref_user_group (ref_user_group_id uniqueidentifier not null, address varchar(255), city varchar(255), sys_create_dt timestamp not null, created_by varchar(255) not null, description varchar(255) not null, email varchar(255) not null, group_code varchar(255) not null, phone varchar(255) not null, poc varchar(255) not null, state varchar(255), status integer, display_ui boolean not null, updated_at timestamp, updated_by varchar(255), zip varchar(255), app_id uniqueidentifier, primary key (ref_user_group_id))
13:41:11.238 [main] DEBUG org.hibernate.SQL - alter table ref_user_group add constraint FKij5uhedc20orc8a8f3yk9vatu foreign key (app_id) references ref_application
13:52:32.473 [main] TRACE o.h.type.spi.TypeConfiguration$Scope - Handling #sessionFactoryCreated from [org.hibernate.internal.SessionFactoryImpl@73d292e9] for TypeConfiguration
13:52:32.506 [main] INFO gov.dhs.tsa.tvu.TvuApplication - TvuApplication running in UTC timezone: 2020-03-16T19:52:32.506
13:52:34.073 [main] INFO g.d.t.t.r.ApplicationRepositoryTests - Started ApplicationRepositoryTests in 14.469 seconds (JVM running for 22.27)
13:52:34.402 [main] DEBUG g.d.t.t.r.ApplicationRepositoryTests - *************** flush()
13:52:34.595 [main] DEBUG org.hibernate.SQL - insert into ref_application (app, sys_create_tm, created_by, description, updated_at, updated_by, app_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into ref_application (app, sys_create_tm, created_by, description, updated_at, updated_by, app_id) values (?, ?, ?, ?, ?, ?, ?)
13:52:34.599 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Table "REF_APPLICATION" not found; SQL statement:
insert into ref_application (app, sys_create_tm, created_by, description, updated_at, updated_by, app_id) values (?, ?, ?, ?, ?, ?, ?) [42102-200]
13:52:34.624 [main] ERROR g.d.t.t.r.ApplicationRepositoryTests - could not prepare statement; SQL [insert into ref_application (app, sys_create_tm, created_by, description, updated_at, updated_by, app_id) values (?, ?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [insert into ref_application (app, sys_create_tm, created_by, description, updated_at, updated_by, app_id) values (?, ?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare 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:528)
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:153)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:178)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
at com.sun.proxy.$Proxy138.flush(Unknown Source)
at gov.dhs.tsa.tvu.repository.ApplicationRepositoryTests.whenFindByAppName_thenReturnApp(ApplicationRepositoryTests.java:83)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
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.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at org.junit.runner.JUnitCore.run(JUnitCore.java:115)
at org.junit.vintage.engine.execution.RunnerExecutor.execute(RunnerExecutor.java:40)
at java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:184)
at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:193)
at java.util.Iterator.forEachRemaining(Iterator.java:116)
at java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801)
at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:482)
at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:472)
at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:151)
at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:174)
at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:418)
at org.junit.vintage.engine.VintageTestEngine.executeAllChildren(VintageTestEngine.java:80)
at org.junit.vintage.engine.VintageTestEngine.execute(VintageTestEngine.java:71)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:229)
at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:197)
at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:211)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:191)
at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:128)
at org.apache.maven.surefire.junitplatform.JUnitPlatformProvider.invokeAllTests(JUnitPlatformProvider.java:150)
at org.apache.maven.surefire.junitplatform.JUnitPlatformProvider.invoke(JUnitPlatformProvider.java:124)
at org.apache.maven.surefire.booter.ForkedBooter.invokeProviderInSameClassLoader(ForkedBooter.java:384)
at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:345)
at org.apache.maven.surefire.booter.ForkedBooter.execute(ForkedBooter.java:126)
at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:418)
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "REF_APPLICATION" not found; SQL statement:
insert into ref_application (app, sys_create_tm, created_by, description, updated_at, updated_by, app_id) values (?, ?, ?, ?, ?, ?, ?) [42102-200]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:453)
at org.h2.message.DbException.getJdbcSQLException(DbException.java:429)
at org.h2.message.DbException.get(DbException.java:205)
at org.h2.message.DbException.get(DbException.java:181)
at org.h2.command.Parser.readTableOrView(Parser.java:7628)
插入ref_application
找不到表格“ REF_APPLICATION”
不适用于@Table（name =“ REF_APPLICATION”）
naming-strategy属性无法解决。
删除冲洗时同样的问题。 findByAppName发生异常
使用@Sql（{“ / import_applications.sql”}）的相同问题
更新
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false
04:48:27.821 [main] INFO g.d.t.t.r.ApplicationRepositoryTests - Started ApplicationRepositoryTests in 13.727 seconds (JVM running for 20.265)
04:48:28.418 [main] DEBUG org.hibernate.SQL - insert into ref_application (app, sys_create_tm, created_by, description, updated_at, updated_by, app_id) values (? ?, ?, ?, ?, ?, ?)
Hibernate: insert into ref_application (app, sys_create_tm, created_by, description, updated_at, updated_by, app_id) values (?, ?, ?, ?, ?, ?, ?)
04:48:28.421 [main] ERROR o.h.e.jdbc.spi.SqlExceptionHelper - Table "REF_APPLICATION" not found; SQL statement:
insert into ref_application (app, sys_create_tm, created_by, description, updated_at, updated_by, app_id) values (?, ?, ?, ?, ?, ?, ?) [42102-200]
[ERROR] Tests run: 7, Failures: 0, Errors: 1, Skipped: 4, Time elapsed: 15.02 s <<< FAILURE! - in gov.dhs.tsa.tvu.repository.ApplicationRepositoryTests
[ERROR] saveApp Time elapsed: 0.135 s <<< ERROR!
org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [insert into ref_application (app, sys_create_tm, created_by, dscription, updated_at, updated_by, app_id) values (?, ?, ?, ?, ?, ?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statment
at gov.dhs.tsa.tvu.repository.ApplicationRepositoryTests.saveApp(ApplicationRepositoryTests.java:65)
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
at gov.dhs.tsa.tvu.repository.ApplicationRepositoryTests.saveApp(ApplicationRepositoryTests.java:65)
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException:
Table "REF_APPLICATION" not found; SQL statement:
insert into ref_application (app, sys_create_tm, created_by, description, updated_at, updated_by, app_id) values (?, ?, ?, ?, ?, ?, ?) [42102-200]
at gov.dhs.tsa.tvu.repository.ApplicationRepositoryTests.saveApp(ApplicationRepositoryTests.java:65)
最佳答案
将您的数据源URL更改为：
spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false
H2在内存DB中，一旦创建表，它就会关闭连接并放弃更改。在这里看看：H2 in-memory database. Table not found
但是您的主要问题是错别字：
命名策略应设置为org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
createdBy的列定义类型错误（应为@Column（name =“ created_by”，columnDefinition =“ varchar（150）default'ORION'”））