Skip to content

Instantly share code, notes, and snippets.

@bouzuya
Created Jan 19, 2013
Embed
What would you like to do?
;;; [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)
@bouzuya
Copy link
Author

bouzuya commented Jan 19, 2013

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

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment