Spring H2 embedded-database file?

Question

I currently run my petproject on maven using an embedded jetty and H2 database:

<jdbc:embedded-database id="dataSource" type="H2">
   <jdbc:script location="/WEB-INF/database.sql"/>
</jdbc:embedded-database>

This setup resets my DB every time I run the server. I want to save the DB as a file in disk so that I do not lose my data upon every server start. How do I accomplish this?

Solution 1

You can controll this by the connection string.

jdbc:h2:~/test; # saves to the file ~/test
jdbc:h2:mem:db1 # in memory

More info here.

EDIT:

Seems like the connection string is hard-coded in the spring H2 Configuration, so I assume that means you have to write you own implementation by extending the EmbeddedDatabaseConfigurer, assuming there is no other way of changing the connection string after it's set in the H2EmbeddedDatabaseConfigurer.

Solution 2

Old question but i spent a lot of time figuring out how to save an embedded H2 database to a file and i want to share what i learned.

As @ebaxt said you can configure the location of the embedded database within the connection string. If you want to save it in the file system use ~/ syntax:

jdbc:h2:~/example/embeddedDb

If you want to save it inside your project folder you have to use ./ syntax

jdbc:h2:./example/embeddedDb

This will create embeddedDb.mv.db file in example folder inside your home folder, or inside your project root folder. But this will erase the database every time the application starts. To avoid this i used the INIT property to tell H2 to create the schema (Queue in my case) only if it not exists:

INIT=create schema if not exists Queue;

Then in your DDL script you have to use create table if not exists statement, to create all your tables:

// create-db.sql
    CREATE TABLE IF NOT EXISTS Queue (
       id INTEGER PRIMARY KEY AUTO_INCREMENT,
       ...
   );

and tell H2 to run the script every time it gets the connection:

// if you want to store the script in the file system
runscript from '~/example/create-db.sql'

// if you want to store the script in the project folder
runscript from './example/create-db.sql'

Summarizing this is what you have to do to create the database (EmbeddedDb) in java annotation way:

    import org.springframework.jdbc.datasource.DriverManagerDataSource;

    @Bean
    DataSource datasource(){

        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("org.h2.Driver");
        dataSource.setUsername("embedded");
        dataSource.setPassword("embedded");
        dataSource.setUrl("jdbc:h2:~/example/EmbeddedDb;INIT=create schema if not exists Queue\\; runscript from '~/example/create-db.sql'");

        return dataSource;
    }

or using XML:

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"
        destroy-method="close">
        <property name="driverClassName" value="org.h2.Driver" />
        <property name="url" value="jdbc:h2:~/example/EmbeddedDb;INIT=create schema if not exists Queue\; runscript from '~/example/create-db.sql'" />
        <property name="username" value="embedded" />
        <property name="password" value="embedded" />
    </bean>

With this method the database will be created only if it's the first time the application runs or if the database file doesn't exist. Otherwise only the connection will be loaded.

You can also monitor the database status with a nice interface using the hsqldb library, adding the following bean:

    import org.h2.tools.Server;
    import org.hsqldb.util.DatabaseManagerSwing;

    @Bean(initMethod = "start", destroyMethod = "stop")
    public Server startDBManager() throws SQLException {
        DatabaseManagerSwing.main(new String[] { "--url", "jdbc:h2:~/example/EmbeddedDb", "--user", "embedded", "--password", "embedded" });
        return Server.createWebServer();
    }

References:

http://www.mkyong.com/spring/spring-embedded-database-examples/

http://www.h2database.com/html/features.html#embedded_databases (Execute SQL on Connect section)

Comments

[Admin]

Following your solution, I cannot find the "mydb" file. Am I missing something?<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerData‌​Source"> <property name="driverClassName" value="org.h2.Driver" /> <property name="url" value="jdbc:h2:~/mydb"/> </bean>