I wanted to load SQLite database from disk to memory and I'm using JDBC driver, but I couldn't find any proper method in Java to do this.

JDBC Driver:

    implementation group: 'org.xerial', name: 'sqlite-jdbc', version: '3.36.0.3'

I found that

CodePudding user response：

Analysis

Could not reproduce the problem.

Just a guess. Please, make sure that the closeable resources (java.sql.Connection, java.sql.Statement, java.sql.ResultSet, etc.) are used and closed appropriately.

Working draft example program

Maven project (pom.xml)

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.36.0.3</version>
    <scope>runtime</scope>
</dependency>

Program class

For the first run, please, apply the following changes:

• Uncomment the forceSeed() method call.
• Comment the restore() method call.

Afterwards, for the subsequent runs, please, undo these changes.

package info.brunov.stackoverflow.question72870080;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public final class Program {
    public static void main(final String[] args) throws SQLException {
        try (
            final Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");
        ) {
            // NOTE: Uncomment the below line for the first run:
            // forceSeed(connection);
            // NOTE: Comment the below line for the first run:
            restore(connection);
            printPersons(connection);
            backup(connection);
        }
    }

    private static void forceSeed(final Connection connection) throws SQLException {
        try (final Statement statement = connection.createStatement()) {
            statement.executeUpdate("DROP TABLE IF EXISTS person");
            statement.executeUpdate("CREATE TABLE person (id integer, name string)");
            statement.executeUpdate("INSERT INTO person VALUES(1, 'First')");
            statement.executeUpdate("INSERT INTO person VALUES(2, 'Second')");
            statement.executeUpdate("INSERT INTO person VALUES(3, 'Third')");
        }
    }

    private static void printPersons(final Connection connection) throws SQLException {
        try (
            final Statement statement = connection.createStatement();
            final ResultSet resultSet = statement.executeQuery("SELECT * FROM person");
        ) {
            while (resultSet.next()) {
                System.out.println(
                    String.format(
                        "Person: ID: %d, Name: %s.",
                        resultSet.getInt("id"),
                        resultSet.getString("name")
                    )
                );
            }
        }
    }

    private static void backup(final Connection connection) throws SQLException {
        try (final Statement statement = connection.createStatement()) {
            statement.executeUpdate("BACKUP TO backup.db");
        }
    }

    private static void restore(final Connection connection) throws SQLException {
        try (final Statement statement = connection.createStatement()) {
            statement.executeUpdate("RESTORE FROM backup.db");
        }
    }
}

Program output

Person: ID: 1, Name: First.
Person: ID: 2, Name: Second.
Person: ID: 3, Name: Third.