Web server and DB integration(3)DB connection and control

Artificial Intelligence Technology   Semantic Web Technology   Search Technology     DataBase Technology   Ontology Technology   Digital Transformation Technology   User Interface and DataVisualization   Workflow & Services.  Programming  Clojure 　　　Database Technologies

In the previous article, we have covered setting up the server. In this article, we will start up the DB and connect and control it using Clojure.

As a DB, we will first target postgresql, a free and open relational database management system (RDMS) with the trademark elephant.

Postgresql is a free and open relational database management system (RDMS) with the trademark of the elephant. a “relational database” is built based on a “relational model”, which is a model based on the assumption that all data is represented by n relationships. (e.g., customer (customer ID, name, address, city, prefecture, zip code, phone number)). The database language (query language) for manipulating and defining data in this RDMS is called SQL.

There are several ways to install postgresql, such as downloading and installing the installer from the official page, or using homebrew on mac. Here I will describe the method using homebrew.

Basic operations are performed in a terminal. The following sample code shows a series of steps from installing postgresql, starting postgresql, creating a database, logging in to the database, creating a table, and inserting/retrieving data.

#postgresqlをインストール
brew install postgresql  
#postgresqlをスタート
brew services start postgresql  
#psql(データベースやテーブルを作成したりデータの追加や取得をするためのコマンドラインベースツール)のログイン
psql postgres
#DB作成(データベース名my-dbをowner名postgresで生成)
create database my-db owner=postgres;
#作成したDBを確認
l
#DB削除
Drop database my-db;
#終了
q
#DBログイン
psql my-db -U postgres
#テーブル名と、テーブルの全ての列の名前と型を指定することで、新しいテーブルを作成する。
CREATE TABLE weather (
    city            varchar(80),
    temp_lo         int,           -- 最低気温
    temp_hi         int,           -- 最高気温
    prcp            real,          -- 降水量
    date            date         --日付
);
#テーブル確認
 d
#テーブル削除
DROP TABLE weather;
#データの挿入
INSERT INTO weather ( city, temp_lo, temp_hi) VAKUES (‘tokyo’, 5, 36);
#検索
SELECT city FROM weather;
#データの変更
UPDATE weather SET city ‘Osaka’  WHERE id =1 ;
#データの削除
DELETE FROM weather  WHERE id = 1;
#終了
q
#postgresqlストップ
brew services stop postgresql

As an example, we have created the database “my-db” and the table “weather” in the above operation. Next, we will show how to start up the server, connect to the DB, and exchange data. Before executing the code, it is necessary to start up the db as described above. (Be sure to stop posgres afterwards).

(ns db-test01.core)

(require '[clojure.java.jdbc :as jdbc]
         '[java-jdbc.ddl :as ddl]
         '[compojure.core :refer [defroutes context GET POST]])

;;db設定
(def db-spec 
{:dbtype "postgresql" :dbname "my-db" :host "localhost" :port 5432})

;;テーブル作成
(jdbc/db-do-commands db-spec
                     (ddl/create-table :member
                                            [:id :serial]
                                            [:name "varchar(20)" "not null"]
                                            [:age :int]
                                            [:address :varchar]))

;; dbへのデータ追加
(jdbc/insert! db-spec :member {:name "nobody"})
(jdbc/insert! db-spec :member {:name "mrbox"})
(jdbc/insert! db-spec :member {:name "alice" :address "wonderland"})
(jdbc/insert! db-spec :member {:name nil})
(jdbc/insert! db-spec :member [:name :address] ["cheshire" nil] ["mad hatter" nil])

;; dbからのデータ確認
(jdbc/query "postgresql://localhost:5432/my-db"
            ["select *from member"])

;; dbのデータ更新  idが1の年齢を24に変える
(jdbc/update! db-spec :member {:age 24} ["id = ?" 1])

;; データ取得(2)
(jdbc/query db-spec "select * from member")

(jdbc/query db-spec ["select * from member where id = ?" 1])

(jdbc/query db-spec ["select * from member where id = ?" 2])

;;テーブルの削除
(jdbc/db-do-commands db-spec (jdbc/drop-table-ddl :fruit))
(jdbc/db-do-commands db-spec (jdbc/drop-table-ddl :member))
(jdbc/query db-spec "select * from fruit")

(jdbc/db-do-commands db-spec
                     (ddl/create-table :fruit
                                       [:name "varchar(16)" "PRIMARY KEY"]
                                       [:appearance "varchar(32)"]
                                       [:cost :int "NOT NULL"]
                                       [:unit "varchar(16)"]
                                       [:grade :real]))

(jdbc/insert! db-spec :fruit
              nil ; column names omitted
              ["Red Delicious" "dark red" 20 "bushel" 8.2]
              ["Plantain" "mild spotting" 48 "stalk" 7.4]
              ["Kiwifruit" "fresh"  35 "crate" 9.1]
              ["Plum" "ripe" 12 "carton" 8.4])

The following is a sample code that connects to the db set up in “db setup”, creates multiple tables, inserts data, and searches.

In the next article, I would like to take a little detour and discuss database technology.