MacOS Xで使うMySQL

説明をはしょっている部分はご容赦下さい。
なお、次の書籍を参考としました。
「MySQL徹底入門」日本MySQLユーザー会（翔泳社）
「MySQL活用ガイド」株式会社ソフトエージェンシー監修（秀和システム）
＊「活用ガイド」はWindows向けの内容ですが、分かりやすくて良い本です。

０）MySQLという選択肢

OS X上ではPostgreSQLも動作しますが、MySQLはコマンドが覚えやすく、入門書も親切なものが多いので、いくらかでも習得しやすいのではないかと思っています。（ヴァージョン４にならないと副問い合わせの機能がありませんが、テーブルの結合だとかトランザクションは大丈夫です。）


１）バイナリの入手とインストール

有名かもしれませんが、Macintosh用は下記URLから入手出来ます。
http://www.entropy.ch/software/macosx/mysql
OS Xでのインストール方法、動作の確認方法、JDBCの簡単なテストプログラムまで、親切に記載されています（むずかしくない英語です）。Macintoshの起動と同時にMySQLが起動できるプログラムも、ダウンロード出来ます。
ですので、インストール方法と動作確認法については、そちらをご覧下さい。
JDBCドライバも入手しておきましょう。
（PostgrSQLも、http://www.entropy.ch/software/macosx/postgresql　にあります。こちらはJDBCドライバまでセットになっています。）
Macintoshではパッケージのダブルクリックでインストール出来てしまいます。


２）TerminalでmySQLを操作する

MySQLが使えるようになったことを前提に進めます。

・ajという名前のデータベースを作る。（名前は随意ですが・・・）

[localhost:/usr/local/mysql] hongou_k% mysqladmin -uroot -p create aj
確認は、MySQLのルートで行う。
[localhost:/usr/local/mysql] hongou_k% mysql -u root mysql
起動したときのメッセージ：
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 3.23.47-entropy.ch

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

使うデータベース名を指定してみる。
mysql> use aj
Database changed
mysql> show tables ;
まだテーブルを作っていないので、
Empty set (0.00 sec)
と出ました。

・テーブルを作る

mysql> create table adressbook ( FirstName char(20) , LastName char(20) , Tel char(12) );
Query OK, 0 rows affected (0.42 sec)

mysql> show tables ;
+--------------+
| Tables_in_aj |
+--------------+
| adressbook |
+--------------+
1 row in set (0.00 sec)

mysql> show fields from adressbook;（desc adressbook;でもよい）
+-----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+-------+
| FirstName | char(20) | YES | | NULL | |
| LastName | char(20) | YES | | NULL | |
| Tel | char(12) | YES | | NULL | |
+-----------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)
以上のようなテーブルが出来ました。

・ユーザーの追加

次のようにしました。この方法でhongouさんに与えられる権限は、select * from user以下で確認してある通りです。

[localhost:/usr/local/mysql] hongou_k% mysql -u root mysql
（出てくるメッセージは省略）

