H2 Embedded Database loses tables / data on deployment

Question

So I have an application, and it uses a database, now I need to use an embedded database inside the application so that all you do with it is install the program, and the database is already there (So the client does not have to install a copy of mysql server, wamp, xampp, etc to run the mysql database) with all its information.

I have gotten the application to export into a jar file, which then is made into an exe file perfectly fine, and works on my pc, but when I copy it to another pc and run it, the database is there, but the table data and the tables I put in are gone. What am I doing wrong?

Thanks in advance

it sounds like you are accidentally re-initializing the database. Perhaps this SO thread will lend some insight: http://stackoverflow.com/questions/15870168/what-is-wiping-my-h2-database-every-time-i-run-a-unit-test — SnakeDoc, Sep 20 '13 at 20:07
Check the path to the database files, as shown [here](http://stackoverflow.com/a/2443685/230513). — trashgod, Sep 20 '13 at 20:08
If the database is embedded within your application jar, it will not be writable — MadProgrammer, Sep 20 '13 at 20:12
Lots of options when running H2. Are you sure you're not running it "in memory" mode? In that case everything would be lost when install completes. — Mark O'Connor, Sep 21 '13 at 11:25
Usually the problem is the database URL. What is your database URL, and what is the full path and name of the database file? — Thomas Mueller, Sep 21 '13 at 18:51
@ThomasMueller how do I get a database file, and its URL? I only now how to connect to a database on workbench or phpMyAdmin, I want to have a database file in the app folder that I can reference and still be able to access it through a gui if needed. Please advise on how this is possible? — , Sep 21 '13 at 19:37
Do you actually use the H2 database, or do you use MySQL? You can not connect to an H2 database using phpMyAdmin as far as I know. — Thomas Mueller, Sep 21 '13 at 19:52
The database URL for an H2 database starts with "jdbc:h2:". The database URL for a MySQL database starts with "jdbc:mysql:". — Thomas Mueller, Sep 21 '13 at 19:55
@ThomasMueller I am using the h2 database that starts with `jdbc:h2:~/dbname` — , Sep 22 '13 at 05:53
OK, in this case the database is stored in the current user home directoy, in the file `dbname.h2.db`. Do you need the database to be writable? If read-only is enough, you could use a read-only database. To load the database from a SQL script (that's one way to do it), you could use the `RUNSCRIPT` command (see the docs). — Thomas Mueller, Sep 22 '13 at 11:18
@ThomasMueller The database needs to be writable, but how do I access the database when it is stored in the home directory? — , Sep 22 '13 at 11:51

score 0 · Answer 1 · answered Sep 22 '13 at 12:00

There are multiple options. One option is:

Create a SQL script file from the current database using the SQL statement SCRIPT TO '~/script.sql'
Copy the file script.sql from your user home directory to the source directory of your application, so that you can bundle it as a resource. Use the root directory of the source code of the application (because that's the simplest solution).
In the target computer, use the database URL jdbc:h2:~/data;INIT=runscript from 'classpath:script.sql'. This will open the database and run the script. It will read the script as a resource.

There are other options as well, but they require that you read the documentation of the H2 database.

score 0 · Answer 2 · edited May 23 '17 at 12:14

Looking at a similar problem (using an exported database into H2), I found the answer above useful but did not manage to make SCRIPT TO work. My database is mySQL. The way to export thet database as a script that worked for me was from linux shell :

mysqldump -u[user] [databasename] > exportscriptfile.sql

I had then to do some polishing work on the script so that H2 can read it (see similar stuff on this SO thread)

Here is my working java based spring configurations using jdbc EmbeddedDatabaseBuilder. The datasource created as a bean here can be used in any jdbc calls :

@Configuration
public class H2DBConfig  {

@Bean
public DataSource dataSource() {
    return new EmbeddedDatabaseBuilder()
        .setType(EmbeddedDatabaseType.H2)
        .addScript("classpath:exportscriptfile.sql")
        .build();
}

H2 Embedded Database loses tables / data on deployment

2 Answers2