10. Interacting with a Database


You can have data without information, but you cannot have information without data.
— Daniel Keys Moran


$ lein new messenger

project.clj dependencies:

[org.clojure/java.jdbc "0.7.5"]
[hsqldb/hsqldb ""]
we need the driver we plan to use to connect to a database


Require jdbc and configure a db connection url

(ns messenger.core
  (:require [clojure.java.jdbc :as jdbc]))
(def db "jdbc:hsqldb:mem:testdb")


Create a table called testing

(jdbc/execute! db
  "create table messages (message varchar(1024))")

Insert some rows

(jdbc/insert-multi! db :messages
                    [{:message "Hello World"}
                     {:message "How now?"}])


(jdbc/query db ["select * from messages"])
=> ({:message "Hello World"}
    {:message "How now?"})


To selectively delete some data:

(jdbc/delete! db :messages ["message like '%World%'"])
(jdbc/query db ["select * from messages"])
=> ({:message "How now?"})

Now there is only one row remaining


(jdbc/insert-multi! db :messages
                [{:message "Nobody panic!!!"}
                 {:message "What in the world?"}
                 {:message "All is well."}])

Parameterized query

(defn search [s]
  (jdbc/query db
    ["select * from messages where message like ?" s]))
(search "%How%")
=> ({:message "How now?"})
  • String concatenation is susceptible to SQL injection

  • Parameters are not part of the query; cannot perform SQL from malicious input

Starting fresh

If you want to redo any steps, remember that you can always drop the table and start again

(jdbc/execute! db "drop table messages")

Solutions for SQL management

HoneySQL can be used to build SQL statements from data structures.


See manual end of section 11


(ns messenger.core
  (:require [clojure.java.jdbc :as jdbc]))
(def db "jdbc:hsqldb:mem:testdb")
(jdbc/execute! db
  "create table person (id bigint, name varchar(1024))")
(jdbc/execute! db
  "create table policy (id bigint, name varchar(1024))")
(jdbc/execute! db
  "create table person_policy
  (person_id bigint, policy_id bigint)")


(jdbc/insert-multi! db :person
                    [{:id 1 :name "Sally"}
                     {:id 2 :name "Billy"}])
(jdbc/insert-multi! db :policy
                    [{:id 1 :name "Corgi Cover"}
                     {:id 2 :name "Poodle Protection"}])
(jdbc/insert-multi! db :person_policy
                    [{:person_id 1 :policy_id 1}
                     {:person_id 1 :policy_id 2}
                     {:person_id 2 :policy_id 1}])


(defn find-policies [person-name]
  (jdbc/query db ["select a.name from policy a
                  inner join person_policy b on a.id = b.policy_id
                  inner join person c on b.person_id = c.id
                  where c.name = ?"
(find-policies "Sally")
=> ({:name "Corgi Cover"} {:name "Poodle Protection"})
(find-policies "Jane")
=> ()
(find-policies "Billy")
=> ({:name "Corgi Cover"})

Challenge 4: Corgi Cover Database

Sending files around is proving to be problematic. Sometimes applications are lost or the results of the eligibility check are not communicated back to the customer. You have been tasked with creating a central source of truth that can be queried as to what applications have been submitted and processed.

Part 1: Set up the schema

Using the database of your choice, set up an initial database for the Corgi Cover project. In the code, connect to the database and create the initial table required. You can use whatever schema you like, but the first requirement is to store the applications with exactly the same data as was retrieved from the file format in Challenge 2.

Part 2: Populate the data

Modify the code to store the applications as they are processed, and the result of the eligibility check.

Part 3: Write a spec

Ensure that all records processed from the files meets your expectations for required fields. Write a spec that explicitly defines what should be in the applications. Validate the spec on the incoming records.

Part 4: Extending to Poodle Protection

Insuricorp is about to launch a new policy called “Poodle Protection”. Soon they will be processing applications with completely new rules. Set up a multimethod to handle “Poodle Protection” applications differently from “Corgi Cover” applications. For now the only difference with the rules from “Corgi Cover” is that “Poodle Protection” is available in different states: California (CA), Florida (FL), Wyoming (WY), and Hawaii (HI).

End Databases