SQLite and JDBC
These examples utilize Java's
JDBC,
which allows
a Java program to communicate with a wide range of
databases, but the focus here
is on SQLite,
a simple, but powerful,
system, which doesn't require complex database management. The link
between SQLite and Java is managed by Taro Saito's
sqlite-jdbc
driver.
It should be straightforward to modify these
examples to employ other database systems,
by installing a different driver, and changing the string argument
to DriverManager.getConnection() at the start of
the code. However, I haven't actually tried it 😀
Preliminaries
Before you start grappling with the intricacies of JDBC, you should
first become familiar with SQL and SQLite.
Have a look at the following:
My Examples
- SQLiteInfo.java:
Ways to find out about SQLite and its JDBC driver
- ViewTables.java: Display
tables information to stdout
- TableDisplay.java: Display
a table inside a scrollable JTable in a window. This utilizes
ColumnColorRenderer.java
- CreateDB.java: Create (or
reinitialize) names.db and add three records to the "person" table.
- InsertDB.java: Add an (id, name)
record to the "person" table of names.db.
- DeleteDB.java: Delete an (id, name)
record from the "person" table of names.db based on the id
- UpdateDB.java: Update the name in
an existing (id, name) record in the "person" table of names.db
- QueryDB.java: Query the
"cities" table of worldcities.db, retrieving all the
records which match the supplied city name.
- StoreImage.java: Store an image
in the "pics" table of pictures.db. The example image is
cat.png
- GetImage.java: Retrieve an
image from the "pics" table in pictures.db by supplying an id
- DBUtils.java: A couple of
useful JDBC functions
Other Downloads and Links
- The SQLite databases used by my examples:
names.db,
worldcities.db, and
chinook.db (from
here)
- Batch files to easily compile and run my code:
compile.bat and
run.bat
- All the examples in one zipped file
(2.57 MB)
- My copies of the libraries
(5.91 MB). They are sqlite-jdbc-3.16.1.jar,
commons-csv-1.9.0.jar, and
DBTablePrinter.jar.
Unzip the file, and place the libs folder in the
same directory as the examples and batch files. (Commons CSV is
the topic of another
page
on this site.)
- The official libraries:
SQLite-JDBC,
Apache Commons CSV, and
Database
Table Printer.
- Tutorials on SQLite and Java:
here and
here
- More on JDBC:
here
(the official source),
here,
here, and
here.
And examples:
here,
here
Dr. Andrew Davison
E-mail: ad@coe.psu.ac.th
Back to the third-party libraries page
