Loading SQLite db to memory in Java using JDBC?

Question

Usually a SQLite db is read from a file. Each operation can require a file I/O operation if the db is large enough, and this can be slow.

However SQLite provides a way to load the db to memory - where it can be accessed using a JDBC url which looks something likejdbc:memory...

For example, this question describes how to achieve this using python: How to load existing db file to memory in Python sqlite3?

However I can't figure out how to achieve the same thing in Java or Scala using JDBC.

Any ideas?

@MarkRotteveel I've added more details to the question (although in this case the question would've been obvious to those who would know the answer) — Ali, May 17 '19 at 13:09
Don't bother. It's really not worth the effort. Just increase the cache size and mmap size using pragmas. — Shawn, May 17 '19 at 13:23
It is worth the effort or I wouldn't have asked. I also need it to be loaded up front rather than lazily. — Ali, May 17 '19 at 13:36
You can use in-memory databases with sqlite using the JDBC driver, see https://stackoverflow.com/questions/8831514/sqlitehow-to-use-in-memory That makes your question more one of "how do I backup and restore a sqlite database" or "how do I copy the contents of a sqlite database". There is nothing like that in JDBC itself, so you'd need something sqlite specific. — Mark Rotteveel, May 17 '19 at 17:37

score 9 · Accepted Answer · answered May 17 '19 at 18:05

I just tried with Xerial's sqlite-jdbc-3.27.2.1.jar and it appears that they let us restore from a native (binary) SQLite database file into a :memory: database like so:

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    try (Statement st = conn.createStatement()) {
        st.execute("restore from C:\\__tmp\\thing.sqlite");

        // let's see if we can read one of the tables
        ResultSet rs = st.executeQuery("SELECT * FROM Table1");
        while (rs.next()) {
            System.out.println(rs.getString(1));
        }
    }

} catch (Throwable e) {
    e.printStackTrace(System.err);
}

Big perf improvement and barely takes a second for a 400ish mb file to be loaded into mem. — Ali, May 20 '19 at 13:12

Loading SQLite db to memory in Java using JDBC?

1 Answers1