View content of embedded H2 database started by Spring

Question

I would like to view in a web browser the content of the H2 database started by Spring thanks to the following configuration:

<jdbc:embedded-database id="dataSource" type="H2" />

<jdbc:initialize-database data-source="dataSource">
    <jdbc:script location="classpath:db/populateDB.sql"/>
</jdbc:initialize-database>

I searched for the JDBC URL in the logs:

DEBUG o.s.j.d.SimpleDriverDataSource - Creating new JDBC Driver Connection to [jdbc:h2:mem:dataSource;DB_CLOSE_DELAY=-1]

So that I could fill the connection form as follows:

But unfortunately, the db is still empty, whereas it shouldn't due to the populateDB.sql script.

Any idea?

Thanks!

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

When you use the an embeddeb with the xml jdbc configuration the default name of the database is 'testdb'

Try to use in your url connection:

jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1

Answer 6

For those wanting a Java Config setup it's fairly easy to do as well initializing the TCP server when implementing ServletContextInitializer and chaining the Console Server...

@Configuration
public class WebConfig implements ServletContextInitializer{
...

@Override
public void onStartup( ServletContext servletContext )
//do stuff onStartUp...
initH2TCPServer( servletContext );
....    

@Bean(initMethod="start", destroyMethod="stop")
public Server initH2TCPServer(ServletContext servletContext) {
    log.debug( "Initializing H2 TCP Server" );
    try {
        server = Server.createTcpServer( "-tcp", "-tcpAllowOthers", "-tcpPort", "9092" );
    } catch( SQLException e ) {
        e.printStackTrace();
    } finally {
        //Always return the H2Console...
        initH2Console( servletContext );
    }
    return server;
}

public void initH2Console( ServletContext servletContext ) {
    log.debug( "Initializing H2 console" );
    ServletRegistration.Dynamic h2ConsoleServlet = servletContext.addServlet(
    "H2Console", new org.h2.server.web.WebServlet() );
    h2ConsoleServlet.addMapping( "/console/*" );
 );
}

Answer 7

I was facing similar issue. But the fix was really very small. Please refer page : https://springframework.guru/using-the-h2-database-console-in-spring-boot-with-spring-security/ for more details.

In my case, I have added scope of H2 dependency as "runtime". I removed it and it fixed my issue. Not I am able to see tables in H2-console.

Previous dependency in my pom was :

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

And new dependency which fixed my issue :

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
</dependency>