sqlite-jdbc-ffm

The goal of this fork of sqlite-jdbc is to keep the same JDBC interface and much of (see below) the same Java interface as the original library but remove the set of bundled native SQLite libraries and JNI bindings, instead using the JDK 22+ Foreign Function & Memory API to communicate with an external SQLite native library.

Benefits

• Each version of sqlite-jdbc-ffm supports many versions of SQLite, including future updates

• Much smaller JAR size (<0.3 MB vs >14 MB)

• No temporary files extracted at runtime

• May be measurably faster in some cases

Drawbacks

• Must locate or supply your own SQLite shared library file (libsqlite3.so/sqlite3.dll/libsqlite3.dylib)

  • This may make cross-platform distribution more trouble than it is worth

• No GraalVM Native Image support

• May be measurably slower in some cases

Requirements

• JDK 22+

• SQLite 3.7.13+

  • 3.5.0 (2007-09-04) has not been tested, but likely works to some extent (if you can find a binary for your architecture).

  • 3.7.13 (2012-06-11) is the earliest version that has been tested. Many features do not work, but it is possible to read from and write to databases.

  • 3.26.0+ (2018-12-01) is strongly recommended.

  • 3.37.0+ (2021-11-27) is recommended for full feature support.

Note	These are SQLite library versions, not database versions. An SQLite library should always be able to read a database modified by any earlier library, but may not always be able to read databases modified by newer library versions.

Usage

Maven pom.xml:

<dependency>
    <groupId>net.nnnes</groupId>
    <artifactId>sqlite-jdbc-ffm</artifactId>
    <version>0.1.0</version>
</dependency>

Gradle build.gradle.kts:

implementation("net.nnnes:sqlite-jdbc-ffm:0.1.0")

JDBC

See sqlite-jdbc for code samples. The JDBC API should be identical.

SQLite library file

Ready-to-use SQLite shared libraries for Windows (DLLs) are available from the official SQLite website. Other platforms' package managers might have sqlite3-dev or similar available, or you may want to compile from source yourself.

Example compile of libsqlite3.so on Linux (replace .so with .dylib for macOS)

Requirements: GCC/Clang, Make

1. Download sqlite-autoconf-X.tar.gz from the official SQLite website

2. Extract and cd

3. ./configure && make libsqlite3.so

Note	The sqlitejdbc.* library files built by and distributed with sqlite-jdbc currently cannot be used by sqlite-jdbc-ffm. They contain JNI code that looks for variables and methods whose types are different in sqlite-jdbc-ffm. This may change in the future.

Loading SQLite

sqlite-jdbc-ffm uses SymbolLookup#libraryLookup(System.mapLibraryName("sqlite3"), …​) internally to load the SQLite native library. Use the JVM argument

-Djava.library.path="/example/path/to/folder containing sqlite/"

to tell it where to look. If you do not do this, Java will search the system PATH for a match. Beware that incompatible versions may exist in unexpected places in your PATH!

Note	The SQLite library file must be named libsqlite3.so (Linux), sqlite3.dll (Windows), or libsqlite3.dylib (macOS). This should change in the future.

You should enable native access for module net.nnnes.sqlitejdbcffm (or for UNNAMED-MODULE if not using JPMS). Future JDK releases will not allow you to use sqlite-jdbc-ffm (or sqlite-jdbc) without doing so.

API differences from sqlite-jdbc

• The JDBC API is identical.

Changed

• All uses of long for raw memory addresses, mostly involving org.sqlite.core.SafeStmtPtr, have been changed to instead use MemorySegment.

  • This includes 2 package-private fields in org.sqlite.Function and a large number of fields and methods in org.sqlite.core.* classes.

Removed

• Math and string extension functions

  • The SQLite library bundled with sqlite-jdbc includes some extension functions. Note that many of the math functions are present in SQLite 3.35.0+ if it has been compiled with -DSQLITE_ENABLE_MATH_FUNCTIONS.

• org.sqlite.SQLiteJDBCLoader; many classes in package org.sqlite.util

  • These classes were mostly involved with loading native libraries.

• org.sqlite.date.* (Apache FastDateFormat)

  • The copy of Apache FastDateFormat in org.sqlite.date has been deleted in favor of java.time.format.DateTimeFormatter, which is more rigid when parsing some date strings.

  • Use org.sqlite.SQLiteConnectionConfig#setDateStringFormat(String) as needed.

