Skip to content

Latest commit

 

History

History
203 lines (158 loc) · 8.14 KB

USAGE.md

File metadata and controls

203 lines (158 loc) · 8.14 KB
Raw

How to Specify Database Files

Here is an example to establishing a connection to a database file C:\work\mydatabase.db (in Windows)

try (Connection connection = DriverManager.getConnection("jdbc:sqlite:C:/work/mydatabase.db")) { /*...*/ }

Opening a UNIX (Linux, maxOS, etc.) file /home/leo/work/mydatabase.db

try (Connection connection = DriverManager.getConnection("jdbc:sqlite:/home/leo/work/mydatabase.db")) { /*...*/ }

How to Use Memory or Temporary Databases

SQLite supports in-memory databases, which do not create any database files. To use a memory database in your Java code, get the database connection as follows:

try (Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:")) { /*...*/ }

You can create temporary database as follows:

try (Connection connection = DriverManager.getConnection("jdbc:sqlite:")) { /*...*/ }

How to use Online Backup and Restore Feature

Take a backup of the whole database to backup.db file:

try (
    // Create a memory database
    Connection conn = DriverManager.getConnection("jdbc:sqlite:");
    Statement stmt = conn.createStatement();
) {
    // Do some updates
    stmt.executeUpdate("create table sample(id, name)");
    stmt.executeUpdate("insert into sample values(1, \"leo\")");
    stmt.executeUpdate("insert into sample values(2, \"yui\")");
    // Dump the database contents to a file
    stmt.executeUpdate("backup to backup.db");
}

Restore the database from a backup file:

try (
    // Create a memory database
    Connection conn = DriverManager.getConnection("jdbc:sqlite:");
    // Restore the database from a backup file
    Statement stat = conn.createStatement();
) {
    stat.executeUpdate("restore from backup.db");
}

Creating BLOB data

  1. Create a table with a column of blob type: create table T (id integer, data blob)
  2. Create a prepared statement with ? symbol: insert into T values(1, ?)
  3. Prepare a blob data in byte array (e.g., byte[] data = ...)
  4. preparedStatement.setBytes(1, data)
  5. preparedStatement.execute()...

Reading Database Files in classpaths or network (read-only)

To load db files that can be found from the class loader (e.g., db files inside a jar file in the classpath), use jdbc:sqlite::resource: prefix.

For example, here is an example to access an SQLite DB file, sample.db in a Java package org.yourdomain:

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::resource:org/yourdomain/sample.db")) { /*...*/ }

In addition, external DB resources can be used as follows:

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::resource:http://www.xerial.org/svn/project/XerialJ/trunk/sqlite-jdbc/src/test/java/org/sqlite/sample.db")) { /*...*/ }

To access db files inside some specific jar file (in local or remote), use the JAR URL:

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::resource:jar:http://www.xerial.org/svn/project/XerialJ/trunk/sqlite-jdbc/src/test/resources/testdb.jar!/sample.db")) { /*...*/ }

DB files will be extracted to a temporary folder specified in System.getProperty("java.io.tmpdir").

Configure directory to extract native library

sqlite-jdbc extracts a native library for your OS to the directory specified by java.io.tmpdir JVM property. To use another directory, set org.sqlite.tmpdir JVM property to your favorite path.

How to use a specific native library

You can use a specific version of the native library by setting the following JVM properties:

-Dorg.sqlite.lib.path=/path/to/folder
-Dorg.sqlite.lib.name=your-custom.dll

Override detected architecture

If the detected architecture is incorrect for your system, thus loading the wrong native library, you can override the value setting the following JVM property:

-Dorg.sqlite.osinfo.architecture=arm

Configure Connections

SQLiteConfig config = new SQLiteConfig();
// config.setReadOnly(true);   
config.setSharedCache(true);
config.recursiveTriggers(true);
// ... other configuration can be set via SQLiteConfig object
try (Connection conn = DriverManager.getConnection("jdbc:sqlite:sample.db", config.toProperties())) { /*...*/ }

How to Use Encrypted Databases

Important: xerial/sqlite-jdbc does not support encryption out of the box, you need a special .dll/.so

SQLite support encryption of the database via special drivers and a key. To use an encrypted database you need a driver which supports encrypted database via pragma key or pragma hexkey, e.g. SQLite SSE or SQLCipher. You need to specify those drivers via directly referencing the .dll/.so through:

-Dorg.sqlite.lib.path=.
-Dorg.sqlite.lib.name=sqlite_cryption_support.dll

Now the only need to specify the password is via:

try (Connection connection = DriverManager.getConnection("jdbc:sqlite:db.sqlite", "", "password")) { /*...*/ }

Binary Passphrase

If you need to provide the password in binary form, you have to specify how the provided .dll/.so needs it. There are two different modes available:

SSE

The binary password is provided via pragma hexkey='AE...'

SQLCipher

The binary password is provided via pragma key="x'AE...'"

You set the mode at the connection string level:

try (Connection connection = DriverManager.getConnection("jdbc:sqlite:db.sqlite?hexkey_mode=sse", "", "AE...")) { /*...*/ }

Generated keys

SQLite has limited support to retrieve generated keys, using last_insert_rowid, with the following limitations:

  • a single ID can be retrieved, even if multiple rows were added or updated
  • it needs to be called right after the statement

By default the driver will eagerly retrieve the generated keys after each statement, which may impact performances.

You can disable the retrieval of generated keys in 3 ways:

  • via SQLiteDataSource#setGetGeneratedKeys(false)
  • via SQLiteConnectionConfig#setGetGeneratedKeys(false):
  • using the pragma jdbc.get_generated_keys:
try (Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:?jdbc.get_generated_keys=false")) { /*...*/ }

Explicit read only transactions (use with Hibernate)

In order for the driver to be compliant with Hibernate, it needs to allow setting the read only flag after a connection has been created.

SQLite has a notion of "auto-upgrading" read-only transactions to read-write transactions. This can cause SQLITE_BUSY exceptions which are difficult to deal with in a JPA/Hibernate/Spring scenario.

For example:

  • open connection
  • query data <--- this uses a read-only transaction in SQLite by default
  • write data <--- this is risky as it promotes the transaction to read-write
  • commit

The approach taken is:

  • open transactions on demand
  • allow setting readOnly only if no statement has been executed yet
  • if readOnly(false) is received, then we quit out of our transaction, and open a new transaction with BEGIN IMMEDIATE. This forces a global lock on the database, preventing SQLITE_BUSY.

You can activate explicit read only support in 2 ways:

  • via SQLiteConfig#setExplicitReadOnly(true):
SQLiteConfig config = new SQLiteConfig();
config.setExplicitReadOnly(true);
  • using the pragma jdbc.explicit_readonly:
try (Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:?jdbc.explicit_readonly=true")) { /*...*/ }

How to use with Android

Android expects JNI native libraries to be bundled differently than a normal Java application.

You will need to extract the native libraries from our jar (from org/sqlite/native/Linux-Android), and place them in the jniLibs directory:

android-studio-screenshot

The name of directories in our jar and in Android Studio differ, here is a mapping table:

Jar directory Android Studio directory
aarch64 arm64-v8a
arm armeabi
x86 x86
x86_64 x86_64