Red Creation Technology Blog: hugsql 通用方法

October 10, 2020

By: Alisa

hugsql 通用方法

数据库测试表
新增

新增一条或批量新增

更新

更新一条(因为更新时的where条件限制只能更新哪一条，此种写法不支持更新不同条件的数据)

新增或更新

批量新增或更新

查询
逻辑删除-类似更新

在后台开发过程中，和数据库打交道，经常用到的增删改查，操作一条或批量，都可以提取为通用的方法，一个sql文件中的方法即可匹配大部分的数据库操作需要。

数据库测试表

CREATE TABLE `t_test` (
  `id` varchar(40) NOT NULL COMMENT '主键',
  `test_name` varchar(40) DEFAULT NULL COMMENT '测试名称',
  `test_code` varchar(40) DEFAULT NULL COMMENT '测试编码',
  `delete_flag` varchar(4) DEFAULT '0' COMMENT '删除标记 0：未删除 1：已删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB COMMENT='测试表';

新增

新增一条或批量新增

数据库sql

-- 新增
insert into `t_test`
(`id`, `test_name`, `test_code`)
values
('1', 'name-001', 'code-001'),
('2', 'name-002', 'code-002');
-- 第一遍执行 2 row(s) affected
-- 再次执行会报错，Error Code: 1062. Duplicate entry '1' for key 'PRIMARY', 体现在代码中就是抛出异常，如何避免看第3个例子

结果
id test_name test_code delete_flag
'1' 'name-001' 'code-001' '0'
'2' 'name-002' 'code-002' '0'
改为hugsql, 将表名、字段名、数据提取成变量传递

id	test_name	test_code	delete_flag
'1'	'name-001'	'code-001'	'0'
'2'	'name-002'	'code-002'	'0'

-- :name insert-into-table! :! :1
-- :doc 新增一条
INSERT INTO :i:table-name
(:i*:cols)
VALUES
(:v*:info)

-- :name batch-insert-into-table! :! :n
-- :doc 新增批量
INSERT INTO :i:table-name
(:i*:cols)
VALUES
:t*:records

实例调用

;; 新增一条
(common-db/insert-into-table! 
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :info ["1" "name-001" "code-001"]})
;; 新增批量
(common-db/batch-insert-into-table! 
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :records [["3" "name-003" "code-003"]
              ["2" "name-002" "code-002"]]})

更新

更新一条(因为更新时的where条件限制只能更新哪一条，此种写法不支持更新不同条件的数据)

数据库

-- 更新一条
update `t_test` 
set `test_name` = 'name-01', 
    `test_code` = 'code-01'
where `id` = '1';
-- 第一遍执行 1 row(s) affected Rows matched: 1  Changed: 1  Warnings: 0
-- 第二遍执行 0 row(s) affected Rows matched: 1  Changed: 0  Warnings: 0

结果:
id test_name test_code delete_flag
'1' 'name-01' 'code-01' '0'
'2' 'name-002' 'code-002' '0'
改为hugsql, 将表名、要更新的数据、主键名、主键值提取成变量传递, 参考hugsql documet

id	test_name	test_code	delete_flag
'1'	'name-01'	'code-01'	'0'
'2'	'name-002'	'code-002'	'0'

-- :name update-table-info! :! :1
-- :doc 修改
/* :require [clojure.string :as string]
            [hugsql.parameters :refer [identifier-param-quote]] */
UPDATE :i:table-name
SET
/*~
(string/join ","
  (for [[field _] (:updates params)]
    (str (identifier-param-quote (name field) options)
      " = :v:updates." (name field))))
~*/
WHERE :i:id-name = :id

实例调用

;; 更新一条
(common-db/update-table-info!
  {:table-name "t_test"
    :updates {:test_name "name-01"
              :test_code "code-01"}
    :id-name "id"
    :id "1"})
;; 不管执行多少遍，返回结果总是 1

新增或更新

不知道id是否存在，新增一条或更新一条

数据库

-- 新增或更新一条
insert into `t_test`
(`id`, `test_name`, `test_code`)
values
('1', 'name-001', 'code-001')
ON DUPLICATE KEY
UPDATE 
  `test_name`='name-001',
  `test_code` = 'code-001';
-- 第一遍执行 2 row(s) affected
-- 第二遍执行 0 row(s) affected

结果:
id test_name test_code delete_flag
'1' 'name-001' 'code-001' '0'
'2' 'name-002' 'code-002' '0'
改为hugsql, 将表名、新增字段名、新增数据、要更新的数据提取成变量传递

id	test_name	test_code	delete_flag
'1'	'name-001'	'code-001'	'0'
'2'	'name-002'	'code-002'	'0'

-- :name insert-or-update-table-info! :! :1
-- :doc 新增或修改
/* :require [clojure.string :as string]
            [hugsql.parameters :refer [identifier-param-quote]] */
INSERT INTO :i:table-name
(:i*:cols)
VALUES
(:v*:info)
ON DUPLICATE KEY
UPDATE
/*~
(string/join ","
  (for [[field _] (:updates params)]
    (str (identifier-param-quote (name field) options)
      " = :v:updates." (name field))))
~*/

实例调用

;; 更新一条
(common-db/insert-or-update-table-info!
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :info ["1" "name-01" "code-01"]
    :updates {:test_name "name-01"
              :test_code "code-01"}})
;; 返回结果 2

(common-db/insert-or-update-table-info!
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :info ["1" "name-01" "code-01"]
    :updates {:test_name "name-01"
              :test_code "code-01"}})
;; 返回结果 1

(common-db/insert-or-update-table-info!
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :info ["1" "name-001" "code-001"]
    :updates {:test_name "name-001"
              :test_code "code-001"}})
;; 返回结果 2

批量新增或更新

数据库

