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 ?
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");
(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";
}
}
2022-07-09 13:23:10.741 INFO 2591 --- [nio-8081-exec-1] com.example.sqlite.service.CacheService : Successfully loaded 584524 entries into Memory
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.
pom.xml
)<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.36.0.3</version>
<scope>runtime</scope>
</dependency>
Program
classFor the first run, please, apply the following changes:
forceSeed()
method call.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");
}
}
}
Person: ID: 1, Name: First.
Person: ID: 2, Name: Second.
Person: ID: 3, Name: Third.