H2 database doesn't work in memory only with file

Question

I have a Spring MVC project set up with Hibernate and wanted to create some tests for some Services. The main application uses a PostgreSQL database and for tests I want to use H2 in memory database

I created separate configuration files for the tests (both Spring and Hibernate) Everything works well until I try to hit the in memory database

Hibernate configuration:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories("<repository>")
public class DataSourceTestConfig {

private static final Logger LOG = LogManager.getLogger(DataSourceTestConfig.class);

private static final String DATABASE_DRIVER = "org.h2.Driver";
//private static final String DATABASE_URL = "jdbc:h2:file:d:/test";
private static final String DATABASE_URL = "jdbc:h2:mem:test";
private static final String DATABASE_USERNAME = "sa";
private static final String DATABASE_PASSWORD = "";

private static final String HIBERNATE_DIALECT = "org.hibernate.dialect.H2Dialect";
private static final String HIBERNATE_SHOW_SQL = "true";
private static final String ENTITYMANAGER_PACKAGES_TO_SCAN = "<packages>";
private static final String HIBERNATE_HBM2DDL_AUTO = "create";
...

The problem seems to be with the DATABASE_URL:

If I use:

DATABASE_URL = "jdbc:h2:file:d:/test";

Everything works as expected. All tests run as they should

If I use:

DATABASE_URL = "jdbc:h2:mem:test";

All Hell breaks loose and it does not work :)

In this case I get

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

Looking through the Hibernate logs it can be clearly seen that the table is in fact generated:

 DEBUG org.hibernate.SQL - drop table test if exists
 ...
 DEBUG org.hibernate.SQL - create table test (<columns>)
 ...
 DEBUG org.hibernate.SQL - alter table test add constraint FKg74a38x6t762qifuge9cux03i foreign key ...

And so on ...

To me it looks like in this case the database is generated and somehow I am working or on a different instance or something happens and the tables are dropped between the creation and my tests.

These scenarios seem to me unlikely as there are no Hibernate logs to indicate this (no drop queries) the test logs start immediately after the creation logs

I found this similar problem here: H2 in-mem-DB with hibernate set to create giving me table not found errors

But the solution there is to use a file.

This is not a solution for me because these tests have to be performed on a lot of machines running different operating systems so a hard coded path file is not an option. Also JDBC doesn't permit a relative path so that I can put a database file in the resources folder.

Does anyone have an idea how to fix this?

Thanks.

Answer 1

I faced the same issue and eventually found a solution: It's because the after the database has been initialized it is closed again. However with the standard settings this causes the DB to be deleted. When the same URL is connected again in your tests you will have a "fresh" instance withour any tables created.

You can prevent the DB being closed by appending ";DB_CLOSE_DELAY=-1" at the end of your your JDBC URL, e.g.

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

The H2 documentation gives more details on these settings.