H2内存中数据库，即使我使用DB\U CLOSE\U DELAY=-1报告表未找到

Question

我使用h2数据库作为maven项目的依赖项：

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

要在内存模式下使用，我已经配置了应用程序。属性文件：

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

正如这里和这里所提到的，因为对于variant:jdbc:h2:mem:test。

数据库的内容在最后一个连接关闭时丢失。

下一步，我已经使用带有h2方言的脚本成功地填充了数据：

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

填充连接后没有关闭，但如果我在连接期间运行程序，我仍然会收到错误：

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)

此外，我还尝试使用：

jdbc:h2:mem:test;IFEXISTS=FALSE

如本文所述，但在连接过程中存在相同的问题。

Demo类的逻辑如下所示：

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

请有人解释一下，如果出现以下情况，为什么会出现此错误：

默认情况下，关闭与数据库的最后一个连接会关闭数据库。对于内存数据库，这意味着内容丢失。要保持数据库打开，请在数据库URL中添加；DB_CLOSE_DELAY=-1。要在虚拟机处于活动状态时保留内存数据库的内容，请使用jdbc： h2： mem： test；DB_CLOSE_DELAY=-1。




Answer 1

您从IntelliJ启动的H2的嵌入内存实例在不同的进程中运行，并且与您的应用程序的嵌入式H2实例无关。您正在处理两个单独的数据库，您的应用程序看不到您使用IntelliJ创建的表。

如果需要使用嵌入式内存数据库，那么还需要从应用程序本身创建数据库架构。例如，通过在连接上执行SQL脚本或使用模式演化工具（如Flyway或Liquibase）。Spring Boot之类的框架也可以配置为执行数据库初始化

如果实际上需要多个进程才能访问同一个数据库实例，则需要在服务器模式下运行H2（如果从应用程序以编程方式启动服务器，则可以使用内存中的数据库）或自动混合模式（不适用于内存中的数据库）。