Created
July 18, 2018 06:37
-
-
Save leblowl/32bd62f1db3f1040ed1b530ee6a806df to your computer and use it in GitHub Desktop.
One way to deal with many params in Clojure JDBC
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
(defn reduce-sql-vec | |
"Takes an array of SQL statement vectors and combines | |
them into a valid clojure.java.jdbc query vector. In each statement vector | |
you can have parameters at the end just like a jdbc vector. Easier to show | |
than explain: | |
take this: | |
[[\"SELECT * FROM dude\"] | |
[\" WHERE id = ?\" 1]] | |
and turn it into this: | |
[\"SELECT * FROM dude WHERE id = ?\" 1] | |
This makes it a little easier to manage queries with large numbers of params." | |
[sql-vec] | |
(reduce (fn [acc line] | |
(-> acc | |
(update 0 #(str % (first line))) | |
(into (rest line)))) | |
[] | |
sql-vec)) | |
;; Example | |
;; | |
;; Requires one to have a db namespace, | |
;; JDBC param coercion from java.time.Instant | |
;; and a task table with described columns. | |
(require '[clojure.java.jdbc :as sql] | |
'[java-time :as jt] | |
'[my.db :as db]) | |
(defn sql-test | |
[id sdt edt] | |
[["WITH"] | |
[" totals AS ("] | |
[" SELECT"] | |
[" date_trunc('day', created_at) as dt,"] | |
[" count(1) as total"] | |
[" FROM"] | |
[" task"] | |
[" WHERE"] | |
[" id = ?" id] | |
[" AND created_at"] | |
[" BETWEEN ? AND ?" sdt edt] | |
[" GROUP BY"] | |
[" date_trunc('day', created_at)"] | |
[" )"] | |
[" SELECT * from totals"]]) | |
(def now (jt/instant)) | |
(def a-while-ago | |
(-> now (jt/minus (jt/days 1)))) | |
(->> (sql-test 1234 a-while-ago now) | |
(reduce-sql-vec) | |
(sql/query db/db)) | |
;; => ({:dt #object[java.time.Instant 0x7474a5fe "2018-07-18T00:00:00Z"], :total 2}) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment