CODEWITHSUNDEEP
javascalasqlitejdbc
Ali
Ali

Reputation: 267077

Loading SQLite db to memory in Java using JDBC?

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?

Upvotes: 4

Views: 4515

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

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

Upvotes: 9

Related Questions