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...)

The file exists, and it's a valid SQLite database - but does it contain any tables? — mthmulders
– mthmulders, Commented Jul 9, 2013 at 12:24
@mjreaper..did you find the solution for this code? can you help me how to restore? — Java Man
– Java Man, Commented Oct 23, 2013 at 8:50

Sergey Vyacheslavovich Brunov · Accepted Answer · 2022-07-08 17:08:46Z

4

It looks like you are missing two things:

The quotes around the file name.
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.

edited Jul 8, 2022 at 17:08

Sergey Vyacheslavovich Brunov

18.2k7 gold badges53 silver badges85 bronze badges

answered Nov 27, 2013 at 15:38

Mad Physicist

116k29 gold badges202 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Luke Machowski Over a year ago

It seems that the JDBC implementation looks specifically for lower case strings for 'backup' and 'restore'. When I try it with upper case, it doesn't parse it correctly.

Wei Lin · Accepted Answer · 2015-03-13 02:02:51Z

1

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.

edited Mar 13, 2015 at 2:02

answered Mar 11, 2015 at 0:40

Wei Lin

1651 silver badge7 bronze badges

Collectives™ on Stack Overflow

Backup and restore sqlite from disk to memory in Java

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related