打包后的Java程序无法连接SQLite数据库（调试环境正常）

使用Gradle jlink和jpackage创建Windows可执行文件后，安装的应用无法连接SQLite数据库，但开发环境中可正常运行。错误日志如下：

SEVERE: path to 'jrt:/com.scholarly/assets/databases/local_database.db': 'C:\Users\DELL\AppData\Local\Scholarly CBT\jrt:' does not exist
Nov 09, 2023 3:47:30 PM com.scholarly.MainApplication log
SEVERE: com.scholarly.merged.module@0.3/org.sqlite.SQLiteConnection.open(Unknown Source)
Nov 09, 2023 3:47:30 PM com.scholarly.MainApplication log
SEVERE: com.scholarly.merged.module@0.3/org.sqlite.SQLiteConnection.<init>(Unknown Source)
Nov 09, 2023 3:47:30 PM com.scholarly.MainApplication log
SEVERE: com.scholarly.merged.module@0.3/org.sqlite.jdbc3.JDBC3Connection.<init>(Unknown Source)
Nov 09, 2023 3:47:30 PM com.scholarly.MainApplication log
SEVERE: com.scholarly.merged.module@0.3/org.sqlite.jdbc4.JDBC4Connection.<init>(Unknown Source)
Nov 09, 2023 3:47:30 PM com.scholarly.MainApplication log
SEVERE: com.scholarly.merged.module@0.3/org.sqlite.JDBC.createConnection(Unknown Source)
Nov 09, 2023 3:47:30 PM com.scholarly.MainApplication log
SEVERE: com.scholarly.merged.module@0.3/org.sqlite.JDBC.connect(Unknown Source)
Nov 09, 2023 3:47:30 PM com.scholarly.MainApplication log
SEVERE: java.sql/java.sql.DriverManager.getConnection(Unknown Source)
Nov 09, 2023 3:47:30 PM com.scholarly.MainApplication log
SEVERE: java.sql/java.sql.DriverManager.getConnection(Unknown Source)
Nov 09, 2023 3:47:30 PM com.scholarly.MainApplication log
SEVERE: com.scholarly@0.3/com.scholarly.data.util.DbConnection.getDbConnection(Unknown Source)

报错相关代码：

private static final String TAG = "DbConnection: ";
private static final String location = MainApplication.class.getResource("/assets/databases/local_database.db").getPath();
private static final String DATABASE_URL = "jdbc:sqlite:"+location;

private static Connection connection;

public static Connection getDbConnection() {
    try {
        if (connection == null) {
            MainApplication.logInfo("Database Connecting..");
            connection = DriverManager.getConnection(DATABASE_URL, "", "");

            System.out.println(TAG + "Connection created successfully: " + connection.toString());
            MainApplication.logInfo(TAG + "Connection created successfully: " + connection.toString());
        } else {
            System.out.println(TAG + "Retrieved existing Connection : " + connection);
            MainApplication.logInfo(TAG + "Retrieved existing Connection : " + connection);
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
        MainApplication.log(e);
    }
    return connection;
}

开发环境中，数据库文件位于classpath下，getResource().getPath()能返回本地文件系统路径；但通过jlink打包成模块化运行时镜像后，资源被封装在jrt:/格式的模块路径中，SQLite JDBC无法识别这种路径格式，且模块内资源无法直接支持读写操作。

将模块内的数据库文件复制到用户可读写的本地目录（比如Windows的AppData\Local下的应用专属目录），再使用本地文件路径建立连接，修改后的代码示例：

private static final String TAG = "DbConnection: ";
private static Connection connection;

public static Connection getDbConnection() {
    try {
        if (connection == null) {
            // 获取应用专属数据目录
            String appDataDir = System.getenv("LOCALAPPDATA") + File.separator + "Scholarly CBT";
            File dbDir = new File(appDataDir + File.separator + "databases");
            if (!dbDir.exists()) {
                dbDir.mkdirs(); // 创建多级目录
            }
            File dbFile = new File(dbDir, "local_database.db");

            // 若本地无数据库文件，从模块资源中复制
            if (!dbFile.exists()) {
                try (InputStream is = MainApplication.class.getResourceAsStream("/assets/databases/local_database.db");
                     OutputStream os = new FileOutputStream(dbFile)) {
                    byte[] buffer = new byte[1024];
                    int bytesRead;
                    while ((bytesRead = is.read(buffer)) != -1) {
                        os.write(buffer, 0, bytesRead);
                    }
                }
            }

            // 使用本地文件路径创建JDBC连接
            String DATABASE_URL = "jdbc:sqlite:" + dbFile.getAbsolutePath();
            MainApplication.logInfo("Database Connecting..");
            connection = DriverManager.getConnection(DATABASE_URL, "", "");

            System.out.println(TAG + "Connection created successfully: " + connection.toString());
            MainApplication.logInfo(TAG + "Connection created successfully: " + connection.toString());
        } else {
            System.out.println(TAG + "Retrieved existing Connection : " + connection);
            MainApplication.logInfo(TAG + "Retrieved existing Connection : " + connection);
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
        MainApplication.log(e);
    }
    return connection;
}

• 若数据库为只读状态，可尝试使用SQLite JDBC的::resource:语法，比如jdbc:sqlite::resource:/assets/databases/local_database.db，但需在module-info.java中添加opens assets.databases to org.sqlite;确保资源可被SQLite模块访问，且该方式不支持写入操作。
• 复制到本地目录的方式通用性更强，支持读写操作，适合绝大多数场景。

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