sqlite-jdbc

SQLite JDBC Driver

2,788

611

2,788

View on GitHub

Top Related Projects

mssql-jdbc

1,049

The Microsoft JDBC Driver for SQL Server is a Type 4 JDBC driver that provides database connectivity with SQL Server through the standard JDBC application program interfaces (APIs).

HikariCP

19,837

光 HikariCP・A solid, high-performance, JDBC connection pool at last.

h2database

4,169

H2 is an embeddable RDBMS written in Java.

Quick Overview

SQLite JDBC is a Java library that provides a JDBC driver for SQLite databases. It allows Java applications to interact with SQLite databases using standard JDBC API calls, making it easy to integrate SQLite into Java projects.

Pros

Lightweight and self-contained, requiring no external dependencies
Cross-platform compatibility, working on various operating systems
Supports both file-based and in-memory SQLite databases
Regular updates and active maintenance

Cons

Limited concurrent write operations due to SQLite's nature
Not suitable for high-volume, multi-user applications
Lacks some advanced features found in larger database systems
Performance may be slower compared to server-based databases for complex queries

Code Examples

Connecting to a SQLite database:

import java.sql.Connection;
import java.sql.DriverManager;

String url = "jdbc:sqlite:database.db";
Connection conn = DriverManager.getConnection(url);

Executing a simple query:

import java.sql.Statement;
import java.sql.ResultSet;

String query = "SELECT * FROM users";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);

while (rs.next()) {
    System.out.println(rs.getString("name"));
}

Inserting data using prepared statements:

import java.sql.PreparedStatement;

String insert = "INSERT INTO users (name, age) VALUES (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(insert);
pstmt.setString(1, "John Doe");
pstmt.setInt(2, 30);
pstmt.executeUpdate();

Getting Started

Add the SQLite JDBC dependency to your project:

For Maven:

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

For Gradle:

implementation 'org.xerial:sqlite-jdbc:3.36.0.3'

Use the JDBC driver in your Java code:

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

public class SQLiteExample {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:sample.db";
        
        try (Connection conn = DriverManager.getConnection(url)) {
            System.out.println("Connection to SQLite has been established.");
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }
}

This code establishes a connection to a SQLite database named "sample.db" in the current directory. If the database doesn't exist, it will be created automatically.

Competitor Comparisons

mssql-jdbc

1,049

The Microsoft JDBC Driver for SQL Server is a Type 4 JDBC driver that provides database connectivity with SQL Server through the standard JDBC application program interfaces (APIs).

Pros of mssql-jdbc

Supports enterprise-level features like distributed transactions and Always Encrypted
Offers better performance for large-scale applications and complex queries
Provides more advanced security features, including Azure Active Directory authentication

Cons of mssql-jdbc

Requires a more complex setup and configuration compared to sqlite-jdbc
Limited to Microsoft SQL Server databases, while sqlite-jdbc works with SQLite databases
Generally has a steeper learning curve for developers new to enterprise databases

Code Comparison

mssql-jdbc connection example:

String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=AdventureWorks;user=MyUserName;password=*****;";
try (Connection connection = DriverManager.getConnection(connectionUrl)) {
    // Use the connection
}

sqlite-jdbc connection example:

String url = "jdbc:sqlite:sample.db";
try (Connection connection = DriverManager.getConnection(url)) {
    // Use the connection
}

Both libraries use similar JDBC API calls, but mssql-jdbc typically requires more configuration parameters for connection strings and supports more advanced features in its implementation.

pgjdbc

1,482

Postgresql JDBC Driver

Pros of pgjdbc

Supports advanced PostgreSQL-specific features and data types
Better performance for large-scale applications and complex queries
More frequent updates and active community support

Cons of pgjdbc

Larger footprint and more complex setup compared to sqlite-jdbc
Limited to PostgreSQL databases, lacking sqlite-jdbc's portability

Code Comparison

pgjdbc:

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost/test", "user", "password");
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO users(name, email) VALUES(?, ?)");
pstmt.setString(1, "John Doe");
pstmt.setString(2, "john@example.com");
pstmt.executeUpdate();

sqlite-jdbc:

Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO users(name, email) VALUES(?, ?)");
pstmt.setString(1, "John Doe");
pstmt.setString(2, "john@example.com");
pstmt.executeUpdate();

The code snippets demonstrate that both drivers use similar JDBC syntax for basic operations. However, pgjdbc offers additional PostgreSQL-specific features and optimizations not shown in this basic example. sqlite-jdbc, on the other hand, provides a simpler connection string and is more suitable for lightweight, embedded database applications.

HikariCP

19,837

光 HikariCP・A solid, high-performance, JDBC connection pool at last.

Pros of HikariCP

Significantly faster connection pooling performance
Advanced pool optimization features like connection leak detection
Extensive configuration options for fine-tuning

Cons of HikariCP

More complex setup compared to SQLite JDBC's simplicity
Requires additional configuration for optimal performance
Not specifically designed for SQLite, unlike SQLite JDBC

Code Comparison

HikariCP configuration:

HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/mydb");
config.setUsername("user");
config.setPassword("password");
HikariDataSource dataSource = new HikariDataSource(config);

SQLite JDBC usage:

Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");

Summary

HikariCP is a high-performance JDBC connection pool, while SQLite JDBC is a driver for SQLite databases. HikariCP offers superior performance and advanced features for connection pooling, making it ideal for applications with high concurrency. However, it requires more setup and configuration. SQLite JDBC, on the other hand, provides a simpler solution specifically tailored for SQLite databases, with easier integration but fewer advanced features.

h2database

4,169

H2 is an embeddable RDBMS written in Java.

Pros of H2 Database

