一、准备
目标:为报表服务开发一个REST风格的xml的数据源。当以http GET方式访问url时,根据url解析出要执行的oracle存储过程和要传递给它的参数,从过程的out参数中取出xml结果作为响应。url格式如:/xml/pkgname.procname/param1,param2,param3,这里xml为url前缀。pkgname.procname为要调用的oracle存储过程的名称,这个过程的第一个参数固定为out clob类型,param1,param2等参数为存储过程的第二个参数以后的参数,全部为varchar2类型。上面的url实际调用的存储过程为pkgname.procname(p_xml out clob,param1 varchar2,param2 varchar2,param3 varchar2)。
工具:clojure 1.3.0、Leiningen 1.7.1、emacs
配置clojure emacs开发环境请参考swank-clojure 。
使用lein创建新工程,工程定义如下:(defproject cljxxbixml "1.0.0-SNAPSHOT"
:description "调用pl/sql过程,以text/xml形式将结果输出"
:dependencies [[org.clojure/clojure "1.3.0"]
[compojure "1.1.0"]
[org.clojure/java.jdbc "0.1.3"]
[com.oracle/ojdbc14 "10.2.0.3.0"]
[com.h2database/h2 "1.3.167"]
[c3p0/c3p0 "0.9.1.2"]]
:dev-dependencies [[lein-ring "0.7.1"]
[ring-server "0.2.3"]]
:plugins [[lein-ring "0.7.1"] [lein-swank "1.4.4"]]
:ring {:handler cljxxbixml.core/app})
lein-ring 插件是用于开发基于ring的web应用程序的,同时也提供了将应用程序打包成war的功能。
初次使用clojure开发web应用程序可以参考compojure的wiki 。
二、web层
web层主要使用了compojure来处理rest风格的请求并产生响应。
;; # -*- coding: utf-8 -*-
(ns cljxxbixml.core
(:use compojure.core cljxxbixml.db)
(:require [compojure.route :as route]
[compojure.handler :as handler]))
(defn xml-response [data & [status]]
"用于生成utf-8 xml响应"
{:status (or status 200)
:headers {"Content-Type" "text/xml; charset=utf-8"}
:body data})
(defn main-page []
"首页"
(xml-response "<data>Hello World1! 汉字</data>"))
(defn xml-page [proc param]
"生成xml数据"
(xml-response
(gen-xml proc
(drop-last
(clojure.string/split
;; 防止,,,,之类的字符串split之后数组长度为0
(str param ",EOF") #",")))))
(defn parse-uri [uri]
"获取uri中第二个/开始的以/分割的Seq"
(nnext (clojure.string/split uri #"\/")))
(defroutes main-routes
"请求地址映射"
(GET "/" []
(main-page))
(GET "/xml/*" {:keys [uri]}
(apply xml-page (parse-uri uri)))
(GET "*" {:keys [scheme server-name server-port uri]}
(str (name scheme) "://" server-name ":" server-port uri)))
(def app
"主handler"
(handler/site main-routes))
为了便于在lein swank下进行增量开发。可以将下面的代码直接放到repl中执行。
;; # -*- coding: utf-8 -*-
;; slime里用于交互式调试(require '[ring.server.standalone :as rserver])(require '[cljxxbixml.core :as bi]);; 仅定义一次,route修改时不会发生作用;;(defonce server (rserver/serve bi/app));;(.stop server);;(.start server)(def server (rserver/serve bi/app))
这里的通过(.stop server)和(.start server)可以停止或启动嵌入的web服务器。
三、数据访问
数据访问这部分主要使用了java.jdbc这个程序库。这个程序库的示例中都是直接使用jdbc连接而没有使用数据库连接池,使用的示例中也基本只有crud之类的操作,没有找到如何调用oracle存储过程,一翻google之后我找到了使用连接池 和调用oracle存储过程 的方法。
;; # -*- coding: utf-8 -*-
(ns cljxxbixml.db
(:require [clojure.java.jdbc :as sql])
(:import com.mchange.v2.c3p0.ComboPooledDataSource
java.sql.Types))
;; ===连接池使用的数据源===
(def db-spec
"数据源定义"
{:classname "oracle.jdbc.driver.OracleDriver"
:subprotocol "oracle:thin"
:subname "@//bieedb.dtsxpower.com:1521/biee"
:user "biee"
:password "biee"})
(defn pool [spec]
"连接池"
(let [cpds (doto (ComboPooledDataSource.)
(.setDriverClass (:classname spec))
(.setJdbcUrl (str "jdbc:" (:subprotocol spec) ":" (:subname spec)))
(.setUser (:user spec))
(.setPassword (:password spec))
;; expire excess connections after 30 minutes of inactivity:
(.setMaxIdleTimeExcessConnections (* 30 60))
;; expire connections after 3 hours of inactivity:
(.setMaxIdleTime (* 3 60 60)))]
;; with-connection可以使用:datasource
;; 参见:http://clojure.github.com/java.jdbc/index.html#clojure.java.jdbc/with-connection
{:datasource cpds}))
(def pooled-db (delay (pool db-spec)))
(defn db-connection [] @pooled-db)
;; ===不使用连接池===
(def db {:classname "oracle.jdbc.driver.OracleDriver" ; must be in classpath
:subprotocol "oracle:thin"
:subname "@10.232.234.50:1609:TEMP"
;; Any additional keys are passed to the driver
;; as driver-specific properties.
:user "apps"
:password "apps" })
(defn connection-test []
"测试数据库连接"
(sql/with-connection (db-connection)
(sql/with-query-results res
["select 1 from dual"]
(doseq [rec res]
(println rec)))))
(defn gen-xml [procName params]
"调用过程生成xml数据"
(sql/with-connection (db-connection)
(with-open [stmt (.prepareCall
(sql/connection)
(str "{call " procName "(?"
(clojure.string/join (map (fn [v] ",?") params))
")}"))]
(.registerOutParameter stmt 1 Types/CLOB)
(doseq [[index param] (map vector (iterate inc 0) params)]
(.setString stmt (+ index 2) param))
(.execute stmt)
(let [content (.getClob stmt 1)]
(.getSubString content 1 (.length content ))))))