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:

enter image description here

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

Any idea?

Thanks!

score 48 · Accepted Answer · edited May 23 '17 at 10:31

48

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.

edited May 23 '17 at 10:31

Community

1
1

answered Nov 13 '13 at 18:29

hshib

1,691
1
17
22

any idea what shall be the url for tcp? I tried with jdbc:h2:tcp://localhost:9092/dataSource but it gives me an empty database called DATASOURCE. web consoles show my tables on 8082 with jdbc:h2:mem:dataSource – VJ. Dec 24 '14 at 05:01
1

solved it.. the url needs to have "mem:" word in it.. New url: jdbc:h2:tcp://localhost:9092/mem:dataSource – VJ. Dec 24 '14 at 05:04
1

Any idea how to do the equivalent config in code, if you've configured your DataSource [using EmbeddedDatabaseBuilder along these lines](http://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html#jdbc-embedded-database-java)? – Jonik Oct 23 '15 at 12:08
I have done the configuration as you mentioned. But when I try to connect from squirrel SQL client with url "jdbc:h2:tcp://localhost:9092/mem:dataSource", it shows connection failure. – Manu Dec 23 '16 at 08:46

score 28 · Answer 2 · answered Aug 23 '16 at 18:37

28

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.

answered Aug 23 '16 at 18:37

chAmi

1,774
3
20
27

Choose "Generic H2 (Embedded)" & hit "Connect". – Nikunj Lahoti Nov 15 '17 at 02:53
I don't why I can't get this to work. I have in my pom.xml :` com.h2database h2 ${h2.db.version} test ` and the above properties in the `application-test.properties` I am working with. My e2e tests work with the h2 db, but going to `localhost:8080` returns site can't be reached. What am I missing? – Nom1fan Dec 29 '19 at 08:53
Donc forget to adapt based on your **port** and **contextPath**: `Tomcat started on port(s): 8082 (http) with context path '/foo'` means you'll have to go on `http://localhost:8082/foo/console/` – payne May 28 '20 at 18:57

score 20 · Answer 3 · answered Jul 23 '13 at 07:49

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.

score 17 · Answer 4 · edited Jun 19 '16 at 18:34

17

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

edited Jun 19 '16 at 18:34

Andrew Tobilko

48,120
14
91
142

answered Oct 25 '15 at 01:57

user2754985

329
3
5

1

i am getting the syntax error "WebServlet is abstract cannot be instatiated" – P-RAD Mar 09 '16 at 05:07
@P-RAD you imported the wron WebServlet You must `import org.h2.server.web.WebServlet;` – Robert Jan 02 '17 at 23:09
2

More details about this approach can be found here: https://springframework.guru/using-the-h2-database-console-in-spring-boot-with-spring-security/ – Astrowie Jan 17 '17 at 10:32

score 14 · Answer 5 · edited Jun 19 '16 at 18:37

14

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

edited Jun 19 '16 at 18:37

Andrew Tobilko

48,120
14
91
142

answered Jul 25 '13 at 09:55

F. Geraerts

1,150
17
23

score 3 · Answer 6 · answered Feb 22 '15 at 11:19

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/*" );
 );
}

Gunjan Shah · Answer 7 · 2023-05-12T11:19:36.357

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 the scope declaration and it fixed my issue. Now, 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>

View content of embedded H2 database started by Spring

7 Answers7

Linked