$ lein new messenger
10. Interacting with a Database
You can have data without information, but you cannot have information without data.
— Daniel Keys Moran
clojure.java.jdbc
project.clj dependencies:
[org.clojure/java.jdbc "0.7.5"] [hsqldb/hsqldb "1.8.0.10"]
| we need the driver we plan to use to connect to a database |
Connecting
Require jdbc and configure a db connection url
(ns messenger.core (:require [clojure.java.jdbc :as jdbc]))
(def db "jdbc:hsqldb:mem:testdb")
Inserting
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?"}])Querying
(jdbc/query db ["select * from messages"])
=> ({:message "Hello World"}
{:message "How now?"})Deleting
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
insert-multi!
(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.
Exercises
See manual end of section 11
Answers
(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)")
Answers
(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}])Answers
(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 = ?"
person-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).