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?

Answer 1

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)

Answer 2

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