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 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 as follows.

compile group: "com.h2database", name: "h2"
// instead of
runtime group: "com.h2database", name: "h2"

Configuration

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

@Configuration
@Profile("dev") // Only activate this in the "dev" profile
public class H2ServerConfiguration {

    // TCP port for remote connections, default 9092
    @Value("${h2.tcp.port:9092}")
    private String h2TcpPort;

    // Web port, default 8082
    @Value("${h2.web.port:8082}")
    private String h2WebPort;

    /**
     * TCP connection to connect with SQL clients to the embedded h2 database.
     *
     * Connect to "jdbc:h2:tcp://localhost:9092/mem:testdb", username "sa", password empty.
     */
    @Bean
    @ConditionalOnExpression("${h2.tcp.enabled:false}")
    public Server h2TcpServer() throws SQLException {
        return Server.createTcpServer("-tcp", "-tcpAllowOthers", "-tcpPort", h2TcpPort).start();
    }

    /**
     * Web console for the embedded h2 database.
     *
     * Go to http://localhost:8082 and connect to the database "jdbc:h2:mem:testdb", username "sa", password empty.
     */
    @Bean
    @ConditionalOnExpression("${h2.web.enabled:true}")
    public Server h2WebServer() throws SQLException {
        return Server.createWebServer("-web", "-webAllowOthers", "-webPort", h2WebPort).start();
    }
}

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 login

And 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 connection

A driver is needed to connect. IntelliJ suggests 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

Of 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.

Stay in the Loop

If you would like to receive an email every now and then with new articles, just sign up below. We will never spam you!