How to configure spring-boot to use file based H2 database

Question

I have successfully created a spring boot application that uses the H2 embedded database in-memory. I would now like to change this to a file based version that will persist.

I have tried just changing the spring.datasource.* properties in my application.properties file and they look something like this:

spring.datasource.url=jdbc:h2:file:~/test;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username=test
spring.datasource.password=test
spring.datasource.driverClassName=org.h2.Driver`  

It seems like spring boot just ignores these settings because it just starts as follows:

o.s.j.d.e.EmbeddedDatabaseFactory        : Starting embedded database: url='jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=false', username='sa'

My pom.xml contains the following dependencies that may be relevant to this post:

<parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>1.3.5.RELEASE</version>
</parent>
....
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency> 
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-devtools</artifactId>
</dependency>

My understanding from the documentation and a number of posts is that the configuration should just work but no luck for me. Just to prevent some of the basic errors I have tried and checked the following:

1. My application properties is in the classspath:
2. I have tried to exclude the auto configuration in annotation @EnableAutoConfiguration
3. I have tried to inject a dataSource bean with combinations of annotation @Primary, @ConfigurationProperties(prefix = "spring.datasource") and setting the properties programmatically with DataSourceBuilder. This causes other errors related to the type being null.

Seems like I am missing a key concept or something. Can anyone help.

UPDATE 1: Extract from my auto configuration report:

Positive matches:
-----------------

    DataSourceAutoConfiguration matched
  - @ConditionalOnClass classes found: javax.sql.DataSource,org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType (OnClassCondition)

   DataSourceAutoConfiguration.DataSourceInitializerConfiguration matched
  - @ConditionalOnMissingBean (types: org.springframework.boot.autoconfigure.jdbc.DataSourceInitializer; SearchStrategy: all) found no beans (OnBeanCondition)

   DataSourceAutoConfiguration.EmbeddedConfiguration matched
  - embedded database H2 detected (DataSourceAutoConfiguration.EmbeddedDataSourceCondition)
  - @ConditionalOnMissingBean (types: javax.sql.DataSource,javax.sql.XADataSource; SearchStrategy: all) found no beans (OnBeanCondition)

   DataSourceAutoConfiguration.JdbcTemplateConfiguration matched
  - existing auto database detected (DataSourceAutoConfiguration.DataSourceAvailableCondition)

   DataSourceAutoConfiguration.JdbcTemplateConfiguration#jdbcTemplate matched
  - @ConditionalOnMissingBean (types: org.springframework.jdbc.core.JdbcOperations; SearchStrategy: all) found no beans (OnBeanCondition)

   DataSourceAutoConfiguration.JdbcTemplateConfiguration#namedParameterJdbcTemplate matched
  - @ConditionalOnMissingBean (types: org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations; SearchStrategy: all) found no beans (OnBeanCondition)

   DataSourceTransactionManagerAutoConfiguration matched
  - @ConditionalOnClass classes found: org.springframework.jdbc.core.JdbcTemplate,org.springframework.transaction.PlatformTransactionManager (OnClassCondition)

   DataSourceTransactionManagerAutoConfiguration.TransactionManagementConfiguration matched
  - @ConditionalOnMissingBean (types: org.springframework.transaction.annotation.AbstractTransactionManagementConfiguration; SearchStrategy: all) found no beans (OnBeanCondition)

    H2ConsoleAutoConfiguration matched
  - @ConditionalOnClass classes found: org.h2.server.web.WebServlet (OnClassCondition)
  - found web application StandardServletEnvironment (OnWebApplicationCondition)
  - matched (OnPropertyCondition)

   HibernateJpaAutoConfiguration matched
  - @ConditionalOnClass classes found: org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean,org.springframework.transaction.annotation.EnableTransactionManagement,javax.persistence.EntityManager (OnClassCondition)
  - found HibernateEntityManager class (HibernateJpaAutoConfiguration.HibernateEntityManagerCondition)

Negative matches:
-----------------

    DataSourceAutoConfiguration.NonEmbeddedConfiguration did not match
  - missing supported DataSource (DataSourceAutoConfiguration.NonEmbeddedDataSourceCondition)

`

