🗄 Database Connection Demo 📂

_{· Execution of Database Connection ·}

📖 Table of contents

1. SQLite JDBC Driver
2. Usage
   • Example usage
3. How does SQLiteJDBC work?
4. Supported Operating Systems
5. Download
6. How to Specify Database Files
7. How to Use Memory Databases
8. How to use Online Backup and Restore Feature
9. Creating BLOB data
10. Reading Database Files in classpaths or network (read-only)

📚 SQLite JDBC Driver

SQLite JDBC is a library for accessing and creating SQLite database files in Java. SQLiteJDBC library requires no configuration since native libraries for major OSs, including Windows, macOS and GNU/Linux distributions, are assembled into a single JAR (Java Archive) file.

⚙️ Usage

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-(VERSION).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-(VERSION).jar or the pom.xml file with the dependency is placed in the current directory.

> javac Sample.java
> java -classpath ".;sqlite-jdbc-(VERSION).jar" Sample    # in Windows
or
> java -classpath ".:sqlite-jdbc-(VERSION).jar" Sample    # in macOS or Linux
Name = Harry
Id = 1
Name = Tom
Id = 2

Sample.java

package sample;

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

public class Sample {

    public static void main(String[] args) {
        Connection connection = null;

        try {
            // Create a database connection
            connection = DriverManager.getConnection("jdbc:sqlite:Database.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, 'Harry')");
            statement.executeUpdate("INSERT INTO PERSON VALUES(2, 'Tom')");

            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
            System.err.println(e.getMessage());
        } finally {
            try {
                if(connection != null) {
                    connection.close();
                }
            } catch(SQLException e) {
                // Connection close failed
                System.err.println(e.getMessage());
            }
        }
    }
}

👨🏻‍💻 How does SQLite JDBC work?

Our SQLite JDBC driver package (i.e., sqlite-jdbc-(VERSION).jar) contains three types of native SQLite libraries (sqlite-jdbc.dll, sqlite-jdbc.jnilib, sqlite-jdbc.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
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)

⬇️ Download

Download from Maven Central or from the releases page.

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

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

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>(version)</version>
</dependency>

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, MacOS, 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, \"Harry\")");
stmt.executeUpdate("INSERT INTO Sample VALUES(2, \"Tom\")");
// 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 database files that can be found from the class loader (e.g., database files inside a JAR file in the classpath), use jdbc:sqlite::resource: prefix.

For example, here is an example to access an SQLite database file, Sample.db in a Java package org.yourdomain:

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

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

Connection connection = DriverManager.getConnection("jdbc:sqlite::resource:http://www.xerial.org/svn/project/XerialJ/trunk/sqlite-jdbc/src/test/java/org/sqlite/sample.db");

To access database files inside some specific JAR file (in local or remote), use the JAR URL:

Connection connection = DriverManager.getConnection("jdbc:sqlite::resource:jar:http://www.xerial.org/svn/project/XerialJ/trunk/sqlite-jdbc/src/test/resources/testdb.jar!/sample.db");

Database files will be extracted to a temporary folder specified in System.getProperty("java.io.tmpdir")

---

Made with ♥️ in Spain