Back

-execute-all (clj)

(source)

protocol

(-execute-all this sql-params opts)
Executes the SQL and produces (by default) a vector of fully-realized, datafiable hash maps from the `ResultSet`.

Examples

next-jdbc
  What's left to be tested:
  * ReadableColumn protocol extension point"
  (:require [clojure.core.protocols :as core-p]
            [clojure.datafy :as d]
            [clojure.string :as str]
            [clojure.test :refer [deftest is testing use-fixtures]]
            [next.jdbc.protocols :as p]
            [next.jdbc.result-set :as rs]
            [next.jdbc.specs :as specs]
            [next.jdbc.test-fixtures :refer [with-test-db ds column
                                              default-options
                                              derby? mssql? mysql? postgres?]])
  (:import (java.sql ResultSet ResultSetMetaData)))

(deftest test-map-row-builder
  (testing "default row builder"
    (let [row (p/-execute-one (ds)
                              ["select * from fruit where id = ?" 1]
                              (default-options))]
      (is (map? row))
      (is (contains? row (column :FRUIT/GRADE)))
      (is (nil? ((column :FRUIT/GRADE) row)))
      (is (= 1 ((column :FRUIT/ID) row)))
      (is (= "Apple" ((column :FRUIT/NAME) row))))
    (let [rs (p/-execute-all (ds)
                             ["select * from fruit order by id"]
                             (default-options))]
      (is (every? map? rs))
      (is (= 1 ((column :FRUIT/ID) (first rs))))
      (is (= "Apple" ((column :FRUIT/NAME) (first rs))))
      (is (= 4 ((column :FRUIT/ID) (last rs))))
      (is (= "Orange" ((column :FRUIT/NAME) (last rs))))))
  (testing "unqualified row builder"
    (let [row (p/-execute-one (ds)
                              ["select * from fruit where id = ?" 2]
                              {:builder-fn rs/as-unqualified-maps})]
      (is (map? row))
      (is (contains? row (column :COST)))
      (is (nil? ((column :COST) row)))
      (is (= 2 ((column :ID) row)))
      (is (= "Banana" ((column :NAME) row)))))
  (testing "lower-case row builder"
    (let [row (p/-execute-one (ds)
                              ["select * from fruit where id = ?" 3]
                              (assoc (default-options)
                                     :builder-fn rs/as-lower-maps))]
      (is (map? row))
      (is (contains? row :fruit/appearance))
      (is (nil? (:fruit/appearance row)))
      (is (= 3 (:fruit/id row)))
      (is (= "Peach" (:fruit/name row)))))
  (testing "unqualified lower-case row builder"
    (let [row (p/-execute-one (ds)
                              ["select * from fruit where id = ?" 4]
                              {:builder-fn rs/as-unqualified-lower-maps})]
      (is (map? row))
      (is (= 4 (:id row)))
      (is (= "Orange" (:name row)))))
  (testing "kebab-case row builder"
    (let [row (p/-execute-one (ds)
                              ["select id,name,appearance as looks_like from fruit where id = ?" 3]
                              (assoc (default-options)
                                     :builder-fn rs/as-kebab-maps))]
      (is (map? row))
      (is (contains? row :fruit/looks-like))
      (is (nil? (:fruit/looks-like row)))
      (is (= 3 (:fruit/id row)))
      (is (= "Peach" (:fruit/name row)))))
  (testing "unqualified kebab-case row builder"
    (let [row (p/-execute-one (ds)
                              ["select id,name,appearance as looks_like from fruit where id = ?" 4]
                              {:builder-fn rs/as-unqualified-kebab-maps})]
      (is (map? row))
      (is (contains? row :looks-like))
      (is (= "juicy" (:looks-like row)))
      (is (= 4 (:id row)))
      (is (= "Orange" (:name row)))))
  (testing "custom row builder 1"
    (let [row (p/-execute-one (ds)
                              ["select fruit.*, id + 100 as newid from fruit where id = ?" 3]
                              (assoc (default-options)
                                     :builder-fn rs/as-modified-maps
                                     :label-fn str/lower-case
                                     :qualifier-fn identity))]
      (is (map? row))
      (is (contains? row (column :FRUIT/appearance)))
      (is (nil? ((column :FRUIT/appearance) row)))
      (is (= 3 ((column :FRUIT/id) row)))
      (is (= 103 (:newid row))) ; no table name here
      (is (= "Peach" ((column :FRUIT/name) row)))))
  (testing "custom row builder 2"
    (let [row (p/-execute-one (ds)
                              ["select fruit.*, id + 100 as newid from fruit where id = ?" 3]
                              (assoc (default-options)
                                     :builder-fn rs/as-modified-maps
                                     :label-fn str/lower-case
                                     :qualifier-fn (constantly "vegetable")))]
      (is (map? row))
      (is (contains? row :vegetable/appearance))
      (is (nil? (:vegetable/appearance row)))
      (is (= 3 (:vegetable/id row)))
      (is (= 103 (:vegetable/newid row))) ; constant qualifier here
      (is (= "Peach" (:vegetable/name row)))))
  (testing "adapted row builder"
    (let [row (p/-execute-one (ds)
                              ["select * from fruit where id = ?" 3]
                              (assoc
                               (default-options)
                               :builder-fn (rs/as-maps-adapter
                                            rs/as-modified-maps
                                            (fn [^ResultSet rs
                                                 ^ResultSetMetaData rsmeta
                                                 ^Integer i]
                                              (condp = (.getColumnType rsmeta i)
                                                     java.sql.Types/VARCHAR
                                                     (.getString rs i)
                                                     java.sql.Types/INTEGER
                                                     (.getLong rs i)
                                                     (.getObject rs i))))
                               :label-fn str/lower-case
                               :qualifier-fn identity))]
      (is (map? row))
      (is (contains? row (column :FRUIT/appearance)))
      (is (nil? ((column :FRUIT/appearance) row)))
      (is (= 3 ((column :FRUIT/id) row)))
      (is (= "Peach" ((column :FRUIT/name) row))))
    (let [builder (rs/as-maps-adapter
                   rs/as-modified-maps
                   (fn [^ResultSet rs _ ^Integer i]
                     (.getObject rs i)))
          row (p/-execute-one (ds)
                              ["select * from fruit where id = ?" 3]
                              (assoc
                               (default-options)
                               :builder-fn (rs/as-maps-adapter
                                            builder
                                            (fn [^ResultSet rs
                                                 ^ResultSetMetaData rsmeta
                                                 ^Integer i]
                                              (condp = (.getColumnType rsmeta i)
                                                     java.sql.Types/VARCHAR
                                                     (.getString rs i)
                                                     java.sql.Types/INTEGER
                                                     (.getLong rs i)
                                                     (.getObject rs i))))
                               :label-fn str/lower-case
                               :qualifier-fn identity))]
      (is (map? row))
      (is (contains? row (column :FRUIT/appearance)))
      (is (nil? ((column :FRUIT/appearance) row)))
      (is (= 3 ((column :FRUIT/id) row)))
      (is (= "Peach" ((column :FRUIT/name) row))))))

