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

Question

I have this configuration in src / main / resources for my small Spring application to load:

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

I know that this configuration is correctly selected, so there is a valid port number 8090 in the application launch log. There is also the initDb () @PostConstruct method, which creates and inserts data into 2 tables of this 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 see this logged in at startup, I don't think there are more logs in the database:

 2015-09-30 22:41:22.948 INFO 2832 --- [ main] osjdeEmbeddedDatabaseFactory : Creating embedded database 'testdb' Creating table employees Creating table allocations 

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

Answer 1

Make sure 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 using the H2 database at the specified file path in the URL of your connection.

If you have not installed H2 yet, you can get instructions for starting H2 in server mode here: http://www.h2database.com/html/tutorial.html#tutorial_starting_h2_console p>

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

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

If you prefer the embedded H2 database to create your H2 file, this is also possible. Just use the configuration below.

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

Perhaps the file created will be called stapler.mv.db . To enable H2 in using stapler.h2.db , you can find out how to do it here: Why is my h2 firmware written to a .mv.db file

(Many thanks to Stéphane Nicoll for helping me answer this question)