by @yokolet

Level: intermediate

SQL Database access with Apache Derby

In this lab, we will create a simple application to use SQL database. This lab uses Apache Derby http://db.apache.org/derby/ SQL database since Derby is a zero setup database. We can skip whole lot of database installation and setting.

The first step is to create a Clojure project.

$ lein new hello-derby
$ cd hello-derby

Add three libraries, org.clojure/java.jdbc, java-jdbc/dsl and org.apache.derby/derby, in the dependencies of your project.clj. The file will look like below:

The second step is to write an application code. In this lab, we will add code to src/hello_derby/core.clj. The file is generated by leiningen and has a sample foo function. You may delete unused foo function.

Our SQL database application code is below. You may copy & paste entire code in your core.clj.

The next step is to use our application code on the repl.

Assuming you are in the directory hello-derby, which contains project.clj, start repl by lein repl. The first time you run lein repl in this directory, leiningen downloads jar archives necessaery to run this project.

When the repl gets ready, load the application on your repl by use function. Then, test actually hello-derby.core is successfully loaded by checking the Vars, db-path and db.

user=> (use 'hello-derby.core)
nil
user=> db-path
"./MyDB"
user=> db
{:subprotocol "derby", :subname "./MyDB", :create true}

If you get an exception when you type use function db-path or db above, probably, your code has a typo, unmatched parentheses, or some errors. Correct the error on the editor if you have.

You can reload the editied code with :reload option.

user=> (use 'hello-derby.core :reload)

If everything looks ok, create posts table and write some posts. Then, see those are in database.

user=> (create-posts-table)
(0)
user=> (write-post "ClojureBridge" "I'm learning Clojure. Awesome!")
({:1 1M})
user=> (write-post "What I'm doing" "I'm coding, yay! and my code is working!")
({:1 2M})
user=> (all)
({:created_at #inst "2013-12-23T03:26:44.907000000-00:00", :body "I'm learning Clojure. Awesome!", :title "ClojureBridge", :id 1} {:created_at #inst "2013-12-23T03:27:50.692000000-00:00", :body "I'm coding, yay! and my code is working!", :title "What I'm doing", :id 2})
user=>

Could you see the result of all function?

We added documents in our functions. You can see those by doc function.

user=> (doc create-posts-table)
-------------------------
hello-derby.core/create-posts-table
([])
  Creates posts table.
  Only when posts table doesn't exist, for example, at the very first time,
  use this function and create the posts table.
nil
user=> (doc write-post)
-------------------------
hello-derby.core/write-post
([title body])
  Inserts title and body into the posts table.
  Usage is (write-post title body). Both strings for title and body needs to be
  double quoted.
nil

So far, we have only one query function to get all. However, database system as well as SQL query is there to select data from all.

The next work is to add two query functions in our application. The find-post functions finds a record by id. The order-by-time function finds one of ids, titles, or bodies ordered by the time created.

Back on the repl, reload your code and make queries using newly added functions.

user=> (use 'hello-derby.core :reload)
nil
user=> (find-post 1)
({:created_at #inst "2013-12-23T03:26:44.907000000-00:00", :body "I'm learning Clojure. Awesome!", :title "ClojureBridge", :id 1})
user=> (find-post 2)
({:created_at #inst "2013-12-23T03:27:50.692000000-00:00", :body "I'm coding, yay! and my code is working!", :title "What I'm doing", :id 2})
user=> (order-by-time :id)
({:id 1} {:id 2})
user=> (order-by-time :title)
({:title "ClojureBridge"} {:title "What I'm doing"})
user=> (order-by-time :body)
({:body "I'm learning Clojure. Awesome!"} {:body "I'm coding, yay! and my code is working!"})

If you can see the results of the queries, add more posts using write-post function and try queries again.

References

For further coding, these are helpful references.