Back
with-transaction (clj)
(source)macro
(with-transaction [sym transactable opts] & body)
Given a transactable object, gets a connection and binds it to `sym`,
then executes the `body` in that context, committing any changes if the body
completes successfully, otherwise rolling back any changes made.
Like `with-open`, if `with-transaction` creates a new `Connection` object,
it will automatically close it for you.
If you are working with default options via `with-options`, you might want
to use `with-transaction+options` instead.
The options map supports:
* `:isolation` -- `:none`, `:read-committed`, `:read-uncommitted`,
`:repeatable-read`, `:serializable`,
* `:read-only` -- `true` / `false` (`true` will make the `Connection` readonly),
* `:rollback-only` -- `true` / `false` (`true` will make the transaction
rollback, even if it would otherwise succeed).
Examples
next-jdbc
(ns next.jdbc-test
"Basic tests for the primary API of `next.jdbc`."
(:require [clojure.core.reducers :as r]
[clojure.string :as str]
[clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.connection :as c]
[next.jdbc.test-fixtures
:refer [with-test-db db ds column
default-options stored-proc?
derby? hsqldb? jtds? mssql? mysql? postgres? sqlite?]]
[next.jdbc.prepare :as prep]
[next.jdbc.result-set :as rs]
[next.jdbc.specs :as specs]
[next.jdbc.types :as types])
(:import (com.zaxxer.hikari HikariDataSource)
(com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)
(java.sql ResultSet ResultSetMetaData)))
(deftest basic-tests
;; use ds-opts instead of (ds) anywhere you want default options applied:
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(testing "plan"
(is (= "Apple"
(reduce (fn [_ row] (reduced (:name row)))
nil
(jdbc/plan
ds-opts
["select * from fruit where appearance = ?" "red"]))))
(is (= "Banana"
(reduce (fn [_ row] (reduced (:no-such-column row "Banana")))
nil
(jdbc/plan
ds-opts
["select * from fruit where appearance = ?" "red"])))))
(testing "execute-one!"
(is (nil? (jdbc/execute-one!
(ds)
["select * from fruit where appearance = ?" "neon-green"])))
(is (= "Apple" ((column :FRUIT/NAME)
(jdbc/execute-one!
ds-opts
["select * from fruit where appearance = ?" "red"]))))
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds-opts
["select appearance as looks_like from fruit where id = ?" 1]
jdbc/snake-kebab-opts))))
(let [ds' (jdbc/with-options ds-opts jdbc/snake-kebab-opts)]
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds'
["select appearance as looks_like from fruit where id = ?" 1])))))
(jdbc/with-transaction+options [ds' (jdbc/with-options ds-opts jdbc/snake-kebab-opts)]
(is (= (merge (default-options) jdbc/snake-kebab-opts)
(:options ds')))
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds'
["select appearance as looks_like from fruit where id = ?" 1])))))
(is (= "red" (:looks-like
(jdbc/execute-one!
ds-opts
["select appearance as looks_like from fruit where id = ?" 1]
jdbc/unqualified-snake-kebab-opts)))))
(testing "execute!"
(let [rs (jdbc/execute!
ds-opts
["select * from fruit where appearance = ?" "neon-green"])]
(is (vector? rs))
(is (= [] rs)))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit where appearance = ?" "red"])]
(is (= 1 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-maps})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-arrays})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? qualified-keyword? (first rs)))
;; :FRUIT/ID should be first column
(is (= (column :FRUIT/ID) (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with adapter"
(let [rs (jdbc/execute! ; test again, with adapter and lower columns
ds-opts
["select * from fruit order by id"]
{:builder-fn (rs/as-arrays-adapter
rs/as-lower-arrays
(fn [^ResultSet rs _ ^Integer i]
(.getObject rs i)))})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? qualified-keyword? (first rs)))
;; :fruit/id should be first column
(is (= :fruit/id (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with unqualified"
(let [rs (jdbc/execute!
(ds)
["select * from fruit order by id"]
{:builder-fn rs/as-unqualified-maps})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :ID) (first rs))))
(is (= 4 ((column :ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-unqualified-arrays})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? simple-keyword? (first rs)))
;; :ID should be first column
(is (= (column :ID) (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with :max-rows / :maxRows"
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:max-rows 2})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 2 ((column :FRUIT/ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:statement {:maxRows 2}})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 2 ((column :FRUIT/ID) (last rs)))))))
(testing "prepare"
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare
con
["select * from fruit order by id"]
(default-options))]
(jdbc/execute! ps))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare
con
["select * from fruit where id = ?"]
(default-options))]
(jdbc/execute! (prep/set-parameters ps [4]) nil {}))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 (count rs)))
(is (= 4 ((column :FRUIT/ID) (first rs))))))
(testing "statement"
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))]
(jdbc/execute! (prep/statement con (default-options))
["select * from fruit order by id"]))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))]
(jdbc/execute! (prep/statement con (default-options))
["select * from fruit where id = 4"]))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 (count rs)))
(is (= 4 ((column :FRUIT/ID) (first rs))))))
(testing "transact"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/transact (ds)
(fn [t] (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))
{:rollback-only true})))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "with-transaction rollback-only"
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(is (jdbc/active-tx?) "should be in a transaction")
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con {:rollback-only true}]
(is (jdbc/active-tx?) "should be in a transaction")
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction exception"
(is (thrown? Throwable
(jdbc/with-transaction [t (ds)]
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])
(is (jdbc/active-tx?) "should be in a transaction")
(throw (ex-info "abort" {})))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (thrown? Throwable
(jdbc/with-transaction [t con]
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])
(is (jdbc/active-tx?) "should be in a transaction")
(throw (ex-info "abort" {})))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction call rollback"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t)
;; still in a next.jdbc TX even tho' we rolled back!
(is (jdbc/active-tx?) "should be in a transaction")
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
;; still in a next.jdbc TX even tho' we rolled back to a save point!
(is (jdbc/active-tx?) "should be in a transaction")
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con)))))))
(deftest issue-146
;; since we use an embedded PostgreSQL data source, we skip this:
(when-not (or (postgres?)
;; and now we skip MS SQL because we can't use the db-spec
;; we'd need to build the jdbcUrl with encryption turned off:
(and (mssql?) (not (jtds?))))
(testing "Hikari and SavePoints"
(with-open [^HikariDataSource ds (c/->pool HikariDataSource
(let [db (db)]
(cond-> db
;; jTDS does not support isValid():
(jtds?)
(assoc :connectionTestQuery "SELECT 1")
;; HikariCP needs username, not user:
(contains? db :user)
(assoc :username (:user db)))))]
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))))
(testing "c3p0 and SavePoints"
(with-open [^PooledDataSource ds (c/->pool ComboPooledDataSource (db))]
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))))))
(deftest execute-batch-tests
(testing "simple batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (jdbc/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]])]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "small batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (jdbc/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 3})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "big batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (jdbc/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 8})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "large batch insert"
(when-not (or (jtds?) (sqlite?))
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (jdbc/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 4
:large true})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))))
(testing "return generated keys"
(when-not (or (mssql?) (sqlite?))
(let [results
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"]
{:return-keys true})]
(let [result (jdbc/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 4
:return-generated-keys true})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))]
(is (= 13 (last results)))
(is (every? map? (butlast results)))
;; Derby and SQLite only return one generated key per batch so there
;; are only three keys, plus the overall count here:
(is (< 3 (count results))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))))
next-jdbc
The tests for the deprecated version of `execute-batch!` are here
as a guard against regressions."
(:require [clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.test-fixtures
:refer [with-test-db ds jtds? mssql? sqlite?]]
[next.jdbc.prepare :as prep]
[next.jdbc.specs :as specs]))
(deftest execute-batch-tests
(testing "simple batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (prep/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]])]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "small batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (prep/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 3})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "big batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (prep/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 8})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "large batch insert"
(when-not (or (jtds?) (sqlite?))
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (prep/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 4
:large true})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))))
(testing "return generated keys"
(when-not (or (mssql?) (sqlite?))
(let [results
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"]
{:return-keys true})]
(let [result (prep/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 4
:return-generated-keys true})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))]
(is (= 13 (last results)))
(is (every? map? (butlast results)))
;; Derby and SQLite only return one generated key per batch so there
;; are only three keys, plus the overall count here:
(is (< 3 (count results))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))))
clj-kondo/clj-kondo
The clojure.java.jdbc/with-db-* functions would all be treated the same way."
(:require [next.jdbc :as jdbc]))
(jdbc/with-transaction [tx (jdbc/get-datasource db) {} {}] ;; 2 or 3 forms
(jdbc/execute! tx ["select * from table where foo = ?" 123]))
(jdbc/with-transaction [tx] ;; 2 or 3 forms
(jdbc/execute! tx ["select * from table where foo = ?" 123]))
(jdbc/with-transaction ;; requires vector for binding
(jdbc/execute! tx ["select * from table where foo = ?" 123]))
(jdbc/with-transaction [[tx] (jdbc/get-datasource db)] ;; requires a symbol
(jdbc/execute! tx ["select * from table where foo = ?" 123]))
seancorfield/next-jdbc
The tests for the deprecated version of `execute-batch!` are here
as a guard against regressions."
(:require [clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.test-fixtures
:refer [with-test-db ds jtds? mssql? sqlite?]]
[next.jdbc.prepare :as prep]
[next.jdbc.specs :as specs]))
(deftest execute-batch-tests
(testing "simple batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (prep/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]])]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "small batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (prep/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 3})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "big batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (prep/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 8})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "large batch insert"
(when-not (or (jtds?) (sqlite?))
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (prep/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 4
:large true})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))))
(testing "return generated keys"
(when-not (or (mssql?) (sqlite?))
(let [results
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"]
{:return-keys true})]
(let [result (prep/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 4
:return-generated-keys true})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))]
(is (= 13 (last results)))
(is (every? map? (butlast results)))
;; Derby and SQLite only return one generated key per batch so there
;; are only three keys, plus the overall count here:
(is (< 3 (count results))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))))
seancorfield/next-jdbc
(ns next.jdbc-test
"Basic tests for the primary API of `next.jdbc`."
(:require [clojure.core.reducers :as r]
[clojure.string :as str]
[clojure.test :refer [deftest is testing use-fixtures]]
[next.jdbc :as jdbc]
[next.jdbc.connection :as c]
[next.jdbc.test-fixtures
:refer [with-test-db db ds column
default-options stored-proc?
derby? hsqldb? jtds? mssql? mysql? postgres? sqlite?]]
[next.jdbc.prepare :as prep]
[next.jdbc.result-set :as rs]
[next.jdbc.specs :as specs]
[next.jdbc.types :as types])
(:import (com.zaxxer.hikari HikariDataSource)
(com.mchange.v2.c3p0 ComboPooledDataSource PooledDataSource)
(java.sql ResultSet ResultSetMetaData)))
(deftest basic-tests
;; use ds-opts instead of (ds) anywhere you want default options applied:
(let [ds-opts (jdbc/with-options (ds) (default-options))]
(testing "plan"
(is (= "Apple"
(reduce (fn [_ row] (reduced (:name row)))
nil
(jdbc/plan
ds-opts
["select * from fruit where appearance = ?" "red"]))))
(is (= "Banana"
(reduce (fn [_ row] (reduced (:no-such-column row "Banana")))
nil
(jdbc/plan
ds-opts
["select * from fruit where appearance = ?" "red"])))))
(testing "execute-one!"
(is (nil? (jdbc/execute-one!
(ds)
["select * from fruit where appearance = ?" "neon-green"])))
(is (= "Apple" ((column :FRUIT/NAME)
(jdbc/execute-one!
ds-opts
["select * from fruit where appearance = ?" "red"]))))
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds-opts
["select appearance as looks_like from fruit where id = ?" 1]
jdbc/snake-kebab-opts))))
(let [ds' (jdbc/with-options ds-opts jdbc/snake-kebab-opts)]
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds'
["select appearance as looks_like from fruit where id = ?" 1])))))
(jdbc/with-transaction+options [ds' (jdbc/with-options ds-opts jdbc/snake-kebab-opts)]
(is (= (merge (default-options) jdbc/snake-kebab-opts)
(:options ds')))
(is (= "red" (:fruit/looks-like
(jdbc/execute-one!
ds'
["select appearance as looks_like from fruit where id = ?" 1])))))
(is (= "red" (:looks-like
(jdbc/execute-one!
ds-opts
["select appearance as looks_like from fruit where id = ?" 1]
jdbc/unqualified-snake-kebab-opts)))))
(testing "execute!"
(let [rs (jdbc/execute!
ds-opts
["select * from fruit where appearance = ?" "neon-green"])]
(is (vector? rs))
(is (= [] rs)))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit where appearance = ?" "red"])]
(is (= 1 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-maps})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-arrays})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? qualified-keyword? (first rs)))
;; :FRUIT/ID should be first column
(is (= (column :FRUIT/ID) (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with adapter"
(let [rs (jdbc/execute! ; test again, with adapter and lower columns
ds-opts
["select * from fruit order by id"]
{:builder-fn (rs/as-arrays-adapter
rs/as-lower-arrays
(fn [^ResultSet rs _ ^Integer i]
(.getObject rs i)))})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? qualified-keyword? (first rs)))
;; :fruit/id should be first column
(is (= :fruit/id (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with unqualified"
(let [rs (jdbc/execute!
(ds)
["select * from fruit order by id"]
{:builder-fn rs/as-unqualified-maps})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :ID) (first rs))))
(is (= 4 ((column :ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:builder-fn rs/as-unqualified-arrays})]
(is (every? vector? rs))
(is (= 5 (count rs)))
(is (every? #(= 5 (count %)) rs))
;; columns come first
(is (every? simple-keyword? (first rs)))
;; :ID should be first column
(is (= (column :ID) (ffirst rs)))
;; and all its corresponding values should be ints
(is (every? int? (map first (rest rs))))
(is (every? string? (map second (rest rs))))))
(testing "execute! with :max-rows / :maxRows"
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:max-rows 2})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 2 ((column :FRUIT/ID) (last rs)))))
(let [rs (jdbc/execute!
ds-opts
["select * from fruit order by id"]
{:statement {:maxRows 2}})]
(is (every? map? rs))
(is (every? meta rs))
(is (= 2 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 2 ((column :FRUIT/ID) (last rs)))))))
(testing "prepare"
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare
con
["select * from fruit order by id"]
(default-options))]
(jdbc/execute! ps))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))
ps (jdbc/prepare
con
["select * from fruit where id = ?"]
(default-options))]
(jdbc/execute! (prep/set-parameters ps [4]) nil {}))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 (count rs)))
(is (= 4 ((column :FRUIT/ID) (first rs))))))
(testing "statement"
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))]
(jdbc/execute! (prep/statement con (default-options))
["select * from fruit order by id"]))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 4 (count rs)))
(is (= 1 ((column :FRUIT/ID) (first rs))))
(is (= 4 ((column :FRUIT/ID) (last rs)))))
;; default options do not flow over get-connection
(let [rs (with-open [con (jdbc/get-connection (ds))]
(jdbc/execute! (prep/statement con (default-options))
["select * from fruit where id = 4"]))]
(is (every? map? rs))
(is (every? meta rs))
(is (= 1 (count rs)))
(is (= 4 ((column :FRUIT/ID) (first rs))))))
(testing "transact"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/transact (ds)
(fn [t] (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))
{:rollback-only true})))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "with-transaction rollback-only"
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(is (jdbc/active-tx?) "should be in a transaction")
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con {:rollback-only true}]
(is (jdbc/active-tx?) "should be in a transaction")
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"]))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction exception"
(is (thrown? Throwable
(jdbc/with-transaction [t (ds)]
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])
(is (jdbc/active-tx?) "should be in a transaction")
(throw (ex-info "abort" {})))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (thrown? Throwable
(jdbc/with-transaction [t con]
(jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])
(is (jdbc/active-tx?) "should be in a transaction")
(throw (ex-info "abort" {})))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction call rollback"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t)
;; still in a next.jdbc TX even tho' we rolled back!
(is (jdbc/active-tx?) "should be in a transaction")
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
;; still in a next.jdbc TX even tho' we rolled back to a save point!
(is (jdbc/active-tx?) "should be in a transaction")
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(is (not (jdbc/active-tx?)) "should not be in a transaction")
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t (ds)]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))
(with-open [con (jdbc/get-connection (ds))]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con)))))))
(deftest issue-146
;; since we use an embedded PostgreSQL data source, we skip this:
(when-not (or (postgres?)
;; and now we skip MS SQL because we can't use the db-spec
;; we'd need to build the jdbcUrl with encryption turned off:
(and (mssql?) (not (jtds?))))
(testing "Hikari and SavePoints"
(with-open [^HikariDataSource ds (c/->pool HikariDataSource
(let [db (db)]
(cond-> db
;; jTDS does not support isValid():
(jtds?)
(assoc :connectionTestQuery "SELECT 1")
;; HikariCP needs username, not user:
(contains? db :user)
(assoc :username (:user db)))))]
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))))
(testing "c3p0 and SavePoints"
(with-open [^PooledDataSource ds (c/->pool ComboPooledDataSource (db))]
(testing "with-transaction with unnamed save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t)
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))
(testing "with-transaction with named save point"
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t ds]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! ds ["select * from fruit"]))))
(with-open [con (jdbc/get-connection ds)]
(let [ac (.getAutoCommit con)]
(is (= [{:next.jdbc/update-count 1}]
(jdbc/with-transaction [t con]
(let [save-point (.setSavepoint t (name (gensym)))
result (jdbc/execute! t ["
INSERT INTO fruit (name, appearance, cost, grade)
VALUES ('Pear', 'green', 49, 47)
"])]
(.rollback t save-point)
result))))
(is (= 4 (count (jdbc/execute! con ["select * from fruit"]))))
(is (= ac (.getAutoCommit con))))))))))
(deftest execute-batch-tests
(testing "simple batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (jdbc/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]])]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "small batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (jdbc/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 3})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "big batch insert"
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (jdbc/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 8})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))
(testing "large batch insert"
(when-not (or (jtds?) (sqlite?))
(is (= [1 1 1 1 1 1 1 1 1 13]
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"])]
(let [result (jdbc/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 4
:large true})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"]))))))
(testing "return generated keys"
(when-not (or (mssql?) (sqlite?))
(let [results
(jdbc/with-transaction [t (ds) {:rollback-only true}]
(with-open [ps (jdbc/prepare t ["
INSERT INTO fruit (name, appearance) VALUES (?,?)
"]
{:return-keys true})]
(let [result (jdbc/execute-batch! ps [["fruit1" "one"]
["fruit2" "two"]
["fruit3" "three"]
["fruit4" "four"]
["fruit5" "five"]
["fruit6" "six"]
["fruit7" "seven"]
["fruit8" "eight"]
["fruit9" "nine"]]
{:batch-size 4
:return-generated-keys true})]
(conj result (count (jdbc/execute! t ["select * from fruit"]))))))]
(is (= 13 (last results)))
(is (every? map? (butlast results)))
;; Derby and SQLite only return one generated key per batch so there
;; are only three keys, plus the overall count here:
(is (< 3 (count results))))
(is (= 4 (count (jdbc/execute! (ds) ["select * from fruit"])))))))
luminus-framework/luminus-template
(ns <<project-ns>>.db.core-test
(:require
[<<project-ns>>.db.core :refer [*db*] :as db]
[java-time.pre-java8]
[luminus-migrations.core :as migrations]
[clojure.test :refer :all]
[next.jdbc :as jdbc]
[<<project-ns>>.config :refer [env]]
[mount.core :as mount]))
(deftest test-users
(jdbc/with-transaction [t-conn *db* {:rollback-only true}]
(is (= 1 (db/create-user!
t-conn
{:id "1"
:first_name "Sam"
:last_name "Smith"
:email "sam.smith@example.com"
:pass "pass"}
{})))
(is (= {:id "1"
:first_name "Sam"
:last_name "Smith"
:email "sam.smith@example.com"
:pass "pass"
:admin nil
:last_login nil
:is_active nil}
(db/get-user t-conn {:id "1"} {})))))
retro/penkala
(ns com.verybigthings.penkala.relation.cte-test
(:require [clojure.test :refer [use-fixtures deftest is]]
[com.verybigthings.penkala.next-jdbc :refer [select! select-one! insert!]]
[com.verybigthings.penkala.relation :as r]
[com.verybigthings.penkala.helpers :as h]
[com.verybigthings.penkala.env :as env]
[com.verybigthings.penkala.test-helpers :as th :refer [*env*]]
[next.jdbc :as jdbc]))
(deftest insertable-cte
(jdbc/with-transaction [tx (env/get-db *env*)]
(let [env (env/with-db *env* tx)
insertable-users (r/as-cte
(-> (:users env)
r/->insertable
(r/with-inserts {:email "example@example.com" :name "test user"})))
query (-> insertable-users
(r/select [:email :name]))
res (select! env query)]
(is (= [{:users/email "example@example.com", :users/name "test user"}] res))
(.rollback tx))))
(deftest insertable-cte
(jdbc/with-transaction [tx (env/get-db *env*)]
(let [env (env/with-db *env* tx)
insertable-users (r/as-cte
(-> (:users env)
r/->insertable
(r/with-inserts [{:email "example1@example.com" :name "test user1"}
{:email "example2@example.com" :name "test user2"}])))
query (-> insertable-users
(r/select [:email :name]))
res (select! env query)]
(is (= [{:users/email "example1@example.com", :users/name "test user1"}
{:users/email "example2@example.com", :users/name "test user2"}] res))
(.rollback tx))))
(deftest insertable-cte-with-where-on-select
(jdbc/with-transaction [tx (env/get-db *env*)]
(let [env (env/with-db *env* tx)
insertable-users (r/as-cte
(-> (:users env)
r/->insertable
(r/with-inserts [{:email "example1@example.com" :name "test user1"}
{:email "example2@example.com" :name "test user2"}])))
query (-> insertable-users
(r/select [:email :name])
(r/where [:= :email "example1@example.com"]))
res (select! env query)]
(is (= [{:users/email "example1@example.com", :users/name "test user1"}] res))
(.rollback tx))))
(deftest updatable-cte
(jdbc/with-transaction [tx (env/get-db *env*)]
(let [env (env/with-db *env* tx)
_ (insert! env (-> env :users r/->insertable) [{:email "example1@example.com" :name "test user1"}
{:email "example2@example.com" :name "test user2"}])
updatable-users (r/as-cte
(-> (:users env)
r/->updatable
(r/where [:= :email "example1@example.com"])
(r/with-updates {:email "example1-updated@example.com"})))
query (-> updatable-users
(r/select [:email :name]))
res (select! env query)]
(is (= [{:users/email "example1-updated@example.com", :users/name "test user1"}] res))
(.rollback tx))))
(deftest deletable-cte
(jdbc/with-transaction [tx (env/get-db *env*)]
(let [env (env/with-db *env* tx)
_ (insert! env (-> env :users r/->insertable) [{:email "example1@example.com" :name "test user1"}
{:email "example2@example.com" :name "test user2"}])
deletable-users (r/as-cte
(-> (:users env)
r/->deletable
(r/where [:= :email "example1@example.com"])))
query (-> deletable-users
(r/select [:email :name]))
res (select! env query)]
(is (= [{:users/email "example1@example.com", :users/name "test user1"}] res))
(.rollback tx))))