Log JDBC connection URL for embedded database when using H2 console

[sbrannen]

When testing a web application with an embedded H2 database and the H2 console enabled (spring.h2.console.enabled=true), it would be useful if Spring Boot logged the JDBC connection URL so that users know how to connect to the database from the /h2/console endpoint.

As @wilkinsona mentioned elsewhere, it would be even better if the H2 console could be prepopulated with the right JDBC connection URL.

FWIW, the embedded database support in core Spring always logs the JDBC connection URL at INFO level (see org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseFactory.initDatabase()).

[wilkinsona]

it would be even better if the H2 console could be prepopulated with the right JDBC connection URL.

This would require us to write out a properties file that's then read by H2's console to populate its UI. While technically possible, it's rather clunky.

it would be useful if Spring Boot logged the JDBC connection URL so that users know how to connect to the database from the /h2/console endpoint.

This is relatively straightforward change to H2ConsoleAutoConfiguration:

diff --git a/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/h2/H2ConsoleAutoConfiguration.java b/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/h2/H2ConsoleAutoConfiguration.java
index 89e03abd5a..c857d486fc 100644
--- a/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/h2/H2ConsoleAutoConfiguration.java
+++ b/spring-boot-project/spring-boot-autoconfigure/src/main/java/org/springframework/boot/autoconfigure/h2/H2ConsoleAutoConfiguration.java
@@ -16,13 +16,23 @@
 
 package org.springframework.boot.autoconfigure.h2;
 
+import java.sql.Connection;
+import java.sql.SQLException;
+
+import javax.sql.DataSource;
+
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
 import org.h2.server.web.WebServlet;
 
+import org.springframework.beans.factory.ObjectProvider;
+import org.springframework.boot.autoconfigure.AutoConfigureAfter;
 import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
 import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
 import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
 import org.springframework.boot.autoconfigure.condition.ConditionalOnWebApplication;
 import org.springframework.boot.autoconfigure.condition.ConditionalOnWebApplication.Type;
+import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
 import org.springframework.boot.context.properties.EnableConfigurationProperties;
 import org.springframework.boot.web.servlet.ServletRegistrationBean;
 import org.springframework.context.annotation.Bean;
@@ -40,11 +50,15 @@ import org.springframework.context.annotation.Configuration;
 @ConditionalOnWebApplication(type = Type.SERVLET)
 @ConditionalOnClass(WebServlet.class)
 @ConditionalOnProperty(prefix = "spring.h2.console", name = "enabled", havingValue = "true", matchIfMissing = false)
+@AutoConfigureAfter(DataSourceAutoConfiguration.class)
 @EnableConfigurationProperties(H2ConsoleProperties.class)
 public class H2ConsoleAutoConfiguration {
 
+       private static final Log logger = LogFactory.getLog(H2ConsoleAutoConfiguration.class);
+
        @Bean
-       public ServletRegistrationBean<WebServlet> h2Console(H2ConsoleProperties properties) {
+       public ServletRegistrationBean<WebServlet> h2Console(H2ConsoleProperties properties,
+                       ObjectProvider<DataSource> dataSource) {
                String path = properties.getPath();
                String urlMapping = path + (path.endsWith("/") ? "*" : "/*");
                ServletRegistrationBean<WebServlet> registration = new ServletRegistrationBean<>(new WebServlet(), urlMapping);
@@ -55,6 +69,15 @@ public class H2ConsoleAutoConfiguration {
                if (settings.isWebAllowOthers()) {
                        registration.addInitParameter("webAllowOthers", "");
                }
+               dataSource.ifAvailable((available) -> {
+                       try (Connection connection = available.getConnection()) {
+                               logger.info("H2 console available at '" + path + "'. Database available at '"
+                                               + connection.getMetaData().getURL() + "'");
+                       }
+                       catch (SQLException ex) {
+                               // Continue
+                       }
+               });
                return registration;
        }

It produces a log message like this at startup:

2019-06-05 21:21:28.628  INFO 39116 --- [           main] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:testdb'

While I still think it'd be nice to do this automatically, the latter feels like the more pragmatic option. Let's see what the rest of the team thinks.

[rozeuss]

Hi,

Having two datasources defined as described in this it ends up with wiring only the first datasource. In my case, where MariaDB is the 1st datasource (marked with @Primary) and H2 is the 2nd, it logs following:

H2 console available at '/h2-console'. Database available at 'jdbc:mariadb://localhost:3306/product?useLegacyDatetimeCode=false&serverTimezone=UTC'

[wilkinsona]

Thanks, @rozeuss. That's an interesting problem. I believe you can use the H2 console to connect to any database over JDBC, so arguably the log message isn't wrong. However, given your comment, I guess the database URL it contains isn't the one that you expected. I guess we could list the URLs of every DataSource rather than just the primary DataSource. If you'd like us to do something like this, please open a new issue and we can consider our options.