无法查看h2数据库Web控制台，如何更改默认的h2端口

Question

我对H2数据库还不熟悉。为了开发目的，我想使用H2数据库，并且在将其配置到我的Spring Boot Web应用程序时遇到了麻烦。我查看了几个教程和SO线程，但没有一个能解决我的问题。以下是我所做的尝试，但都没有成功。 要求： 1. H2数据可通过Web界面（控制台视图）查看 2. 即使停止运行Web应用程序或甚至我的机器（笔记本电脑），我也希望数据得以持久化，以便无需重新输入所有数据并可以从上次离开的地方开始。 3. 希望将控制台（8080）的默认端口更改为自己选择的端口（XXXX）。

在查看H2特点后，尝试使用TCP的服务器模式，但未能成功启动我的应用程序，无法查看控制台（Web）应用程序，并且无法将默认端口更改为我自己选择的端口。

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类：

@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

我得到的异常：

    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)

如果需要更好地理解问题，是否需要发布更多的代码或其他详细信息，请告诉我。

- Ghost Rider

---

我的 spring.datasource.url 属性具有以下值 jdbc:h2:file:./target/h2db/db/test;DB_CLOSE_DELAY=-1 - 你能试试吗？（不使用 TCP 协议） - blurfus

我的应用程序能够创建表并运行，但我无法查看位于“http://localhost:8080/h2/”的Web控制台。 - Ghost Rider

我明白了...请检查我的回答是否有帮助。 - blurfus

Answer 1

使用Spring Boot，您可以像这样配置H2控制台Servlet：

（提示：确保导入了正确的库）

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;
    }
}

然后，您应该能够通过您的URL http://localhost:7777/h2-console 访问H2数据库（参考 Spring Guru 和 H2 Console Settings）。请注意保留HTML标签。

- blurfus

---

我已经在我的应用程序中配置了这个Bean，除了添加参数“webAllowOthers”和“webPort”的部分。根据您的答案，我已经添加了这两个参数，但仍然面临同样的问题。 “连接已断开：“java.net.ConnectException：Connection refused: connect: localhost:7777” [90067-196]” - Ghost Rider

@GhostRider，我觉得你的spring.datasource.url有点问题，但是我现在没有时间来调试它。你能看一下**这个答案是否对你有帮助**吗？- 它包含了TCP服务器和Web服务器（用于控制台访问）的配置，也许把所有的拼图放在一起会有所作用？ - blurfus

Answer 2

在尝试使用Spring Security与H2数据库时，我遇到了类似的问题。经过一些调试，我发现我们必须在扩展WebSecurityConfigurerAdapter类中添加路径。

 @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();

参考资料：X-Frame-Options spring guru

- Sumit Paul