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
– Ali, Commented May 17, 2019 at 13:09
Don't bother. It's really not worth the effort. Just increase the cache size and mmap size using pragmas. — Shawn
– Shawn, Commented May 17, 2019 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
– Ali, Commented May 17, 2019 at 13:36
You can use in-memory databases with sqlite using the JDBC driver, see 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
– Mark Rotteveel, Commented May 17, 2019 at 17:37

Gord Thompson · Accepted Answer · 2019-05-17 18:05:59Z

9

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);
}

answered May 17, 2019 at 18:05

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

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

1 Comment

Ali Over a year ago

Big perf improvement and barely takes a second for a 400ish mb file to be loaded into mem.

Collectives™ on Stack Overflow

Loading SQLite db to memory in Java using JDBC?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related