USAGE.md

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 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())) { /*...*/ }

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")) { /*...*/ }