mysql> insert into user values ('%', 'hongou', password('shiori'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
Query OK, 1 row affected (0.47 sec)


新設のユーザーがデータベースを使えるようになるには、データベースを使う権限をもつことが必要です。上記のようだと権限だらけで、普通は適切ではありません。本来は、たとえば、hongouさんがajデータベースを使えるようになるには、ルートユーザーが
mysql> grant select,insert,update,delete,create,drop
-> on aj.*
-> to hongou@localhost
-> identified by 'shiori';
Query OK, 0 rows affected (0.09 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
という具合に権限を与えてくれる必要があります。

・テキストファイルを読み込む
次のようなテーブルを作り、そこへ郵政省の公開データである郵便番号簿のテキストファイルを読み込みます。ファイル名はzip.csvとします。読み込むzip.csvは、あらかじめ/usr/local/mysql/dataに
sudo mv zip.csv /usr/local/mysql/data
などとやって、移動しておきます（UNIXの入門書でファイル操作の仕方を確認して下さい）。
desc zipcode;
+------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+-------+
| PreID | char(5) | YES | | | |
| OldCode | char(5) | YES | | | |
| NewCode | char(7) | YES | | | |
| PreKana | char(50) | YES | | | |
| CityKana | char(100) | YES | | | |
| TownKana | char(100) | YES | | | |
| Prefecture | char(50) | YES | | | |
| City | char(100) | YES | | | |
| Town | char(100) | YES | | | |
| f1 | int(11) | YES | | | |
| f2 | int(11) | YES | | | |
| f3 | int(11) | YES | | | |
| f4 | int(11) | YES | | | |
| f5 | int(11) | YES | | | |
| f6 | int(11) | YES | | | |
+------------+-----------+------+-----+---------+-------+

load data infile 'zip.csv' into table zipcode;

膨大なデータなので、とりあえず中身の確認はしません。あとでJSPを試すときに使ってみます。


３）MySQLで日本語を使う：CocoaSQL

Terminalでは日本語表示が出来ないので、日本語も表示できる道具が無いと困ります。上記サイトではMacSQLというGUIツールが紹介されていますが、シェアウェアです。シェアウェアは他にも何種類かあるようです。
フリーウェアでは、CocoaSQLというのがあります。MySQLでだけ使えます。上部のQueryPaneにSQLコマンドを打ち込むと、下部のTabular ResultsとTextual Resultsに実行結果が表示される、というシンプルな仕組みですが、日本語が通ります（ただし、Sift-JISでないと文字化けします）。ちょっとプログラミングに詳しい人は、同じようなものが自作できそうです（私は、ダメです・・・）。

ここから入手出来ます。
http://homepage.mac.com/mxcantor

CocoaSQLをダウンロードし、ダブルクリックで起動すると、次画面のようなダイアログが出ます。

ホストのアドレス（上例は自機）、データベースの名前、データベースのユーザー名、パスワードを入力すれば、データベースに接続することが出来ます。もちろん、データベースサーバーが動いていることが必要です。あらかじめ、ProcessViewerで、mysqldが動作していることを確認しておきましょう。

データベースへのアクセスが確認されたら、次画面のように、上部のQueryPaneにSQLコマンドを打ち込み、executeボタンをクリックすると、下部のTabular ResultsやTextual Resultsに実行結果が表示されます。

ご覧の通り、日本語がちゃんと表示されます。日本語のデータを挿入することも出来ます。
下部左側には、データベース内の全てのテーブル名称が出ています。テーブル名をダブルクリックすると、「SELECT * FROM テーブル名」が実行されるようです。データの多いテーブルをうかつにダブルクリックしないよう、気をつけましょう。
なお、INSERT文やDELETE文は、実行しても、結果がすぐにはTabular Resultsに現れません。結果を見るには、テーブルを再読み込みする必要があります。
Tabular Resultsウィンドウでは、マウスドラッグで列の幅を変えることが出来ます。また、データをダブルクリックすると、そのデータが編集可能になり、書き換えることが出来ます。
Functionsプルダウンに用意された関数／演算子（全12種）は、選択すると、自動的にQueryPaneに書き込まれます。


４）JSPでMySQLに接続する〜JSPを実行できるようにする方法はこちら（リンクを探しておく）

はなからＪＳＰでいきたいと思います。javacやProjectBuilderでコンパイルする必要がなく、Tomcatにビルドをゆだねられるからです。

・Tomcatが起動していることを確認します。
・JDBCドライバを入手
mm.mysql-2.0.2-src.jarを入手
（入手先はhttp://www.worldserver.com/mm.mysql/）
・Terminal上で、mm.mysql-2.0.2-src.jarを展開する。
[localhost:/usr/local] hongou_k% jar -xf mm.mysql-2.0.2-src.jar
といったかんじ。
・図のように「mm,mysql-2.02」フォルダが出来、その中に「mysql.jar」ができるので、複製して、クラスパスの通っているディレクトリ（Library/Java/Extensions）フォルダに置く。



・JSPのひな形
MySQLサーバにJDBCで接続するひな形を作っておきます。文字化けによる気苦労がないように、まずは日本語を含まないひな形としておきます（エディタはJedit4が無難です。保存は、Sift-JISで改行LFが良いです）。

hinagata.jsp
<%@ page import="java.sql.*" %> //java.sqlパッケージをインポートします。
<%@ page contentType="text/html" %> //日本語エンコードはとりあえずやらない

//以下、実行部（JSPが実行するjavaプログラム）
<%
Connection con; //データベースへの接続
Statement stmt; //SQL実行用のStatement
String message; //接続の状況につきHTMLで表示するメッセージ
String url = "jdbc:mysql://localhost/test"; //urlの構造はjdbc:<subProtcol>:<subName>
//subProtocolはmysql、接続するのはlocalhost上のtestデータベース
String user = "root"; //ユーザーの名前
String passwd = ""; //ユーザーのパスワード

try {
Class.forName("org.gjt.mm.mysql.Driver").newInstance(); //mySQLのJDBCドライバをロード
con = DriverManager.getConnection(url,user,passwd); //接続を確立
stmt = con.createStatement(); //SQL格納の準備
message = "Success!"; //エラーがなければこちらが表示される
System.out.println("OK!"); //念のためTerminalにもうまくいったと表示

con.close(); //データベースへの接続を閉じる
stmt.close(); //Statementを閉じる
} catch (Exception e ) {
System.out.println("Error" + e); //エラーメッセージをTerminalに表示
message = "False!"; //ブラウザ上で失敗を告げる・・・
System.exit(1);
}

%>

//以下、HTML表示部
<html>
<head>
<title>html output</title>
</head>
<body>
<h1>html output</h1>
<p>
<%= message %> //変数messageの値を表示する
</p>
</body>
</html>

実行し、うまく接続できると、次のように表示されます。

また、Tomcatを起動しているプロセスのTerminal画面に、OK! と表示されます。

・テーブルを表示するように、ひな形に付け加えます。日本語のエンコードも付け加えます。赤い字のところが付加した部分です。郵便番号テーブルzipcodeを使います。
郵便番号検索システムは、PostgreSQLで作られたものが公表されていますが、いまは自分の勉強のためにＪＳＰを作るので、そのあたりのことは良しとしましょう。

showzip.jsp
<%@ page import="java.sql.*" %>
<%@ page contentType="text/html; charset=Shift_JIS" %>

<%
Connection con;
Statement stmt;
String message;
String url = "jdbc:mysql://localhost/aj"; //jdbc:<subProtcol>:<subName>
String user = "root";
String passwd = "";
String cmd; //SQLコマンドを格納
ResultSet rs; //SQL実行結果を格納
String rows = ""; //rsをブラウザに表形式で表示するよう加工するための変数

try {
//Connect DB
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
con = DriverManager.getConnection(url,user,passwd);
stmt = con.createStatement();
message = "Welcome to MySQL!";
System.out.println("OK!");

//DO SQL テーブル構造を参照のこと
cmd = "SELECT OldCode, NewCode, Prefecture, City, Town FROM zipcode WHERE OldCode = \"983\"";
//二重引用符は\でエスケープする必要がある
rs = stmt.executeQuery(cmd);
while (rs.next()) {
String OldCode = rs.getString("OldCode");
String NewCode = rs.getString("NewCode");
String Prefecture = rs.getString("Prefecture");
String City = rs.getString("City");
String Town = rs.getString("Town");
rows += "<tr><td>" + OldCode + "</td><td>" + NewCode + "</td><td>" + Prefecture
+ " </td><td>" + City + " </td><td>" + Town + "</tr>\n";
}

//Kill connection
con.close();
stmt.close();
} catch (Exception e ) {
System.out.println("Error" + e);
message = "False!";
System.exit(1);
}
%>

<html>
<head>
<title>html output</title>
</head>
<body>
<h1>html output</h1>
<p>
<%= message %>
<table border=1>
<%= rows %>
</table>
</p>
</body>
</html>

実行すると、旧郵便番号が983の都道府県・市区町村名を表示します。

cmd = "SELECT OldCode, NewCode, Prefecture, City, Town FROM zipcode WHERE OldCode = \"983\"";
の983を適宜書き換えれば、それに沿った結果となります。

以上で、ＪＳＰでMySQLのテーブルがSQLにより操作できることまでを確認しました。
使いやすくするには、ブラウザからの入力を受け付けたり、機能によってモジュール化したりする工夫が必要です。