Back
create (clj)
(source)function
(create con sql params {:keys [return-keys result-type concurrency cursors fetch-size max-rows timeout], :as opts})
This is an implementation detail -- use `next.jdbc/prepare` instead.
Given a `Connection`, a SQL string, some parameters, and some options,
return a `PreparedStatement` representing that.
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 connection-tests
(testing "datasource via jdbcUrl"
(when-not (postgres?)
(let [[url etc] (#'c/spec->url+etc (db))
ds (jdbc/get-datasource (assoc etc :jdbcUrl url))]
(cond (derby?) (is (= {:create true} etc))
(mssql?) (is (= (cond-> #{:user :password}
(not (jtds?))
(conj :encrypt :trustServerCertificate))
(set (keys etc))))
(mysql?) (is (= #{:user :password :useSSL :allowMultiQueries}
(disj (set (keys etc)) :disableMariaDbDriver)))
:else (is (= {} etc)))
(is (instance? javax.sql.DataSource ds))
(is (str/index-of (pr-str ds) (str "jdbc:"
(cond (jtds?)
"jtds:sqlserver"
(mssql?)
"sqlserver"
:else
(:dbtype (db))))))
;; checks get-datasource on a DataSource is identity
(is (identical? ds (jdbc/get-datasource ds)))
(with-open [con (jdbc/get-connection ds {})]
(is (instance? java.sql.Connection con)))))))
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)))
(def ^:private test-derby {:dbtype "derby" :dbname "clojure_test_derby" :create true})
(defn- create-clojure-test []
(when test-mysql
(let [mysql (assoc test-mysql :dbname "mysql")]
(println "Creating clojure-test database in MySQL...")
(loop [n 0]
(when (try
(jdbc/execute-one! mysql ["create database if not exists clojure_test"])
false ; done
(catch Throwable t
(when (< 10 n) (throw t))
(println "\t" (ex-message t) "(will retry)")
(Thread/sleep 3000)
true))
(recur (inc n))))
(println "...done!"))))
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)))))
(create-clojure-test)