SQLite Java Wrapper/JDBC Driver
2014-06-24

This is a Java wrapper including a basic JDBC driver for the SQLite 2/3 database engine.

See:
          Description

Packages
SQLite  

 

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. That API is wrapped by methods in the SQLite.Database class. Since June, 2002, it includes a small JDBC driver, which allows the most basic things to be carried out using the java.sql package. Since September, 2004, SQLite3 (minimum 3.0.7) is supported. The JNI part can be generated to include support for both SQLite 2.x and 3.x and detects at runtime the type of the database.

The current source tarball can be downloaded from javasqlite-20140624.tar.gz. It has been tested on Linux, using SQLite 2.8.17 and 3.7.15, and JDK 1.4/1.5/1.6/1.7 from Sun. The change log starting from August 2007 is here.

For rebuilding the following requirements must be met:

The source code uses GNU autoconf, thus the build process on UN*X like systems should look like:

   $ ./configure
   ...
   $ make
   ...
   $ su -c "make install"
   ...

To override the directory where configure looks for SQLite and JDK the configure options --with-sqlite=DIR, --with-sqlite3=DIR, and --with-jdk=DIR can be used. To specify the place where the native library (the libsqlite_jni.so file) shall be installed, use the --prefix=DIR option. The default place is /usr/local/lib, i.e. the prefix defaults to /usr/local. To specify where the sqlite.jar file containing the high-level part and the JDBC driver shall be installed, use the --with-jardir=DIR option. The default is /usr/local/share/java. At runtime, it is necessary to tell the JVM both places with the -classpath and -Djava.library.path=.. command line options. Optionally, the native part is searched using the path prefix specified in the -DSQLite.library.path=.. property, which has precedence over -Djava.library.path=...

For Win32 (NT4 or above) the makefiles javasqlite.mak, javasqlite23.mak, and javasqlite3.mak are provided in the source archive. These makefiles contain some build instructions and use the J2SE 1.4.2 from Sun and MS Visual C++ 6.0.

A DLL with the native JNI part (including SQLite 3.7.7.1) and the JAR file with the Java part can be downloaded from javasqlite-20110827-win32.zip.

After successful build and installation a small test program can be run by invoking the following commands (on OJEC replace the java command by cvm):

   $ make test
   ...
   $ java -classpath ... -Djava.library.path=... test
   version: 2.8.17
   ==== local callback ====
   #cols = 5
   col0: type
   col1: name
   ...

For testing the newer features of SQLite3 (parameter binding with the help of the SQLite.Stmt class and incremental blob I/O through SQLite.Blob class) refer to another test program.

A simple shell modelled after that of SQLite can be invoked as (on OJEC replace the java command by cvm):

   $ java SQLite.Shell [options] database [sql string]
or using the JAR file
   $ java -jar sqlite.jar [options] database [sql string]

The native part of the Java SQLite wrapper takes the compile-time character encoding of the SQLite engine into account, and tries to map the Java unicode string representation into the system encoding and vice versa when SQLite has not been built with UTF-8 support. This can be overriden programatically or by specifying the -DSQLite.encoding=x command line option to the Java runtime, where x must be a charset name recognized by your Java environment. If the SQLite engine supports UTF-8, no special options are needed.

If you like to try the JDBC driver, use SQLite.JDBCDriver as the JDBC drivers's class name. Make sure that you have the sqlite.jar in your class path and the native library in your java library path. The JDBC URLs to connect to an SQLite database have the format jdbc:sqlite:/path, where path has to be specified as the path name to the SQLite database, for example

    jdbc:sqlite://dirA/dirB/dbfile
    jdbc:sqlite:/DRIVE:/dirA/dirB/dbfile
    jdbc:sqlite:///COMPUTERNAME/shareA/dirB/dbfile
In order to deal with SQLite's in-memory databases, use a JDBC URL with this format:
    jdbc:sqlite:/:memory:
For now, the only data types supported on SQLite tables are java.lang.String, short, int, float, and double. The encoding of SQLite2 databases can be set per JDBC connection using the connect property encoding when creating the connection. Partial support exists for java.sql.Date, java.sql.Time, and java.sql.Timestamp. For SQLite3 engines the connect property daterepr determines, how date/time/timestamp data is interpreted. If set to daterepr=julian, on INSERT/UPDATE these datatypes are converted to a floating point number expressing a julian day number as described in the
SQLite Wiki. On retrieval (SELECT), the floating point representation is preferred. All other values of the daterepr property use the string formats YYYY-mm-dd for date, HH:MM:SS for time, and YYYY-mm-dd HH:MM:SS.f for timestamp. The data type mapping depends mostly on the availability of the SQLite PRAGMAs show_datatypes and table_info. Most basic database meta data methods are implemented, thus enough information is available to access SQLite databases with JDK >= 1.3 and the ISQLViewer tool.

A JDBC based benchmark program is built into sqlite.jar. If using a non OJEC version of Java it can be invoked by

   $ java -cp sqlite.jar SQLite.BenchmarkDriver ...
If using an OJEC version of Java it can be invoked by
   $ cvm -cp sqlite.jar SQLite.BenchmarkDataSource ...
For further details see the usage information when invoked without arguments and consult the source code.

Other useful information to make this package work on ARM Linux can be found in How To compile SQLite with JDBC for the iPAQ.

Tim Anderson has some notes on compiling in his sqlitenotes.

Stanislaw Ciszewski had also success on MacOSX, see his remarks here.

Anton Johansson wrote a howto compile SQLite Java Wrapper for Windows, thanks Anton!

To find out the SQLite.Database instance of a JDBC connection, that code snippet can serve as a starting point:

 Connection conn = null;
 SQLite.Database db = null;
 try {
   Class.forName("SQLite.JDBCDriver").newInstance();
   conn = DriverManager.getConnection("jdbc:sqlite:/blabla");
   java.lang.reflect.Method m =
     conn.getClass().getMethod("getSQLiteDatabase", null);
   db = (SQLite.Database) m.invoke(conn, null);
 } catch (Exception e) {
 }

This software and accompanying documentation is released under a BSD-style license:

This software is copyrighted by Christian Werner <chw@ch-werner.de>
and others. The following terms apply to all files associated with the
software unless explicitly disclaimed in individual files.

The authors hereby grant permission to use, copy, modify, distribute,
and license this software and its documentation for any purpose, provided
that existing copyright notices are retained in all copies and that this
notice is included verbatim in any distributions. No written agreement,
license, or royalty fee is required for any of the authorized uses.
Modifications to this software may be copyrighted by their authors
and need not follow the licensing terms described here, provided that
the new terms are clearly indicated on the first page of each file where
they apply.

IN NO EVENT SHALL THE AUTHORS OR DISTRIBUTORS BE LIABLE TO ANY PARTY
FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES
ARISING OUT OF THE USE OF THIS SOFTWARE, ITS DOCUMENTATION, OR ANY
DERIVATIVES THEREOF, EVEN IF THE AUTHORS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.

THE AUTHORS AND DISTRIBUTORS SPECIFICALLY DISCLAIM ANY WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE, AND NON-INFRINGEMENT.  THIS SOFTWARE
IS PROVIDED ON AN "AS IS" BASIS, AND THE AUTHORS AND DISTRIBUTORS HAVE
NO OBLIGATION TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR
MODIFICATIONS.



Contact: Christian Werner