跳至内容

Clojure 编程/示例/JDBC 示例

Add links
来自 Wikibooks，开放世界中的开放书籍

此页面旨在作为使用 JDBC 与 Clojure 的参考。我们将创建一个简单的博客数据库来查看 clojure.java.jdbc 中的基本函数。

有关 clojure.java.jdbc 库的最新、最及时由社区维护的文档，请查阅 Clojure 文档上的使用 java.jdbc。此 Wikibooks 页面是围绕库的非常旧的版本编写的，这里的大多数示例在较新的版本中将无法使用。

连接示例

[编辑 | 编辑源代码]

以下是通过 Clojure 连接到 JDBC 数据库的几个示例。它们都依赖于 Clojure Contrib 库 org.clojure/java.jdbc。此外，您需要在类路径中拥有合适的 JDBC jar 包。

Microsoft SQL Server

[编辑 | 编辑源代码]
(use 'clojure.java.jdbc)
(def db {:classname "com.microsoft.jdbc.sqlserver.SQLServerDriver"
               :subprotocol "sqlserver"
               :subname "//server-name:port;database=database-name;user=sql-authentication-user-name;password=password"
})
;Add Classpath to your C:\Program Files\Java\JDBC\sqljdbc_3.0\enu\sqljdbc4.jar
;Below code demos how to execute a simple sql select query and print it to console
;This query will print all the user tables in your MS SQL Server Database
(with-connection db 
      (with-query-results rs ["select * from sys.objects  where type = 'U'"] 
           (doseq [row rs] (println (:name row)))
))

;;Instead of passing user and password, you can authenticate yourself using current system user (es. current windows user)
;;To do this you have to add the string "integratedSecurity=true", removing user and password
(def db {:classname "com.microsoft.jdbc.sqlserver.SQLServerDriver"
               :subprotocol "sqlserver"
               :subname "//server-name:port;database=database-name;integratedSecurity=true"})

;;You have also to add the "sqljdc_auth.dll" file to your java.library.path (you can find the dll file into the JDBCDriver folder /enu/auth/platform you probably download before)
;;An easy way to check what is your current java.library.path is calling this from leiningen repl
(. System getProperty "java.library.path")
;;I suggest reload the shell or the system after the dll is added

Apache Derby

[编辑 | 编辑源代码]

Derby 支持客户端/服务器或嵌入式操作。此示例使用嵌入模式。

(use 'clojure.java.jdbc)

(let [db-path "c:/derby/myblog"]
  
  (def db {:classname "org.apache.derby.jdbc.EmbeddedDriver"
           :subprotocol "derby"
           :subname db-path
           :create true}))

H2Database

[编辑 | 编辑源代码]
(let [db-protocol "tcp"            ; "file|mem|tcp"
      db-host     "localhost:9092" ; "path|host:port"
      db-name     "Sample"]
 
  (def db {:classname   "org.h2.Driver" ; must be in classpath
           :subprotocol "h2"
           :subname (str "jdbc:h2:" db-protocol "://" db-host "/" db-name)
           ; Any additional keys are passed to the driver
           ; as driver-specific properties.
           :user     "sa"
           :password ""}))

;
; specify the path to your database driver
; 
(add-classpath "file:///c:/Installation/h2/bin/h2.jar")

;;
;; Here is an example of creating a symbol in the 
;; existing namespace as an alias to a namespace
;;
;(require '[clojure.java.jdbc :as sql]) 
;(sql/with-connection db
;  (sql/with-query-results rs ["select * from customer"]
;    (dorun (map #(println (:lastname %)) rs))))

MySQL

[编辑 | 编辑源代码]

MySQL 连接器设置起来非常简单。classname 和 subprotocol 设置为 MySQL 的值。db-port 设置为 3306，因为这是 MySQL 的默认端口。

(use 'clojure.java.jdbc)
 
(let [db-host "localhost"
      db-port 3306
      db-name "a_database"]
 
  (def db {:classname "com.mysql.jdbc.Driver" ; must be in classpath
           :subprotocol "mysql"
           :subname (str "//" db-host ":" db-port "/" db-name)
           ; Any additional keys are passed to the driver
           ; as driver-specific properties.
           :user "a_user"
           :password "secret"}))

PostgreSQL

[编辑 | 编辑源代码]

PostgreSQL 连接与 MySQL 版本几乎相同。classname 和 subprotocol 属性设置为它们相应的 PostgreSQL 值。db-port 设置为 5432，因为这是 PostgreSQL 的默认端口。

(use 'clojure.java.jdbc)

(let [db-host "localhost"
      db-port 5432
      db-name "a_database"]

  (def db {:classname "org.postgresql.Driver" ; must be in classpath
           :subprotocol "postgresql"
           :subname (str "//" db-host ":" db-port "/" db-name)
           ; Any additional keys are passed to the driver
           ; as driver-specific properties.
           :user "a_user"
           :password "secret"}))

Oracle

[编辑 | 编辑源代码]

Oracle 连接器设置起来非常简单。classname 和 subprotocol 设置为 Oracle 的值。db-port 设置为 1521，因为这是 Oracle XE 的默认端口。

(use 'clojure.java.jdbc)
  (def db {:classname "oracle.jdbc.OracleDriver"  ; must be in classpath
           :subprotocol "oracle"
           :subname "thin:@172.27.1.7:1521:SID"  ; If that does not work try:   thin:@172.27.1.7:1521/SID
           :user "user"
           :password "pwd"})

Virtuoso

[编辑 | 编辑源代码]
(use 'clojure.java.jdbc)
  (def db { :classname "virtuoso.jdbc.Driver"
                :subprotocol "virtuoso"
                :subname "//127.0.0.1:1111"
                :user "dba" :password "dba"  })

DataSource - Oracle

[编辑 | 编辑源代码]

以下是如何在 oracle 之上使用 c3p0 库进行池化数据库连接的示例。确保 c3p0 jar 包和 oracle 驱动程序 jar 包在类路径中。

(ns example
  (:use clojure.java.jdbc)
  (:import javax.sql.DataSource
           com.mchange.v2.c3p0.DataSources))

(def db {:datasource (DataSources/pooledDataSource 
                       (DataSources/unpooledDataSource "jdbc:oracle:thin:USER/PASS@HOST_IP:PORT:SCHEMA"))})

DataSource - PostgreSQL

[编辑 | 编辑源代码]

使用 PostgreSQL 的 PGPoolingDataSource 类的池化数据库连接示例。注意，这对于生产环境并不推荐。请改用 c3p0 或类似库。

(ns example
  (:use clojure.java.jdbc)
  (:import javax.sql.DataSource
	   org.postgresql.ds PGPoolingDataSource))

(let [db-host "localhost"
      db-name "example"
      db-user "username"
      db-pass "notTelling"]
  (def db {:datasource (doto (new PGPoolingDataSource)
				(.setServerName   db-host)
				(.setDatabaseName db-name)
				(.setUser         db-user)
				(.setPassword     db-pass)
				(.setMaxConnections 3))}))

DataSource - JNDI

[编辑 | 编辑源代码]

应用程序服务器通常将数据源绑定到 JNDI

(ns example
  (:use clojure.java.jdbc))

(def db {:name "jdbc/TestDS"})

DDL 示例

[编辑 | 编辑源代码]

在以下示例中，我们将数据库连接称为 db。这些示例已在 MySQL 中测试，Postgres 将使用 “SERIAL” 而不是 “AUTO_INCREMENT”。

创建表

[编辑 | 编辑源代码]

我们将从创建一个名为 blogs 的表开始。此表有三个列。

  • id（主键）
  • 标题
  • 正文

Derby

[编辑 | 编辑源代码]

添加一个时间戳列来展示更多 DDL。

(defn create-blogs
  "Create a table to store blog entries"
  []
  (clojure.java.jdbc/create-table
    :blogs
    [:id :int "PRIMARY KEY" "GENERATED ALWAYS AS IDENTITY"]
    [:title "varchar(255)"]
    [:body :clob]
    [:created_at :timestamp "NOT NULL" "DEFAULT CURRENT_TIMESTAMP"]))

MySQL

[编辑 | 编辑源代码]
(defn create-blogs
  "Create a table to store blog entries"
  []
  (clojure.java.jdbc/create-table
   :blogs
   [:id :integer "PRIMARY KEY" "AUTO_INCREMENT"]
   [:title "varchar(255)"]
   [:body :text]))

此方法将创建一个名为 create-blogs 的方法，该方法在调用时创建表。您可以按如下方式调用该方法

 (clojure.java.jdbc/with-connection
   db
   (clojure.java.jdbc/transaction
     (create-blogs)))

练习

[编辑 | 编辑源代码]

创建一个名为 categories 的表的方法。此表包含以下列

  • id（主键）
  • 名称

删除表

[编辑 | 编辑源代码]

以下是一个删除表的方法。

(defn drop-blogs
  "Drop the blogs table"
  []
  (try
   (clojure.java.jdbc/drop-table :blogs)
   (catch Exception _)))

要调用该方法，请按如下方式调用它

 (clojure.java.jdbc/with-connection
   db
   (clojure.java.jdbc/transaction
     (drop-blogs)))

练习

[编辑 | 编辑源代码]

创建一个方法来删除名为categories的表。

使用 do 命令删除所有对象

[编辑 | 编辑源代码]
(defn drop-all-objects
  []
  (do-commands "drop all objects;"))

(clojure.java.jdbc/with-connection
  db
  (clojure.java.jdbc/transaction
    (drop-all-objects)))

添加列

[编辑 | 编辑源代码]

待办事项

删除列

[编辑 | 编辑源代码]

待办事项

DML 例子

[编辑 | 编辑源代码]

好了，我们有了一个模式。开始 CRUD 操作吧！

SELECT

[编辑 | 编辑源代码]
(with-connection db 
   (with-query-results rs ["select * from blogs"] 
     ; rs will be a sequence of maps, 
     ; one for each record in the result set. 
     (dorun (map #(println (:title %)) rs))))

要使用 Derby 检索 CLOB 列，可以将返回的对象转换为字符串，并且必须在事务中进行操作。

(defn declob [clob]
  "Turn a Derby 10.6.1.0 EmbedClob into a String"
  (with-open [rdr (java.io.BufferedReader. (.getCharacterStream clob))]
    (apply str (line-seq rdr))))

(with-connection db
  (transaction
   (with-query-results rs ["select * from blogs"] 
     ; rs will be a sequence of maps, 
     ; one for each record in the result set. 
     (doseq [row rs] (println (declob (:body row)))))))

INSERT

[编辑 | 编辑源代码]

此函数将条目插入到 blog 表中。

(defn insert-blog-entry
  "Insert data into the table"
  [title,body]
  (clojure.java.jdbc/insert-values
   :blogs
   [:title :body]
   [title body]))

并调用该函数

 (clojure.java.jdbc/with-connection
   db
   (clojure.java.jdbc/transaction
    (insert-blog-entry "Hello World" "Life is awesome in the lisp world.") ))

UPDATE

[编辑 | 编辑源代码]

这是一个更新博客条目的示例。

(defn update-blog
  "This method updates a blog entry"
  [id attribute-map]
  (clojure.java.jdbc/update-values
   :blogs
   ["id=?" id]
   attribute-map))

让我们更新第一个博客条目。

(with-connection db 
  (clojure.java.jdbc/transaction
    (update-blog 1 {:title "Awesome Title"})))

DELETE

[编辑 | 编辑源代码]
;
; the first line allows us to say sql/with-connection instead of
; clojure.java.jdbc/with-connection
;
(require '[clojure.java.jdbc :as sql])
(defn delete-blog
  "Deletes a blog entry given the id"
  [id]
  (sql/with-connection db
    (sql/delete-rows :blogs ["id=?" id])))

事务

[编辑 | 编辑源代码]
Clipboard

待办事项
描述事务

分页

[编辑 | 编辑源代码]

Oracle 和 HSQLDB

[编辑 | 编辑源代码]

请阅读 http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html.

顺序集合必须始终包含唯一的排序值。

(defn as-str [& s] (apply str s))

(defn create-query-paging [{:keys [tbl properties order predicate from max] :or {max 100} }]
  "Creates a SQL query using paging and ROWNUM()"
  (str "SELECT * from (select " (clojure.string/join "," (map #(str "a." %) properties)) 
                        ", ROWNUM() rnum from (select " (clojure.string/join "/" properties) 
                        " from " tbl 
                        " order by " (clojure.string/join "," order) " ) a "
                        " WHERE ROWNUM() <= " max
                        ") WHERE " (if-not predicate "" (str predicate " and ")) " rnum >= " from))

(create-query-paging {:tbl "mytable" :properties ["*"] :order ["id", "ts"] :from 10 :max 20} )
;"SELECT * from (select a.*, ROWNUM() rnum from (select * from mytable order by id,ts ) a  WHERE ROWNUM() <= 20) WHERE rnum >= 10"
检索自 "https://wikibooks.cn/w/index.php?title=Clojure_Programming/Examples/JDBC_Examples&oldid=3676447"
华夏公益教科书