Other notes

• sqlite-jdbc-ffm passes all relevant tests included with sqlite-jdbc on Linux x64 and Windows x64. Some native functions have no test coverage yet.

• Some public methods have been added to various org.sqlite.* classes to avoid use of reflection that a direct rewrite of the JNI C code would have required. These have been prefixed with _ and should not be considered stable.

• Future work will aim to improve performance and SQLite compatibility and reduce library size while maintaining the same level of Java API parity with sqlite-jdbc.

• Questions, bug reports, feature requests, pull requests, test results from more platforms, and performance comparisons are welcome!

---

The original README from sqlite-jdbc appears unedited below. Some parts will not apply to sqlite-jdbc-ffm.

---

SQLite JDBC Driver

SQLite JDBC is a library for accessing and creating SQLite database files in Java.

Our SQLiteJDBC library requires no configuration since native libraries for major OSs, including Windows, macOS, Linux etc., are assembled into a single JAR (Java Archive) file.

Project Status

The project is maintained, but is not being actively developed:

• We follow every new version of SQLite and will release a corresponding version of our driver.

• Bugs will be investigated, and fixed if possible.

• New features are driven by pull requests.

The current maintainer of the project is gotson.

Usage

➡️ More usage examples and configuration are available in USAGE.md

SQLite JDBC is a library for accessing SQLite databases through the JDBC API. For the general usage of JDBC, see JDBC Tutorial or Oracle JDBC Documentation.

1. Download sqlite-jdbc-3.50.3.0.jar then append this jar file into your classpath.

2. Open a SQLite database connection from your code. (see the example below)

Example usage

Assuming sqlite-jdbc-3.50.3.0.jar is placed in the current directory.

> javac Sample.java
> java -classpath ".;sqlite-jdbc-3.50.3.0.jar" Sample   # in Windows
or
> java -classpath ".:sqlite-jdbc-3.50.3.0.jar" Sample   # in macOS or Linux
name = leo
id = 1
name = yui
id = 2

Sample.java

    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;

    public class Sample
    {
      public static void main(String[] args)
      {
        // NOTE: Connection and Statement are AutoCloseable.
        //       Don't forget to close them both in order to avoid leaks.
        try
        (
          // create a database connection
          Connection connection = DriverManager.getConnection("jdbc:sqlite:sample.db");
          Statement statement = connection.createStatement();
        )
        {
          statement.setQueryTimeout(30);  // set timeout to 30 sec.

          statement.executeUpdate("drop table if exists person");
          statement.executeUpdate("create table person (id integer, name string)");
          statement.executeUpdate("insert into person values(1, 'leo')");
          statement.executeUpdate("insert into person values(2, 'yui')");
          ResultSet rs = statement.executeQuery("select * from person");
          while(rs.next())
          {
            // read the result set
            System.out.println("name = " + rs.getString("name"));
            System.out.println("id = " + rs.getInt("id"));
          }
        }
        catch(SQLException e)
        {
          // if the error message is "out of memory",
          // it probably means no database file is found
          e.printStackTrace(System.err);
        }
      }
    }

How does SQLiteJDBC work?

Our SQLite JDBC driver package (i.e., sqlite-jdbc-3.50.3.0.jar) contains three types of native SQLite libraries (sqlitejdbc.dll, sqlitejdbc.dylib, sqlitejdbc.so), each of them is compiled for Windows, macOS and Linux. An appropriate native library file is automatically extracted into your OS’s temporary folder, when your program loads org.sqlite.JDBC driver.

Supported Operating Systems

Since sqlite-jdbc-3.6.19, the natively compiled SQLite engines will be used for the following operating systems:

	x86	x86_64	armv5	armv6	armv7	arm64	ppc64	riscv64
Windows	✔	✔			✔	✔
macOS		✔				✔
Linux (libc)	✔	✔	✔	✔	✔	✔	✔	✔
Linux (musl)	✔	✔				✔
Android (API Level 24+)	✔	✔	✔			✔
FreeBSD	✔	✔				✔

In the other OSs not listed above, the pure-java SQLite is used. (Applies to versions before 3.7.15)

If you want to use the native library for your OS, build the source from scratch.

GraalVM native-image support

Sqlite JDBC supports GraalVM native-image out of the box starting from version 3.40.1.0. There has been rudimentary support for some versions before that, but this was not actively tested by the CI.

