如何使用Clojure JDBC插入Postgres枚举值

Question

例如，PostgreSQL中有一个状态为枚举的产品表：

create type product_status as enum ('InStock', 'OutOfStock');
create table product (
    pid            int primary key default nextval('product_pid_seq'),
    sku            text not null unique,
    name           text not null,
    description    text not null,
    quantity       int not null,
    cost           numeric(10,2) not null,
    price          numeric(10,2) not null,
    weight         numeric(10,2),
    status         product_status not null
);

插入产品的典型Clojure代码是：

(def prod-12345 {:sku "12345"
                 :name "My Product"
                 :description "yada yada yada"
                 :quantity 100
                 :cost 42.00
                 :price 59.00
                 :weight 0.3
                 :status "InStock"})
(sql/with-connection db-spec
   (sql/insert-record :product prod-12345))

但是，status是一个枚举，所以如果不将其强制转换为枚举，就不能将其作为普通字符串插入：

'InStock'::product_status

我知道你可以通过一份事先准备好的声明来做到这一点，比如：

INSERT INTO product (name, status) VALUES (?, ?::product_status)

但是，有没有一种方法可以在不使用事先准备好的声明的情况下做到这一点？

Answer 1

我今天使用stringtype=unspecified破解解决方法完成了这项工作。

您可以将此参数添加到db-spec中，如下所示：

(def db-spec {:classname "org.postgresql.Driver"
              :subprotocol "postgresql"
              :subname "//myserver:5432/mydatabase"
              :user "myuser"
              :password "mypassword"
              :stringtype "unspecified"}) ; HACK to support enums

然后像往常一样使用insert!。

如果有一个解决方案不会太削弱类型安全性，那就太好了。

Answer 2

Kris Jurka回复了Mike Sherrill上面提到的讨论，并提供了一个变通方法：

使用url参数stringtype=unspecified[在JDBC连接url]使setString始终绑定到unknown而不是varchar，这样就不需要任何代码更改。

我在Java中尝试过这个，看起来效果不错。

Answer 3

这篇博客文章很好地解决了这个问题。jdbc提供了ISQLValue协议，该协议只有一个方法sql-value，该方法将clojure值转换为sql值，由PGObject表示。博客文章建议用:type/value形式的关键字表示枚举，因此ISQLValue可以实现如下：

(defn kw->pgenum [kw]
  (let [type (-> (namespace kw)
                 (s/replace "-" "_"))
        value (name kw)]
    (doto (PGobject.)
      (.setType type)
      (.setValue value))))
(extend-type clojure.lang.Keyword
  jdbc/ISQLValue
  (sql-value [kw]
    (kw->pgenum kw)))

在您的示例中，您会在产品中插入：

(def prod-12345 {:sku "12345"
                 :name "My Product"
                 :description "yada yada yada"
                 :quantity 100
                 :cost 42.00
                 :price 59.00
                 :weight 0.3
                 ;; magic happens here
                 :status :product_status/InStock})
(sql/with-connection db-spec
   (sql/insert-record :product prod-12345))

问题是，当查询数据库时，枚举是一个简单的字符串，而不是关键字。这可以通过实现IResultSetReadColumn协议以类似的方式解决：

(def +schema-enums+
  "A set of all PostgreSQL enums in schema.sql. Used to convert
  enum-values back into Clojure keywords."
  ;; add your other enums here
  #{"product_status"})
(extend-type java.lang.String
  jdbc/IResultSetReadColumn
  (result-set-read-column [val rsmeta idx]
    (let [type (.getColumnTypeName rsmeta idx)]
      (if (contains? +schema-enums+ type)
        (keyword (s/replace type "_" "-") val)
        val))))

Answer 4

如果有人在使用clojure.java.jdbc、jdbc.next的继任者时引用了这个问题，那么插入枚举的代码类似于：

(ns whatever
 (:require
  [next.jdbc.sql :as jdbc.sql]
  [next.jdbc.types :as jdbc.types]
 ))
;; ...define your database connection and data source...    
(def prod-12345 {:sku "12345"
                 :name "My Product"
                 :description "yada yada yada"
                 :quantity 100
                 :cost 42.00
                 :price 59.00
                 :weight 0.3
                 :status (jdbc.types/as-other "InStock")})
(jdbc.sql/insert! ds :product prod-12345)

如文件所示https://github.com/seancorfield/next-jdbc/blob/develop/doc/tips-and-tricks.md在"；使用枚举类型"；标题

Answer 5

除非将纯SQL传递到后端，否则必须使用强制转换。（SQL语句INSERT INTO product (name, status) VALUES ('SomeName', 'InStock');应该可以正常工作。）

在您提出问题一周后，Tom Lane就pgsql黑客问题进行了讨论。

AFAIK这与JDBC如出一辙：setString（）意味着该参数为字符串类型。如果类型真的required不是字符串。（我不是Java专家，但我似乎请记住，使用setObject是标准的解决方法。）

枚举在这里没有遭受任何特殊的困难，我反对削弱打字系统，给他们一个特殊的通行证。

我们自己的@CraigRinger参与了讨论，现在可能已经找到了相关的东西。