Supports both in-memory and disk-based databases, offering more flexibility
Provides a web-based console for database management and querying
Offers advanced features like full-text search and encryption

Cons of H2 Database

Larger footprint and more complex setup compared to SQLite
May have slower performance for simple, single-user applications
Less widespread adoption and community support than SQLite

Code Comparison

H2 Database connection:

String url = "jdbc:h2:~/test";
Connection conn = DriverManager.getConnection(url, "sa", "");

SQLite JDBC connection:

String url = "jdbc:sqlite:test.db";
Connection conn = DriverManager.getConnection(url);

Summary

H2 Database offers more features and flexibility, making it suitable for complex applications and development environments. SQLite JDBC is simpler and more lightweight, ideal for embedded databases and single-user applications. The choice between the two depends on specific project requirements, such as performance needs, feature set, and deployment environment.

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot

README

= SQLite JDBC Driver :project-version: 3.46.1.0

image:https://img.shields.io/github/actions/workflow/status/xerial/sqlite-jdbc/ci.yml?branch=master[GitHub Workflow Status (branch),link=https://github.com/xerial/sqlite-jdbc/actions/workflows/ci.yml?query=branch%3Amaster] image:https://badges.gitter.im/xerial/sqlite-jdbc.svg[Join the chat,link=https://gitter.im/xerial/sqlite-jdbc?utm_source=badge&utm_medium=badge&utm_campaign=pr-badge&utm_content=badge] image:https://maven-badges.herokuapp.com/maven-central/org.xerial/sqlite-jdbc/badge.svg[Maven Central,link=https://maven-badges.herokuapp.com/maven-central/org.xerial/sqlite-jdbc/] image:https://javadoc.io/badge2/org.xerial/sqlite-jdbc/javadoc.svg[javadoc,link=https://javadoc.io/doc/org.xerial/sqlite-jdbc] image:https://img.shields.io/nexus/s/org.xerial/sqlite-jdbc?color=blue&label=maven%20snapshot&server=https%3A%2F%2Foss.sonatype.org%2F[Sonatype Nexus (Snapshots),link=https://oss.sonatype.org/content/repositories/snapshots/org/xerial/sqlite-jdbc/]

SQLite JDBC is a library for accessing and creating https://www.sqlite.org[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 https://github.com/gotson[gotson].

== Usage

â¡ï¸ More usage examples and configuration are available in link:USAGE.md[USAGE.md]

SQLite JDBC is a library for accessing SQLite databases through the JDBC API. For the general usage of JDBC, see https://docs.oracle.com/javase/tutorial/jdbc/index.html[JDBC Tutorial] or https://www.oracle.com/technetwork/java/javase/tech/index-jsp-136101.html[Oracle JDBC Documentation].

. <<Download,Download>> sqlite-jdbc-{project-version}.jar then append this jar file into your classpath. . https://search.maven.org/remotecontent?filepath=org/slf4j/slf4j-api/1.7.36/slf4j-api-1.7.36.jar[Download] slf4j-api-1.7.36.jar then append this jar file into your classpath. . Open a SQLite database connection from your code. (see the example below)

=== Example usage

Assuming sqlite-jdbc-{project-version}.jar and slf4j-api-1.7.36.jar are placed in the current directory.

[source,shell,subs="attributes+"]

javac Sample.java java -classpath ".;sqlite-jdbc-{project-version}.jar;slf4j-api-1.7.36.jar" Sample # in Windows or java -classpath ".:sqlite-jdbc-{project-version}.jar:slf4j-api-1.7.36.jar" Sample # in macOS or Linux name = leo id = 1 name = yui id = 2

=== Sample.java

[source,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-{project-version}.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 |â |â |â | | |â | | |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, link:./CONTRIBUTING.md[build the source from scratch].

=== GraalVM native-image support

Sqlite JDBC supports https://www.graalvm.org/native-image/[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

[source,shell]

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 https://graalvm.github.io/native-build-tools/latest/index.html[native-build-tools] maven plugin:

[source,xml]

org.graalvm.buildtools native-maven-plugin -Dorg.sqlite.lib.exportPath=${project.build.directory} ----

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 https://search.maven.org/artifact/org.xerial/sqlite-jdbc[Maven Central] or from the https://github.com/xerial/sqlite-jdbc/releases[releases] page.

[source,xml,subs="attributes+"]

org.xerial sqlite-jdbc {project-version} ----

Snapshots of the development version are available in https://oss.sonatype.org/content/repositories/snapshots/org/xerial/sqlite-jdbc/[Sonatype's snapshots repository].

=== Validating downloads

Maven Central resources are signed using https://gnupg.org/[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) leo@xerial.org

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.

[source,xml]

META-INF/services/java.sql.Driver

[source,xml,subs="attributes+"]

org.xerial sqlite-jdbc {project-version} ----

== How can I help?

We are always looking for:

Reviewers for issues or PRs, you can check image:https://img.shields.io/github/labels/xerial/sqlite-jdbc/review%20wanted[GitHub labels,link=https://github.com/xerial/sqlite-jdbc/labels/review%20wanted]
Contributors to submit PRs, you can check image:https://img.shields.io/github/labels/xerial/sqlite-jdbc/help%20wanted[GitHub labels,link=https://github.com/xerial/sqlite-jdbc/labels/help%20wanted] and image:https://img.shields.io/github/labels/xerial/sqlite-jdbc/good%20first%20issue[GitHub labels,link=https://github.com/xerial/sqlite-jdbc/labels/good%20first%20issue]

Please read our link:./CONTRIBUTING.md[contribution] guide.

Top Related Projects

Convert designs to code with AI

Introducing Visual Copilot: A new AI model to turn Figma designs to high quality code using your components.

Try Visual Copilot