java - 如何从脚本文件创建 HSQL in-memory-db？

Question

我在内存中有两个 HSQL 数据库。使用第一个数据库，我创建了数据库结构和测试数据，这很耗时。此数据库通过 SCRIPT 命令导出到一个 sql 文件中。我的第二个数据库(不一定在同一个 JVM 中)应该从导出的 sql 文件构建。

public static void main(String[] args) throws Exception {
    try (Connection firstConnection = DriverManager.getConnection("jdbc:hsqldb:mem:connection1", "sa", "")) {
        try (Statement statement = firstConnection.createStatement()) {
            statement.execute("CREATE TABLE table1 (id INT)");
            statement.execute("SCRIPT 'my.script'");
        }
    }

    try (Connection secondConnection = DriverManager.getConnection("jdbc:hsqldb:mem:connection2", "sa", "")) {
        ScriptUtils.executeSqlScript(secondConnection, new FileSystemResource("my.script"));
    }
}

调用 ScriptUtils.executeSqlScript() 时出现以下异常:

Caused by: java.sql.SQLInvalidAuthorizationSpecException: invalid authorization specification - already exists: SA in statement [CREATE USER SA PASSWORD DIGEST 'd41d8cd98f00b204e9800998ecf8427e']
    at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:327)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:247)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(JDBCStatement.java:1817)
    at org.hsqldb.jdbc.JDBCStatement.execute(JDBCStatement.java:638)
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:457)
    ... 3 more
Caused by: org.hsqldb.HsqlException: invalid authorization specification - already exists: SA
    at org.hsqldb.error.Error.error(Error.java:83)
    at org.hsqldb.error.Error.error(Error.java:72)
    at org.hsqldb.rights.GranteeManager.addUser(GranteeManager.java:623)
    at org.hsqldb.rights.UserManager.createUser(UserManager.java:115)
    at org.hsqldb.StatementSchema.getResult(StatementSchema.java:1026)
    at org.hsqldb.StatementSchema.execute(StatementSchema.java:268)
    at org.hsqldb.Session.executeCompiledStatement(Session.java:1378)
    at org.hsqldb.Session.executeDirectStatement(Session.java:1248)
    at org.hsqldb.Session.execute(Session.java:1008)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(JDBCStatement.java:1809)
    ... 5 more

导出现有内存数据库并稍后将其导入另一个(空)内存数据库的正确方法是什么？

Answer 1

SCRIPT 语句创建一个完整的数据库脚本，可以作为文件数据库打开。将第二个数据库作为文件打开:不存储修改的数据库。

Connection secondConnection = DriverManager.getConnection("jdbc:hsqldb:file:my;files_readonly=true", "sa", "")