hugsql 中文文档

.../examples/princess-bride $ tree
.
├── LICENSE
├── project.clj
├── README.md
├── resources
├── src
│   └── princess_bride
│       ├── core.clj
│       ├── db
│       │   ├── characters.clj
│       │   ├── quotes.clj
│       │   └── sql
│       │       ├── characters.sql
│       │       └── quotes.sql
│       └── db.clj

-- src/princess_bride/db/sql/characters.sql
-- The Princess Bride Characters

-- :name create-characters-table
-- :command :execute
-- :result :raw
-- :doc Create characters table
--  auto_increment and current_timestamp are
--  H2 Database specific (adjust to your DB)
create table characters (
  id         integer auto_increment primary key,
  name       varchar(40),
  specialty  varchar(40),
  created_at timestamp not null default current_timestamp
)

/* ...snip... */

-- A :result value of :n below will return affected rows:
-- :name insert-character :! :n
-- :doc Insert a single character returning affected row count
insert into characters (name, specialty)
values (:name, :specialty)

-- :name insert-characters :! :n
-- :doc Insert multiple characters with :tuple* parameter type
insert into characters (name, specialty)
values :tuple*:characters

/* ...snip... */

-- A ":result" value of ":1" specifies a single record
-- (as a hashmap) will be returned
-- :name character-by-id :? :1
-- :doc Get character by id
select * from characters
where id = :id

-- Let's specify some columns with the
-- identifier list parameter type :i* and
-- use a value list parameter type :v* for IN()
-- :name characters-by-ids-specify-cols :? :*
-- :doc Characters with returned columns specified
select :i*:cols from characters
where id in (:v*:ids)

(ns princess-bride.db.characters
  (:require [hugsql.core :as hugsql]))

;; The path is relative to the classpath (not proj dir!),
;; so "src" is not included in the path.
;; The same would apply if the sql was under "resources/..."
;; Also, notice the under_scored path compliant with
;; Clojure file paths for hyphenated namespaces
(hugsql/def-db-fns "princess_bride/db/sql/characters.sql")

;; For most HugSQL usage, you will not need the sqlvec functions.
;; However, sqlvec versions are useful during development and
;; for advanced usage with database functions.
(hugsql/def-sqlvec-fns "princess_bride/db/sql/characters.sql")

(characters/characters-by-ids-specify-cols-sqlvec
  {:ids [1 2], :cols ["name" "specialty"]})  ;;=>
["select name, specialty from characters
  where id in (?,?)"
,1
,2]

(characters/characters-by-ids-specify-cols db
  {:ids [1 2], :cols ["name" "specialty"]})  ;;=>
({:name "Westley", :specialty "love"}
 {:name "Buttercup", :specialty "beauty"})

=> (doc hugsql.core/def-db-fns)
-------------------------
hugsql.core/def-db-fns
([file] [file options])
Macro
  Given a HugSQL SQL file, define the database
   functions in the current namespace.

   Usage:

   (def-db-fns file options?)

   where:
    - file is a string file path in your classpath,
      a resource object (java.net.URL),
      or a file object (java.io.File)
    - options (optional) hashmap:
      {:quoting :off(default) | :ansi | :mysql | :mssql
       :adapter adapter }

   :quoting options for identifiers are:
     :ansi double-quotes: "identifier"
     :mysql backticks: `identifier`
     :mssql square brackets: [identifier]
     :off no quoting (default)

   Identifiers containing a period/dot . are split, quoted separately,
   and then rejoined. This supports myschema.mytable conventions.

   :quoting can be overridden as an option in the calls to functions
   created by def-db-fns.

   :adapter specifies the HugSQL adapter to use for all defined
   functions. The default adapter used is
   (hugsql.adapter.clojure-java-jdbc/hugsql-adapter-clojure-java-jdbc)
   when :adapter is not given.

   See also hugsql.core/set-adapter! to set adapter for all def-db-fns
   calls.  Also, :adapter can be specified for individual function
   calls (overriding set-adapter! and the :adapter option here).

[db]
[db params]
[db params options & command-options]

["select * from characters where id = ?", 2]

