如何从 clojure.java.jdbc 删除或创建数据库？

Question

我想从 clojure.java.jdbc 创建/删除数据库。这会失败：

(require '[clojure.java.jdbc :as sql])

(def db
  {:classname "org.postgresql.Driver"
   :subprotocol "postgresql"
   :subname "//localhost/postgres"
   :user "postgres"})

(defn drop-database [name]
  (sql/do-commands (str "drop database " name)))

(sql/with-connection db 
  (drop-database "db_name"))

因为 do-commands 启动一个事务，并且显然您无法在事务内删除或创建数据库。有什么想法吗？

谢谢！

Answer 1

获取do-commands的源代码（此处）并删除对 transaction 的调用：

(defn drop-database [name]
  (sql/with-connection db
    (with-open [s (.createStatement (sql/connection))]
      (.addBatch s (str "drop database " name))
      (seq (.executeBatch s)))))

Answer 2

无事务执行功能被整合到 db-do-commands 中。

现在这个稍微简单的版本正在工作：

(jdbc/db-do-commands postgres-db false "CREATE DATABASE foo")

如果您没有指定 false 作为第二个参数，它将无法工作，因为它将尝试启动事务。

Answer 3

对于较新的 clojure 版本，建议的方法不再有效。我成功地使用了这个功能：

(defn exec-db-command [db command]
  (jdbc/with-db-connection [conn db]
    (with-open [s (.createStatement (:connection conn))]
      (.executeUpdate s command))))

(exec-db-command db "create database foo")

Answer 4

这是唯一对我有用的解决方案

(def datasource-options {:auto-commit        true
                         :read-only          false
                         :connection-timeout 30000
                         :validation-timeout 5000
                         :idle-timeout       600000
                         :max-lifetime       1800000
                         :minimum-idle       10
                         ;; :maximum-pool-size  10
                         :pool-name          "db-pool"
                         :adapter            (:database-adapter env)
                         :username           (:database-username env)
                         :password           (:database-password env)
                         :database-name      (:database-name env)
                         :server-name        (:database-host env)
                         :port-number        (:database-port env)
                         :register-mbeans    false})

(defonce datasource
  (delay (make-datasource datasource-options)))

(defn db-jdbc-uri [& {:as args}]
  (let [datasource-options (merge datasource-options args)]
    (format "jdbc:%s://%s:%s/%s?user=%s&password=%s"
            (datasource-options :adapter) (datasource-options :server-name)
            (datasource-options :port-number) (datasource-options :database-name)
            (datasource-options :username) (datasource-options :password))))

（defn create-database [name]
(println {:connection-uri (db-jdbc-uri :数据库名称 "")})
(jdbc/with-db-connection [conn {:connection-uri (db-jdbc-uri :数据库名称 "")}]
(jdbc/db-do-commands conn false (str "CREATE DATABASE " name))))

(defn drop-database [name]
  (jdbc/with-db-connection [conn {:connection-uri (db-jdbc-uri :database-name "")}]
      (jdbc/db-do-commands conn false (str "DROP DATABASE " name) )))

基本上，您需要在不提供数据库或连接到其他数据库（不是您要删除的数据库）的情况下进行连接

这将得到转换为此代码。

  (defn create-database [name]
  (jdbc/with-db-connection [conn {:connection-uri "jdbc:postgresql://localhost/postgres?user=<name>&password=<pass>"}]
    (jdbc/db-do-commands conn false (str "CREATE DATABASE " name) )))