BetterSqlDriver 1.0.1
dotnet add package BetterSqlDriver --version 1.0.1
NuGet\Install-Package BetterSqlDriver -Version 1.0.1
<PackageReference Include="BetterSqlDriver" Version="1.0.1" />
paket add BetterSqlDriver --version 1.0.1
#r "nuget: BetterSqlDriver, 1.0.1"
// Install BetterSqlDriver as a Cake Addin
#addin nuget:?package=BetterSqlDriver&version=1.0.1
// Install BetterSqlDriver as a Cake Tool
#tool nuget:?package=BetterSqlDriver&version=1.0.1
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...");
Product | Versions Compatible and additional computed target framework versions. |
---|---|
.NET | net5.0 was computed. net5.0-windows was computed. net6.0 was computed. net6.0-android was computed. net6.0-ios was computed. net6.0-maccatalyst was computed. net6.0-macos was computed. net6.0-tvos was computed. net6.0-windows was computed. net7.0 was computed. net7.0-android was computed. net7.0-ios was computed. net7.0-maccatalyst was computed. net7.0-macos was computed. net7.0-tvos was computed. net7.0-windows was computed. net8.0 was computed. net8.0-android was computed. net8.0-browser was computed. net8.0-ios was computed. net8.0-maccatalyst was computed. net8.0-macos was computed. net8.0-tvos was computed. net8.0-windows was computed. |
.NET Core | netcoreapp2.0 was computed. netcoreapp2.1 was computed. netcoreapp2.2 was computed. netcoreapp3.0 was computed. netcoreapp3.1 was computed. |
.NET Standard | netstandard2.0 is compatible. netstandard2.1 was computed. |
.NET Framework | net461 was computed. net462 was computed. net463 was computed. net47 was computed. net471 was computed. net472 was computed. net48 was computed. net481 was computed. |
MonoAndroid | monoandroid was computed. |
MonoMac | monomac was computed. |
MonoTouch | monotouch was computed. |
Tizen | tizen40 was computed. tizen60 was computed. |
Xamarin.iOS | xamarinios was computed. |
Xamarin.Mac | xamarinmac was computed. |
Xamarin.TVOS | xamarintvos was computed. |
Xamarin.WatchOS | xamarinwatchos was computed. |
-
.NETStandard 2.0
- 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.