将Clojure连接到PostgreSQL数据库

Question

我正在尝试将我的Clojure程序连接到PostgreSQL数据库。我有所需的依赖项。这是我的repository.clj文件。

(ns webdev.repository
  (:require [clojure.java.jdbc :as db] ))

(defn create-tables [db]
  (db/execute! db ["create table if not exists movies(id serial not null,
name varchar not null, primary key (id));"])

  (db/execute! db ["create table if not exists users(id varchar not null,
f_name varchar not null, l_name varchar not null, primary key(id));"])
  )

这是我的core.clj文件的一部分。

(ns webdev.core
  (:require [webdev.repository :as repo])
  (:require [ring.adapter.jetty :as jetty]
            [ring.middleware.reload :refer [wrap-reload]]
            [compojure.core :refer [defroutes GET]]
            [compojure.route :refer [not-found]]
            [ring.handler.dump :refer [handle-dump]]
            ))

(def db "postgresql://localhost:5432/webdev")
(repo/create-tables db) ;;call to create the tables
...
...

当我运行这个程序时，会出现一个错误，提示：

Caused by: org.postgresql.util.PSQLException: This ResultSet is closed.
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.checkClosed(AbstractJdbc2ResultSet.java:2654)
    at org.postgresql.jdbc2.AbstractJdbc2ResultSet.setFetchSize(AbstractJdbc2ResultSet.java:1771)
    at org.postgresql.jdbc4.Jdbc4Statement.createResultSet(Jdbc4Statement.java:39)
    at org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler.handleResultRows(AbstractJdbc2Statement.java:211)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1773)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)

无法找出我做错了什么。

编辑

选择语句似乎可以工作。但是插入和创建表格语句会出现“此结果集已关闭”的错误。

- Ashwin

---

顶层代码在编译时运行。不要把它放在那里。请查看适当的数据库迁移方式，或者如果你想快速启动，请在 -main 中调用该函数，并在开发过程中从 repl 调用。 - cfrick

@cfrick 尝试从主函数中调用它。仍然出现相同的错误。所有选择语句似乎都能正常工作。但插入和创建表语句会抛出“此结果集已关闭”的错误。 - Ashwin

Answer 1

使用conman和hugsql的快速解决方案：

(ns ^{:doc "Database access functions, mostly from Luminus template."}
  your-project.db.core
  (:require
    [clojure.java.jdbc :as jdbc]
    [conman.core :as conman]
    [hugsql.core :as hugsql]
    [mount.core :refer [defstate]]
   [postgre-types.json :refer [add-json-type add-jsonb-type]]))

(defstate ^:dynamic *db*
           :start (conman/connect! {:jdbc-url-env (env :database-url)
                                    :jdbc-url "jdbc:postgresql://127.0.0.1/yourdb_dev?user=user&password=thisisnotsecure"
                                    :driver-class-name "org.postgresql.Driver"})
           :stop (conman/disconnect! *db*))

(conman/bind-connection *db* "sql/queries.auto.sql" "sql/queries.sql")

但更简单的是，使用Luminus免费获得这些内容。只需开始您的项目： lein new luminus my-project +postgres 它可以直接开箱即用。 简要概述：不要重复造轮子，除非您正在学习成为一个制造轮子的人。站在巨人的肩膀上。

- Simon Brooke

---

除了Luminus之外，另一个选择是Duct框架。 - myguidingstar

Answer 2

这是一个使用H2的示例：https://github.com/cloojure/demo-jdbc 您可以克隆该存储库并通过lein test运行。正如cfrick所说，创建数据库的调用不应该在顶层运行，因为这会在编译阶段运行。相反，对create-table的调用应该在从-main调用的函数内部（或者在示例中作为单元测试的一部分）。

(ns tst.demo.jdbc
  (:use demo.core tupelo.core tupelo.test)
  (:require
    [clojure.java.jdbc :as jdbc]
    [hikari-cp.core :as pool]
  ))

