H2 in-memory database initialization with data

Question

I'm using H2 with Hibernate to generate in-memory DB on the fly for unit-testing. I managed to create the DB successfully, and everything is working ok. But I have an issue I don't know how to approach. I need to load reference data to the DB for testing prior to the execution of the tests. I have this data sored as a SQL insert's file which I need to run only once in real time envirnemnt, however, because the DB is generated every time from scratch I need to figure out how to insert the data on runtime. The data is quite simple, it's countries lists, states list, etc. Whats the best way to do it ?

btw, everything is working underneath Spring framework.

Solution 1

For your tests you could execute an init script on creation of the connection.

http://www.h2database.com/html/features.html#execute_sql_on_connection

Solution 2

From the question tags I see you're using Hibernate. You can add a file named "import.sql" to your classpath (i.e. in src/main/resources if you're using a Maven project layout).

From Spring documentation

In addition, a file named import.sql in the root of the classpath will be executed on startup if Hibernate creates the schema from scratch (that is if the ddl-auto property is set to create or create-drop). This can be useful for demos and for testing if you are careful, but probably not something you want to be on the classpath in production. It is a Hibernate feature (nothing to do with Spring).

Comments

[HRJ]

I am trying to do something similar, but I have a persistent DB of ~300MB, with many indexes. Converting it to SQL statments and initialising them will be very slow. Is there a way to initialise from the binary DB itself?

[Udo Held]

@HRJ Internally we are using a hsqldb db saved in a jar file. However I didn't test it with such a huge amount of data.

[nonzaprej]

And since some people might now know about it, if you don't already have the "console" H2 endpoint to manage the database, you can add the property spring.h2.console.enabled=true to your .properties (or .yml) file and you will be able to access the endpoint "h2-console". For the in-memory database, you have to change the "JDBC URL" to jdbc:h2:mem:testdb. No password is required, just click on "connect". I'm writing this because I found this useful to see the structure of the database when my initialization queries were failing.

[nonzaprej]

P.S.: I suggest to read that documentation and not stop just at this answer. For example, I actually needed to name the file "data.sql" and not "import.sql".