not able to view h2 database web console and how to change the default h2 port

Question

I am new to H2 database. For the development purpose I would like to use H2 database and I am having trouble in configuring it with my spring boot web application. I have gone thru several tutorials and SO threads but none could solve my issue. Below are the trails that I have done and none was successful.

Requirement: 1. H2 data base that I can view with web interface( console view) 2. I would like my data to be persisted even if I stop my running web application or even my machine(laptop) so that I do not have to enter all the data once again and can start from where I have left. 3. Would like to change the default port of console(8080) to my own choice(XXXX).

After viewing H2 Features Tried with Server Mode with TCP but was not able to successfully start my application , was not able to view Console(web) application and was not able to change the default port to my own choice of port.

pom.xml:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>com.javasree</groupId>
 <artifactId>familytree.thymeleaf</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <name>familytree</name>
 <packaging>jar</packaging>

 <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <!-- <version>1.5.2.RELEASE</version> -->
    <version>2.0.0.RELEASE</version>
</parent>

<properties>
    <java.version>1.8</java.version>
    <hibernate.version>5.2.3.Final</hibernate.version>
    <start- class>com.javasree.spring.familytree.FamilyTreeApplication</start-class>
</properties>

<dependencies>
    <!-- This is a web application -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <!-- This is a web application test dependencies -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
    </dependency>
    <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring- boot-starter-data-jpa -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- thymeleaf -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
    <!-- hot swapping, disable cache for template, enable live reload -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-devtools</artifactId>
        <optional>true</optional>
    </dependency>
    <!-- Tomcat embedded container-->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-tomcat</artifactId>
        <scope>provided</scope>
    </dependency>

    <!-- JSTL for JSP -->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>jstl</artifactId>
    </dependency>

    <!-- Need this to compile JSP -->
    <dependency>
        <groupId>org.apache.tomcat.embed</groupId>
        <artifactId>tomcat-embed-jasper</artifactId>
        <scope>provided</scope>
    </dependency>

    <!-- Need this to compile JSP,
        tomcat-embed-jasper version is not working, no idea why -->
    <dependency>
        <groupId>org.eclipse.jdt.core.compiler</groupId>
        <artifactId>ecj</artifactId>
        <version>4.6.1</version>
        <scope>provided</scope>
    </dependency>

    <!-- hibernate dependencies -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-core</artifactId>
    </dependency>

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
    </dependency>

    <!-- Servlet -->
    <dependency>
        <groupId>javax.servlet</groupId>
        <artifactId>servlet-api</artifactId>
        <version>2.5</version>
        <scope>provided</scope>
    </dependency>

    <dependency>
        <groupId>javax.servlet.jsp</groupId>
        <artifactId>jsp-api</artifactId>
        <version>2.1</version>
        <scope>provided</scope>
    </dependency>

<!--        <dependency>
        <groupId>commons-beanutils</groupId>
        <artifactId>commons-beanutils</artifactId>
        <version>1.8.0</version>
    </dependency> -->

<!--        <dependency>
        <groupId>commons-digester</groupId>
        <artifactId>commons-digester</artifactId>
        <version>2.0</version>
    </dependency> -->

    <!-- Test -->
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <scope>test</scope>
    </dependency>

    <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <!--<version>5.1.36</version>-->
    </dependency>

    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
    </dependency>

    <!-- Optional, for bootstrap -->
    <dependency>
        <groupId>org.webjars</groupId>
        <artifactId>bootstrap</artifactId>
        <version>3.3.7</version>
    </dependency>
    <dependency>
        <groupId>org.webjars</groupId>
        <artifactId>jquery</artifactId>
        <version>3.1.1</version>
    </dependency>

    <dependency>
        <groupId>org.thymeleaf.extras</groupId>
        <artifactId>thymeleaf-extras-java8time</artifactId>
    </dependency>

    <dependency>
        <groupId>nz.net.ultraq.thymeleaf</groupId>
        <artifactId>thymeleaf-layout-dialect</artifactId>
    </dependency>

    <!-- https://mvnrepository.com/artifact/oracle/ojdbc6
    <dependency>
        <groupId>com.oracle</groupId>
        <artifactId>ojdbc6</artifactId>
        <version>11.2.0.3</version>
    </dependency> -->

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

</dependencies>

<repositories>
    <repository>
        <id>codelds</id>
        <url>https://code.lds.org/nexus/content/groups/main-repo</url>
    </repository>
</repositories>

<build>
    <plugins>
        <!-- Package as an executable jar/war -->
        <plugin>
            <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-resources-plugin</artifactId>
        <version>2.7</version>
      <dependencies>
        <dependency>
            <groupId>org.apache.maven.shared</groupId>
            <artifactId>maven-filtering</artifactId>
            <version>1.3</version>
        </dependency>
      </dependencies>
        </plugin>
    </plugins>

  </build>
</project>

