Sqlite Driver Java

This is a Java wrapper including a basic JDBC driver for the SQLite 2/3 database engine. It is designed using JNI to interface to the SQLite API.

This information is obsolete. You are looking at the CVSTrac source management system display for SQLite that was replaced by Fossil on 2009-08-11.

SQLite JDBC driver developed by Taro L. Saito is an extension of Zentus s SQLite JDBC driver

that enables Java to access SQLite database files.

Our SQLiteJDBC library, developed as a part of Xerial project,

requires no configuration since all native libraries for Windows, Mac OS X, Linux

and pure-java SQLite, which works in any OS enviroment, are assembled into a single

JAR Java Archive file. The usage is quite simple; download

our sqlite-jdbc library, then append the library JAR file to your class path.

The original Zentus s SQLite JDBC driver itself

is an excellent utility for using SQLite databases from Java language,

and our SQLiteJDBC library also relies on its implementation. However, its pure-java version,

which totally translates c/c codes of SQLite into Java, is significantly slower

compared to its native version, which uses SQLite binaries compiled for each OS win, mac, linux.

To use the native version of sqlite-jdbc, user had to set a path to the native codes

dll, jnilib, so files, which are JNDI C programs by using command-line arguments,

e.g., -Djava.library.path path to the dll, jnilib, etc., or -Dorg.sqlite.lib.path, etc.

This process was error-prone and bothersome to tell every user to set these variables.

Our SQLiteJDBC library completely does away these inconveniences.

Another difference is that we are keeping this SQLiteJDBC library up-to-date to

the newest version of SQLite engine, because we are one of the hottest users of

this library. For example, SQLite JDBC is a core component of

UTGB University of Tokyo Genome Browser Toolkit, which

is our utility to create personalized genome browsers.

Post bug reports or feature requests to Xerial Public Discussion Group

The usage of SQLite-JDBC driver is the same as the original version.

See for the general usage. For usage of JDBC,

More usage examples are available at https://bitbucket.org/xerial/sqlite-jdbc/wiki/Usage

Download sqlite-jdbc- VERSION. jar from the download page,

then append this jar file into your classpath.

Load the JDBC driver org.sqlite.JDBC from your code. see the example below

Usage Example Assuming sqlite-jdbc- VERSION. jar is placed in the current directory

java -classpath . ;sqlite-jdbc- VERSION. jar Sample in Windows

java -classpath . :sqlite-jdbc- VERSION. jar Sample in Mac or Linux

import java.sql.DriverManager;

public static void main String args throws ClassNotFoundException

// load the sqlite-JDBC driver using the current class loader

Class.forName org.sqlite.JDBC ;

// create a database 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 ;

System.out.println name rs.getString name ;

System.out.println id rs.getInt id ;

// if the error message is out of memory,

// it probably means no database file is found

System.err.println e.getMessage ;

Here is an example to select a file C: work mydatabase.db in Windows

Connection connection DriverManager.getConnection jdbc:sqlite:C:/work/mydatabase.db ;

A UNIX Linux, Mac OS X, etc file /home/leo/work/mydatabase.db

Connection connection DriverManager.getConnection jdbc:sqlite:/home/leo/work/mydatabase.db ;

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: ;

2013 March 22nd: The repositories and documentations were moved to the bitbucket.

2012 December 15th: sqlite-jdbc-3.7.15-SNAPSHOT

2010 August 27th: sqlite-jdbc-3.7.2 released

2010 April 3rd: beta release of sqlite-jdbc-3.6.23.1-SNAPSHOT Added online backup/restore functions. Syntax: backup to file name, restore from file name.

2009 December 10th: sqlite-jdbc-3.6.20.1 release.

Read-only connection, recursive trigger, foreign key validation support etc. using SQLiteConfig class.

SQLiteConfig config new SQLiteConfig ;

config.recursiveTriggers true ;

// other configuration can be set via SQLiteConfig object

Connection conn DriverManager.getConnection jdbc:sqlite:sample.db, config.toProperties ;

2009 November 12th: sqlite-jdbc-3.6.19 released.

added 64-bit OS support: 64-bit native SQLite binaries for Windows x86_64, Mac x86_64 and Linux adm64 are available.

2009 August 19th: sqlite-jdbc-3.6.17.1 released.

2009 July 2nd: sqlite-jdbc-3.6.16 release.

2009 June 4th: sqlite-jdbc-3.6.14.2 released.

2009 May 19th: sqlite-jdbc-3.6.14.1 released.

2009 February 18th: sqlite-jdbc-3.6.11 released.

Fixed a bug in PrepStmt, which does not clear the batch contents after executeBatch.

2009 January 19th: sqlite-jdbc-3.6.10 released. This version is compatible with

