core.clj

;;; [PostgreSQL Tutorial][postgresql-tutorial] in H2
;;;
;;; [postgresql-tutorial]: http://www.postgresql.jp/document/8.2/html/tutorial-table.html

(ns h2-example.core
  (:require [clojure.java.io :as jio]
            [clojure.java.jdbc :as jdbc]
            [clojure.string :as str]))


(def db
  {:classname "org.h2.Driver"
   :subprotocol "h2"
   :subname (.getAbsolutePath
              (jio/file
                (System/getProperty "user.home")
                "clj-h2-example"))
   :user "sa"
   :password ""})

(defn connect-to-database
  []
  (jdbc/with-connection
    db
    (jdbc/with-query-results
      results
      ["SELECT CURRENT_DATE"]
      (into [] results))))

(defn create-table-weather
  []
  (jdbc/with-connection
    db
    (jdbc/do-commands
      (str
        "CREATE TABLE IF NOT EXISTS weather ("
        "  city     varchar(80),"
        "  temp_lo  int,"
        "  temp_hi  int,"
        "  prcp     real,"
        "  date     date"
        ")"))))

(defn drop-table-weather
  []
  (jdbc/with-connection
    db
    (jdbc/do-commands "DROP TABLE weather")))

(defn create-table-cities
  []
  (jdbc/with-connection
    db
    (jdbc/do-commands
      (str
        "CREATE TABLE IF NOT EXISTS cities ("
        "  name       varchar(80),"
        "  latitude   double,"
        "  longitude  double"
        ")"))))

(defn drop-table-cities
  []
  (jdbc/with-connection
    db
    (jdbc/do-commands "DROP TABLE cities")))

(defn insert-into-weather
  []
  (jdbc/with-connection
    db
    (jdbc/do-commands
      (str
        "INSERT INTO weather(city, temp_lo, temp_hi, prcp, date) "
        "VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27')")
      (str
        "INSERT INTO weather(city, temp_lo, temp_hi, prcp, date) "
        "VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29')")
      (str
        "INSERT INTO weather(city, temp_lo, temp_hi, prcp, date) "
        "VALUES ('Hayward', 37, 54, NULL, '1994-11-29')"))))

(defn insert-into-cities
  []
  (jdbc/with-connection
    db
    (jdbc/do-commands
      (str
        "INSERT INTO cities (name, latitude, longitude) "
        "VALUES ('San Francisco', -194.0, 53.0)"))))

(defn select-from-weather-all
  []
  (jdbc/with-connection
    db
    (jdbc/with-query-results
      result-set
      ["SELECT * FROM weather"]
      (doseq [row result-set]
        (let [keys [:city :temp_lo :temp_hi :prcp :date]]
          (println (str/join " " (interleave keys ((apply juxt keys) row)))))))))

(defn select-from-weather-expr
  []
  (jdbc/with-connection
    db
    (jdbc/with-query-results
      result-set
      ["SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather"]
      (doseq [row result-set]
        (let [keys [:city :temp_avg :date]]
          (println (str/join " " (interleave keys ((apply juxt keys) row)))))))))

(defn select-from-weather-where
  []
  (jdbc/with-connection
    db
    (jdbc/with-query-results
      result-set
      ["SELECT * FROM weather WHERE city = 'San Francisco' AND prcp > 0.0"]
      (doseq [row result-set]
        (let [keys [:city :temp_lo :temp_hi :prcp :date]]
          (println (str/join " " (interleave keys ((apply juxt keys) row)))))))))

(defn select-from-weather-order-by
  []
  (jdbc/with-connection
    db
    (jdbc/with-query-results
      result-set
      ["SELECT * FROM weather ORDER BY city, temp_lo"]
      (doseq [row result-set]
        (let [keys [:city :temp_lo :temp_hi :prcp :date]]
          (println (str/join " " (interleave keys ((apply juxt keys) row)))))))))

(defn select-from-weather-distinct
  []
  (jdbc/with-connection
    db
    (jdbc/with-query-results
      result-set
      ["SELECT DISTINCT city FROM weather"]
      (doseq [row result-set]
        (let [keys [:city]]
          (println (str/join " " (interleave keys ((apply juxt keys) row)))))))))

(defn select-from-weather-and-cities
  []
  (jdbc/with-connection
    db
    (jdbc/with-query-results
      result-set
      ["SELECT * FROM weather w JOIN cities c ON w.city = c.name"]
      (doseq [row result-set]
        (let [keys [:city :temp_lo :temp_hi :prcp :date :name :latitude :longitude]]
          (println (str/join " " (interleave keys ((apply juxt keys) row)))))))))

(connect-to-database)
(create-table-weather)
(create-table-cities)

(insert-into-weather)
(insert-into-cities)

(select-from-weather-all)
(select-from-weather-expr)
(select-from-weather-where)
(select-from-weather-order-by)
(select-from-weather-distinct)
(select-from-weather-and-cities)

(drop-table-weather)
(drop-table-cities)

とりあえず CREATE TABLE/DROP TABLE/SELECT 。H2 は標準準拠度が高かったように記憶しているので、ごく普通の SQL は通る。

CREATE TABLE/DROP TABLE のような DDL など、パラメーターなどのない SQL を発行したい場合は do-commands で OK 。今回は INSERT などもそれでごまかした。

INSERT/UPDATE/DELETE についても触っておく必要があるかもしれない。