By default, the sqlitejdbc library for the compilation target will be included in the native image, accompanied by the required JNI configuration. At runtime, this library will be extracted to the temp folder and loaded from there. For faster startup however, it is recommended to set the org.sqlite.lib.exportPath property at build-time. This will export the sqlitejdbc library at build-time to the specified directory, and the library will not be included as a resource. As a result, the native image itself will be slightly smaller and the overhead of exporting the library at run-time is eliminated, but you need to make sure the library can be found at run-time. The best way to do this is to simply place the library next to the executable.

CLI example

native-image -Dorg.sqlite.lib.exportPath=~/outDir -H:Path=~/outDir -cp foo.jar org.example.Main

This will place both the sqlitejdbc shared library and the native-image output in the ~/outDir folder.

Maven example

This example uses the native-build-tools maven plugin:

<plugin>
    <groupId>org.graalvm.buildtools</groupId>
    <artifactId>native-maven-plugin</artifactId>
    <configuration>
        <buildArgs>
            <buildArg>-Dorg.sqlite.lib.exportPath=${project.build.directory}</buildArg>
        </buildArgs>
    </configuration>
</plugin>

This will automatically place the sqlitejdbc library in the /target folder of your project, creating a functional execution environment. When packaging the resulting app, simply include the library in the distribution bundle.

Download

Download from Maven Central or from the releases page.

<dependencies>
    <dependency>
      <groupId>org.xerial</groupId>
      <artifactId>sqlite-jdbc</artifactId>
      <version>3.50.3.0</version>
    </dependency>
</dependencies>

Snapshots of the development version are available in Sonatype’s snapshots repository.

Validating downloads

Maven Central resources are signed using GPG and the signature files, ending in .asc, are available in the same location as the other downloads.

The following key is currently used to sign releases:

-----BEGIN PGP PUBLIC KEY BLOCK-----
Comment: C1CB A75E C9BD 0BAF 8061  9354 59E0 5CE6 1818 7ED4
Comment: Taro L. Saito (For GitHub Actions) <[email protected]>

xjMEYuRVGhYJKwYBBAHaRw8BAQdA2Dp4m1Yhtb1g94pQzzL24FuP6b9KXF8lP9Dh
hZnynhfNM1Rhcm8gTC4gU2FpdG8gKEZvciBHaXRIdWIgQWN0aW9ucykgPGxlb0B4
ZXJpYWwub3JnPsKUBBMWCgA8FiEEwcunXsm9C6+AYZNUWeBc5hgYftQFAmLkVRoC
GwMFCwkIBwIDIgIBBhUKCQgLAgQWAgMBAh4HAheAAAoJEFngXOYYGH7UfPwBAK7x
TVRebZeWcAwmGaMUsbg7SgJou8xnkhByObPLUC/4AQDPsZeYmi4KXyXPzmqhCicd
Y+ZSJWIDQqitK2ujPDFXA844BGLkVRoSCisGAQQBl1UBBQEBB0Atu9kejBi+6wfO
T0a9z/LYEEdNXM/VX6xt1onKToPPdQMBCAfCeAQYFgoAIBYhBMHLp17JvQuvgGGT
VFngXOYYGH7UBQJi5FUaAhsMAAoJEFngXOYYGH7UlMABAKyRCazhVyUFg5FOpAnm
ckBY38CaMGPPLXVyY8Kr6dYFAP9wYLu7nsDZCOXkAgS+et4Pk1WZCggoYUkxsX1o
0KZXBQ==
=Wyas
-----END PGP PUBLIC KEY BLOCK-----

Project versioning explained

The project’s version follows the version of the SQLite library that is bundled in the jar, with an extra digit to denote the project’s increment.

For example, if the SQLite version is 3.39.2, the project version will be 3.39.2.x, where x starts at 0, and increments with every release that is not changing the SQLite version.

If the SQLite version is updated to 3.40.0, the project version will be updated to 3.40.0.0.

Hint for maven-shade-plugin

You may need to add shade plugin transformer to solve No suitable driver found for jdbc:sqlite: issue.

<transformer
	implementation="org.apache.maven.plugins.shade.resource.AppendingTransformer">
	<resource>META-INF/services/java.sql.Driver</resource>
</transformer>

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.50.3.0</version>
</dependency>

How can I help?

We are always looking for:

• Reviewers for issues or PRs, you can check

• Contributors to submit PRs, you can check and

Please read our contribution guide.