(sql/with-connection {... mydatabase }
(sql/with-query-results rows [ "select * from users where email in (?)"
[ "[email protected]" "[email protected]" ] ]
(doseq [rows row]
(prn row))))
This will always return no result. I've also tried using just "?" in place of "(?)"
AFAIR you can't do this directly. Behind the scene macro with-query-results uses java.sql.PreparedStatement and invokes .setObject for each parameter. So you have to write something like this
(sql/with-connection db
(sql/with-query-results rs
[(str "select * from users where email in ("
(apply
str
(interpose ", " (map #(str "\"" % "\"")
["[email protected]" "[email protected]"]))) ")")]
...))
to generate already prepared sql string with "in" parameters: "select * from users where email in (\"[email protected]\", \"[email protected]\")".
The better way is just to use one of clojure sql libraries like sqlkorma.
Now in 2020, you can do this without string concatenation, at least in postgres.
Extend the ISQLParameter protocol like this:
; This allows us to use clojure arrays as query parameters for JDBC.
; Thus, we don't need to do string concatenation to make IN clauses.
(extend-protocol clojure.java.jdbc/ISQLParameter
Sequential
; Important: Sometimes you have to explicitly specify the type of array like ?::text[].
(set-parameter [v ^PreparedStatement stmt ^long i]
(let [conn (.getConnection stmt)
meta (.getParameterMetaData stmt)
type-name (.getParameterTypeName meta i)]
(if-let [elem-type (when (= (first type-name) \_) (apply str (rest type-name)))]
(.setObject stmt i (.createArrayOf conn elem-type (to-array v)))
(.setObject stmt i v)))))
Then you can run queries like
(jdbc/query db/db ["select 'banana' = ANY(?) as banana_included,
'apple' = ANY(?) as apple_included"
["banana" "potato"] ["banana" "potato"]])
The = ANY(array) syntax does the same job as an in clause - as far as I know IN clauses are converted to the = ANY(array) internally in postgres - don't quote me on that though.
