Helidon MP/Java/H2 database: H2 console cannot find database 'testdb'

Question

I am building a simple Helidon MP (v 3.2.2) application that uses H2 db and makes queries on an Employee table. I am able to make rest calls to my table. However, I am having trouble getting the H2 console to work so that I can view my entity table.

I am configuring my H2 database programmatically as shown below:

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

(Reason for doing this programmatically is so that the DataSource class can support different types of databases (i.e H2, Oracle, etc).

I am getting the db credentials from my local microprofile-config.properties file:

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>

I also have a repository class that uses EntityManager, and a controller class as well. And finally, I have an H2Server class that should start H2 server on localhost:8082 so that I can actually view the employee table:

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

With this configuration, I am able to run my application and make REST calls to the employee table. However, I am unable to view the Employee table on the H2 server running on localhost:8082. When I enter the url, username, and password on the H2 console, I get the following error:

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

I verified the jdbc url, username, and password on the H2 console are indeed correct. Any suggestions on how to make the H2 console work?

Answer 1

In one VM, you started a database server in webserver mode to support the console:

this.server = Server.createWebServer("-webPort", "8082").start();

Then in another VM I think you attempt to connect to another, differently-named database which, being an in-memory database, was never created:

my.application.db.h2.url=jdbc:h2:mem:testdb

I suggest you read about how H2 works in its excellent, comprehensive documentation.