java - 为什么 H2 数据库仅在某些测试中告诉我我的表不存在

Question

我有一个 Spring JPA 存储库，我正在尝试使用 H2 内存数据库对其进行测试。一些测试通过，而另一些则没有。问题是 H2 告诉我我的表不存在(这很奇怪，因为通过的测试也使用同一张表)。

这是我的数据库配置:

@Configuration
@Import({BaseConfiguration.class, DatabaseProperties.class})
@EnableJpaRepositories(basePackages = DatabaseConfiguration.REPOSITORIES_PACKAGE)
public class DatabaseConfiguration {

    /*
     * Constants
     */
    public static final String MODEL_PACKAGE = "be.dupirefr.examples.spring.batch.simple.model";
    public static final String REPOSITORIES_PACKAGE = "be.dupirefr.examples.spring.batch.simple.repositories";

    /*
     * Beans
     */
    @Bean
    public DataSource dataSource(DatabaseProperties properties) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(properties.url);
        dataSource.setUsername(properties.username);
        dataSource.setPassword(properties.password);
        dataSource.setDriverClassName(properties.driverClassName);

        return dataSource;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(DataSource dataSource) {
        LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        entityManagerFactoryBean.setDataSource(dataSource);
        entityManagerFactoryBean.setPackagesToScan(MODEL_PACKAGE);
        entityManagerFactoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        return entityManagerFactoryBean;
    }

    @Bean
    public PlatformTransactionManager transactionManager(DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

在前面的配置类中使用的属性存储在映射到以下配置类的 database.properties 文件中:

@Configuration
@PropertySource("classpath:be/dupirefr/examples/spring/batch/simple/config/database/database.properties")
public class DatabaseProperties {

    /*
     * Fields
     */
    @Value("${spring.datasource.url}")
    public String url;

    @Value("${spring.datasource.username}")
    public String username;

    @Value("${spring.datasource.password}")
    public String password;

    @Value("${spring.datasource.driver-class-name}")
    public String driverClassName;

}

此文件使用在 database.properties 文件中找到的属性:

spring.datasource.url=jdbc:h2:mem:test
spring.datasource.username=admin
spring.datasource.password=admin
spring.datasource.driver-class-name=org.h2.Driver

下面是 Employer 类及其存储库测试类:

雇主:

@Entity
public class Employer {

    /*
     * Fields
     */
    @Id
    private Long id;

    @Column(nullable = false)
    private String name;

    @OneToMany(mappedBy = "employer")
    private List<Employee> employees;

    /*
     * Constructors
     */
    private Employer() {}

    public Employer(Long id, String name) {
        this.id = id;
        this.name = name;

        this.employees = new ArrayList<>();
    }

    /*
     * Getters
     */
    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public List<Employee> getEmployees() {
        return employees;
    }

    /*
     * Methods
     */
    //...

}

EmployerRepositoryIT:

@RunWith(SpringRunner.class)
@ContextConfiguration(classes = DatabaseConfiguration.class)
@Transactional
public class EmployerRepositoryIT {

    /*
     * Constants
     */
    public static final Employer GOOGLE = new Employer(1L, "Google");
    public static final Employer MICROSOFT = new Employer(2L, "Microsoft");
    public static final Employer APPLE = new Employer(3L, "Apple");

    /*
     * Fields
     */
    @Autowired
    private EmployerRepository repository;

    @Autowired
    private EntityManager entityManager;

    /*
     * Setups
     */
    @Before
    public void setUp() {
        entityManager.persist(GOOGLE);
        entityManager.persist(MICROSOFT);
    }

    /*
     * Tests
     */
    @Test
    public void findById_Exists() {
        assertEquals(GOOGLE, repository.findById(GOOGLE.getId()).get());
        assertEquals(MICROSOFT, repository.findById(MICROSOFT.getId()).get());
    }

    @Test
    public void findById_NotExists() {
        assertFalse(repository.findById(Long.MAX_VALUE).isPresent());
    }

    @Test
    public void findAll() {
        assertEquals(Arrays.asList(GOOGLE, MICROSOFT), repository.findAll());
    }

    @Test
    public void save() {
        repository.save(APPLE);
        assertEquals(APPLE, entityManager.find(Employer.class, APPLE.getId()));
    }

    @Test
    public void delete() {
        repository.delete(MICROSOFT);
        assertNull(entityManager.find(Employer.class, MICROSOFT.getId()));
    }

}

因为我只测试 JpaRepository 方法，所以我省去了 EmployerRepository 接口(interface)，它在这里没有附加值。

在上面显示的测试中，只有 findById_Exists 和 delete 有效。对于其他人，我收到以下错误:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select employer0_.id as id1_2_0_, employer0_.name as name2_2_0_ from Employer employer0_ where employer0_.id=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

这似乎是由于:

org.h2.jdbc.JdbcSQLException: Table "EMPLOYER" not found; SQL statement:

从雇主 employer0_ 中选择 employer0_.id 作为 id1_2_0_，employer0_.name 作为 name2_2_0_，其中 employer0_.id=? [42102-187]

我还为我的数据库 url 尝试了以下内容:

你知道问题出在哪里吗？

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

jdbc:h2:mem:test;DB_CLOSE_ON_EXIT=FALSE

但它们都不起作用。

编辑:我想通了一些事情。失败的测试是那些实际到达数据库的测试。其他的只是在 EntityManager 的缓存中执行。所以看来我的问题出在 H2 数据库配置或类似的东西上。我会检查一下，但如果有人有想法，我将不胜感激。

编辑 2:很好，添加 DB_CLOSE_DELAY=-1 就可以了。我只是忘了告诉 Hibernate 生成 DDL。

Answer 1

This appears to be due to the close of connect when the database is initialized,which results in the shutdown of database.

所以当你执行单独的查询SQL时，由于数据库已经关闭，你将找不到表:Table "EMPLOYER"not found

If you want to keep the database open for use, you can try to add parameters to the database URL @Value("${spring.datasource.url}")

DB_CLOSE_DELAY=-1

喜欢:

jdbc:h2:tcp://localhost/xxxxx:xxxxxx;DB_CLOSE_DELAY=-1