In SQLite how to backup database from disk into memory using JDBC driver

Question

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 here, In python we can use below code to do that

import sqlite3

source = sqlite3.connect('existing_db.db')
dest = sqlite3.connect(':memory:')
source.backup(dest)

Is there any similar approach in Java also ?

Update - 1

I tried using restore command but even that didn't work

Connection connection = 
DriverManager.getConnection("jdbc:sqlite:file:prod?mode=memory&cache=shared", config.toProperties());
Statement statement = connection.createStatement();
statement.executeUpdate("restore from products.db");

Update - 2

(In response to Answer by @Sergey Vyacheslavovich Brunov)

I'm attaching the code I used and it's output. If we see the Non-Heap Memory(Native Memory) usage there is no much increase, but also I was able to print the number of entries loaded (Output Section).

Now where are records getting loaded? Not able to track it

package com.example.sqlite.service;

import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.sqlite.SQLiteConfig;

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

@Service
@Slf4j
public class CacheService {

  private static Connection globalConnection = null;

  public static Connection getConnection() {
    return globalConnection;
  }

  public String buildCache() {
    try {
      SQLiteConfig config = new SQLiteConfig();
      config.setPragma(SQLiteConfig.Pragma.JOURNAL_MODE, "wal");
      config.setPragma(SQLiteConfig.Pragma.SYNCHRONOUS, "normal");
      config.setPragma(SQLiteConfig.Pragma.TEMP_STORE, "memory");

      globalConnection = DriverManager.getConnection("jdbc:sqlite:file::memory:?cache=shared",
          config.toProperties());
      try (
          Connection connection = DriverManager.getConnection(
          "jdbc:sqlite:file::memory:?cache=shared", config.toProperties());
          Statement statement = connection.createStatement()
      ) {
        statement.executeUpdate("restore from /Users/swastikn/Desktop/products.db");
        ResultSet resultSet =
            statement.executeQuery("SELECT COUNT(*) AS count FROM products_1");
        log.info("Successfully loaded {} entries into Memory", resultSet.getInt("count"));
        resultSet.close();
        return "SUCCESS";
      }

    } catch (Exception e) {
      log.error("Error while building cache: {}", e.getMessage());
    }
    return "ERROR";
  }

}

Output

2022-07-09 13:23:10.741  INFO 2591 --- [nio-8081-exec-1] com.example.sqlite.service.CacheService  : Successfully loaded 584524 entries into Memory

Non-Heap (Native Memory) Usage

Answer 1

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.