(deftest custom-map-builder
  (let [row (p/-execute-one (ds)
                            ["select * from fruit where appearance = ?" "red"]
                            {:builder-fn fruit-builder})]
    (is (instance? Fruit row))
    (is (= 1 (:id row))))
  (let [rs (p/-execute-all (ds)
                           ["select * from fruit where appearance = ?" "red"]
                           {:builder-fn fruit-builder})]
    (is (every? #(instance? Fruit %) rs))
    (is (= 1 (count rs)))
    (is (= 1 (:id (first rs))))))

(deftest clob-reading
  (when-not (or (mssql?) (mysql?) (postgres?)) ; no clob in these
    (with-open [con (p/get-connection (ds) {})]
      (try
        (p/-execute-one con ["DROP TABLE CLOBBER"] {})
        (catch Exception _))
      (p/-execute-one con [(str "
CREATE TABLE CLOBBER (
  ID INTEGER,
  STUFF CLOB
)")]
                      {})
      (p/-execute-one con
                      [(str "insert into clobber (id, stuff)"
                            "values (?,?), (?,?)")
                       1 "This is some long string"
                       2 "This is another long string"]
                      {})
      (is (= "This is some long string"
             (-> (p/-execute-all con
                                 ["select * from clobber where id = ?" 1]
                                 {:builder-fn (rs/as-maps-adapter
                                               rs/as-unqualified-lower-maps
                                               rs/clob-column-reader)})
                 (first)
                 :stuff))))))
seancorfield/next-jdbc
(ns ^:no-doc next.jdbc.default-options
  "Implementation of default options logic."
  (:require [next.jdbc.protocols :as p]))

(extend-protocol p/Executable
  DefaultOptions
  (-execute [this sql-params opts]
            (p/-execute (:connectable this) sql-params
                        (merge (:options this) opts)))
  (-execute-one [this sql-params opts]
                (p/-execute-one (:connectable this) sql-params
                                (merge (:options this) opts)))
  (-execute-all [this sql-params opts]
                (p/-execute-all (:connectable this) sql-params
                                (merge (:options this) opts))))