05．設計5： データアクセス・DBの設計
概要
ここでは、DaoやDBのテーブルなどの設計をみてみます。
テーブル設計
基本的には、テーブルのカラムはNOT NULLにします。
何故かと言うと、SQL文でNULLを扱うのは面倒で、間違えやすいからです。
例えば、nameの値をキーにしてレコードを取得する場合、以下は正しいでしょうか？
【以下は正しい？】
select * from t_member where name = ?
nameカラムがNOT NULLの場合は正しいです。
しかし、NULLを許容する場合は正しくありません。
【NULLを許容する場合の正しい書き方】
select * from t_member where ( (? is not null and name = ?) or (? is null and name is null))
のように書かないと?がNULL値のときに期待通りの動きをしません。
かなり難しく、レベルの違うすべてのプログラマが正しく書けるとは、とても思えません。
また、JDBCはnullの場合にJavaの型を判別できないという特性があるので、
POJOのプロパティ値がNULLかどうかを判定してsetNullをするような処理が必要です。
つまり、コード量が増え、上記のようにSQL文のミスを誘い、メンテが大変になります。
NULLを許容するメリットはDBのハードディスク使用量を減らすくらいしかありません。
今はハードディスク容量が大きいので不足に悩むことも少ないです。
NOT NULLにしないと面倒になることがご理解いただけたでしょうか？
【初期値について】
NOT NULLにすると、最初にinsertでレコードを作るときに全てに値を設定しないといけないのか？という
疑問があるかも知れません。
しかし、create table文にデフォルト値を設定すれば指定されないカラムは
値がデフォルト値が設定されるようになります。
【容量設計について】
DBは必ず容量設計をします。
容量設計とは、DBのレコードなどを保管する表領域などがHDDをどれだけ使用するか？を計算することです。
お客様に何年分を保管目標にするかを聞き、計算します。
これにより、用意してもらうHDDの容量をお客様に伝えることができます。
また、昔のOSですと1ファイル2GBまでしか書き込めないといった制限もあり、ある表領域にどのテーブルを含めるか？
なども計算していたと思います。1つのテーブルで保存先の表領域ファイルが分かれると遅くなることもあると思います。
そのような制限もありうるので、容量設計も地味に重要な作業だと思います。
Daoのメソッドの設計
データアクセスのクラスは、メソッド名とその処理内容を以下のように決めます。
例えば、updateMember() は、会員情報の更新処理をします。
ビジネスロジックのトランザクション設計
コミットとロールバックのタイミング
本サンプルでは、トランザクションは、ビジネスロジックで開始・終了する設計としました。
その振る舞いとメソッドの関係を以下のように決めます。
メソッド名のプレフィックス
find
トランザクションの振る舞い
read-only
REQUIRED
update
obtain
insert
delete
本サンプルでは、Daoのメソッドの中ではロールバックやトランザクションの処理を書かないルールにします。
ビジネスロジックでトランザクションを扱うため、Daoの中でもトランザクション処理をしてしまうと
予期せぬ動作を作り出すことになります。
以下のリンクの図も参考にしていただければと思います。
参考： 05．設計2： フレームワークの組み合わせとエラートラップ
【Daoやビジネスロジックでロールバックさせたくなった場合】
ロールバックはSpringトランザクションにお任せします。
そのため、ロールバックしたい場合は、例外を発生させます。
排他制御（楽観的ロックと悲観的ロック）
排他制御は、DBを扱うような処理では絶対に考えなければいけない処理です。
排他制御とは何でしょうか？
それを考えるには、在庫数が決まっている商品を購入するシステムなどを考えると分かりやすいかもしれません。
例えば、在庫が4個で、Aさんが4個購入しようとして購入ボタンを押したとします。
しかし、同時にBさんが2個購入しようとして購入ボタンを押したとします。
Aさんの購入時にDBから在庫数を取得すると4個と返ってきます。
そしてDBの更新をする前にBさんの処理が実行されると、やはり在庫数4個が返ってくるので、
両方とも在庫数チェックがOKとして購入処理を完了してしまいます。
さて、何が悪かったのでしょうか？
Aさんが処理を始めた時点もしくは、DBを更新するタイミングで、Bさんの処理を実施しないようにしないといけませんでした。
さて、具体的にどうすればよいかというと、一般的には以下の方法があります。
【悲観的ロック】
ネーミングから、あまりやらない方が良いように聞こえてしまいますが、優位性が低いという意味ではありません。
この方法では例えば、Aさんの購入処理開始時に、トランザクションを開始し、すぐにselect for update などで対象テーブルのレコードを全てロックします。
もしくは、対象テーブルすべてに処理中フラグを作っておき、トランザクションを開始して、すぐに処理中フラグを立ててロックしても良いです。
すると、Bさんが同じ商品を購入しようとして、対象のレコードを更新しようとすると、
ロックが掛かっているので、ロックが解除されるまで待つことになります。
（select for updateを使う方法ではselect for updateをBさんが行ったときに、処理中フラグの方法では処理中フラグの更新をBさんが行ったときに
ロックの待ちの状態になります。）
ですので、Bさんの処理では在庫数の取得もまだ行いません。
Aさんの処理がすべて終わったらロックを解除します。（ロールバックかコミットをするとトランザクションが終了するので解除されます）
解除するとBさんの処理が始まり、在庫数を取得しますが、０個なので
エラー画面に「在庫数がありません」のようなエラーメッセージを出すことになります。
これで先ほどの問題は解決しますね。
ただ、BさんはAさんの処理を待ってからエラーが出ますので、「さんざん待ったあげく、購入できないのか！？」となってしまうかもしれません。
きっちりしたバッチ処理をしたいときには有効ですが、WEBなどの早く結果を教えてあげたい場合には不利な方法です。
【楽観的ロック】
ネーミングから、適当なのかな？と思ってしまいますが、そういうわけではありません。
この方法では例えば、対象商品にバージョン（verカラム）のような情報を持たせておき、最初はver=1とします。
まず、Aさんの購入処理開始時にトランザクションを開始します。
次に、購入の更新対象レコードの条件として、商品コードだけでなく、バージョンが同じ番号（ver = 1）を指定します。
そして、更新する値は、在庫数を4個減らすと同時に、ver = ver +1 のようにバージョンカラムをインクリメントします。
例：
update goods set stock_num = ?, ver = ver + 1 where item_code = ? and ver = ?
するとBさんが在庫数を取得したときは、Aさんが更新前ということなので、在庫数4個、ver=1 という情報が取得されます。
しかし、在庫数を 4 - 2 ＝ 2 個に更新しようとしたとき、
対象レコードの条件にver=1 があるため、更新対象レコードが見つかりません。（Aさんがver=2に更新した後なので）
更新レコードが0件のときはエラーにし、ロールバックさせ、エラーメッセージを出します。
そうするとBさんはエラーになりDBが更新されないため、先ほどの問題は解決します。
こちらの方法の良いところは、Aさんの処理がすべて終わるまで待たなくてよいので、Bさんが早くエラーを知ることができることです。
ただ、この楽観的ロックは在庫数が十分な数量ある場合にも、タイミングによってはバージョンが変わってしまい、エラーになることがあります。
逆に悲観的ロックの場合は、数量が足りないとき以外はエラーにならないため、エラーになる確率が低くなります。
また、悲観的ロックはデッドロックが発生しないように、すべての処理で、テーブルを更新する順番を決めておく必要があります。
最近はDBの方でデッドロックが発生しにくいように設計されていますが、完全ではないので
このような設計は必要になり、設計上も稼働がかかります。
結局、2つの方法はトレードオフですので、どちらも一長一短あります。
しかし、WEBでは処理が早い、楽観的ロックが良く使われます。
このサンプルでも楽観的ロックで排他制御を実装します。
楽観的ロックは、バージョンで排他をする方法の他に、システム時刻のタイムスタンプで排他する方法があります。
しかし、この方法には難があります。
通常、更新のためにDaoの関数に渡したモデル（POJO）は、関数を抜けたときにはDBの状態と同じにします。
タイムスタンプの方法の場合、モデルを更新後の状態にするためには、もう一度selectして、現在のレコードに保存されたタイムスタンプを
取得しないといけません。少し扱いが面倒になります。
そこで、このサンプルではバージョンの方法を使います。
Daoのメソッドで処理するSQLのルール
用意するSQL
まず、よくやってしまう間違いとして、
画面ごとにDaoを作ってしまう、という設計があります。
【よくやるNGの例】
画面１用
select id,name from t_member where ・・・
画面２用
select id,login_id, login_pw from t_member where ・・・
画面３用（更新）
update t_member set name=? where id = ?
画面４用（更新）
update t_member set login_id=?, login_pw=? where id = ?
上記の例は、カラム数を絞ることで検索スピードや更新スピードが上がるというメリットはあります。
ただ、その分、作成するDao、SQLの数が増えてしまい、ほぼ画面が増えるごとに増えてしまいます。
しかし、実際のところWEBの処理で大量の検索をすることはまずありません。
更新についても、WEBについてはテーブル１つにつき、せいぜい1行を更新する程度で、
スピードはそれほど変わりません。
【良い例】
画面１用（*はNGの例と対比を分かりやすくするため使いましたが、カラムを1つずつ書いた方が良いかと思います）
select * from t_member where ・・・
画面２でも画面１のSQLを使用する
画面３用（更新）
update name=?, login_id=?, login_pw=? where id = ?
画面４用（更新）でも画面３のSQLを使用する
上記のようにすると、Daoで作るべきSQLがかなり減るのは分かるでしょうか？
疑問１：画面で使用しない不要なカラムもselectするの？
実際のところ、上記のような汎用的なSQLを作成し、再利用していくのが良いです。
あまりにも検索数や更新数が多く、遅くなりすぎるときにSQLを追加すればよいです。
2つ以上のテーブルにまたがる取得の場合は、select文を2つに分けて、最初に1つ目のテーブルのレコードを
次に1つ目のレコードに紐づく2つ目のテーブルのレコードを取得する方法があり、SQLを減らせます。
ただ、MyBatisなどのORマッピングを使用していると、inner joinで2つ以上のテーブルを取得しても、
ひとまとめにモデル（POJO）に値を設定してくれる機能もありますので、効率のよい方法を取れば
良いかと思います。
疑問２：更新についても、変更がないカラムも更新するの？
もしくは、ブラウザから更新対象でないカラムの値が送られてきたらどうするの？と思うかもしれません。
これについてはまず、DBから取得したすべてのカラムの値が入ったMemberなどのオブジェクトに、
その画面で送られてくるべきパラメタだけを設定することで解決します。
変更がないカラムの値はDBから取得した値で更新されることになるので、値は変わりません。
このような方法で、作成するSQLを減らすのは良くやる手です。
もちろん、大量に更新する場合があれば、性能みあいで新たにSQLを作成するべきです。
ケースバイケースにはなるのですが、昔見たコードで上記のように、モデルを意識せずに
大量にSQLを作っているケースが多かったので、初級者の注意点として書いてみました。
基本的には、モデルごとにSQLを作るのが良いかなと思います。
SQLの汎用性
このサンプルでは、作成したSQLを、バッチ処理（SpringBatch）でも使用できるように考えます。
具体的には、以下の点を守ればバッチ処理でも流用可能です。
①検索処理は、_skiprows、_pagesizeというパラメタ名でページングのSQLを書きます。
②update、insert処理は、SQLが変わらないように書きます。
①は、07.バッチ処理の実際のサンプル（チャンク処理：DB読み込み）を見てもらうと意味が分かると思います。
SpingBatchのMyBatisでは、_skiprows、_pagesizeというパラメタ名でページングするルールになっています。
②は、JDBCのバッチ処理の仕様に起因したルールです。
JDBC自体には連続で同じSQLを実行することができ、スピードをかなり速くするバッチ機能があります。
SQL文はプレースホルダ（"？"）で記述されていて、？を置換する値だけを次々に変更して実行していきます。
このバッチ機能では、SQL文は全く同じでないといけません。
ですので、②のようなルールになります。
ただ、こちらのルールはすべての更新系のSQLで守ることはできないので、ユニーク指定のupdateなど
限られたSQLだけで守ればよいと思います。
値のエスケープ
SQLインジェクションができないように、プレースホルダを使用するのは割と常識になってきました。
ですので、ここではプレースホルダでは解決できないエスケープの話をします。
それは、like のエスケープです。
likeで使用される%、? などの特殊文字もエスケープしないといけません。
でなければ、「100%ジュース」という文字列を前方一致で引っかけたいのに、「1000円ジュース」なども
引っかかってしまいます。
100%の%部分がワイルドカードと解釈され、どんな文字列も許されてしまうからです。
【Postgresでの前方一致検索の例】
name like regexp_replace( ?, '([\\%_])', '\\\1', 'g') || '%'
色々やり方はあると思いますので、likeエスケープの1例として見てください。
postgresでは￥でエスケープします。正規表現で￥と%と_をエスケープしています。
最後の 「 || '%' 」 は、前方一致のために最後にワイルドカードをつけています。
Created Date： 2015/03/28