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

Question

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

@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;
    }
}

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)

-- 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');

由：org.springframework.beans.beanInstantiationException引起：无法实例化[javax.sql.DataSource]:工厂方法“Data Source”引发异常；嵌套异常是org.springframework.jdbc.datasource.init.scriptStatementFailedException：未能在资源类路径resource[schema.SQL]的第1行执行SQL脚本语句：创建序列voucher_id_seq作为整数从100开始递增1；嵌套异常是java.SQL.sqlsyntaxerroreXception：对象名称已经存在:VOUCHER_ID_SEQ in语句[CREATE SEQUENCE VOUCHER_ID_SEQ AS INTEGER START WITH 100 INCREMENT BY 1]在org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:189)~[spring-beans-4.1.6.release.jar:4.1.6.release]在.release]...158个常见帧被省略，原因是：org.springframework.jdbc.datasource.init.ScriptStatementFailedException：在资源类路径resource[schema.SQL]的第1行执行SQL脚本语句失败：创建序列VOUCHER_ID_SEQ为整数从100开始递增1；嵌套异常是java.sql.sqlsyntaxerrorexception：对象名称已经存在:VOUCHER_ID_SEQ in语句[CREATE SEQUENCE VOUCHER_ID_SEQ AS INTEGER START WITH 100 INCREMENT BY 1]在org.springframework.jdbc.datasource.init.scriptutils.executesqlscript(Scriptutils.java:474)~[spring-jdbc-4.0.9.release.jar:4.0.9.release]在org.springframework.jdbc.datasource.init.databasePopulatorUtils.execute(databasePopulatorUtils.java:49)~[spring-jdbc-4.0.9.release.jar:4.0.9.release]在spring-jdbc-4.0.9.release.jar:4.0.9.release]位于org.springframework.jdbc.datasource.embeddedd.embeddedDatabaseBuilder.build（embeddedDatabaseBuilder.java:251)~[spring-jdbc-4.0.9.release.jar:4.0.9.release.jar:4.0.9.release]在com.siemens.ott.testDatabaseConfig.datasource(TestDatabaseConfig.java:46)~[test-classs/:na]在$$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 com.siemens.ott.TestDatabaseConfig$$EnhancerBySpringCGlib$$6a150586.datasource()~[spring-core-4.1.6.release.jar:na]at sun.reflect.nativeMethodAccessorImpl.invoke0（本机Method)~[NA:1.8.0_45]在sun.reflect.nativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)~[NA:1.8.0_45]在sun.reflect.delegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)~[NA:1.8.0_45]在java.lang.reflect.method.invoke(Method.java:497)~[NA:1.8.0_45]在6.release]...159个常见帧被省略，原因是:java.sql.sqlsyntaxerroreXception:对象名称已经存在:VOUCHER_ID_SEQ在语句[CREATE SEQUENCE VOUCHER_ID_SEQ AS INTEGER START and 100 INTEGER BY 1]中位于org.hsqldb.jdbc.jdbcutil.sqlexception（未知源）~[hsqldb-2.3.2.jar:2.3.2]位于org.hsqldb.jdbc.jdbcutil.sqlexception（未知源）sqldb.jdbc.jdbcstatement.execute（未知源）~[hsqldb-2.3.2.jar:2.3.2]在org.springframework.jdbc.datasource.init.scriptutils.executesQLScript(Scriptutils.java:459)~[spring-jdbc-4.0.9.release.jar:4.0.9.release]...175个常见帧被省略，原因是:org.hsqldb.hsqlexception:对象名称已经存在: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只执行一次。