View contents of H2 embedded database started by Spring

Question

I would like to view the contents of the H2 database started by Spring in a web browser, 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 JDBC URLs in logs:

 DEBUG osjdSimpleDriverDataSource - Creating new JDBC Driver Connection to [jdbc:h2:mem:dataSource;DB_CLOSE_DELAY=-1] 

So that I can fill out the connection form as follows:

But unfortunately db is still empty, and not because of the populateDB.sql script.

Any idea?

Thank!

Answer 1

Basically the same question as Viewing the contents of an H2 or HSQLDB database in memory .

Just 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 launch both the H2 web console and the 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 the URL address) or access port 9092 with an external SQL client, such as SQuirreLSQL, and view the same data.

Answer 2

When using Spring Boot, you can register the H2 servo 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 accessible remotely, the important line is addInitParameter to set "webAllowOthers" to "true" .

Answer 3

The jdbc:h2:mem:dataSource database jdbc:h2:mem:dataSource means you are using the database in memory. Now, if you start the second Java process and connect to this database, you will have two databases in memory (one for each process).

If you want to connect to an existing database, you have several options:

• Connect to the database from the same process. Do not start the second process.

• Use a persistent database with a hard code for the absolute path, for example: `jdbc: h2: / data / db / dataSource '.

• More complicated / not recommended: if you start the second process, theoretically you can connect to the database in memory using server mode. But this means that you need to start the server on which you ran the test.

Answer 4

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

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

You can then access the h2 web console at http: // localhost: 8080 / console / . The default login configuration should work unless you change them.

See spring boot documentation .

Answer 5

When using embed with xml jdbc configuration, the default database name is "testdb"

Try using in your url connection:

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

Answer 6

For those who want to configure Java Config, it’s quite simple to initialize the TCP server while implementing the ServletContextInitializer and bind 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/*" ); ); }