=> (doc hugsql.core/def-sqlvec-fns)
-------------------------
hugsql.core/def-sqlvec-fns
([file] [file options])
Macro
  Given a HugSQL SQL file, define the <name>-sqlvec functions in the
  current namespace.  Returns sqlvec format: a vector of SQL and
  parameter values. (e.g., ["select * from test where id = ?" 42])

  Usage:

   (def-sqlvec-fns file options?)

   where:
    - file is a string file path in your classpath,
      a resource object (java.net.URL),
      or a file object (java.io.File)
    - options (optional) hashmap:
      {:quoting :off(default) | :ansi | :mysql | :mssql
       :fn-suffix "-sqlvec" (default)

   :quoting options for identifiers are:
     :ansi double-quotes: "identifier"
     :mysql backticks: =identifier=
     :mssql square brackets: [identifier]
     :off no quoting (default)

   Identifiers containing a period/dot . are split, quoted separately,
   and then rejoined. This supports myschema.mytable conventions.

   :quoting can be overridden as an option in the calls to functions
   created by def-db-fns.

   :fn-suffix is appended to the defined function names to
   differentiate them from the functions defined by def-db-fns.

-- :name create-characters-table
-- :command :execute
-- :result :raw
-- :doc Create characters table
-- auto_increment and current_timestamp are
-- H2 Database specific (adjust to your DB)
create table characters (
id integer auto_increment primary key,
name varchar(40),
specialty varchar(40),
created_at timestamp not null default current_timestamp
)

/* The create-character-table definition above uses the full,
long-hand "-- :key :value" syntax to specify the :command and
:result. We can save some typing by using the short-hand notation
as the second and (optionally) third values for the :name. Below, the
:! is equivalent to ":command :!", where :! is an alias for
:execute. The default :result is :raw when not specified, so
there is no need to specify it as the third value. */

-- :name drop-characters-table :!
-- :doc Drop characters table if exists
drop table if exists characters

-- A :result value of :n below will return affected row count:
-- :name insert-character :! :n
-- :doc Insert a single character
insert into characters (name, specialty)
values (:name, :specialty)

-- :name insert-characters :! :n
-- :doc Insert multiple characters with :tuple* parameter type
insert into characters (name, specialty)
values :tuple*:characters

(characters/insert-character-sqlvec
 {:name "Westley", :specialty "love"})  ;;=>
["insert into characters (name, specialty)
  values (?, ?)"
,"Westley"
,"love"]

(characters/insert-character db
  {:name "Westley", :specialty "love"})  ;;=>
1

(characters/insert-character db
  {:name "Buttercup", :specialty "beauty"})  ;;=>
1

(characters/insert-characters-sqlvec
 {:characters
  [["Vizzini" "intelligence"]
   ["Fezzik" "strength"]
   ["Inigo Montoya" "swordmanship"]]})  ;;=>
["insert into characters (name, specialty)
  values (?,?),(?,?),(?,?)"
,"Vizzini"
,"intelligence"
,"Fezzik"
,"strength"
,"Inigo Montoya"
,"swordmanship"]

(characters/insert-characters
 db
 {:characters
  [["Vizzini" "intelligence"]
   ["Fezzik" "strength"]
   ["Inigo Montoya" "swordmanship"]]})  ;;=>
3

-- :name insert-into-test-table-returning :<!
-- :doc insert with an sql returning clause
insert into test (id, name) values (:id, :name) returning id

-- :name insert-into-test-table-return-keys :insert :raw
insert into test (id, name) values (:id, :name)

(testing "insert w/ return of .getGeneratedKeys"
;; return generated keys, which has varying support and return values
;; clojure.java.jdbc returns a hashmap, clojure.jdbc returns a vector of hashmaps
  (when (= adapter-name :clojure.java.jdbc)
    (condp = db-name
      :postgresql
      (is (= {:id 8 :name "H"}
          (insert-into-test-table-return-keys db {:id 8 :name "H"} {})))

      :mysql
      (is (= {:generated_key 9}
             (insert-into-test-table-return-keys db {:id 9 :name "I"})))

      :sqlite
      (is (= {(keyword "last_insert_rowid()") 10}
             (insert-into-test-table-return-keys db {:id 10 :name "J"} {})))

      :h2
      (is (= {(keyword "scope_identity()") 11}
             (insert-into-test-table-return-keys db {:id 11 :name "J"} {})))

      ;; hsql and derby don't seem to support .getGeneratedKeys
      nil))

  (when (= adapter-name :clojure.jdbc)
    (condp = db-name
      :postgresql
      (is (= [{:id 8 :name "H"}]
             (insert-into-test-table-return-keys db {:id 8 :name "H"} {})))

      :mysql
      (is (= [{:generated_key 9}]
             (insert-into-test-table-return-keys db {:id 9 :name "I"})))

      :sqlite
      (is (= [{(keyword "last_insert_rowid()") 10}]
             (insert-into-test-table-return-keys db {:id 10 :name "J"} {})))

      :h2
      (is (= [{(keyword "scope_identity()") 11}]
             (insert-into-test-table-return-keys db {:id 11 :name "J"} {})))

      ;; hsql and derby don't seem to support .getGeneratedKeys
      nil)))

-- :name update-character-specialty :! :n
update characters
set specialty = :specialty
where id = :id

(let [vizzini (characters/character-by-name db {:name "vizzini"})]
 (characters/update-character-specialty-sqlvec
  {:id (:id vizzini), :specialty "boasting"}))  ;;=>
["update characters
  set specialty = ?
  where id = ?"
,"boasting"
,3]

(let [vizzini (characters/character-by-name db {:name "vizzini"})]
 (characters/update-character-specialty db
  {:id (:id vizzini), :specialty "boasting"}))  ;;=>
1

-- :name delete-character-by-id :! :n
delete from characters where id = :id

(let [vizzini (characters/character-by-name db {:name "vizzini"})]
  (characters/delete-character-by-id-sqlvec {:id (:id vizzini)}))  ;;=>
["delete from characters where id = ?",3]

(let [vizzini (characters/character-by-name db {:name "vizzini"})]
  (characters/delete-character-by-id db {:id (:id vizzini)}))  ;;=>
1

-- A ":result" value of ":*" specifies a vector of records
-- (as hashmaps) will be returned
-- :name all-characters :? :*
-- :doc Get all characters
select * from characters
order by id

-- A ":result" value of ":1" specifies a single record
-- (as a hashmap) will be returned
-- :name character-by-id :? :1
-- :doc Get character by id
select * from characters
where id = :id

-- :name character-by-name :? :1
-- :doc Get character by case-insensitive name
select * from characters
where upper(name) = upper(:name)

-- :name characters-by-name-like :?
-- :doc Get characters by name like, :name-like should include % wildcards
select * from characters
where name like :name-like

-- Let's specify some columns with the
-- identifier list parameter type :i* and
-- use a value list parameter type :v* for SQL IN()
-- :name characters-by-ids-specify-cols :? :*
-- :doc Characters with returned columns specified
select :i*:cols from characters
where id in (:v*:ids)

(clojure.java.jdbc/with-db-transaction [tx db]
  (characters/insert-character tx
   {:name "Miracle Max", :specialty "miracles"})
  (characters/insert-character tx
   {:name "Valerie", :specialty "speech interpreter"}))

(is (= [[:name] ["A"] ["B"]]
      (select-ordered db
        {:cols ["name"] :sort-by ["name"]} {} {:as-arrays? true})))

-- :key value1 value2 value3
OR
/* :key
value1
value2
value3
*/

-- regular SQL comment ignored by hugsql
/*
regular SQL multi-line comment ignored by hugsql
*/

-- :name query-get-many :? :*
-- :doc My query doc string to end of this line
select * from my_table;

-- :name query-get-one :? :1
/* :doc
My multi-line
comment doc string
*/
select * from my_table limit 1

-- :name all-characters :?

-- :name all-characters :? :*

:param-name

:param-type:param-name

com.layerware/hugsql-core {:mvn/version "0.5.3"}
com.layerware/hugsql-adapter-next-jdbc {:mvn/version "0.5.3"}

[com.layerware/hugsql-core "0.5.3"]
[com.layerware/hugsql-adapter-next-jdbc "0.5.3"]

com.layerware/hugsql-core {:mvn/version "0.5.3"}
com.layerware/hugsql-adapter-clojure-java-jdbc {:mvn/version "0.5.3"}

[com.layerware/hugsql-core "0.5.3"]
[com.layerware/hugsql-adapter-clojure-java-jdbc "0.5.3"]

com.layerware/hugsql-core {:mvn/version "0.5.3"}
com.layerware/hugsql-adapter-clojure-jdbc {:mvn/version "0.5.3"}

[com.layerware/hugsql-core "0.5.3"]
[com.layerware/hugsql-adapter-clojure-jdbc "0.5.3"]

(ns my-app
  (:require [hugsql.core :as hugsql]
            [hugsql.adapter.next-jdbc :as next-adapter
            [next.jdbc.result-set :as rs]])

(defn app-init []
  (hugsql/set-adapter! (next-adapter/hugsql-adapter-next-jdbc)))

;; OR override the :builder-fn behavior
(defn app-init []
  (hugsql/set-adapter! (next-adapter/hugsql-adapter-next-jdbc {:builder-fn result-set/as-unqualified-maps})))

(ns my-db-stuff
  (:require [hugsql.core :as hugsql]
            [hugsql.adapter.next-jdbc :as next-adapter]))

(hugsql/def-db-fns "path/to/db.sql"
  {:adapter (next-adapter/hugsql-adapter-next-jdbc)})

(ns my-db-stuff
(:require [hugsql.core :as hugsql]
          [hugsql.adapter.next-jdbc :as next-adapter]))

(def db ;;a db-spec here)

(hugsql/def-db-fns "path/to/db.sql")

(my-query db {:id 1}
  {:adapter (next-adapter/hugsql-adapter-next-jdbc)})