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.

Are you using the same connection to create the schema and to run the tests? — Maurice Perry, Sep 26 '17 at 08:25
Yes, between tests that work (with the file configurations) and the ones that do not work (with the mem configuration) the only thing I am changing is the DATABASE_URL property — eXtreme, Sep 26 '17 at 08:30
How/where to you set the properties? Maybe there's something wrong with how the properties are set and the test is configured. — msp, Sep 26 '17 at 12:13
I am configuring Spring and Hibernate via Annotations in Java. They are mostly copy-paste from the configuration of the main application. Just changed the database connection properties to use H2. The properties seem to be set correctly, while debugging they are correctly set and the logs show that the tables are generated as expected. They just don't seem to be available when queries are executed — eXtreme, Sep 27 '17 at 07:33

score 6 · Accepted Answer · answered Aug 13 '18 at 14:08

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.

score 1 · Answer 2 · answered Sep 28 '17 at 10:10

As a conclusion to this I did not end up using the in memory database but rather a file one.

As mentioned here one can add a explicit relative file path to the database url.

What I ended up doing is set the DATABASE_URL to :

 DATABASE_URL = "jdbc:h2:file:./src/test/resources/test";

This creates a database file in the resources folder, which is perfectly acceptable.

score 0 · Answer 3 · answered Sep 26 '17 at 12:59

0

can you show all config files related to testing. do you have the table called "test", because in url "..test" means the datasource

you can find some tips here

answered Sep 26 '17 at 12:59

andron

24
1
11

Hi, the post would get rather long if I post all the classes related to testing. The "test" table and the database name are just for readability sake. The actual table and database names are different and and there is no confusion between them. Also, the problem is that when I change the database url to use h2:mem things break down. If I leave the configuration to h2:file everything works fine (over 50 tests using the database work as expected) – eXtreme Sep 27 '17 at 07:40

score 0 · Answer 4 · answered Feb 25 '19 at 09:57

0

Create a file with the database name. Example: test.h2.db

answered Feb 25 '19 at 09:57

Harinath

3,791
2
16
34

H2 database doesn't work in memory only with file

4 Answers4

Linked