Added READ_UNCOMMITTED mode support for better query performance: see also

// READ_UNCOMMITTED mode works only in shared_cache mode.

Properties prop new Properties ;

prop.setProperty shared_cache, true ;

Connection conn DriverManager.getConnection jdbc:sqlite:, prop ;

conn.setTransactionIsolation Conn.TRANSACTION_READ_UNCOMMITTED ;

2008 December 17th: sqlite-jdbc-3.6.7 released.

2008 December 1st: sqlite-jdbc-3.6.6.2 released,

2008 November 20th: sqlite-jdbc-3.6.6 release.

2008 November 11th: sqlite-jdbc-3.6.4.1. A bug fix release Pure-java version didn t work correctly. Fixed in both 3.6.4.1 and 3.6.4.

If you have already downloaded 3.6.4, please obtain the latest one on the download page.

2008 October 16th: sqlite-jdbc-3.6.4 released.

2008 October 14th: sqlite-jdbc-3.6.3 released. Compatible with SQLite 3.6.3.

2008 September 18th: sqlite-jdbc-3.6.2 released. Compatible with SQLite 3.6.2

and contains pure-java and native versions.

2008 July 17th: sqlite-jdbc-3.6.0 released. Compatible with SQLite 3.6.0, and

includes both pure-java and native versions.

2008 July 3rd: sqlite-jdbc-3.5.9-universal

This version contains both native and pure-java SQLite libraries, so it probably works in any OS environment.

2008 May 29th: Current development revision sqlite-jdbc-3.5.9-1 can be compiled

with JDK 6. No need to use JDK 1.5 for compiling SQLiteJDBC.

2008 May 20th: sqlite-jdbc-3.5.9 released.

2008 May 20th: sqlite-jdbc-3.5.8 released corresponding to SQLite 3.5.8 and

sqlite-jdbc-v047. From this release, Windows, Mac OS X, Linux i386, amd64

and Solaris SunOS, sparcv9 libraries are bundled into one jar file.

2008 May 1st: sqlite-jdbc is now in the maven central repository.

How to use SQLiteJDBC with Maven2

2008 Mar. 18th: sqlite-jdbc-3.5.7 released.

This version corresponds to SQLite 3.5.7.

2008 Mar. 10th: sqlite-jdbc-v042 released.

Corresponding to SQLite 3.5.6, which integrates FTS3 full text search.

2008 Jan. 31st: sqlite-jdbc-v038.4 released. SQLiteJDBCLoder.initialize is no longer requried.

2008 Jan. 11th: The Jar files for Windows, Mac OS X and Linux are packed into

a single Jar file. So, no longer need to use an OS-specific jar file.

2007 Dec. 31th: Upgraded to sqlitejdbc-v038

Download the latest version of SQLiteJDBC from the downloads page.

The early releases beta of sqlite-jdbc with some advanced features are available

The old releases are still available from here, but the site might be closed in future.

Since sqlite-jdbc-3.6.19, the natively compiled SQLite engines will be used for

the following operating systems:

Windows XP, Vista Windows, x86 architecture, x86_64

Mac OS X 10.4 Tiger, 10.5 Leopard, 10.6 SnowLeopard for i386, x86_64, Intel CPU machines

Linux i386 Intel, amd64 64-bit X86 Intel processor

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.

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, Mac OS and Linux. An appropriate native library

file is automatically extracted into your OS s temporary folder, when your program

DUMPBIN /DEPENDENTS sqlitejdbc.dll

Mac OS X 10.4.10 Tiger 10.5 Leopard

build/Darwin-i386/libsqlitejdbc.jnilib compatibility version 0.0.0, current version 0.0.0

/usr/lib/libSystem.B.dylib compatibility version 1.0.0, current version 88.3.9

libc.so.6 /lib/i686/nosegneg/libc.so.6 0x002dd000

This program follows the Apache License version 2.0 That means:

freely download and use this software, in whole or in part, for personal, company internal, or commercial purposes;

use this software in packages or distributions that you create.

redistribute any piece of our originated software without proper attribution;

use any marks owned by us in any way that might state or imply that we xerial.org endorse your distribution;

use any marks owned by us in any way that might state or imply that you created this software in question.

include a copy of the license in any redistribution you may make that includes this software;

provide clear attribution to us, xerial.org for any distributions that include this software

include the source of this software itself, or of any modifications you may have

made to it, in any redistribution you may assemble that includes it;

submit changes that you make to the software back to this software though such feedback is encouraged.

See License FAQ for more details.

If you are familiar with Maven2, add the following XML

