Back
insert-multi! (clj)
(source)function
(insert-multi! connectable table hash-maps)
(insert-multi! connectable table hash-maps opts)
(insert-multi! connectable table cols rows)
(insert-multi! connectable table cols rows opts)
Syntactic sugar over `execute!` or `execute-batch!` to make inserting
columns/rows easier.
Given a connectable object, a table name, a sequence of column names, and
a vector of rows of data (vectors of column values), inserts the data as
multiple rows in the database and attempts to return a vector of maps of
generated keys.
Given a connectable object, a table name, a sequence of hash maps of data,
which all have the same set of keys, inserts the data as multiple rows in
the database and attempts to return a vector of maps of generated keys.
If called with `:batch` true will call `execute-batch!` - see its documentation
for situations in which the generated keys may or may not be returned as well as
additional options that can be passed.
Note: without `:batch` this expands to a single SQL statement with placeholders for
every value being inserted -- for large sets of rows, this may exceed the limits
on SQL string size and/or number of parameters for your JDBC driver or your
database!
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-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 [] []))))))
(deftest no-empty-columns
(is (thrown? clojure.lang.ExceptionInfo
(sql/insert-multi! (ds) :fruit [] [[] [] []]))))
(deftest no-mismatched-columns
(is (thrown? IllegalArgumentException
(sql/insert-multi! (ds) :fruit [{:name "Apple"} {:cost 1.23}]))))
next-jdbc
(ns next.jdbc.test-fixtures
"Multi-database testing fixtures."
(:require [clojure.string :as str]
[next.jdbc :as jdbc]
[next.jdbc.prepare :as prep]
[next.jdbc.sql :as sql])
(:import (io.zonky.test.db.postgres.embedded EmbeddedPostgres)))
Tests can reach into here and call ds (above) to get a DataSource for use
in test functions (that operate inside this fixture)."
[t]
(doseq [db test-db-specs]
(reset! test-db-spec db)
(if (= "embedded-postgres" (:dbtype db))
(reset! test-datasource
(.getPostgresDatabase ^EmbeddedPostgres @embedded-pg))
(reset! test-datasource (jdbc/get-datasource db)))
(let [fruit (if (mysql?) "fruit" "FRUIT") ; MySQL is case sensitive!
btest (if (mysql?) "btest" "BTEST")
auto-inc-pk
(cond (or (derby?) (hsqldb?))
(str "GENERATED ALWAYS AS IDENTITY"
" (START WITH 1, INCREMENT BY 1)"
" PRIMARY KEY")
(postgres?)
(str "GENERATED ALWAYS AS IDENTITY"
" PRIMARY KEY")
(mssql?)
"IDENTITY PRIMARY KEY"
(sqlite?)
"PRIMARY KEY AUTOINCREMENT"
:else
"AUTO_INCREMENT PRIMARY KEY")]
(with-open [con (jdbc/get-connection (ds))]
(when (stored-proc?)
(try
(jdbc/execute-one! con ["DROP PROCEDURE FRUITP"])
(catch Throwable _)))
(try
(do-commands con [(str "DROP TABLE " fruit)])
(catch Exception _))
(try
(do-commands con [(str "DROP TABLE " btest)])
(catch Exception _))
(when (postgres?)
(try
(do-commands con ["DROP TABLE LANG_TEST"])
(catch Exception _))
(try
(do-commands con ["DROP TYPE LANGUAGE"])
(catch Exception _))
(do-commands con ["CREATE TYPE LANGUAGE AS ENUM('en','fr','de')"])
(do-commands con ["
CREATE TABLE LANG_TEST (
LANG LANGUAGE NOT NULL
)"]))
(do-commands con [(str "
CREATE TABLE " fruit " (
ID INTEGER " auto-inc-pk ",
NAME VARCHAR(32),
APPEARANCE VARCHAR(32) DEFAULT NULL,
COST INT DEFAULT NULL,
GRADE REAL DEFAULT NULL
)")])
(let [created (atom false)]
;; MS SQL Server does not support bool/boolean:
(doseq [btype ["BOOL" "BOOLEAN" "BIT"]]
;; Derby does not support bit:
(doseq [bitty ["BIT" "SMALLINT"]]
(try
(when-not @created
(do-commands con [(str "
CREATE TABLE " btest " (
NAME VARCHAR(32),
IS_IT " btype ",
TWIDDLE " bitty "
)")])
(reset! created true))
(catch Throwable _))))
(when-not @created
(println (:dbtype db) "failed btest creation")
#_(throw (ex-info (str (:dbtype db) " has no boolean type?") {}))))
(when (stored-proc?)
(let [[begin end] (if (postgres?) ["$$" "$$"] ["BEGIN" "END"])]
(try
(do-commands con [(str "
CREATE PROCEDURE FRUITP" (cond (hsqldb?) "() READS SQL DATA DYNAMIC RESULT SETS 2 "
(mssql?) " AS "
(postgres?) "() LANGUAGE SQL AS "
:else "() ") "
" begin " " (if (hsqldb?)
(str "ATOMIC
DECLARE result1 CURSOR WITH RETURN FOR SELECT * FROM " fruit " WHERE COST < 90;
DECLARE result2 CURSOR WITH RETURN FOR SELECT * FROM " fruit " WHERE GRADE >= 90.0;
OPEN result1;
OPEN result2;")
(str "
SELECT * FROM " fruit " WHERE COST < 90;
SELECT * FROM " fruit " WHERE GRADE >= 90.0;")) "
" end "
")])
(catch Throwable t
(println 'procedure (:dbtype db) (ex-message t))))))
(sql/insert-multi! con :fruit
[:name :appearance :cost :grade]
[["Apple" "red" 59 nil]
["Banana" "yellow" nil 92.2]
["Peach" nil 139 90.0]
["Orange" "juicy" 89 88.6]]
{:return-keys false})
(t)))))
seancorfield/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-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 [] []))))))
(deftest no-empty-columns
(is (thrown? clojure.lang.ExceptionInfo
(sql/insert-multi! (ds) :fruit [] [[] [] []]))))
(deftest no-mismatched-columns
(is (thrown? IllegalArgumentException
(sql/insert-multi! (ds) :fruit [{:name "Apple"} {:cost 1.23}]))))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc])
(:import (java.util UUID)))
(defmethod dispatch :start-conversation
[[_ db {:keys [to from] :as message}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversing (jdbc/execute-one! conn-opts ["SELECT a.conversation_id
FROM conversation a
JOIN conversation b
ON a.conversation_id = b.conversation_id
WHERE a.uid = ? AND b.uid = ?" to from])]
(if-let [conversation-id (:conversation/conversation-id conversing)]
(do
(dispatch [:insert-message conn-opts (assoc message :conversation-id conversation-id)])
conversation-id)
(jdbc/with-transaction [tx conn]
(let [conversation-id (str (UUID/randomUUID))]
(sql/insert! tx :message (-> message
(assoc :uid from :conversation-id conversation-id)
(dissoc :from :to))
(:options db))
(sql/insert-multi! tx :conversation
[:notifications :uid :conversation_id]
[[1 to conversation-id]
[0 from conversation-id]])
conversation-id))))))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc])
(:import (java.util UUID)))
(defmethod dispatch :start-conversation
[[_ db {:keys [to from] :as message}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversing (jdbc/execute-one! conn-opts ["SELECT a.conversation_id
FROM conversation a
JOIN conversation b
ON a.conversation_id = b.conversation_id
WHERE a.uid = ? AND b.uid = ?" to from])]
(if-let [conversation-id (:conversation/conversation-id conversing)]
(do
(dispatch [:insert-message conn-opts (assoc message :conversation-id conversation-id)])
conversation-id)
(jdbc/with-transaction [tx conn]
(let [conversation-id (str (UUID/randomUUID))]
(sql/insert! tx :message (-> message
(assoc :uid from :conversation-id conversation-id)
(dissoc :from :to))
(:options db))
(sql/insert-multi! tx :conversation
[:notifications :uid :conversation_id]
[[1 to conversation-id]
[0 from conversation-id]])
conversation-id))))))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc])
(:import (java.util UUID)))
(defmethod dispatch :start-conversation
[[_ db {:keys [to from] :as message}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversing (jdbc/execute-one! conn-opts ["SELECT conversation_id
FROM conversation a
JOIN conversation b
ON a.conversation_id = b.conversation_id
WHERE a.uid = ? AND b.uid = ?" to from])]
(if-let [conversation-id (:conversation/conversation-id conversing)]
(do
(dispatch [:insert-message conn-opts (assoc message :conversation-id conversation-id)])
conversation-id)
(jdbc/with-transaction [tx conn]
(let [conversation-id (str (UUID/randomUUID))]
(sql/insert! tx :message (-> message
(assoc :uid from :conversation-id conversation-id)
(dissoc :from :to))
(:options db))
(sql/insert-multi! tx :conversation
[:notifications :uid :conversation_id]
[[1 to conversation-id]
[0 from conversation-id]])
conversation-id))))))
jacekschae/learn-reitit-course-files
(ns cheffy.conversation.db
(:require [next.jdbc.sql :as sql]
[next.jdbc :as jdbc])
(:import (java.util UUID)))
(defmethod dispatch :start-conversation
[[_ db {:keys [to from] :as message}]]
(with-open [conn (jdbc/get-connection db)]
(let [conn-opts (jdbc/with-options conn (:options db))
conversing (jdbc/execute-one! conn-opts ["SELECT conversation_id
FROM conversation a
JOIN conversation b
ON a.conversation_id = b.conversation_id
WHERE a.uid = ? AND b.uid = ?" to from])]
(if-let [conversation-id (:conversation/conversation-id conversing)]
(do
(dispatch [:insert-message conn-opts (assoc message :conversation-id conversation-id)])
conversation-id)
(jdbc/with-transaction [tx conn]
(let [conversation-id (str (UUID/randomUUID))]
(sql/insert! tx :message (-> message
(assoc :uid from :conversation-id conversation-id)
(dissoc :from :to))
(:options db))
(sql/insert-multi! tx :conversation
[:notifications :uid :conversation_id]
[[1 to conversation-id]
[0 from conversation-id]])
conversation-id))))))