使用jooq上下文将内存sqlite db备份到字节数组 [英] backup in memory sqlite db to byte array using jooq Context

问题描述

private byte[] inMemSqliteDbBackup() {
    byte[] data = null;
    try (DSLContext dsl = DSL.using("jdbc:sqlite::memory:") {
        ...
        //insert some data 
        dsl.execute("backup to " + data); // backup to byte[], but unsupported , a file is needed 
        dsl.connection(connection -> {
            // or, get underlying connection and open inputstream 
            // but there is no getInputStream in SqliteConnection
        });
    }
    return data;
}

如何将sqlite db的内存备份到byte []? 我经历了SqliteConnection，它也没有提供InputStream.

How can we backup in memory sqlite db to byte[] ? I went through SqliteConnection and it does not give an InputStream either.

一个选择是不使用url作为"jdbc:sqlite:/some-location"在内存db中使用，然后我们可以使用FileUtils.readFileToByteArray(new File(some-location))，但是不确定是否可以在内存sqlite db中使用相同的功能，并且仍然使用Jooq的DSLContext.

one option is to not use in memory db by using url as "jdbc:sqlite:/some-location" and then we can use FileUtils.readFileToByteArray(new File(some-location)) , but not sure if we can do the same with in memory sqlite db and still using DSLContext by Jooq.

推荐答案

该backup to语法不是本机SQLite SQL语法，而是

That backup to syntax is not a native SQLite SQL syntax, but offered by the Xerial JDBC driver according to their docs here:

将整个数据库备份到backup.db文件:

Take a backup of the whole database to backup.db file:

// Create a memory database
Connection conn = DriverManager.getConnection("jdbc:sqlite:");
Statement stmt = conn.createStatement();
// Do some updates
stmt.executeUpdate("create table sample(id, name)");
stmt.executeUpdate("insert into sample values(1, \"leo\")");
stmt.executeUpdate("insert into sample values(2, \"yui\")");
// Dump the database contents to a file
stmt.executeUpdate("backup to backup.db");
Restore the database from a backup file:
// Create a memory database
Connection conn = DriverManager.getConnection("jdbc:sqlite:");
// Restore the database from a backup file
Statement stat = conn.createStatement();
stat.executeUpdate("restore from backup.db");

如果对它们的源进行反向工程，则可以看到该命令已被拦截，并在org.sqlite.core.NativeDB中转换为该特定方法:

If you reverse engineer their sources, you can see that the command is intercepted, and translated to this particular method in org.sqlite.core.NativeDB:

native synchronized int backup(byte[] dbNameUtf8, byte[] destFileNameUtf8,
        ProgressObserver observer) throws SQLException;

即它绑定到SQLite备份API，该API仅可用于实际文件，而不能用于内存中的数据结构.

I.e. it is bound to the SQLite backup API, which can operate only with actual files, not with in-memory data structures.

因此，很抱歉，对于当前版本的SQLite，无论是否直接使用jOOQ或JDBC或本机SQLite，都无法在不编写中间临时文件的情况下拦截该备份并将其发送到byte[]变量中

So, I'm afraid you cannot, with the current versions of SQLite, intercept that backup and send that into a byte[] variable, without an intermediate temporary file being written, regardless if using jOOQ or JDBC or native SQLite directly

这篇关于使用jooq上下文将内存sqlite db备份到字节数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！