Created
January 19, 2013 00:23
-
-
Save bouzuya/4569831 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
;;; [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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
とりあえず CREATE TABLE/DROP TABLE/SELECT 。H2 は標準準拠度が高かったように記憶しているので、ごく普通の SQL は通る。
CREATE TABLE/DROP TABLE のような DDL など、パラメーターなどのない SQL を発行したい場合は
do-commands
で OK 。今回は INSERT などもそれでごまかした。INSERT/UPDATE/DELETE についても触っておく必要があるかもしれない。