为PostgreSQL复合类型实现SQLData接口并配置全局连接类型映射

Great question! When working with Clojure, clojure.java.jdbc, and connection pools, you don't have to manually configure type mappings for every connection—there are two clean, scalable ways to set this up once for all connections:

pgjdbc-ng provides a global type mapping registry that you can populate once during application startup. All connections created after this registration will automatically inherit the mapping, which is perfect for shared configurations.

Here's how to implement it:

(import '[com.impossibl.postgres.api.jdbc PGTypeMap]
        '[com.impossibl.postgres.types TypeRegistry])

;; Run this once when your application starts (e.g., in your main function or system init)
(defn register-global-inventory-type []
  (let [type-registry (TypeRegistry/getInstance)
        global-type-map (PGTypeMap/getGlobal)
        inventory-type (.lookupType type-registry "inventory_item")]
    (.registerType global-type-map inventory-type InventoryItem)))

This code fetches PostgreSQL's global type registry, locates your inventory_item composite type, and registers your InventoryItem SQLData implementation to the global type map. Every new connection will use this mapping automatically.

If you need environment-specific mappings or prefer scoping the configuration to your connection pool, use a connection initialization hook. Most connection pools (like HikariCP, which clojure.java.jdbc often uses under the hood) let you run code when a new connection is created.

For HikariCP, here's how to set this up in your DB spec:

(require '[clojure.java.jdbc :as jdbc])
(import '[com.impossibl.postgres.api.jdbc PGConnection]
        '[com.zaxxer.hikari.util.ConnectionInitCallback])

(def db-spec
  {:classname "com.impossibl.postgres.jdbc.PGDriver"
   :subprotocol "postgresql-ng"
   :subname "//localhost:5432/your-database-name"
   :username "your-username"
   :password "your-password"
   ;; Callback to configure type mapping for new connections
   :connection-init-callback
   (reify ConnectionInitCallback
     (initialize [_ conn]
       (let [pg-conn (.unwrap conn PGConnection)
             type-map (.getTypeMap pg-conn)
             inventory-type (.lookupType (.getRegistry pg-conn) "inventory_item")]
         (.registerType type-map inventory-type InventoryItem))))})

;; Initialize your connection pool
(def db-pool (jdbc/get-connection db-spec))

Each time the pool creates a new connection, this callback runs once to register your type mapping. Since connections are reused, you won't incur repeated configuration overhead.

Note that your current writeSQL method is empty—if you ever need to write InventoryItem instances to the database, fill it in to properly serialize the fields:

(deftype InventoryItem [^:volatile-mutable name 
                        ^:volatile-mutable supplier-id 
                        ^:volatile-mutable price] 
  java.sql.SQLData 
  (getSQLTypeName [_] "inventory_item") 
  (readSQL [_ stream _type-name]
    (set! name (.readString stream))
    (set! supplier-id (.readInt stream))
    (set! price (.readBigDecimal stream))
    this)
  (writeSQL [_ stream]
    (.writeString stream name)
    (.writeInt stream supplier-id)
    (.writeBigDecimal stream price)))

Using ^:volatile-mutable ensures the fields can be updated in the readSQL method, and the writeSQL method properly serializes each field to the PostgreSQL stream.

To confirm the mapping works, run a simple query:

(jdbc/query db-spec ["SELECT '(''widget'', 123, 9.99)'::inventory_item AS item"])

You should get a result where the :item key holds an InventoryItem instance with the correct name, supplier-id, and price values.

内容的提问来源于stack exchange，提问作者Kirill Salykin