Contoso.SQL.BetterSqlDriver 1.8.3

dotnet add package Contoso.SQL.BetterSqlDriver --version 1.8.3
NuGet\Install-Package Contoso.SQL.BetterSqlDriver -Version 1.8.3
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Contoso.SQL.BetterSqlDriver" Version="1.8.3" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Contoso.SQL.BetterSqlDriver --version 1.8.3
#r "nuget: Contoso.SQL.BetterSqlDriver, 1.8.3"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Contoso.SQL.BetterSqlDriver as a Cake Addin
#addin nuget:?package=Contoso.SQL.BetterSqlDriver&version=1.8.3

// Install Contoso.SQL.BetterSqlDriver as a Cake Tool
#tool nuget:?package=Contoso.SQL.BetterSqlDriver&version=1.8.3

How to Specify Database Files

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

Connection connection = DriverManager.getConnection("jdbc:sqlite:C:/work/mydatabase.db");

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

Connection connection = DriverManager.getConnection("jdbc:sqlite:/home/leo/work/mydatabase.db");

How to Use Memory Databases

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

Connection connection = DriverManager.getConnection("jdbc:sqlite::memory:");

And also, you can create memory database as follows:

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:


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


Connection conn = DriverManager.getConnection("jdbc:sqlite::resource:org/yourdomain/sample.db"); 

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


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:


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

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 connectionstring level:

Connection connection = DriverManager.getConnection("jdbc:sqlite:db.sqlite?hexkey_mode=sse", "", "AE...");
There are no supported framework assets in this package.

Learn more about Target Frameworks and .NET Standard.

This package has no dependencies.

NuGet packages

This package is not used by any NuGet packages.

GitHub repositories

This package is not used by any popular GitHub repositories.

Version Downloads Last updated
1.8.3 564 3/19/2019

Bug fixes and performance improvements