用Clojure插入PostgreSQL数组 [英] Inserting PostgreSQL arrays with Clojure

问题描述

我找不到用Clojure插入Postgres数组类型的方法.

I can't find a way to insert Postgres' array type with Clojure.

(sql/insert! db :things {:animals ["cow" "pig"]})

没有用，我有点期望.错误消息:

Didn't work which I kind of expected. Error message:

PSQLException Can't infer the SQL type to use for an instance of clojure.lang.PersistentVector. Use setObject() with an explicit Types value to specify the type to use.  org.postgresql.jdbc2.AbstractJdbc2Statement.setObject (AbstractJdbc2Statement.java:1936)

即使我发现对SQL的最直接访问也不起作用:

Even the most direct access to SQL that I could find didn't work:

(sql/execute! db "INSERT INTO things (animals) VALUES ('{\"cow\", \"pig\"}')")

真的不知道这是怎么回事:

Don't really know what's going on here:

ClassCastException java.lang.Character cannot be cast to java.lang.String  clojure.java.jdbc/prepare-statement (jdbc.clj:419)

肯定一定有可能吗?如果不是通过辅助函数，则通过某种方式执行原始SQL.

Surely it must be possible somehow? If not by the helper functions, then by somehow executing raw SQL.

推荐答案

要使用 insert！插入字符串向量，则必须创建一个实现java的对象(根据字符串向量). sql.Array.您可以使用

to use insert! to insert a vector of strings you must create an object (from the vector of strings) that implements java.sql.Array. You can use java.sql.Connection.createArrayOf to create such object

(def con (sql/get-connection db))

(def val-to-insert 
    (.createArrayOf con "varchar" (into-array String ["cow", "pig"]))

(sql/insert! db :things {:animals val-to-insert})

和

clojure.java.jdbc在 execute！ 说

clojure.java.jdbc's docs on execute! said

(execute! db-spec [sql & params] :multi? false :transaction? true)
(execute! db-spec [sql & param-groups] :multi? true :transaction? true)

您必须将sql字符串放入向量中才能使其正常工作.

Your must put your sql string in a vector to make it work.

(sql/execute! db ["INSERT INTO things (animals) VALUES ('{\"cow\", \"pig\"}')"])

这篇关于用Clojure插入PostgreSQL数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！