August 10, 2019
By: 马海强
clojure luminus开发之HugSQL
在springboot里习惯使用spring data jpa，分页使用Pageable、PageRequest，还能携带Sort，放回结果自动分页，确实方便。 在luminusweb里没有看到分页的说明，于是在底层的HugSQL里找到的方案,举个动态sql，并且使用like模糊查询的例子：
-- :name get-patient-like :? :*
-- :doc 模糊查询患者列表
SELECT
/*~ (if (:count params) */
count(*) AS 'total-elements'
/*~*/
p.`patient_id`,
p.`name`,
p.`headimgurl`,
p.`patient_no`
/*~ ) ~*/
FROM
`t_patient` p
WHERE
p.deleted = FALSE
AND p.`hospital_id` = :hospital-id
/*~ (if (= nil (:keywords params)) */
AND 1=1
/*~*/
AND (
p.`name` LIKE :keywords
OR p.mobile LIKE :keywords
OR p.patient_no LIKE :keywords
)
/*~ ) ~*/
ORDER BY p.`create_time` DESC
--~ (if (:count params) ";" "LIMIT :page, :size ;")
调用：
["/patient/search"
{:get {:summary "医生模糊检索患者列表"
:parameters {:query (s/keys :req-un [:base/page :base/size]
:opt-un [::keywords])}
:handler (fn [{{{:keys [page size keywords]} :query} :parameters :as request}]
(let [hospital-id (-> request :doctor :hospital-id)]
{:status
200
:body
{:code 1
:data {:total-elements
(->> (db-pat/get-patient-like
{:count true
:keywords (str "%" keywords "%")
:hospital-id hospital-id})
(map :total-elements)
(first))
:content
(db-pat/get-patient-like
{:page page
:size size
:hospital-id hospital-id
:keywords (str "%" keywords "%")})}}}))}}]
说明：接口的page,size为必须参数，keywords是非必须参数，sql中根据count的boolean值判断是不是求count，根据keywords是否有值判断是否加模糊查询条件，实现动态sql调用。 更多hugSQL的高阶使用，使用时参考官网 边用边学吧。
- 一个in查询的例子，下例中的type用逗号隔开传入:
:get {:summary "分页获取患者检查报告列表"
:parameters {:query (s/keys :req-un [:base/patient-id ::type])}
:handler (fn [{{{:keys [type, patient-id]} :query} :parameters}]
{:status 200
:body {:code 1
:data (db/get-examine-reports
{:patient-id patient-id
:types (str/split type #",")})}})}
sql：
-- :name get-reports :? :*
-- :doc 查询列表
SELECT
*
FROM `t_report`
WHERE `deleted` = FALSE AND `id` =:id AND `type` in (:v*:types)
调用处保证types是个array就行：
:get {:summary "获取报告列表"
:parameters {:query (s/keys :req-un [:base/id ::type])}
:handler (fn [{{{:keys [type, id]} :query} :parameters}]
{:status 200
:body {:code 1
:data (db/get-reports
{:id id
:types (str/split type #",")})}})}
- 批量操作,hugsql支持批量操作，语法是
:t*，看看sql
-- :name batch-create-cure-reaction-detail! :! :n
-- :doc: 新建
INSERT INTO `t_cure_reaction_detail` (`main_id`, `type`, `dict_key_id`, `dict_key_name`, `dict_value_id`, `dict_value_name`) VALUES
:t*:reaction-detail
一个UT：
(ns alk-wxapi.routes.service.cure-reaction-service-test
(:require [clojure.test :as t]
[alk-wxapi.routes.service.cure-reaction-service :as sut]
[alk-wxapi.db.db-patient-cure :as db]
[alk-wxapi.db.core :refer [*db*]]
[luminus-migrations.core :as migrations]
[clojure.java.jdbc :as jdbc]
[alk-wxapi.config :refer [env]]
[mount.core :as mount]))
(t/use-fixtures
:once
(fn [f]
(mount/start
#'alk-wxapi.config/env
#'alk-wxapi.db.core/*db*)
(migrations/migrate ["migrate"] (select-keys env [:database-url]))
(f)))
(def test-batch-create-cure-reaction-detail-data
'[[1
"REACTION"
62
"哮喘症状"
68
"气闭"]
[1
"REACTION"
58
"全身非特异性反应"
59
"发热"]
[1
"DISPOSE"
86
"处理方式"
89
"局部处理(冰敷)"]])
(t/deftest test-batch-create-cure-reaction-detail
(jdbc/with-db-transaction
[t-conn *db*]
(jdbc/db-set-rollback-only! t-conn)
(t/is (= 2 (db/batch-create-cure-reaction-detail-data!
{:reaction-detail test-batch-create-cure-reaction-detail-data})))))
执行结果：
(alk-wxapi.db.db-patient-cure/batch-create-cure-reaction-detail!
{:reaction-detail [[1
"REACTION"
62
"哮喘症状"
68
"气闭"]
[1
"REACTION"
58
"全身非特异性反应"
59
"发热"]
[1
"DISPOSE"
86
"处理方式"
89
"局部处理(冰敷)"]]})
=> 3
2019-06-15 15:16:06,929 [nRepl-session-353b6f60-9fd8-415c-9baa-19f7eb4a97f9] INFO jdbc.sqlonly - batching 1 statements: 1: INSERT INTO `t_cure_reaction_detail` (`main_id`, `type`, `dict_key_id`,
`dict_key_name`, `dict_value_id`, `dict_value_name`) VALUES (1,'REACTION',62,'哮喘症状',68,'气闭'),(1,'REACTION',58,'全身非特异性反应',59,'发热'),(1,'DISPOSE',86,'处理方式',89,'局部处理(冰敷)')
需要注意的是传入的vector，里面也是vector，按照sql中的顺序，不是map结构。 下面是一个构造的例子
(conman.core/with-transaction
[*db*]
(let [tmz-id (utils/generate-db-id)]
(db/create-tmz! (assoc body
:id tmz-id))
(when (pos? (count (:detail body)))
(let [funcs [(constantly tmz-id)
:drug-id
:injection-num
:classify
:attribute]
records (map (apply juxt funcs) (:detail body))]
(if (pos? (count records))
(db/batch-create-tmz-detail! {:records records}))))))
对应的传参方式：
{
"date": "2019-08-09",
"patient-id": "222",
"detail": [
{
"drug-id": 1,
"classify": 167,
"attribute": "常规法",
"injection-num": 3
},
{
"drug-id": 2,
"classify": 168,
"attribute": "改良法",
"injection-num": 1
}
]
}
HugSql其实支持动态sql的，动态表名也可以，更多使用用到的时候查阅官网
适配器
druids提供了几个adapter，用来处理转换关系，驼峰、中线、下划线之间互转的。 我们使用连接符转换，即创建connection时加入kebab-case：
(defstate ^:dynamic *db*
:start (do (Class/forName "net.sf.log4jdbc.DriverSpy")
(if-let [jdbc-url (env :database-url)]
(conman/connect! {:jdbc-url jdbc-url})
(do
(log/warn "database connection URL was not found, please set :database-url in your config, e.g: dev-config.edn")
*db*)))
:stop (conman/disconnect! *db*))
(conman/bind-connection *db* {:adapter (kebab-adapter)} "sql/queries.sql")
这个adapter在init项目时已经引入了，就看使用不使用。