How do you connect to H2 as a remote database instead of embedded mode using Spring Boot?

Question

I have this configuration under src/main/resources for my little Spring Boot application:

server.port = 8090
spring.datasource.driverClassName = org.h2.Driver
spring.datasource.url = jdbc:h2:file:~/stapler

I know this configuration is picked up properly, cause there is valid port number 8090 in application startup log. There is also a @PostConstruct initDb() method which creates and inserts data into 2 tables of that database:

package com.avk.stapler.init;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.annotation.PostConstruct;

@SpringBootApplication
public class DbInitializer {
    @Autowired
    private JdbcTemplate jdbcTemplate;

    public static void main(String[] args) {
        SpringApplication.run(DbInitializer.class, args);
    }

    @PostConstruct
    private void initDb() {
        System.out.println("Creating table employees");
        jdbcTemplate.execute("drop table employees if exists");
        jdbcTemplate.execute("create table employees(id serial, name varchar(255), surname varchar(255))");
        jdbcTemplate.execute("insert into employees(name, surname) values('Jan', 'Kowalski')");
        jdbcTemplate.execute("insert into employees(name, surname) values('Stefan', 'Nowak')");


        System.out.println("Creating table allocations");
        jdbcTemplate.execute("drop table allocations if exists");
        jdbcTemplate.execute("create table allocations(id serial, week int, year int, shift int, employee_id bigint)");
        jdbcTemplate.execute("insert into allocations(week, year, shift, employee_id) values(29, 2015, 1, 1)");
        jdbcTemplate.execute("insert into allocations(week, year, shift, employee_id) values(28, 2015, 2, 1)");
        jdbcTemplate.execute("insert into allocations(week, year, shift, employee_id) values(29, 2015, 3, 2)");
        jdbcTemplate.execute("insert into allocations(week, year, shift, employee_id) values(28, 2015, 2, 2)");
    }
}

I can see this logged on startup, I don't think there are more logs regarding DB:

2015-09-30 22:41:22.948  INFO 2832 --- [           main] o.s.j.d.e.EmbeddedDatabaseFactory        : Creating embedded database 'testdb'
Creating table employees
Creating table allocations

And as a result of above, I'd like to see a "stapler.h2.db" file in my home directory, which is not the case. What should be changed here for the DB file to appear?

just a wild guess: are you sure the db is not in memory only? http://www.h2database.com/html/features.html#in_memory_databases — Ueli Hofstetter, Sep 30 '15 at 21:48
In-memory is just one of modes H2 supports, I believe. I definitely have seen *.h2.db files on my drive earlier. — kabeen, Oct 01 '15 at 10:27
I was not implying that H2 is in memory only but that you are maybe running it as such .... — Ueli Hofstetter, Oct 01 '15 at 13:31

score 12 · Accepted Answer · edited May 23 '17 at 12:34

Make sure that your maven dependencies look like this:

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

If you want to use H2 as a remote database using JDBC, you need to make sure that you are already running an H2 database at the specified file path in your connection url.

If you haven't already installed H2, you can get the instructions to run H2 in server mode here: http://www.h2database.com/html/tutorial.html#tutorial_starting_h2_console

Once you have it running, you can connect to it using the same JDBC connection URL you've provided. Just use the following application properties.

spring.datasource.url=jdbc:h2:tcp://localhost/~/stapler
spring.datasource.username=sa
spring.datasource.password=

If you'd rather that the embedded H2 database create your H2 file, that's also possible. Just use the configuration below.

spring.datasource.url=jdbc:h2:file:~/stapler;AUTO_SERVER=true
spring.datasource.username=
spring.datasource.password=

It's possible that the file that is created will be named stapler.mv.db. To tell H2 embedded to use stapler.h2.db instead, you can learn how to do that here: Why is my embedded h2 program writing to a .mv.db file

(Big thanks to Stéphane Nicoll for helping me answer this one)

Kenny, I think i don't want anything remote here, I want simplest case with a file persisted on hard drive in any location. That said, it looks like I had some missing pom dependency - I didn't have spring-boot-starter-jdbc, instead I had spring-jdbc only. Adding spring-boot-starter-jdbc fixed it for me and I can see mv/h2 file in my home directory, but I can't figure out what was the reason behind that. — kabeen, Oct 01 '15 at 10:24

score 1 · Answer 2 · answered Oct 17 '20 at 07:22

Try this in your application.properties. It worked for me:

  spring.datasource.url=jdbc:h2:~/test
  spring.datasource.driverClassName=org.h2.Driver
  spring.datasource.username=sa
  spring.datasource.password=
  spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

How do you connect to H2 as a remote database instead of embedded mode using Spring Boot?

2 Answers2

Linked