-- 多条新增或更新
insert into `t_test`
(`id`, `test_name`, `test_code`)
values
('1', 'name-01', 'code-01'),
('2', 'name-02', 'code-02')
ON DUPLICATE KEY
UPDATE 
  `test_name` = VALUES(`test_name`),
  `test_code` = VALUES(`test_code`);
-- 第一遍执行 4 row(s) affected Records: 2  Duplicates: 2  Warnings: 0
-- 第二遍执行 0 row(s) affected Records: 2  Duplicates: 0  Warnings: 0

结果:
id test_name test_code delete_flag
'1' 'name-01' 'code-01' '0'
'2' 'name-02' 'code-02' '0'
改为hugsql, 将表名、新增字段名、新增数据提取成变量传递

id	test_name	test_code	delete_flag
'1'	'name-01'	'code-01'	'0'
'2'	'name-02'	'code-02'	'0'

-- :name batch-insert-or-update! :! :n
-- :doc 新增或修改
/* :require [clojure.string :as string]
            [hugsql.parameters :refer [identifier-param-quote]] */
INSERT INTO :i:table-name
(:i*:cols)
VALUES
:t*:records
ON DUPLICATE KEY
UPDATE
/*~
(string/join ","
  (for [field (:cols params)]
    (str (identifier-param-quote field options)
      " = VALUES(" field ")")))
~*/

实例调用

;; 批量新增或更新
(common-db/batch-insert-or-update!
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :records [["1" "name-001" "code-001"]
              ["2" "name-002" "code-002"]]})
;; 返回结果 4

(common-db/batch-insert-or-update!
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :records [["1" "name-001" "code-001"]
              ["2" "name-002" "code-002"]]})
;; 返回结果 2

(common-db/batch-insert-or-update!
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :records [["1" "name-01" "code-01"]
              ["2" "name-02" "code-02"]]})
;; 返回结果 4

查询

查-返回所有字段或指定某些字段或多个查询条件

数据库

-- 查询所有字段，一个条件
select *
from `t_test`
where id = '1';

-- 查询所有字段，多个条件
select *
from `t_test`
where `id` = '1'
  and `test_name` = 'name-01';

-- 查询指定字段，多个条件
select `id`, `test_name`, `test_code`
from `t_test`
where `id` = '1'
  and `test_name` = 'name-01';

改为hugsql, 此处只写最复杂的一个，将表名、字段名、条件提取成变量传递

-- :name find-table-infos :? :*
-- :doc 查询列表, 查询指定字段，多个等于条件
/* :require [clojure.string :as string]
            [hugsql.parameters :refer [identifier-param-quote]] */
SELECT :i*:cols
FROM :i:table-name
WHERE 
/*~
(string/join " AND "
  (for [[field _] (:conds params)]
    (str (identifier-param-quote (name field) options)
      " = :v:conds." (name field))))
~*/

-- :name find-table-infos-complex :? :*
-- :doc 查询列表, 查询指定字段，多个条件不同运算符
/* :require [clojure.string :as string]
            [hugsql.parameters :refer [identifier-param-quote]] */
SELECT :i*:cols
FROM :i:table-name
WHERE 
/*~
(string/join " AND "
  (for [[field v] (:conds params)]
    (str (identifier-param-quote (name field) options)
      " " (or (:symbol v) "=") " "
      (if (contains? #{"in" "IN"} (:symbol v))
        (str "(:v*:conds." (name field) ".v)")
        (str ":v:conds." (name field) ".v")))))
~*/

实例调用

;; 查询指定字段，多个条件
(common-db/find-table-infos
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :conds {:test_code "code-01"
            :test_name "name-01"}})
;; 返回 ({:id "1", :test_name "name-01", :test_code "code-01"})

(common-db/find-table-infos-complex
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :conds {:id {:v "1"}
            :test_name {:v "%name%" :symbol "like"}}})
;; 返回 ({:id "1", :test_name "name-01", :test_code "code-01"})

(common-db/find-table-infos-complex
  {:table-name "t_test"
    :cols ["id" "test_name" "test_code"]
    :conds {:id {:v ["1" "2"] :symbol "in"}
            :test_code {:v "%code-0%" :symbol "like"}
            :test_name {:v "%name-0%" :symbol "like"}}})
;; 返回结果
;; ({:id "1", :test_name "name-01", :test_code "code-01"} 
;;  {:id "2", :test_name "name-02", :test_code "code-02"})

逻辑删除-类似更新

删除一般是逻辑删除，相当于更新操作

数据库

-- 逻辑删除
update `t_test` 
set `delete_flag` = '1'
where `id` in ('1', '2');

结果:
id test_name test_code delete_flag
'1' 'name-01' 'code-01' '1'
'2' 'name-02' 'code-02' '1'
hugsql

id	test_name	test_code	delete_flag
'1'	'name-01'	'code-01'	'1'
'2'	'name-02'	'code-02'	'1'

-- :name batch-logic-delete-table-infos! :! :n
-- :doc 批量删除信息, 更复杂的条件可以参考查询
UPDATE :i:table-name
SET delete_flag = '1'
WHERE :i:id-name in (:v*:ids)

实例调用

(common-db/batch-logic-delete! 
  {:table-name "t_test"
    :id-name "id"
    :ids ["1" "2"]})
;; 不管执行多少次，返回结果总是 2

Tags: mysql clojure

« Reagent文档: React Features Mac迁移到Windows的开发体验 »

hugsql 通用方法

数据库测试表

新增

新增一条或批量新增

更新

更新一条(因为更新时的where条件限制只能更新哪一条，此种写法不支持更新不同条件的数据)

新增或更新

批量新增或更新

查询

逻辑删除-类似更新

加入我们

文档&Doc

Recent Posts

Tags