How to access relational DBs

core.clj

(ns dbgists.core
  "Demonstrates working with a DB connection."
  (:require [clojure.java.jdbc :as jdbc])
  (:import [com.jolbox.bonecp BoneCPDataSource]))


;; to start H2 DB

#_ (do (require '[dbgists.h2 :as h2])
       (h2/start-db))



;; -------------------------------------------------------------------
;; the simplest thing that works

#_(def db-spec {:classname "org.h2.Driver"
                :subprotocol "h2"
                :subname "tcp://localhost/~/test"
                :user "sa"
                :password ""})

#_(jdbc/query db-spec ["select * from project"])

;; -------------------------------------------------------------------
;; setup pooled data source and H2 db-spec

(defn make-pooled-datasource
  "Creates a pooled data source using BoneCP."
  [{:keys [classname jdbc-url partitions increment minconnections maxconnections user username password]}]
  {:pre [(string? classname) (seq classname)
         (Class/forName classname)
         (string? jdbc-url) (seq jdbc-url)]}
  (let [s (BoneCPDataSource.)]
    (.setDriverClass s classname)
    (.setJdbcUrl s jdbc-url)
    (when minconnections (.setMinConnectionsPerPartition s minconnections))
    (when maxconnections (.setMaxConnectionsPerPartition s maxconnections))
    (when partitions (.setPartitionCount s partitions))
    (when increment (.setAcquireIncrement s increment))
    (when user (.setUsername s user))
    (when username (.setUsername s username))
    (when password (.setPassword s password))
    s))


(defn h2-db-spec
  [url]
  {:datasource (make-pooled-datasource {:classname "org.h2.Driver"
                                        :user "sa"
                                        :password ""
                                        :jdbc-url (str "jdbc:h2:" url)})})

(def db-spec (h2-db-spec "tcp://localhost/~/test"))

create-schema.sql

create table if not exists project (
       id bigint generated by default as identity primary key, 
       name varchar(50));

create table if not exists member (
       id bigint generated by default as identity primary key,
       name varchar(30));

create table if not exists project_members (
       member_id bigint,
       project_id bigint);

drop-schema.sql

drop table project;
drop table member;
drop table project_members;

ex_honeysql.clj

(ns dbgists.ex-honeysql
  "Demonstrates HoneySQL. 
  https://github.com/jkk/honeysql"
  (:require [clojure.java.jdbc :as jdbc]
            [dbgists.core :refer [db-spec]]
            [honeysql.core :as sql]
            [honeysql.helpers :refer :all]))


;; renders a simple SQL query
#_ (sql/format {:select [:*] :from [:project]})


(defn project-members
  [db-spec projectid]
  (jdbc/query db-spec
              (sql/format
               (-> (select :m.*)
                   (from [:member :m])
                   (left-join [:project_members :pm] [:= :pm.member_id :m.id])
                   (where [:= :pm.project_id projectid])))))



#_ (jdbc/with-db-transaction [tx db-spec]
     (project-members tx 1))

ex_jdbc.clj

(ns dbgists.ex-jdbc
  "Demonstrates clojure.java.jdbc. 
  https://github.com/clojure/java.jdbc/"
  (:require [clojure.java.jdbc :as jdbc]
            [dbgists.core :refer [db-spec]]))


(defn project-members
  [db-spec projectid]
  (jdbc/query db-spec ["select m.id, m.name 
from member m join project_members pm on m.id = pm.member_id 
where project_id = ?" projectid]))


#_(jdbc/with-db-transaction [tx db-spec]
     (project-members tx 1))


;; -------------------------------------------------------------------
;; persisting a nested data structure

(defn save-member!
  [db-spec {:keys [id] :as member}]
  (if id
    (do (jdbc/update! db-spec :member member ["id = ?" id])
        member)
    (let [id (->> member
                  (jdbc/insert! db-spec :member)
                  first vals first)]
      (assoc member :id id))))


(defn save-project!
  [db-spec {:keys [id name members] :as project}]
  (let [projectid (if id
                    (do (jdbc/update! db-spec :project {:name name} ["id = ?" id])
                        id)
                    (->> {:name name}
                         (jdbc/insert! db-spec :project)
                         first vals first))
        members (->> members
                     (mapv (partial save-member! db-spec)))]
    (jdbc/delete! db-spec :project_members ["project_id = ?" projectid])
    (->> members
         (map #(hash-map :project_id projectid :member_id (:id %)))
         (apply (partial jdbc/insert! db-spec :project_members))
         doall)
    (assoc project
        :id projectid
        :members members)))

ex_yesql.clj

(ns dbgists.ex-yesql
  "Demonstrates Yesql.
  https://github.com/krisajenkins/yesql"
  (:require [clojure.java.jdbc :as jdbc]
            [dbgists.core :refer [db-spec]]
            [yesql.core :refer [defqueries]]))


(defqueries "queries.sql")

#_ (jdbc/with-db-transaction [tx db-spec]
		(project-members tx 1))

h2.clj

(ns dbgists.h2
  "Manage in process DB H2 for testing purposes."
  (:import [org.h2.tools Server]))

(defonce db-server (atom nil))

(defn start-db
  "Starts H2 server including web console (available on localhost:8082)."
  []
  (when-not @db-server
    (println "Starting DB, web console is available on localhost:8082")
    (reset! db-server {:tcp (Server/createTcpServer (into-array String []))
                       :web (Server/createWebServer (into-array String []))})
    (doseq [s (vals @db-server)] (.start s))))


(defn stop-db
  "Stops H2 server including web console."
  []
  (when-let [s @db-server]
    (println "Stopping DB")
    (doseq [s (vals s)] (.stop s))
    (reset! db-server nil)))

project.clj

(defproject dbgists "0.1.0-SNAPSHOT"
  :description "Demo how to start working with a relational DB"
  :url ""
  :license {:name "Eclipse Public License"
            :url "http://www.eclipse.org/legal/epl-v10.html"}
  :dependencies [[org.clojure/clojure "1.5.1"]
                 [com.h2database/h2 "1.4.178"]
                 [org.clojure/java.jdbc "0.3.3"]
                 [yesql "0.4.0"]
                 [com.jolbox/bonecp "0.8.0.RELEASE"]
                 [org.slf4j/slf4j-nop "1.7.7"]])

queries.sql

-- name: projects
-- Returns all projects.
select * 
from project;

-- name: project-members
-- Returns the members of a given project.
select m.id, m.name 
from member m join project_members pm on m.id = pm.member_id 
where project_id = :projectid;

test-data.sql

insert into project (id, name) values (1, 'Learning Java');
insert into project (id, name) values (2, 'Learning Clojure');

insert into member (id, name) values (1, 'Foo');
insert into member (id, name) values (2, 'Bar');
insert into member (id, name) values (3, 'Baz');

insert into project_members (member_id, project_id) values (1, 1);
insert into project_members (member_id, project_id) values (2, 1);
insert into project_members (member_id, project_id) values (3, 2);