Backup and restore sqlite from disk to memory in Java

Question

I'm trying to read a sqlite-File into memory for better performance, when closing my application I want to write it back to hdd.

I'm using the jdbc (3.7.2) driver in Java.

According to the docs, my Code looks like

this._conn = DriverManager.getConnection("jdbc:sqlite:");
Statement stat  = this._conn.createStatement();
File dbFile = new File(this._config.GetDataBaseFile());
if (dbFile.exists()) {
    this._logger.AddInfo("File exists.");
    stat.executeUpdate("restore from " + dbFile.getAbsolutePath());
}

The file exists (and its a valid sqlite db), this._conn is open, but if I want to execute statements on it, it appears that there is no table nor data inside. It seems it doesn't restore anything.

Any suggestions on how to solve/debug that further?

(by the way - if I use stat.executeUpdate("backup to test.db")on my connection, it backups my empty :memory: db...)

Answer 1

It looks like you are missing two things: 1) The quotes around the file name, and 2) stat.close. Try the following:

this._conn = DriverManager.getConnection("jdbc:sqlite:");
Statement stat  = this._conn.createStatement();
File dbFile = new File(this._config.GetDataBaseFile());
if (dbFile.exists()) {
    this._logger.AddInfo("File exists.");
    stat.executeUpdate("restore from '" + dbFile.getAbsolutePath() + "'");
    stat.close();
}

That was the only way I could get it to work with Xerial SQLite JDBC version 3.7.15-M1. I do not think that the version matters much in this case.

Answer 2

The quotes and stat.close() don't matter, according to the link: `https://bitbucket.org/xerial/sqlite-jdbc/wiki/Usage', which I have tested. Quotes help when file path contains spaces, though.

I think it maybe due to the jdbc driver. Try Xerial's JDBC driver for SQLite, which works fine for me.