|
Introduction
SQLite JDBC DriverSQLite JDBC driver developed by [wiki:leo 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. See the sample code. What is different from Zentus's SQLite JDBC?The original Zentus's SQLite JDBC driver http://www.zentus.com/sqlitejdbc/ 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 libray 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. Public Discussion Forum
News
SQLiteConfig config = new SQLiteConfig();
// config.setReadOnly(true);
config.setSharedCache(true);
config.recursiveTriggers(true);
// ... other configuration can be set via SQLiteConfig object
Connection conn = DriverManager.getConnection("jdbc:sqlite:sample.db", config.toProperties());
// 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 November 11th: sqlite-jdbc-3.6.4.1. A bug fix release
DownloadDownload the latest version of SQLiteJDBC from here.
If your are an Maven user, follow the instruction described here. Beta ReleaseThe early releases (beta) of sqlite-jdbc with some advanced features are available from here: Supported Operating SystemsSince sqlite-jdbc-3.6.19, the natively compiled SQLite engines will be used for the following operating systems:
In the other OSs not listed above, the pure-java SQLite is used. If you want to use the native library for your OS, build the source from scratch. Usage
> javac Sample.java > java -classpath ".;sqlite-jdbc-(VERSION).jar" Sample # in Windows or > java -classpath ".:sqlite-jdbc-(VERSION).jar" Sample # in Mac or Linux name = leo id = 1 name = yui id = 2 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) throws ClassNotFoundException
{
// load the sqlite-JDBC driver using the current class loader
Class.forName("org.sqlite.JDBC");
Connection connection = null;
try
{
// 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");
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);
}
}
}
}The usage of SQLite-JDBC driver is the same with the original version. See http://www.zentus.com/sqlitejdbc/ for the general usage. For usage of JDBC, see my article about JDBC. How to Specify Database FilesHere 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");How to Use Memory DatabasesSQLite 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:");How to test the running mode: Native or Pure-Java Version?import org.sqlite.SQLiteJDBCLoader;
// in your function ...
void test()
{
System.out.println(String.format("running in %s mode", SQLiteJDBCLoader.isNativeMode() ? "native" : "pure-java"));
}
Run in Pure-Java modeIn some OS (e.g., old Linux kernel), loading native library causes JVM crashes. In this case, set sqlite.purejava=true JVM variable: > java -Dsqlite.purejava=true -cp .:sqlite-jdbc-3.6.10.jar Sample Or, set this System property before loading the JDBC driver: System.setProperty("sqlite.purejava", "true");
Class.forName("org.sqlite.JDBC");In this mode, sqlite-jdbc never uses native SQLite libraries. How to use Online Backup and Restore Feature
// Create a memory database
Connection conn = DriverManager.getConnection("jdbc:sqlite:");
// Do some updates
stmt.executeUpdate("create table sample(id, name)");
stmt.executeUpdate("insert into sample values(1, \"leo\")");
stmt.executeUpdate("insert into sample values(2, \"yui\")");
// Dump the database contents to a file
stmt.executeUpdate("backup to backup.db");
// 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");Another sample code is here. How does SQLiteJDBC 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, Mac OS and Linux. An appropriate native library file is automatically extracted into your OS's temporary folder, when your program loads "org.sqlite.JDBC" driver. Source Codes
$ hg clone https://sqlite-jdbc.googlecode.com/hg/ sqlite-jdbc
LicenseThis program follows the Apache License version 2.0 (http://www.apache.org/licenses/ ) That means: It allows you to:
It forbids you to:
It requires you to:
It does not require you to:
See License FAQ http://www.apache.org/foundation/licence-FAQ.html for more details. Using SQLiteJDBC with Maven2If you are familier 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. <dependencies>
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.6.16</version>
</dependency>
</dependencies>Using SQLiteJDBC with Tomcat6 Web ServerDo 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. <dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
<version>3.6.16</version>
<scope>provided</scope>
</dependency>Build from SourceIf your OS is not supported, consider to build the source from scratch.
export MAVEN_HOME=$HOME/local/maven-3.0.2 export PATH=$MAVEN_HOME/bin:$PATH hg clone https://sqlite-jdbc.googlecode.com/hg/ sqlite-jdbc > export JAVA_HOME=/usr/java/jdk1.5.0_13/; export PATH=$JAVA_HOME/bin:$PATH > javac -version javac 1.5.0_13 > make Dependency Tests
> DUMPBIN /DEPENDENTS sqlitejdbc.dll KERNEL32.dll msvcrt.dll
> otool -L libsqlitejdbc.jnilib
libsqlitejdbc.jnilib:
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)
> ldd libsqlitejdbc.so
linux-gate.so.1 => (0x00b45000)
libc.so.6 => /lib/i686/nosegneg/libc.so.6 (0x002dd000)
/lib/ld-linux.so.2 (0x47969000)
|
► Sign in to add a comment