Back
query (clj)
(source)function
(query connectable sql-params)
(query connectable sql-params opts)
Syntactic sugar over `execute!` to provide a query alias.
Given a connectable object, and a vector of SQL and its parameters,
returns a vector of hash maps of rows that match.
Examples
next-jdbc
(ns next.jdbc.sql-test
"Tests for the syntactic sugar SQL functions."
(:require [clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.specs :as specs]
[next.jdbc.sql :as sql]
[next.jdbc.test-fixtures
:refer [with-test-db ds column default-options
derby? jtds? maria? mssql? mysql? postgres? sqlite?]]
[next.jdbc.types :refer [as-other as-real as-varchar]]))
(deftest test-query
(let [ds-opts (jdbc/with-options (ds) (default-options))
rs (sql/query ds-opts ["select * from fruit order by id"])]
(is (= 4 (count rs)))
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs))))))
(deftest test-insert-delete
(let [new-key (cond (derby?) :1
(jtds?) :ID
(maria?) :insert_id
(mssql?) :GENERATED_KEYS
(mysql?) :GENERATED_KEY
(postgres?) :fruit/id
:else :FRUIT/ID)]
(testing "single insert/delete"
(is (== 5 (new-key (sql/insert! (ds) :fruit
{:name (as-varchar "Kiwi")
:appearance "green & fuzzy"
:cost 100 :grade (as-real 99.9)}
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 5 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 5})))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "multiple insert/delete"
(is (= (cond (derby?)
[nil] ; WTF Apache Derby?
(mssql?)
[8M]
(maria?)
[6]
:else
[6 7 8])
(mapv new-key
(sql/insert-multi! (ds) :fruit
[:name :appearance :cost :grade]
[["Kiwi" "green & fuzzy" 100 99.9]
["Grape" "black" 10 50]
["Lemon" "yellow" 20 9.9]]
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 7 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 6})))
(is (= 6 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 2}
(sql/delete! (ds) :fruit ["id > ?" 4])))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "multiple insert/delete with sequential cols/rows" ; per #43
(is (= (cond (derby?)
[nil] ; WTF Apache Derby?
(mssql?)
[11M]
(maria?)
[9]
:else
[9 10 11])
(mapv new-key
(sql/insert-multi! (ds) :fruit
'(:name :appearance :cost :grade)
'(("Kiwi" "green & fuzzy" 100 99.9)
("Grape" "black" 10 50)
("Lemon" "yellow" 20 9.9))
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 7 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 9})))
(is (= 6 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 2}
(sql/delete! (ds) :fruit ["id > ?" 4])))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "multiple insert/delete with maps"
(is (= (cond (derby?)
[nil] ; WTF Apache Derby?
(mssql?)
[14M]
(maria?)
[12]
:else
[12 13 14])
(mapv new-key
(sql/insert-multi! (ds) :fruit
[{:name "Kiwi"
:appearance "green & fuzzy"
:cost 100
:grade 99.9}
{:name "Grape"
:appearance "black"
:cost 10
:grade 50}
{:name "Lemon"
:appearance "yellow"
:cost 20
:grade 9.9}]
{:suffix
(when (sqlite?)
"RETURNING *")}))))
(is (= 7 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 1}
(sql/delete! (ds) :fruit {:id 12})))
(is (= 6 (count (sql/query (ds) ["select * from fruit"]))))
(is (= {:next.jdbc/update-count 2}
(sql/delete! (ds) :fruit ["id > ?" 10])))
(is (= 4 (count (sql/query (ds) ["select * from fruit"])))))
(testing "empty insert-multi!" ; per #44 and #264
(is (= [] (sql/insert-multi! (ds) :fruit
[:name :appearance :cost :grade]
[]
{:suffix
(when (sqlite?)
"RETURNING *")})))
;; per #264 the following should all be legal too:
(is (= [] (sql/insert-multi! (ds) :fruit
[]
{:suffix
(when (sqlite?)
"RETURNING *")})))
(is (= [] (sql/insert-multi! (ds) :fruit
[]
[]
{:suffix
(when (sqlite?)
"RETURNING *")})))
(is (= [] (sql/insert-multi! (ds) :fruit [])))
(is (= [] (sql/insert-multi! (ds) :fruit [] []))))))
next-jdbc
(ns next.jdbc.sql.builder-test
"Tests for the SQL string building functions in next.jdbc.sql.builder."
(:require [clojure.test :refer [deftest is testing]]
[next.jdbc.quoted :refer [mysql sql-server]]
[next.jdbc.sql.builder :as builder]))
(deftest test-for-query
(testing "by example"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]})
["SELECT * FROM [user] WHERE `id` = ? ORDER BY `a`, `b` DESC" 9]))
(is (= (builder/for-query :user {:id nil} {:table-fn sql-server :column-fn mysql})
["SELECT * FROM [user] WHERE `id` IS NULL"]))
(is (= (builder/for-query :user
{:id nil}
{:table-fn sql-server :column-fn mysql
:suffix "FOR UPDATE"})
["SELECT * FROM [user] WHERE `id` IS NULL FOR UPDATE"])))
(testing "by where clause"
(is (= (builder/for-query
:user
["id = ? and opt is null" 9]
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]})
[(str "SELECT * FROM [user] WHERE id = ? and opt is null"
" ORDER BY `a`, `b` DESC") 9])))
(testing "by :all"
(is (= (builder/for-query
:user
:all
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]})
["SELECT * FROM [user] ORDER BY `a`, `b` DESC"])))
(testing "top N"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:top 42})
["SELECT TOP ? * FROM [user] WHERE `id` = ? ORDER BY `a`, `b` DESC"
42 9])))
(testing "limit"
(testing "without offset"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:limit 42})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC LIMIT ?")
9 42])))
(testing "with offset"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:limit 42 :offset 13})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC LIMIT ? OFFSET ?")
9 42 13]))))
(testing "offset"
(testing "without fetch"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:offset 13})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC OFFSET ? ROWS")
9 13])))
(testing "with fetch"
(is (= (builder/for-query
:user
{:id 9}
{:table-fn sql-server :column-fn mysql :order-by [:a [:b :desc]]
:offset 13 :fetch 42})
[(str "SELECT * FROM [user] WHERE `id` = ?"
" ORDER BY `a`, `b` DESC OFFSET ? ROWS FETCH NEXT ? ROWS ONLY")
9 13 42])))))