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!

Community · Accepted Answer · 2017-05-23 10:31:07Z

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, 2017 at 10:31

CommunityBot

11 silver badge

answered Nov 13, 2013 at 18:29

hshib

1,7601 gold badge18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

VJ. Over a year ago

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. Over a year ago

solved it.. the url needs to have "mem:" word in it.. New url: jdbc:h2:tcp://localhost:9092/mem:dataSource

Jonik Over a year ago

Any idea how to do the equivalent config in code, if you've configured your DataSource using EmbeddedDatabaseBuilder along these lines?

Manu Over a year ago

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.

chAmi · Accepted Answer · 2016-08-23 18:37:36Z

31

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, 2016 at 18:37

chAmi

1,8233 gold badges21 silver badges27 bronze badges

4 Comments

Nikunj Lahoti Over a year ago

Choose "Generic H2 (Embedded)" & hit "Connect".

Nom1fan Over a year ago

I don't why I can't get this to work. I have in my pom.xml :

<dependency>             <groupId>com.h2database</groupId>             <artifactId>h2</artifactId>             <version>${h2.db.version}</version>             <scope>test</scope>         </dependency>

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?

payne Over a year ago

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/

NiB Over a year ago

By default console can be accessed on localhost:8080/h2-console

Thomas Mueller · Accepted Answer · 2013-07-23 07:49:44Z

20

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.

answered Jul 23, 2013 at 7:49

Thomas Mueller

50.4k16 gold badges123 silver badges136 bronze badges

Comments

Andrew · Accepted Answer · 2016-06-19 18:34:57Z

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, 2016 at 18:34

Andrew

49.8k15 gold badges99 silver badges145 bronze badges

answered Oct 25, 2015 at 1:57

user2754985

3293 silver badges5 bronze badges

3 Comments

P-RAD Over a year ago

i am getting the syntax error "WebServlet is abstract cannot be instatiated"

Robert Over a year ago

@P-RAD you imported the wron WebServlet You must import org.h2.server.web.WebServlet;

Astrowie Over a year ago

More details about this approach can be found here: springframework.guru/…

Andrew · Accepted Answer · 2016-06-19 18:37:27Z

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, 2016 at 18:37

Andrew

49.8k15 gold badges99 silver badges145 bronze badges

answered Jul 25, 2013 at 9:55

F. Geraerts

1,19017 silver badges24 bronze badges

Comments

Edward J Beckett · Accepted Answer · 2015-02-22 11:19:57Z

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 · Accepted Answer · 2023-05-12 11:19:36Z

1

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>

edited May 12, 2023 at 11:19

answered Mar 9, 2019 at 18:40

Gunjan Shah

5,17816 gold badges58 silver badges76 bronze badges

Collectives™ on Stack Overflow

View content of embedded H2 database started by Spring

7 Answers 7

4 Comments

4 Comments

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

4 Comments

4 Comments

Comments

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related