(def raw-db-spec
  {:classname   "org.h2.Driver"
   :subprotocol "h2:mem"    ; the prefix `jdbc:` is added automatically
   :subname     "demo;DB_CLOSE_DELAY=-1" ; `;DB_CLOSE_DELAY=-1` very important!!!
                     ; http://www.h2database.com/html/features.html#in_memory_databases
                     ; http://makble.com/using-h2-in-memory-database-in-clojure
   :user        "sa"        ; "system admin"
   :password    ""          ; empty string by default
   })

(dotest
  ; creates & drops a connection (& transaction) for each command
  (jdbc/db-do-commands raw-db-spec ["drop table if exists langs"
                                    "drop table if exists releases"])

  ; Creates and uses a connection for all commands
  (jdbc/with-db-connection
    [conn raw-db-spec]
    (jdbc/db-do-commands
      conn
      [(jdbc/create-table-ddl :langs
                              [[:id :serial]
                               [:lang "varchar not null"]])
       (jdbc/create-table-ddl :releases
                              [[:id :serial]
                               [:desc "varchar not null"]
                               [:langId "numeric"]])]))

  ; create & use a connection for multiple commands
  (jdbc/with-db-connection
    [conn raw-db-spec]
    (jdbc/insert-multi! raw-db-spec :langs ; => ({:id 1} {:id 2})
                        [{:lang "Clojure"}
                         {:lang "Java"}])

    (let [result (jdbc/query raw-db-spec ["select * from langs"])]
      (is= result [{:id 1, :lang "Clojure"}
                   {:id 2, :lang "Java"}])))

  ; Wraps all commands in a single transaction
  (jdbc/with-db-transaction
    [tx raw-db-spec]
    (let [clj-id (grab :id (only (jdbc/query tx ["select id from langs where lang='Clojure'"])))]
      (jdbc/insert-multi! tx :releases
                          [{:desc "ancients" :langId clj-id}
                           {:desc "1.8" :langId clj-id}
                           {:desc "1.9" :langId clj-id}]))
    (let [java-id (grab :id (only (jdbc/query tx ["select id from langs where lang='Java'"])))]
      (jdbc/insert-multi! tx :releases
                          [{:desc "dusty" :langId java-id}
                           {:desc "8" :langId java-id}
                           {:desc "9" :langId java-id}
                           {:desc "10" :langId java-id}])))

  ; Creates and uses a connection for each command
  (let [
        ; note cannot wrap select list in parens or get "bulk" output
        result-0 (jdbc/query raw-db-spec ["select langs.lang, releases.desc
                                             from    langs join releases
                                             on     (langs.id = releases.langId)
                                             where  (lang = 'Clojure') "])
        result-1 (jdbc/query raw-db-spec ["select l.lang, r.desc
                                             from    langs as l
                                                       join releases as r
                                             on     (l.id = r.langId)
                                             where  (l.lang = 'Clojure') "])
        result-2 (jdbc/query raw-db-spec ["select langs.lang, releases.desc
                                              from    langs, releases
                                              where  ( (langs.id = releases.langId)
                                                and    (lang = 'Clojure') ) "])
        result-3 (jdbc/query raw-db-spec ["select l.lang, r.desc
                                            from    langs as l, releases as r
                                            where  ( (l.id = r.langId)
                                              and    (l.lang = 'Clojure') ) "])
        ]
    (nl)
    (spyx-pretty result-0)
    ;(sets= result-0 result-1 result-2 result-3  ; #todo use this
    ;       [{:lang "Clojure", :desc "1.8"}
    ;        {:lang "Clojure", :desc "1.9"}
    ;        {:lang "Clojure", :desc "ancients"}])
    (is (= (set [{:lang "Clojure", :desc "1.8"}
                 {:lang "Clojure", :desc "1.9"}
                 {:lang "Clojure", :desc "ancients"}])
           (set result-0)
           (set result-1)
           (set result-2)
           (set result-3))) ))

- Alan Thompson