Last active
August 29, 2015 14:01
-
-
Save friemen/54f861c760868fc60fe3 to your computer and use it in GitHub Desktop.
How to access relational DBs
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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")) | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
drop table project; | |
drop table member; | |
drop table project_members; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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)) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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))) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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)) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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))) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
(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"]]) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment