View content of embedded H2 database started by Spring

Question

Pretty much the same question as View content of H2 or HSQLDB in-memory database.

Simply add the following to your configuration.

<bean id="h2Server" class="org.h2.tools.Server" factory-method="createTcpServer" init-method="start" destroy-method="stop" depends-on="h2WebServer">
    <constructor-arg value="-tcp,-tcpAllowOthers,-tcpPort,9092"/>
</bean>
<bean id="h2WebServer" class="org.h2.tools.Server" factory-method="createWebServer" init-method="start" destroy-method="stop">
    <constructor-arg value="-web,-webAllowOthers,-webPort,8082"/>
</bean>

This will start both H2 web console and TCP server in the same JVM as your embedded database so that you can access port 8082 with your web browser (enter jdbc:h2:mem:dataSource as URL), or access port 9092 with external SQL client such as SQuirreLSQL and view the same data.

With spring boot you can do this with couple of configurations in the application.properties file.

spring.h2.console.enabled=true
spring.h2.console.path=/console/

Then you can access h2 web console in http://localhost:8080/console/. Default login configuration should work unless you change them.

See spring boot documentation.

The database URL jdbc:h2:mem:dataSource means you are using an in-memory database. Now if you start a second Java process and connect to this database, you will end up having two in-memory databases (one for each process).

If you want to connect to the existing database, you have multiple options:

• Connect to the database from within the same process. Don't start a second process.

• Use a persisted database, with a hardcoded absolute path, for example: `jdbc:h2:/data/db/dataSource'.

• More complicated / not recommended: If you start a second process, you could theoretically connect to an in-memory database using the server mode. But that means you need to start the server where you ran the test.

When using Spring Boot you can register the H2 Console Servlet as follows:

@Bean
public ServletRegistrationBean h2servletRegistration() {
    ServletRegistrationBean registration = new ServletRegistrationBean(new WebServlet());
    registration.addUrlMappings("/console/*");
    registration.addInitParameter("webAllowOthers", "true");
    return registration;
}

If you want the console to be available remotely, the important line is the addInitParameter to set the "webAllowOthers" to "true".