1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN""http://www.w3.org/
TR/REC-html40/loose.dtd>
<!--NewPage-->
<HTML>
<HEAD>
<TITLE>
SQLite Java Wrapper/JDBC Driver
</TITLE>
</HEAD>
<LINK REL="stylesheet" TYPE="text/css" HREF="stylesheet.css" TITLE="Style">
<BODY BGCOLOR="white">
<A NAME="overview_description">
This is a Java wrapper including a basic JDBC driver
for the <A HREF="http://www.sqlite.org" TARGET="_blank">
SQLite 2/3 database engine</A>.
It is designed using JNI to interface to the SQLite API. That
API is wrapped by methods in the <A HREF="SQLite/Database.html">
SQLite.Database</A> class.
Since June, 2002, it includes a small <A HREF="#jdbc_driver">JDBC driver</A>,
which allows the most basic things to be carried out using the
<TT>java.sql</TT> 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.
<P>
The current source tarball can be downloaded from
<A HREF="http://www.ch-werner.de/javasqlite/javasqlite-20120209.tar.gz">
javasqlite-20120209.tar.gz</A>.
It has been tested on Linux, using SQLite 2.8.17 and 3.7.9,
and JDK 1.4/1.5/1.6 from
<A HREF="http://java.sun.com" TARGET="_blank">Sun</A>.
The change log starting from August 2007 is <A HREF="ChangeLog">here</A>.
<P>
For rebuilding the following requirements must be met:
<UL>
<LI TYPE="circle">SQLite 2.4.12 or higher
<LI TYPE="circle">JDK 1.1 or higher
<LI TYPE="circle">C compiler and make
</UL>
The source code uses GNU autoconf, thus the build process on UN*X like
systems should look like:
<P>
<PRE>
$ ./configure
...
$ make
...
$ su -c "make install"
...
</PRE>
<P>
To override the directory where configure looks for SQLite and JDK the
configure options <TT>--with-sqlite=DIR</TT>, <TT>--with-sqlite3=DIR</TT>,
and <TT>--with-jdk=DIR</TT> can be used. To specify the place where the
native library (the <TT>libsqlite_jni.so</TT> file) shall be
installed, use the <TT>--prefix=DIR</TT> option. The default place is
<TT>/usr/local/lib</TT>, i.e. the prefix defaults to <TT>/usr/local</TT>.
To specify where the <TT>sqlite.jar</TT> file containing the high-level
part and the JDBC driver shall be
installed, use the <TT>--with-jardir=DIR</TT> option. The default is
<TT>/usr/local/share/java</TT>.
At runtime, it is necessary to tell the JVM both places with
the <TT>-classpath</TT> and <TT>-Djava.library.path=..</TT>
command line options. Optionally, the native part is searched
using the path prefix specified in the <TT>-DSQLite.library.path=..</TT>
property, which has precedence over <TT>-Djava.library.path=..</TT>.
<P>
For Win32 (NT4 or above) the makefiles <TT>javasqlite.mak</TT>,
<TT>javasqlite23.mak</TT>, and <TT>javasqlite3.mak</TT> 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.
<P>
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
<A HREF="http://www.ch-werner.de/javasqlite/javasqlite-20110827-win32.zip">
javasqlite-20110827-win32.zip</A>.
<P>
After successful build and installation a small
<A HREF="test.java">test program</A> can be run by invoking the
following commands (on OJEC replace the <TT>java</TT> command by
<TT>cvm</TT>):
<P>
<PRE>
$ make test
...
$ java -classpath ... -Djava.library.path=... test
version: 2.8.17
==== local callback ====
#cols = 5
col0: type
col1: name
...
</PRE>
<P>
For testing the newer features of SQLite3 (parameter binding with
the help of the <A HREF="SQLite/Stmt.html">SQLite.Stmt</A> class
and incremental blob I/O through <A HREF="SQLite/Blob.html">SQLite.Blob</A>
class) refer to another <A HREF="test3.java">test program</A>.
<P>
A simple shell modelled after that of SQLite can be invoked as
(on OJEC replace the <TT>java</TT> command by <TT>cvm</TT>):
<PRE>
$ java SQLite.Shell [options] database [sql string]
</PRE>
or using the JAR file
<PRE>
$ java -jar sqlite.jar [options] database [sql string]
</PRE>
<P>
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
<TT>-DSQLite.encoding=x</TT> command line option to the Java
runtime, where <TT>x</TT> must be a charset name recognized
by your Java environment. If the SQLite engine supports UTF-8,
no special options are needed.
<P>
<A NAME="jdbc_driver">
If you like to try the JDBC driver, use <TT>SQLite.JDBCDriver</TT>
as the JDBC drivers's class name. Make sure that you have the
<TT>sqlite.jar</TT> 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 <TT>jdbc:sqlite:/path</TT>,
where <TT>path</TT> has to be specified as the path
name to the SQLite database, for example
<PRE>
jdbc:sqlite://dirA/dirB/dbfile
jdbc:sqlite:/DRIVE:/dirA/dirB/dbfile
jdbc:sqlite:///COMPUTERNAME/shareA/dirB/dbfile
</PRE>
In order to deal with SQLite's in-memory databases, use a
JDBC URL with this format:
<PRE>
jdbc:sqlite:/:memory:
</PRE>
For now, the only data types
supported on SQLite tables are <TT>java.lang.String</TT>,
<TT>short</TT>, <TT>int</TT>, <TT>float</TT>, and <TT>double</TT>.
The encoding of SQLite2 databases can be set per
JDBC connection using the connect property <TT>encoding</TT>
when creating the connection.
Partial support exists for <TT>java.sql.Date</TT>,
<TT>java.sql.Time</TT>, and <TT>java.sql.Timestamp</TT>.
For SQLite3 engines the connect property <TT>daterepr</TT>
determines, how date/time/timestamp data is interpreted.
If set to <TT>daterepr=julian</TT>, on INSERT/UPDATE these
datatypes are converted to a floating point number
expressing a julian day number as described in the
<A href="http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions" TARGET="_blank">
SQLite Wiki</A>. On retrieval (SELECT), the floating point
representation is preferred. All other values of the <TT>daterepr</TT>
property use the string formats <TT>YYYY-mm-dd</TT> for date,
<TT>HH:MM:SS</TT> for time, and <TT>YYYY-mm-dd HH:MM:SS.f</TT>
for timestamp.
The data type mapping depends mostly on the availability of
the SQLite <TT>PRAGMAs</TT> <TT>show_datatypes</TT> and <TT>table_info</TT>.
Most basic database meta data methods are implemented,
thus enough information is available to access SQLite
databases with JDK >= 1.3 and the
<A HREF="http://www.isqlviewer.com" TARGET="_blank">ISQLViewer</A>
tool.
<P>
A JDBC based benchmark program is built into <TT>sqlite.jar</TT>.
If using a non OJEC version of Java it can be invoked by
<PRE>
$ java -cp sqlite.jar SQLite.BenchmarkDriver ...
</PRE>
If using an OJEC version of Java it can be invoked by
<PRE>
$ cvm -cp sqlite.jar SQLite.BenchmarkDataSource ...
</PRE>
For further details see the usage information when invoked without
arguments and consult the source code.
<P>
Other useful information to make this package work on
ARM Linux can be found in
<A HREF="http://www.kecher.de/howtos/SQLite-JDBC-Howto.html" TARGET="_blank">
How To compile SQLite with JDBC for the iPAQ</A>.
<P>
Tim Anderson has some notes on compiling in his
<A HREF="http://www.itwriting.com/sqlitenotes.php" TARGET="_blank">sqlitenotes</A>.
<P>
Stanislaw Ciszewski had also success on MacOSX, see his
<A HREF="scnotes.txt">remarks here</A>.
<P>
Anton Johansson wrote a <A HREF="ajhowto.txt">howto</A> compile
SQLite Java Wrapper for Windows, thanks Anton!
<P>
To find out the SQLite.Database instance of a JDBC connection,
that code snippet can serve as a starting point:
<PRE>
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) {
}
</PRE>
<P>
This software and accompanying documentation is released under
a BSD-style license:
<P>
<TABLE ALIGN="center"BORDER="0" CELLPADDING="0" CELLSPACING="3">
<TR><TD BGCOLOR="EEEEEE">
<PRE>
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.
</PRE>
</TD></TR>
</TABLE>
</BODY>
</HTML>
|