Spring boot class:

@SpringBootApplication
@EntityScan(basePackages={"com.javasree.spring.familytree.model"})
@EnableJpaRepositories(basePackages = { 
 "com.javasree.spring.familytree.web.jpa"})
public class FamilyTreeApplication extends SpringBootServletInitializer{

 @Override
protected SpringApplicationBuilder configure(SpringApplicationBuilder 
application){
    return application.sources(FamilyTreeApplication.class);
}
public static void main(String[] args) {
    try {
        Server.createTcpServer("-tcpAllowOthers","-webAllowOthers").start();
    } catch (SQLException e) {
        e.printStackTrace();
    }
    new SpringApplicationBuilder(FamilyTreeApplication.class)
    //.bannerMode(Mode.CONSOLE)
    .build().run(args);
}
}

application.properties:

    server.contextPath=/familytree
    server.port=7030

    # JPA properties
     spring.jpa.properties.hibernate.current_session_context_class=org.springframework.orm.hibernate5.SpringSessionContext
    spring.data.jpa.repositories.enabled=true
    spring.jpa.properties.hibernate.use_sql_comments=true

    spring.jpa.show-sql=true

    spring.jpa.generate-ddl=false
    spring.jpa.properties.hibernate.type=trace 


    #H2 config
    spring.h2.console.enabled=true
    spring.h2.console.path=/h2
    spring.h2.console.settings.trace=false
    # Datasource
     spring.datasource.url=jdbc:h2:tcp://localhost:8084/~/test;MODE=MYSQL;AUTO_SERVER=TRUE;
    spring.datasource.username=sa
    spring.datasource.password=
    spring.datasource.driver-class-name=org.h2.Driver
    spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

Exception that I am getting:

    org.h2.jdbc.JdbcSQLException: Connection is broken: "java.net.ConnectException: Connection refused: connect: localhost:8084" [90067-196]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
        at org.h2.message.DbException.get(DbException.java:168)
        at org.h2.engine.SessionRemote.connectServer(SessionRemote.java:457)
        at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:334)
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:116)
        at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:100)
        at org.h2.Driver.connect(Driver.java:69)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:117)
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:123)
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:365)
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:194)
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:460)
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:534)
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112)

Let me know if it's required to post any more code or any other details to better understand the issue.

my `spring.datasource.url` property has this value `jdbc:h2:file:./target/h2db/db/test;DB_CLOSE_DELAY=-1` - can you try this? (without tcp protocol) — blurfus, Apr 08 '18 at 17:08
my application is able to create tables and run but I am not able to view the web console at "http://localhost:8080/h2/" — Ghost Rider, Apr 09 '18 at 05:33

score 2 · Answer 1 · answered Apr 09 '18 at 21:26

With Spring Boot, you can configure your H2 console servlet like this:

(hint: make sure the right libraries are imported)

import org.h2.server.web.WebServlet;
import org.springframework.boot.context.embedded.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class WebConfiguration {
    @Bean
    ServletRegistrationBean h2servletRegistration(){
        ServletRegistrationBean registration = new ServletRegistrationBean( new org.h2.server.web.WebServlet());
        registration.addUrlMappings("/h2-console/*");
        registration.addInitParameter("webAllowOthers", "true");
        registration.addInitParameter("webPort", "7777");// <-- the port your wish goes here

        return registration;
    }
}

Then you should be able to access H2 via your URL http://localhost:7777/h2-console

(with help of Spring Guru, H2 Console Settings)

I already have this Bean configured in my application except the part of adding parameters "webAllowOthers" and "webPort". I have added the two parameters as per your answer and I still face the same problem. "Connection is broken: "java.net.ConnectException: Connection refused: connect: localhost:7777" [90067-196]" — Ghost Rider, Apr 10 '18 at 08:12
@GhostRider your `spring.datasource.url` looks off to me but I have no time at the moment to debug it. Can you see if **[this answer helps you](https://stackoverflow.com/a/28657208/600486)** ? - It has configuration of the TCP server as well as the WebServer (for the console access) perhaps seeing all the pieces of the puzzle together will work? — blurfus, Apr 10 '18 at 17:38

score 1 · Answer 2 · answered Jun 25 '20 at 16:17

I had this similar kind of issue when I was trying to use H2 db with Spring Security, after some debugging I find out we have to add the paths in the WebSecurityConfigurerAdapter extending class.

 @Override
    protected void configure(HttpSecurity http) throws Exception {
        http.csrf().disable()

        .antMatchers(HttpMethod.GET,"/h2/**").permitAll()
        .antMatchers(HttpMethod.POST,"/h2/**").permitAll()
        .anyRequest().authenticated();

//to avoid X-Frame-Options header of Spring Security. 
 http.headers().frameOptions().disable();

reference:X-Frame-Options spring guru

not able to view h2 database web console and how to change the default h2 port

2 Answers2

Linked