clojure JDBC SQLite HoneySQL

ClojureでDBを触る練習の個人的なメモ

org.clojure/java.jdbc 0.7.12 : JDBC(Java DataBase Connectivity) でDBへ接続するためのClojure ラッパー
- JDBCとは、Javaプログラムからデータベースにアクセスするための標準インターフェース（API）の一つ。データベースの違いによらず同じ手順で接続し、データを読み書きすることができる
org.xerial:sqlite-jdbc：JDBCでSQLITEに接続するためのドライバ。

ディレクトリ構成

├── deps.edn
└── src
    └── db.clj

しんせいたろう

deps.edn

{:paths ["src"]
 :deps {org.clojure/java.jdbc {:mvn/version "0.7.12"}
        org.xerial/sqlite-jdbc {:mvn/version "3.43.0.0"}}}

しんせいたろう

接続テスト

src/db.clj

※ここから先は、個人的な好みで、評価実行はComment関数に入れます。

(ns db
  (:require [clojure.java.jdbc :as jdbc]))

(def db-spec
  {:classname   "org.sqlite.JDBC"
   :subprotocol "sqlite"
   :subname     "database.db"})

(comment
  (jdbc/query db-spec
              ["SELECT 3*5 AS result"]) ;; ({:result 15})
  :rcf)

コメント内をREPLしたあとに、database.db も生成されていることを確認

しんせいたろう

テーブル作成と削除

jdbc/create-table-ddl / jdbc/drop-table-ddl 関数を使ってコマンドを作成する
keyword と vector で、文を作成できる関数

db.clj

(def create-user-table 
  (jdbc/create-table-ddl
   :user
   [[:name "TEXT"]
    [:email "TEXT"]
    [:age :int]]))

(def drop-user-table 
  (jdbc/drop-table-ddl :user))

(comment 
  create-user-table  ;; "CREATE TABLE user (name TEXT, email TEXT, age int)"
  drop-user-table  ;; "DROP TABLE user"
  :rcf)

実行： jdbc/db-do-commands 関数
db-spec と statement を渡して、コマンドを実行する

(comment
  (jdbc/db-do-commands db-spec
                       create-user-table)
  (jdbc/db-do-commands db-spec
                       drop-user-table)
  :rcf)

index をつけたいとき

(jdbc/db-do-commands db-spec
                       [create-user-table
                        "CREATE INDEX name_ix ON user ( name );"])

しんせいたろう

データ挿入、クエリ、更新、削除


(comment
  (jdbc/insert! db-spec :user {:name "John"
                               :email "john@test.com"
                               :age 30}) ;; ((1))
  (jdbc/insert! db-spec :user {:name "Mike"
                               :email "mike@test.com"
                               :age 20})  ;; ((1)) 
  (jdbc/query db-spec ["SELECT * FROM user"]) ;; ({:name "John", :email "john@test.com", :age 30} {:name "Mike", :email "mike@test.com", :age 20})
  
  :rcf)

(comment
  (jdbc/update! db-spec :user {:age 100} ["name = ?" "John"]) ;; ((1))
  (jdbc/query db-spec ["SELECT * FROM user"]) ;; ({:name "John", :email "john@test.com", :age 100} {:name "Mike", :email "mike@test.com", :age 20})
  :rcf)

(comment
  (jdbc/delete! db-spec :user ["name = ?" "John"]) ;; (1)
  (jdbc/query db-spec ["SELECT * FROM user"]) ;; ({:name "Mike", :email "mike@test.com", :age 20})
  :rcf)

しんせいたろう

clojure data で SQL を表現する honeysql

しんせいたろう

deps.edn

{:paths ["src"]
 :deps {org.clojure/java.jdbc {:mvn/version "0.7.12"}
        org.xerial/sqlite-jdbc {:mvn/version "3.43.0.0"}
        com.github.seancorfield/honeysql {:mvn/version "2.4.1066"}}}

しんせいたろう

honeysql

SQL文をハッシュマップとキーワードで表現
format 関数で評価してSQL文を評価すると文字列でSQL文がベクターで返る

(ns db
  (:require [clojure.java.jdbc :as jdbc]
            [honey.sql :as sql]))


(comment
  (-> {:select [:name] :from [:user]}
      (sql/format)) ;; ["SELECT name FROM user"]
  (-> {:select [:*] :from [:user]}
      (sql/format)) ;; ["SELECT * FROM user"]

  :rcf)

しんせいたろう

honey.sql.helpers

select や from なども関数として書ける

(ns db
  (:require [clojure.java.jdbc :as jdbc]
            [honey.sql :as sql]
            [honey.sql.helpers :as h]))
(comment
  (-> (h/select :name)
      (h/from :user)) ;; {:select [:name], :from [:user]}

  (-> (h/select :name)
      (h/from :user)
      (sql/format)) ;; ["SELECT name FROM user"]

 (-> (h/create-table :user)
     (h/with-columns [[:name :text]
                      [:email :text]
                      [:age :int]])
     (sql/format))) ;; ["CREATE TABLE user (name TEXT, email TEXT, age INT)"]

  :rcf)

しんせいたろう

クエリ実行

(comment
  (jdbc/query db-spec (-> (h/select :name)
                          (h/from :user)
                          (sql/format))) ;; ({:name "Mike"})
  (jdbc/query db-spec (-> (h/select :*)
                          (h/from :user)
                          (sql/format))) ;; ({:name "Mike", :email "mike@test.com", :age 20})
  :rcf)

しんせいたろう

テーブル作成

(ns db
  (:require [clojure.java.jdbc :as jdbc]
            [honey.sql :as sql]
            [honey.sql.helpers :as h]
            [clojure.java.io :as io]))

(def db-spec
  {:classname   "org.sqlite.JDBC"
   :subprotocol "sqlite"
   :subname     "database.db"})

(def create-table
  (-> (h/create-table :user)
      (h/with-columns [[:name :text]
                       [:email :text]
                       [:age :int]])
      (sql/format)))

(defn create-db! []
  (when-not (.exists (io/file "database.db"))
    (jdbc/db-do-commands db-spec create-table)))

(comment
  (create-db!)
  :rcf)

しんせいたろう

データ追加

jdbc/execute! db-spec [vector sql] を使ってInsertする。


(defn insert! []
  (jdbc/execute! db-spec
                 (-> (h/insert-into :user)
                     (h/columns :name :email :age)
                     (h/values
                      [["Jon" "Smith@test.com" 34]
                       ["Andrew" "Cooper@test.com" 12]
                       ["Jane" "Daniels@test.com" 56]])
                     (sql/format))))

(comment
  (insert!)
  :rcf)

ここで渡されたインサート文を展開すると

(-> (h/insert-into :user)
    (h/columns :name :email :age)
    (h/values
     [["Jon" "Smith@test.com" 34]
      ["Andrew" "Cooper@test.com" 12]
      ["Jane" "Daniels@test.com" 56]])
    (sql/format))

最初にINSERT文が、その後は、パラメータが続く形

["INSERT INTO user (name, email, age) VALUES (?, ?, ?), (?, ?, ?), (?, ?, ?)"
 "Jon"  "Smith@test.com"  34
 "Andrew"  "Cooper@test.com"  12
 "Jane"  "Daniels@test.com"  56]

このコマンドを実行するときは、jdbc/execute! db-spec [vector sql] を使う。