UPDATE 2: added actuator and looked at endpoint /configprops. What is interesting here is that my config has been taken and the database exists but when the application runs it does not use this dataSource.

"spring.datasource.CONFIGURATION_PROPERTIES":
    {"prefix":"spring.datasource",
     "properties":{
        "schema":null,
        "data":null,
        "xa":{"dataSourceClassName":null,
               "properties":{}
             },
        "type":null,
        "separator":";",
        "url":"jdbc:h2:file:~/test;DB_CLOSE_ON_EXIT=FALSE",
        "platform":"all",
        "continueOnError":false,
        "jndiName":null,               
        "sqlScriptEncoding":null,
        "password":"******",
        "name":"testdb",
        "driverClassName":"org.h2.Driver",
        "initialize":true,
        "username":"test"
        }
    }  

Alverson

Answer 1

Refer to http://www.h2database.com/html/cheatSheet.html

I guess it might be problem with the jdbc.url, change it like this:

# from:
spring.datasource.url=jdbc:h2:file:~/test;DB_CLOSE_ON_EXIT=FALSE

# to:
spring.datasource.url=jdbc:h2:~/test;DB_CLOSE_ON_EXIT=FALSE

Paraldehyde

thank you but the configuration actually creates a running database instance with the correct url (as configured see UPDATE 2 above). Issue is that the application is not using it. It seems to be using the default 'EmbeddedDatabase' – Alverson

did you add the spring-boot-starter-jdbc into pom.xml? here is a sample project : github.com/lenicliu/eg-spring/tree/master/eg-spring-boot/…, flyway for migration & h2database for embed database by file mode. – Paraldehyde

Adding the spring-boot-starter-jdbc to the pom.xml worked! Thank you. Would be great to know why this fixed it. – Alverson

Answer 2

I am adding this answer to avoid confusion and further research.

Actually I have the same problem and none of the answer worked for me completely rather than the mix for some answers worked.

Here is the minimal configuration which is required to persist H2 db in spring boot.

application.properties

# H2
spring.h2.console.enabled=true
spring.h2.console.path=/h2
# Datasource
spring.datasource.url=jdbc:h2:file:~/spring-boot-h2-db
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.jpa.hibernate.ddl-auto=update

Here spring.jpa.hibernate.ddl-auto=update does the trick. Nothing else is required.

No need to add spring-boot-starter-jdbc in pom.xml

No need to add any parameter in jdbc url.

Shrum

This should be the accepted answer. The spring.jpa.hibernate.ddl-auto=update was all I needed to make it work. – Chromyl

you could edit it from application.xml to application.properties (because you mention spring boot) – West

Might help someone looking for this - If you have an initialization script in data.sql file, and need to instantiate the db on application launch, you have to add this too, in the application.properties - spring.sql.init.mode=always I go this from a baeldung article But, if you require the official documentation, here is the link – Zenaidazenana

Answer 3

Using the following setting on application.properties, I manage to keep the data persisted even after shutting down and restarting SpringBoot, and even after restarting the computer.

spring.datasource.name=japodb
spring.datasource.initialize=false
spring.datasource.driverClassName=org.h2.Driver

spring.datasource.url=jdbc:h2:file:~/japodb;DB_CLOSE_ON_EXIT=FALSE;IFEXISTS=TRUE;DB_CLOSE_DELAY=-1;

Don't Close a Database when the VM Exits, yes, but also don’t make a new database if it’s already there.

jdbc:h2:<url>;IFEXISTS=TRUE

spring.jpa.hibernate.ddl-auto = update

Dichromatic

Hi @guntarion, please see suggestion by lenicliu above. The issue was resolved by adding the spring-boot-starter-jdbc to the pom.xml which worked. – Alverson

