Ever wanted to execute a query on the in-memory H2 database in your Spring Boot application? It’s actually very easy to do so – and you can even set up a web client. It’s all included in the H2 package!

This blog post will show you how to set up H2 so that you can execute queries in the browser and/or connect with your favorite SQL client.

h2 database web client

Dependencies

If you used the spring.io initializr it probably added H2 as a runtime dependency. Since we want to access classes from the jar you need to switch it to a compile time dependency. In Gradle it has to be:

Configuration

Now add a new Java config file that starts up the H2 servers.

First by using @Profile("dev") we make sure the servers only get started in the dev profile. We add some convenience for developers who don’t want/need these features by using Spring Boot’s @ConditionalOnExpression so both servers can be disabled and enabled separately. By default only the web server is active.

Connecting to the web console

If you left the default port for the H2 web console at 8082 go to http://localhost:8082. If you didn’t change the name for the database in your properties file, the default connection URL is jdbc:h2:mem:testdb and the username is „sa“, password empty.

H2 web console loginAnd that’s it! Now you can explore the database of the running service in your browser.

Connecting via TCP

If you rather want to use your favorite SQL client to connect to the database you can also do that. First you have to enable the TCP server in the configuration above: either put h2.tcp.enabled=true in the application-dev.properties or change the default value in the Java file.

I will show you how to connect to the database with IntelliJ IDEA (Ultimate Edition only). In the database tool window, add a new H2 remote connection.

H2 new remote IntelliJA driver is needed to connect. IntelliJ suggest to download one, but sadly it’s version 1.3.x and Spring Boot uses 1.4.x for H2 right now. They are not compatible, so you have to download and reference it yourself. You can find them here in the JAR file section. Now you can enter the connection details. The port is 9092 if you didn’t change it. The most important part is to prefix the database name with „mem:“.

H2 connection properties IntelliJOf course your driver file will probably be somewhere else. And that’s it! Now you can use IntelliJ to query the embedded database while your app is running.

Join our newsletter – stay in the loop!

  • FYI, the H2 web console is auto-configured as of Spring Boot 1.3.0.M4 (as long as you are using devtools). Otherwise there is a simple property to add to your config to enable it.

    • Moritz Schulze

      Hi Stéphane,
      ha, that’s funny, I wrote this after a friend told me it is in M3 and I didn’t find it. Good to know!

    • @snicoll:disqus If the default datasource name (spring.datasource.name) is testdb and therefore the default JDBC URL is „jdbc:h2:mem:testdb“ why does changing the db name (spring.datasource.name=foodb) not change the JDBC connection URL to „jdbc:h2:mem:testdb“ ?

  • Thanks for this post, really helped me out in connecting to the embedded h2 database with IntelliJ.