lib.jdbc

JDBC connections, prepared statements, eager queries, and cursors

Use database specifications, URLs, data sources, or existing connections through one protocol-oriented JDBC API.

1 Overview

lib.jdbc normalizes connection inputs and query forms. Queries may be SQL strings, SQL vectors with parameters, or prepared statements. Results can be fetched eagerly or streamed through an explicit cursor.

2 Walkthrough

2.1 Open a connection

(require '[lib.jdbc :as jdbc])

(def dbspec
  {:vendor "postgresql"
   :host "localhost"
   :port 5432
   :name "application"
   :user "application"})

(with-open [connection (jdbc/connection dbspec)]
  (jdbc/fetch-one connection
                  ["select ? as value" 42]))

2.2 Execute and fetch

(with-open [connection (jdbc/connection dbspec)]
  (jdbc/execute connection
                ["insert into events(kind) values (?)" "login"])
  (jdbc/fetch connection
              ["select * from events where kind = ?" "login"]))

2.3 Stream large results

fetch-lazy returns a cursor. Keep the cursor inside with-open so its statement and result set are released even when iteration stops early.

(with-open [connection (jdbc/connection dbspec)
            cursor (jdbc/fetch-lazy connection
                                    "select * from events")]
  (doseq [row (take 100 (jdbc/cursor->lazyseq cursor))]
    (process-row row)))

connection ^

[dbspec] [dbspec options]

Added 4.0

creates a connection to a database.

v 4.0

(defn connection
  ([dbspec] (connection dbspec {}))
  ([dbspec options]
   (let [^Connection conn (protocol/-connection dbspec)
         options (merge (when (map? dbspec) dbspec) options)]

     ;; Set readonly flag if it found on the options map
     (some->> (:read-only options)
              (.setReadOnly conn))

     ;; Set the concrete isolation level if it found
     ;; on the options map
     (some->> (:isolation-level options)
              (get constants/isolation-levels)
              (.setTransactionIsolation conn))

     ;; Set the schema if it found on the options map
     (some->> (:schema options)
              (.setSchema conn))
     
     (types/->connection conn))))

link
(with-redefs [protocol/-connection (constantly (mock-conn))]
  (connection {}))
=> #(satisfies? protocol/IConnection %)

cursor->lazyseq ^

[cursor] [cursor opts]

Added 4.0

transform a cursor in a lazyseq.

v 4.0

(defn cursor->lazyseq
  ([cursor] (impl/cursor->lazyseq cursor {}))
  ([cursor opts] (impl/cursor->lazyseq cursor opts)))

link
(with-redefs [lib.jdbc.impl/cursor->lazyseq (constantly [])]
  (cursor->lazyseq :cursor))
=> []

execute ^

[conn q] [conn q opts]

Added 4.0

execute a query and return a number of rows affected.

v 4.0

(defn execute
  ([conn q] (execute conn q {}))
  ([conn q opts]
   (let [rconn (protocol/-connection conn)]
     (protocol/-execute q rconn opts))))

link
(with-redefs [protocol/-connection (constantly (mock-conn))
              protocol/-execute (constantly 1)]
  (execute (mock-conn) "sql"))
=> 1

fetch ^

[conn q] [conn q opts]

Added 4.0

fetch eagerly results executing a query.

v 4.0

(defn fetch
  ([conn q] (fetch conn q {}))
  ([conn q opts]
   (let [rconn (protocol/-connection conn)]
     (protocol/-fetch q rconn opts))))

link
(with-redefs [protocol/-connection (constantly (mock-conn))
              protocol/-fetch (constantly [])]
  (fetch (mock-conn) "sql"))
=> []

fetch-lazy ^

[conn q] [conn q opts]

Added 4.0

fetch lazily results executing a query.

v 4.0

(defn fetch-lazy
  ([conn q] (fetch-lazy conn q {}))
  ([conn q opts]
   (let [^Connection conn (protocol/-connection conn)
         ^PreparedStatement stmt (protocol/-prepared-statement q conn opts)]
     (types/->cursor stmt))))

link
(with-redefs [protocol/-connection (constantly (mock-conn))
              protocol/-prepared-statement (constantly (mock-pstmt))]
  (fetch-lazy (mock-conn) "sql"))
=> (partial instance? lib.jdbc.types.Cursor)

fetch-one ^

[conn q] [conn q opts]

Added 4.0

fetch eagerly one restult executing a query.

v 4.0

(defn fetch-one
  ([conn q] (fetch-one conn q {}))
  ([conn q opts]
   (first (fetch conn q opts))))

link
(with-redefs [protocol/-connection (constantly (mock-conn))
              protocol/-fetch (constantly [1])]
  (fetch-one (mock-conn) "sql"))
=> 1

prepared-statement ^

[conn sqlvec] [conn sqlvec options]

Added 4.0

given a string or parametrized sql in sqlvec format return an instance of prepared statement.

v 4.0

(defn prepared-statement
  ([conn sqlvec] (prepared-statement conn sqlvec {}))
  ([conn sqlvec options]
   (let [conn (protocol/-connection conn)]
     (protocol/-prepared-statement sqlvec conn options))))

link
(with-redefs [protocol/-connection (constantly (mock-conn))
              protocol/-prepared-statement (constantly (mock-pstmt))]
  (prepared-statement (mock-conn) "sql"))
=> (partial instance? PreparedStatement)

prepared-statement? ^

[obj]

Added 4.0

check if specified object is prepared statement.

v 4.0

(defn prepared-statement?
  [obj]
  (instance? PreparedStatement obj))

link
(prepared-statement? (mock-pstmt)) => true

isolation-levels
resultset-options

isolation-levels ^

NONE

(def ^{:doc "Transaction isolation levels" :static true}
  isolation-levels)

link

resultset-options ^

NONE

(def ^{:doc "ResultSet keyword constants" :static true}
  resultset-options)

link

catalog-name
db-major-version
db-minor-version
db-product-name
db-product-version
driver-name
driver-version
is-readonly?
is-valid?
isolation-level
network-timeout
schema-name
vendor-name

catalog-name ^

[c]

Added 4.0

given a connection, get a catalog name.

v 4.0

(defn catalog-name
  [c]
  (let [^java.sql.Connection conn (protocol/-connection c)]
    (.getCatalog conn)))

link
(catalog-name (mock-conn)) => "catalog"

db-major-version ^

[c]

Added 4.0

given a connection, return a database major version number.

v 4.0

(defn db-major-version
  [c]
  (let [^java.sql.DatabaseMetaData meta (protocol/-get-database-metadata c)]
    (.getDatabaseMajorVersion meta)))

link
(db-major-version (mock-conn)) => 10

db-minor-version ^

[c]

Added 4.0

given a connection, return a database minor version number.

v 4.0

(defn db-minor-version
  [c]
  (let [^java.sql.DatabaseMetaData meta (protocol/-get-database-metadata c)]
    (.getDatabaseMinorVersion meta)))

link
(db-minor-version (mock-conn)) => 1

db-product-name ^

[c]

Added 4.0

given a connection, return a database product name.

v 4.0

(defn db-product-name
  [c]
  (let [^java.sql.DatabaseMetaData meta (protocol/-get-database-metadata c)]
    (.getDatabaseProductName meta)))

link
(db-product-name (mock-conn)) => "PostgreSQL"

db-product-version ^

[c]

Added 4.0

given a connection, return a database product version.

v 4.0

(defn db-product-version
  [c]
  (let [^java.sql.DatabaseMetaData meta (protocol/-get-database-metadata c)]
    (.getDatabaseProductVersion meta)))

link
(db-product-version (mock-conn)) => "10.1"

driver-name ^

[c]

Added 4.0

given a connection, return a current driver name

v 4.0

(defn driver-name
  [c]
  (let [^java.sql.DatabaseMetaData meta (protocol/-get-database-metadata c)]
    (.getDriverName meta)))

link
(driver-name (mock-conn)) => "pgjdbc"

driver-version ^

[c]

Added 4.0

given a connection, return a current driver version

v 4.0

(defn driver-version
  [c]
  (let [^java.sql.DatabaseMetaData meta (protocol/-get-database-metadata c)]
    (.getDriverVersion meta)))

link
(driver-version (mock-conn)) => "9.4"

is-readonly? ^

[c]

Added 4.0

returns true if a current connection is in read-only model.

v 4.0

(defn is-readonly?
  [c]
  (let [^java.sql.Connection conn (protocol/-connection c)]
    (.isReadOnly conn)))

link
(is-readonly? (mock-conn)) => true

is-valid? ^

[c] [c timeout]

Added 4.0

given a connection, return true if connection has not ben closed it still valid.

v 4.0

(defn is-valid?
  ([c]
     (is-valid? c 0))
  ([c ^long timeout]
     (let [^java.sql.Connection conn (protocol/-connection c)]
       (.isValid conn timeout))))

link
(is-valid? (mock-conn)) => true

isolation-level ^

[c]

Added 4.0

given a connection, get a current isolation level.

v 4.0

(defn isolation-level
  [c]
  (let [^java.sql.Connection conn (protocol/-connection c)
        ilvalue (.getTransactionIsolation conn)]
    (condp = ilvalue
      java.sql.Connection/TRANSACTION_READ_UNCOMMITTED :read-commited
      java.sql.Connection/TRANSACTION_REPEATABLE_READ  :repeatable-read
      java.sql.Connection/TRANSACTION_SERIALIZABLE     :serializable
      :none)))

link
(isolation-level (mock-conn)) => :read-commited

network-timeout ^

[c]

Added 4.0

given a connection, get network timeout.

v 4.0

(defn network-timeout
  [c]
  (let [^java.sql.Connection conn (protocol/-connection c)]
    (.getNetworkTimeout conn)))

link
(network-timeout (mock-conn)) => 1000

schema-name ^

[c]

Added 4.0

given a connection, get a schema name.

v 4.0

(defn schema-name
  [c]
  (let [^java.sql.Connection conn (protocol/-connection c)]
    (.getSchema conn)))

link
(schema-name (mock-conn)) => "schema"

vendor-name ^

[c]

Added 4.0

get connection vendor name.

v 4.0

(defn vendor-name
  [c]
  (let [^java.sql.DatabaseMetaData meta (:metadata c)]
    (.getDatabaseProductName meta)))

link
(vendor-name {:metadata (mock-conn-meta)}) => "PostgreSQL"

result-set->lazyseq
result-set->vector

result-set->lazyseq ^

[conn rs {:keys [identifiers as-rows? header?], :or {identifiers str/lower-case, as-rows? false, header? false}, :as options}]

Added 4.0

function that wraps result in a lazy seq.

v 4.0

(defn result-set->lazyseq
  [conn ^ResultSet rs {:keys [identifiers as-rows? header?]
                       :or {identifiers str/lower-case
                            as-rows? false
                            header? false}
                       :as options}]
  (let [^ResultSetMetaData metadata (.getMetaData rs)
        idseq (range 1 (inc (.getColumnCount metadata)))
        labels (mapv (fn [^long i] (.getColumnLabel metadata i)) idseq)
        keyseq (mapv (comp keyword identifiers) labels)
        values (fn []
                 (mapv (fn [^long i]
                         (-> (.getObject rs i)
                             (protocol/-from-sql-type conn metadata i)))
                       idseq))
        rows (fn thisfn []
               (when (.next rs)
                 (cons (values) (lazy-seq (thisfn)))))
        records (fn thisfn []
                  (when (.next rs)
                    (-> (zipmap keyseq (values))
                        (cons (lazy-seq (thisfn))))))
        header (mapv identifiers labels)]
    (if-not as-rows?
      (records)
      (if-not header?
        (rows)
        (cons header (lazy-seq (rows)))))))

link
(result-set->lazyseq (mock-conn) (mock-result-set) {})
=> [{:col "val"}]

result-set->vector ^

[conn rs options]

Added 4.0

function that evaluates a result into one clojure persistent vector.

v 4.0

(defn result-set->vector
  [conn ^ResultSet rs options]
  (vec (result-set->lazyseq conn rs options)))

link
(result-set->vector (mock-conn) (mock-result-set) {})
=> [{:col "val"}]

->connection
->cursor

->connection ^

[conn]

Added 4.0

create a connection wrapper.

v 4.0

(defn ->connection
  [^Connection conn]
  (reify
    protocol/IConnection
    (-connection [_] conn)

    protocol/IDatabaseMetadata
    (-get-database-metadata [_]
      (.getMetaData conn))

    java.io.Closeable
    (close [_]
      (.close conn))))

link
(->connection (mock-conn))
=> #(satisfies? protocol/IConnection %)

->cursor ^

[stmt]

Added 4.0

creates a cursor from prepared statement

v 4.0

(defn ->cursor
  [^PreparedStatement stmt]
  (Cursor. stmt))

link
(->cursor (mock-stmt))
=> #(satisfies? protocol/IConnection %)

lib.jdbc

1 Overview

2 Walkthrough

2.1 Open a connection

2.2 Execute and fetch

2.3 Stream large results

3 API

connection ^

cursor->lazyseq ^

execute ^

fetch ^

fetch-lazy ^

fetch-one ^

prepared-statement ^

prepared-statement? ^

isolation-levels ^

NONE

resultset-options ^

NONE

catalog-name ^

db-major-version ^

db-minor-version ^

db-product-name ^

db-product-version ^

driver-name ^

driver-version ^

is-readonly? ^

is-valid? ^

isolation-level ^

network-timeout ^

schema-name ^

vendor-name ^

result-set->lazyseq ^

result-set->vector ^

->connection ^

->cursor ^

Related