Helidon MP 3.2.2中H2控制台无法找到testdb数据库的问题

我正在构建一个基于Helidon MP（v3.2.2）的应用，使用H2数据库对Employee表进行查询，REST接口能正常访问该表，但无法通过H2控制台查看实体表。

我通过编程方式配置H2数据源，从microprofile-config.properties获取数据库凭证，通过初始化脚本employee.sql创建并插入Employee表数据，persistence.xml配置了JTA数据源，同时通过H2Server类启动了localhost:8082的H2服务。

但在H2控制台输入正确的JDBC URL、用户名和密码后，出现错误：

Database "mem:testdb" not found, either pre-create it or allow remote database creation (not recommended in secure environments) [90149-212] 90149/90149

相关代码 #

MyDataSource类

@ApplicationScoped
@Named("myDs")
public class MyDataSource implements DataSource {
    
    @Override
    public Connection getConnection() throws SQLException {
            JdbcDataSource h2DataSource = new JdbcDataSource();
            h2DataSource.setURL(config.getValue("my.application.db.h2.url", String.class));
            h2DataSource.setUser(config.getValue("my.applicaton.db.h2.user", String.class));
            h2DataSource.setPassword(config.getValue("my.application.db.h2.pass", String.class));
            return h2DataSource.getConnection();
    }
}

microprofile-config.properties

my.application.db.h2.url=jdbc:h2:mem:testdb;INIT=RUNSCRIPT FROM 'classpath:employee.sql'
my.applicaton.db.h2.user=sa
my.application.db.h2.pass=""

employee.sql

CREATE TABLE IF NOT EXISTS EMPLOYEE (
    ID INT NOT NULL PRIMARY KEY,
    CREATED_BY VARCHAR(240) NOT NULL,
    CREATION_DATE TIMESTAMP NOT NULL,
    DEPT VARCHAR(64) NOT NULL,
    LAST_UPDATE_DATE TIMESTAMP NOT NULL,
    LAST_UPDATED_BY VARCHAR(240) NOT NULL,
    NAME VARCHAR(64) NOT NULL
);

insert into EMPLOYEE (ID, CREATED_BY, CREATION_DATE, DEPT, LAST_UPDATE_DATE, LAST_UPDATED_BY, NAME) values (1, 'John', CURRENT_TIMESTAMP, 'Accounting', CURRENT_TIMESTAMP, 'John', 'Manon');
insert into EMPLOYEE (ID, CREATED_BY, CREATION_DATE, DEPT, LAST_UPDATE_DATE, LAST_UPDATED_BY, NAME) values (2, 'John', CURRENT_TIMESTAMP, 'Support', CURRENT_TIMESTAMP, 'John', 'Tedmund');

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="3.0"
             xmlns="https://jakarta.ee/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence
                                 https://jakarta.ee/xml/ns/persistence/persistence_2_2.xsd">
    <persistence-unit name="oalPu" transaction-type="JTA">
        <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
        <jta-data-source>myDs</jta-data-source>
        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <properties>
            <property name="eclipselink.target-server" value="io.helidon.integrations.cdi.eclipselink.CDISEPlatform"/>
        </properties>
    </persistence-unit>
</persistence>

H2Server类

@ApplicationScoped
public class H2Server {
    private Server server;
    private Config config = ConfigProvider.getConfig();

    public void init(@Observes @Initialized(ApplicationScoped.class) Object o) throws SQLException {
        String dbType = config.getValue("my.application.db.type", String.class);
        if(dbType.equals("h2")) {
            log.debug("STARTING H2");
            this.server = Server.createWebServer("-webPort", "8082").start();
        }
    }

    @PreDestroy
    public void destroy() {
        if(server != null)
            server.stop();
    }
}

1. 修复内存数据库的会话隔离问题 #

H2的mem:模式内存数据库默认是会话私有的，应用和H2控制台属于不同JDBC会话，因此控制台无法识别应用创建的testdb。

修改JDBC URL，添加全局共享参数：

my.application.db.h2.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;INIT=RUNSCRIPT FROM 'classpath:employee.sql'

DB_CLOSE_DELAY=-1会让数据库在最后一个连接关闭后才销毁，DB_CLOSE_ON_EXIT=FALSE避免应用退出时强制关闭数据库，确保所有会话共享同一实例。

2. 统一数据源实例，避免重复创建 #

当前MyDataSource每次调用getConnection()都会新建JdbcDataSource，可能导致配置不一致。改为初始化时创建一次数据源：

@ApplicationScoped
@Named("myDs")
public class MyDataSource implements DataSource {
    private final JdbcDataSource h2DataSource;

    // 注入Config替代硬编码获取
    public MyDataSource(Config config) {
        h2DataSource = new JdbcDataSource();
        h2DataSource.setURL(config.getValue("my.application.db.h2.url", String.class));
        h2DataSource.setUser(config.getValue("my.application.db.h2.user", String.class));
        h2DataSource.setPassword(config.getValue("my.application.db.h2.pass", String.class));
    }
    
    @Override
    public Connection getConnection() throws SQLException {
        return h2DataSource.getConnection();
    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return h2DataSource.getConnection(username, password);
    }

    // 实现其他DataSource接口方法，可直接委托给h2DataSource或抛出UnsupportedOperationException
}

3. 修正配置拼写错误 #

microprofile-config.properties中用户名配置项存在拼写错误：my.applicaton.db.h2.user（少了一个i），修正为my.application.db.h2.user，避免后续配置读取异常。

4. 确保控制台连接参数与应用一致 #

H2控制台输入的JDBC URL必须和应用配置完全相同（包括所有附加参数），用户名填sa，密码留空（因为配置中是""）。

5. 调整H2服务启动时机 #

当前H2Server监听@Initialized(ApplicationScoped.class)事件，可能早于数据库初始化。改为监听@Initialized(ServletContext.class)事件，确保H2服务在应用完全初始化后启动：

public void init(@Observes @Initialized(ServletContext.class) ServletContext context) throws SQLException {
    String dbType = config.getValue("my.application.db.type", String.class);
    if(dbType.equals("h2")) {
        log.debug("STARTING H2");
        this.server = Server.createWebServer("-webPort", "8082").start();
    }
}

内容的提问来源于stack exchange，提问作者swing1234