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
<PackageReference Include="Contoso.SQL.BetterSqlDriver" Version="1.8.3" />
paket add Contoso.SQL.BetterSqlDriver --version 1.8.3
#r "nuget: Contoso.SQL.BetterSqlDriver, 1.8.3"
// 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
- Create a table with a column of blob type:
create table T (id integer, data blob)
- Create a prepared statement with
?
symbol:insert into T values(1, ?)
- Prepare a blob data in byte array (e.g.,
byte[] data = ...
) preparedStatement.setBytes(1, data)
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...");
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