Why does H2 database tells me my table does not exist for some tests only

Question

I have a Spring JPA Repository which I'm trying to test using a H2 in-memory database. Some of the tests pass while others are not. The problem is that H2 is telling me my table doesn't exist (which is weird because the tests that pass also use that very same table).

Here is my database configuration:

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

}

The properties used in the previous configuration class are stored in a database.properties file mapped to the following configuration class:

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

}

This file uses the properties found in the database.properties file:

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

And here are the Employer class and its repository test class:

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()));
    }

}

As I'm only testing JpaRepository methods I spare you the EmployerRepository interface which has no added value here.

Of the tests shown above, only findById_Exists and delete are working. For the others I got the following error:

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

Which seemed to be caused by:

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

select employer0_.id as id1_2_0_, employer0_.name as name2_2_0_ from Employer employer0_ where employer0_.id=? [42102-187]

I also tried the followings for my database url:

Do you have any idea what the problem could be?

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

jdbc:h2:mem:test;DB_CLOSE_ON_EXIT=FALSE

But none of them worked.

EDIT : I figured out something. The tests that are failing are those that are actually reaching the database. The others are just performing in the EntityManager's cache. So it seems that my problem lies in the H2 database configuration or something like that. I'll check that out but if somebody have an idea it'll be welcomed.

EDIT 2: In fine, adding DB_CLOSE_DELAY=-1 did the trick. I just forgot to tell Hibernate to generate the 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.

So when you execute a separate query SQL, because of the database has been closed, you will not be able to find the Table: 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

which like:

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