Quickie - Using SQLite4Java from Clojure
We use SQLite4Java at work, and I’m playing around with our databases from Clojure at the moment and just wanted to quickly jot down how to do it.
First, native library management in SQLite4Java is a little hairy: it’s going to look
for the libraries either in
the SQLite4Java jar file, or you can explicitly tell it where to find things. The
Maven config to do this automatically is suitably ugly, and as far
as I know, there’s not a good way to do the same thing with Leiningen.
I opted to just explicitly tell it for now. I list the SQLite4Java dependency in my
project.clj, copied the native libraries I’m interested in into
and then just added this at startup time:
(I’m leaving all of the namespace components in there to be explicit: I require/import them as normal in my code.)
Next issue: actually running queries against a database has to be done using a SQLiteConnection instance, and those instances are limited to being used from the thread that created them. That precludes using them from the REPL: you don’t have any control over what thread your expressions are going to be executed from. So, you need to use SQLiteQueue, which wraps a SQLiteConnection and ensures all calls come from a single thread.
SQLiteQueue works by taking SQLiteJob instances (basically just a function) and running them in first-in-first-out order. Unfortunately, SQLiteQueue can’t just take a Callable, so you can’t use Clojure functions directly. Wrapping a function up in a job is pretty easy though, and is the first time I’ve needed to use clojure.core/proxy:
Then using those jobs is done by calling SQLiteQueue::execute, which returns the job again. Those jobs implement Future, so you can use them as normal, or they have a convenience method SQLiteJob::complete that deals with exceptions for you.
So all bundled together, my little library for working with SQLite4Java looks like this right now: