java - HSQL数据库偶尔会执行两次schema.sql

Question

我在 Spring 中的 HSQL 数据库有一些不确定的行为。有时，sequence 会生成两次，并且 DataSource 初始化会因此失败。

奇怪的是，如果失败，它总是来自尝试读取不存在的数据库条目的测试。读取现有条目的其他测试不会失败。

我真的不明白我做错了什么以及为什么会发生这种情况。

HSQLDB版本是2.3.2

更新:我已经完全删除了序列，现在我有时再次收到错误，表凭证被创建了两次。

这是我的配置:

@EnableJpaRepositories
@EnableTransactionManagement
@Configuration
@Profile("test")
public class TestDatabaseConfig {

@Bean
@Primary
public EntityManagerFactory entityManagerFactory() throws ClassNotFoundException {

    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(false);
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan(ENTITIES_PACKAGE);
    factory.setDataSource(dataSource());
    factory.afterPropertiesSet();
    return factory.getObject();

}

@Bean
@Primary
public DataSource dataSource() {

    return new EmbeddedDatabaseBuilder().addDefaultScripts()
                                        .setType(EmbeddedDatabaseType.HSQL)
                                        .build();
}

@Bean
public PlatformTransactionManager transactionManager(EntityManagerFactory entityManagerFactory) throws ClassNotFoundException {
    JpaTransactionManager txManager = new JpaTransactionManager();
    txManager.setEntityManagerFactory(entityManagerFactory);
    return txManager;
    }
}

以及 schema.sql:

CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1;
CREATE TABLE voucher (id INTEGER, code VARCHAR(64) NOT NULL UNIQUE, type VARCHAR(64) NOT NULL, state VARCHAR(64) NOT NULL, class_name VARCHAR(64), serial VARCHAR(64), consumption_user VARCHAR(255), creation_date TIMESTAMP DEFAULT current_timestamp, consumption_date TIMESTAMP, expiry_date TIMESTAMP)

数据.sql:

-- VALID
INSERT INTO voucher (id, code, type, state, serial ) VALUES (1,'success', '1', 'E', 'serial: 123');

--ALREADY CONSUMED
INSERT INTO voucher (id, code, type, state) VALUES (2,'used', '1', 'U');

-- DATE EXPIRED
INSERT INTO voucher (id, code, type, state, expiry_date) VALUES (3,'expired', '1', 'E', DATE '2014-12-12');

以及错误跟踪，在顶部您可以看到序列有时生成两次:

Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'dataSource' threw exception; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [schema.sql]: CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1; nested exception is java.sql.SQLSyntaxErrorException: object name already exists: VOUCHER_ID_SEQ in statement [CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1] at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189) ~[spring-beans-4.1.6.RELEASE.jar:4.1.6.RELEASE] at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:588) ~[spring-beans-4.1.6.RELEASE.jar:4.1.6.RELEASE] ... 158 common frames omitted Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [schema.sql]: CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1; nested exception is java.sql.SQLSyntaxErrorException: object name already exists: VOUCHER_ID_SEQ in statement [CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1] at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:474) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:208) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.initDatabase(EmbeddedDatabaseFactory.java:159) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.getDatabase(EmbeddedDatabaseFactory.java:132) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder.build(EmbeddedDatabaseBuilder.java:251) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] at com.siemens.ott.TestDatabaseConfig.dataSource(TestDatabaseConfig.java:46) ~[test-classes/:na] at com.siemens.ott.TestDatabaseConfig$$EnhancerBySpringCGLIB$$6a150586.CGLIB$dataSource$1() ~[spring-core-4.1.6.RELEASE.jar:na] at com.siemens.ott.TestDatabaseConfig$$EnhancerBySpringCGLIB$$6a150586$$FastClassBySpringCGLIB$$125e7bce.invoke() ~[spring-core-4.1.6.RELEASE.jar:na] at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:228) ~[spring-core-4.1.6.RELEASE.jar:4.1.6.RELEASE] at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:309) ~[spring-context-4.1.6.RELEASE.jar:4.1.6.RELEASE] at com.siemens.ott.TestDatabaseConfig$$EnhancerBySpringCGLIB$$6a150586.dataSource() ~[spring-core-4.1.6.RELEASE.jar:na] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_45] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_45] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_45] at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_45] at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:162) ~[spring-beans-4.1.6.RELEASE.jar:4.1.6.RELEASE] ... 159 common frames omitted Caused by: java.sql.SQLSyntaxErrorException: object name already exists: VOUCHER_ID_SEQ in statement [CREATE SEQUENCE voucher_id_seq AS INTEGER START WITH 100 INCREMENT BY 1] at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source) ~[hsqldb-2.3.2.jar:2.3.2] at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:459) ~[spring-jdbc-4.0.9.RELEASE.jar:4.0.9.RELEASE] ... 175 common frames omitted Caused by: org.hsqldb.HsqlException: object name already exists: VOUCHER_ID_SEQ

Answer 1

对于任何可能遇到这种情况的人来说，问题出在意想不到的地方:loader = AnnotationConfigWebContextLoader。

@RunWith(SpringJUnit4ClassRunner.class)
@WebAppConfiguration
@ContextConfiguration(classes = { TestConfiguration.class }, loader = AnnotationConfigWebContextLoader.class)
@ActiveProfiles("test")
@TestExecutionListeners(listeners = { DependencyInjectionTestExecutionListener.class })

只要除去罪魁祸首就可以解决所有问题。现在 schema.sql 只执行一次。