fragments into your pom.xml file. With those settings, your Maven will automatically download our SQLiteJDBC library into your local Maven repository, since our sqlite-jdbc libraries are synchronized with the Maven s central repository.

Do not include sqlite-jdbc- version. jar in WEB-INF/lib folder of your web application

package, since multiple web applications hosted by the same Tomcat server cannot

load the sqlite-jdbc native library more than once. That is the specification of

JNI Java Native Interface. You will observe UnsatisfiedLinkError exception with

the message no SQLite library found.

Work-around of this problem is to put sqlite-jdbc- version. jar file into TOMCAT_HOME /lib

direcotry, in which multiple web applications can share the same native library

file . dll. jnilib. so extracted from this sqlite-jdbc jar file.

If you are using Maven for your web application, set the dependency scope as provided,

and manually put the SQLite JDBC jar file into TOMCAT_HOME /lib folder.

I am using one simple code to access the SQLite database from Java application. My code is import java.sql.Connection; import java.sql.DriverManager; import java.

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed.

SQLite Java Tutorial

SQLite JDBC driver News. 2014 October 8th: sqlite-jdbc-3.8.6.jar Updated to sqlite 3.8.6; 2014 August 11th: The source code repository is moved to https://github.

Installation. Before we start using SQLite in our Java programs, we need to make sure that we have SQLite JDBC Driver and Java set up on the machine.

Before we start using SQLite in our Java programs, we need to make sure that we have SQLite JDBC Driver and Java set up on the machine. You can check Java tutorial for Java installation on your machine. Now, let us check how to set up SQLite JDBC driver.

Download latest version of sqlite-jdbc- VERSION. jar from sqlite-jdbc repository.

Add downloaded jar file sqlite-jdbc- VERSION. jar in your class path, or you can use it along with -classpath option as explained below in examples.

Following section assumes you have little knowledge about Java JDBC concepts. If you don t, then it is suggested to spent half an hour with JDBC Tutorial to become comfortable with concepts explained below.

Following Java programs shows how to connect to an existing database. If database does not exist, then it will be created and finally a database object will be returned.

public static void main String args

Class.forName org.sqlite.JDBC ;

c DriverManager.getConnection jdbc:sqlite:test.db ;

System.err.println e.getClass. getName : e.getMessage ;

System.out.println Opened database successfully ;

Now, let s compile and run above program to create our database test.db in the current directory. You can change your path as per your requirement. We are assuming current version of JDBC driver sqlite-jdbc-3.7.2.jar is available in the current path

java -classpath . :sqlite-jdbc-3.7.2.jar SQLiteJDBC

If you are going to use Windows machine, then you can compile and run your code as follows:

java -classpath . ;sqlite-jdbc-3.7.2.jar SQLiteJDBC

Following Java program will be used to create a table in previously created database:

String sql CREATE TABLE COMPANY

System.out.println Table created successfully ;

When above program is compiled and executed, it will create COMPANY table in your test.db and final listing of the file will be as follows:

-rw-r--r--. 1 root root 3201128 Jan 22 sqlite-jdbc-3.7.2.jar

-rw-r--r--. 1 root root 1506 May 8 SQLiteJDBC.class

-rw-r--r--. 1 root root 832 May 8 SQLiteJDBC.java

-rw-r--r--. 1 root root 3072 May 8 test.db

Following Java program shows how we can create records in our COMPANY table created in above example:

String sql INSERT INTO COMPANY ID,NAME,AGE,ADDRESS,SALARY

VALUES 1, Paul, 32, California, 20000.00 ; ;

sql INSERT INTO COMPANY ID,NAME,AGE,ADDRESS,SALARY

VALUES 2, Allen, 25, Texas, 15000.00 ; ;

VALUES 3, Teddy, 23, Norway, 20000.00 ; ;

VALUES 4, Mark, 25, Rich-Mond , 65000.00 ; ;

System.out.println Records created successfully ;

When above program is compiled and executed, it will create given records in COMPANY table and will display following two line:

Following Java program shows how we can fetch and display records from our COMPANY table created in above example:

ResultSet rs stmt.executeQuery SELECT FROM COMPANY; ;

String name rs.getString name ;

String address rs.getString address ;

float salary rs.getFloat salary ;

System.out.println NAME name ;

System.out.println ADDRESS address ;

System.out.println SALARY salary ;

System.out.println Operation done successfully ;

When above program is compiled and executed, it will produce the following result:

Following Java code shows how we can use UPDATE statement to update any record and then fetch and display updated records from our COMPANY table:

String sql UPDATE COMPANY set SALARY 25000.00 where ID 1; ;

Following Java code shows how we can use DELETE statement to delete any record and then fetch and display remaining records from our COMPANY table:

String sql DELETE from COMPANY where ID 2; ;