Yes, I noticed the suggestion and I've tried it. But it doesn't work. – Dichromatic

It was the "spring.jpa.hibernate.ddl-auto = update" that did the trick for me. Tried all the other tips here. – Mountaineer

Answer 4

Here is the configuration worked for me

#File based h2 DB
spring.datasource.url=jdbc:h2:file:C:/temp/test_db;DB_CLOSE_ON_EXIT=FALSE;AUTO_RECONNECT=TRUE;DB_CLOSE_DELAY=-1
#In memory
#spring.datasource.url=jdbc:h2:mem:testdb:security_permission;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.driverClassName=org.h2.Driver
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console
spring.jpa.hibernate.ddl-auto=update
spring.datasource.username=user
spring.datasource.password=admin
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
#Use datasource.initialization-mode if you are configured to use file based h2 and data.sql
spring.datasource.initialization-mode=always
spring.jpa.defer-datasource-initialization=true

When I used data initialization with data.sql, I have to use the following option to make it work

spring.datasource.initialization-mode=always

After the data is initialized, it can be set to never

spring.datasource.initialization-mode=never

Wenzel

spring.datasource.initialization-mode=always made it work for me also, using a file based H2 DB. But I switched it off again, once it worked, because I do not want to reset the db most of the time. – Iatrics

Answer 5

Just generated a brand new Spring Boot project with start.spring.io with a few dependencies h2, JPA, web, devtools, actuator. After adding a simple Entity and Spring Data repository, the database is indeed created in memory by default.

Adding the following to my application.properties definitely creates the database file in the right place:

spring.datasource.url=jdbc:h2:file:~/test;DB_CLOSE_ON_EXIT=FALSE
spring.datasource.username=test
spring.datasource.password=test
spring.datasource.driverClassName=org.h2.Driver

I can even connect to it with the H2 console when devtools is enabled http://localhost:8080/h2-console/.

The next logical step is to visit the http://localhost:8080/autoconfig endpoint and check the auto-configuration status.

In my case, the following is positiveMatches:

DataSourceAutoConfiguration.NonEmbeddedConfiguration: [
{
  condition: "DataSourceAutoConfiguration.NonEmbeddedDataSourceCondition",
  message: "supported DataSource class found"
},
{
  condition: "OnBeanCondition",
  message: "@ConditionalOnMissingBean (types: javax.sql.DataSource,javax.sql.XADataSource; SearchStrategy: all) found no beans"
}
],

and the following in negativeMatches:

DataSourceAutoConfiguration.EmbeddedConfiguration: [
{
  condition: "DataSourceAutoConfiguration.EmbeddedDataSourceCondition",
  message: "existing non-embedded database detected"
}
],

Could you try the following and check the auto-configuration report for those?

Fassold

Hi Brian, mine are a reverse of yours: positive matches include 'DataSourceAutoConfiguration.EmbeddedConfiguration' and negative matches include 'DataSourceAutoConfiguration.NonEmbeddedConfiguration'. I am not concerned that the DB is embedded but I am concerned that my data is stored in the default embedded in-memory database. I would like the data to be stored on the file based URL I configured. – Alverson

Answer 6

Create a file .h2.server.properties in your class path and put below things and try again. You can create this file in resources folder.

#H2 Server Properties
0=H2 File|org.h2.Driver|jdbc\:h2\:file\:~/test;DB_CLOSE_ON_EXIT=FALSE

# Enable if you want other applications to connect
#webAllowOthers=true
#webPort=8082
#webSSL=false

Wimsatt

I tried this suggestion using both filenames .h2.server.properties and h2.server.properties with the content above. Neither had an impact. – Alverson

had a look at the h2 documentation and this file is for configuration of the console. I have no issues connecting to the console or my database tables being created or the data being stored. The issue is that the url that the application uses to connect to the database is not affected by the application.properties configuration. – Alverson