Backing up and restoring sqlite from disk to memory in Java

Question

I am trying to read sqlite-File in memory for better performance, when closing my application I want to write it back to hdd.

I am using jdbc driver (3.7.2) 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 valid sqlite db), this._conn open, but if I want to execute instructions on it, it seems that there is no table and data inside. It does not seem to restore anything.

Any suggestions for further solution / debugging?

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

Answer 1

It looks like you are missing two things: 1) 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(); } 

This was the only way to get it working with Xerial SQLite JDBC version 3.7.15-M1. I don't think the version matters in this case.

Answer 2

The quotes and stat.close() do not matter at the link: ` https://bitbucket.org/xerial/sqlite-jdbc/wiki/Usage 'that I tested. Quotations help when the file path contains spaces.

I think this is possible due to the jdbc driver. Try the Xerial JDBC driver for SQLite, which works great for me.