Extended JDBC type support for JSONB in Postgres

jdbc-types-jsonb

(ns db.jdbc-types-jsonb
  (:require [jdbc.proto]
            [clojure.data.json :as json])
  (:import [org.postgresql.util PGobject]))

;; use the proto/ISQLType to extend a new Postgres type
;; that will be generated from a clojure map type
(extend-protocol jdbc.proto/ISQLType 
  clojure.lang.IPersistentMap

  ;; function is used for setting parameter on sql statement
  ;; index is the ordinal location of this parameter
  ;; want to call as-sql-type on self above to set the parameter
  (set-stmt-parameter! [this conn stmt index]
    (let [prepared-value (jdbc.proto/as-sql-type this conn)]
      (.setObject stmt index prepared-value)))

  ;; converts user type to the sql type
  (as-sql-type [this conn]
    (doto (PGobject.)
      (.setType "jsonb")
      (.setValue (json/write-str this)))))

(extend-protocol jdbc.proto/ISQLResultSetReadColumn
  PGobject
  (from-sql-type [pgobj conn metadata index]
    (let [type (.getType pgobj)
      value (.getValue pgobj)]
      (case type
        "jsonb" (json/read-str value)
        :else value))))