H2 in-memory database even if I use DB_CLOSE_DELAY=-1 reports Table not found

Question

I'm using h2 database as dependency for maven project:

 <dependency>
         <groupId>com.h2database</groupId>
         <artifactId>h2</artifactId>
         <version>1.4.200</version>
 </dependency>

To use in-memory mode I've configured application.properties file:

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

as mentioned here and here, because for variant: jdbc:h2:mem:test .

the content of the database is lost at the moment the last connection is closed.

At the same time, I've configured Data Sources using Intelij Idea:

Next step, I've successfully filled data using the script w/ h2-dialect:

CREATE TABLE users
(
    id    int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    login varchar(10),
    PRIMARY KEY (id),
    UNIQUE (login)
);
CREATE TABLE teams
(
    id   int NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    name varchar(10),
    PRIMARY KEY (id)
);
CREATE TABLE users_teams
(
    user_id int,
    team_id int,
    FOREIGN KEY (user_id)
        REFERENCES users (id) ON DELETE CASCADE,
    FOREIGN KEY (team_id)
        REFERENCES teams (id) ON DELETE CASCADE,
    UNIQUE (
            user_id,
            team_id
        )
);

after filling the connection wasn't closed, but I'm still getting the error if I run the program during the connection:

jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager insertUser
Table "USERS" not found; SQL statement:
INSERT INTO users (login) VALUES ('petrov'); [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager insertUser
Table "USERS" not found; SQL statement:
INSERT INTO users (login) VALUES ('obama'); [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager findAllUsers
Table "USERS" not found; SQL statement:
SELECT * FROM users; [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager insertTeam
Table "TEAMS" not found; SQL statement:
INSERT INTO teams (name) VALUES ('teamB'); [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager insertTeam
Table "TEAMS" not found; SQL statement:
INSERT INTO teams (name) VALUES ('teamC'); [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager findAllTeams
Table "TEAMS" not found; SQL statement:
SELECT * FROM teams; [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager getUser
Table "USERS" not found; SQL statement:
SELECT * FROM users WHERE users.login='petrov'; [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager getUser
Table "USERS" not found; SQL statement:
SELECT * FROM users WHERE users.login='ivanov'; [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager getUser
Table "USERS" not found; SQL statement:
SELECT * FROM users WHERE users.login='obama'; [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager returnTeam
Table "TEAMS" not found; SQL statement:
SELECT * FROM teams WHERE teams.name='teamA'; [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager returnTeam
Table "TEAMS" not found; SQL statement:
SELECT * FROM teams WHERE teams.name='teamB'; [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager returnTeam
Table "TEAMS" not found; SQL statement:
SELECT * FROM teams WHERE teams.name='teamC'; [42102-200]
jan 19, 2021 3:50:01 AM com.epam.rd.java.basic.practice8.db.DBManager findAllUsers
Table "USERS" not found; SQL statement:
SELECT * FROM users; [42102-200]
Exception in thread "main" java.lang.NullPointerException
===========================
===========================
===========================
    at com.epam.rd.java.basic.practice8.Demo.main(Demo.java:382)

Also I've tried to use:

jdbc:h2:mem:test;IFEXISTS=FALSE

as stated here, but the same issue during the connection.

Logic for Demo class looks like:

public class Demo {

    private static <T> void printList(List<T> list) {
        for (T element : list) {
            System.out.println(element);
        }
    }

    public static void main(String[] args) {

        DBManager dbManager = DBManager.getInstance();

        dbManager.insertUser(User.createUser("petrov"));
        dbManager.insertUser(User.createUser("obama"));

        printList(dbManager.findAllUsers());

        System.out.println("===========================");

        dbManager.insertTeam(Team.createTeam("teamB"));
        dbManager.insertTeam(Team.createTeam("teamC"));

        printList(dbManager.findAllTeams());

        System.out.println("===========================");

        User userPetrov = dbManager.getUser("petrov");
        User userIvanov = dbManager.getUser("ivanov");
        User userObama = dbManager.getUser("obama");

        Team teamA = dbManager.getTeam("teamA");
        Team teamB = dbManager.getTeam("teamB");
        Team teamC = dbManager.getTeam("teamC");

        dbManager.setTeamsForUser(userIvanov, teamA);
        dbManager.setTeamsForUser(userPetrov, teamA, teamB);
        dbManager.setTeamsForUser(userObama, teamA, teamB, teamC);

        for (User user : dbManager.findAllUsers()) {
            printList(dbManager.getUserTeams(user));
            System.out.println("~~~~~");
        }

        System.out.println("===========================");

        dbManager.deleteTeam(teamA);

        teamC.setName("teamX");
        dbManager.updateTeam(teamC);

        printList(dbManager.findAllTeams());
    }
}

Can someone explain me, please, why this error appears if:

By default, closing the last connection to a database closes the database. For an in-memory database, this means the content is lost. To keep the database open, add ;DB_CLOSE_DELAY=-1 to the database URL. To keep the content of an in-memory database as long as the virtual machine is alive, use jdbc:h2:mem:test;DB_CLOSE_DELAY=-1.

Anthony Accioly · Accepted Answer · 2021-02-02T22:45:31.737

The embbeded in-memory instance of H2 that you are starting from IntelliJ runs in a different process and is unrelated to your application's embedded H2 instance. You are dealing with two separate databases, your application can't see the tables that you have created with IntelliJ.

If you need to use an embedded in-memory database then you will also need to create the database schema from the application itself. For example, by executing a SQL script on connection or by using a schema evolution tool such as Flyway or Liquibase. Frameworks like Spring Boot can also be configured to perform Database Initialisation

If you actually need multiple processes to have access to the same database instance then you'll need to run H2 in Server Mode (it works with in-memory databases if you start the server programmatically from your application) or Automatic Mixed Mode (doesn't work with in-memory databases).

H2 in-memory database even if I use DB_CLOSE_DELAY=-1 reports Table not found

1 Answers1