Clojure 程式設計/示例/JDBC 示例
< Clojure 程式設計 | 示例
此頁面旨在作為使用 JDBC 與 Clojure 的參考。我們將建立一個簡單的部落格資料庫來檢視 clojure.java.jdbc 中的基本函式。
有關 clojure.java.jdbc 庫的最新、最及時由社群維護的文件，請查閱 Clojure 文件上的使用 java.jdbc。此 Wikibooks 頁面是圍繞庫的非常舊的版本編寫的，這裡的大多數示例在較新的版本中將無法使用。
以下是透過 Clojure 連線到 JDBC 資料庫的幾個示例。它們都依賴於 Clojure Contrib 庫 org.clojure/java.jdbc。此外，您需要在類路徑中擁有合適的 JDBC jar 包。
(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
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}))
(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 聯結器設定起來非常簡單。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 連線與 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 聯結器設定起來非常簡單。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"})
(use 'clojure.java.jdbc)
(def db { :classname "virtuoso.jdbc.Driver"
:subprotocol "virtuoso"
:subname "//:1111"
:user "dba" :password "dba" })
以下是如何在 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"))})
使用 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))}))
應用程式伺服器通常將資料來源繫結到 JNDI
(ns example
(:use clojure.java.jdbc))
(def db {:name "jdbc/TestDS"})
在以下示例中，我們將資料庫連線稱為 db。這些示例已在 MySQL 中測試，Postgres 將使用 “SERIAL” 而不是 “AUTO_INCREMENT”。
我們將從建立一個名為 blogs 的表開始。此表有三個列。
- id（主鍵）
- 標題
- 正文
新增一個時間戳列來展示更多 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"]))
(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的表。
(defn drop-all-objects
[]
(do-commands "drop all objects;"))
(clojure.java.jdbc/with-connection
db
(clojure.java.jdbc/transaction
(drop-all-objects)))
待辦事項
待辦事項
好了，我們有了一個模式。開始 CRUD 操作吧！
(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)))))))
此函式將條目插入到 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.") ))
這是一個更新部落格條目的示例。
(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"})))
;
; 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])))
請閱讀 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"