javasqlitejdbcsqlitejdbc

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


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 enter image description here


Solution

  • 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:

    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.