今回は、SQLで頻繁に使う基本構文をJOOQで書いたら、どんな感じになるのかを整理してみます。
なお、STS3(3.9.6)+SpringBoot2.0+tymeleaf3.0迄動作確認しています。
なお、事前にテーブル定義を生成(CodeGenerator)できている前提の話になります。
テーブル定義の生成方法やセットアップについては、前回の記事を参照してください。
JOOQ本体のDI
まずは、JOOQの本体であるDSLContextをDIします。
@Autowired
DSLContext jooq;
使うテーブルについては、自動生成したTablesパッケージをstaticインポートしておきます。
例です。
import static jp.boku.model.jooq.Tables.USER;
import static jp.boku.model.jooq.Tables.USER_INFO;
これだと、user と user_infoテーブルを参照することになります。
基本、テーブル・項目の物理名を大文字にしたもので参照します。
userテーブルの項目「uid」なら、USER.UIDという感じです。
さて、まずはSELECT文から整理していきます。
単一テーブルで一行だけ取り出すパターン
UserRecord user = jooq.selectFrom(USER).where(USER.ID.eq(1)).fetchOne();
DBに「user」テーブルがあれば、UserRecordクラス(後ろにRecordがついてキャメルケースになる)が自動生成されてます。
fetchOne()で1行だけ取得して、UserRecordで受けて、値の取り出しはそのGetterを使います。
例
user.getUid();
単一テーブルで複数行取り出すパターン
Result<Record> result = jooq.select().from(USER).fetch();
テーブルの全項目を受け取る場合は、Result<Record>で受けるのが汎用的なやり方になります。
受けたあとは、iterateしてなんかの処理をします。
例えば、上記の処理で取得した「result」であれば
for (Record r : result) {
Integer id = r.getValue(USER.UID);
String firstName = r.getValue(USER.FIRST_NAME);
String lastName = r.getValue(USER.LAST_NAME);// なんかの処理
}
こんな感じですね。
複数テーブルをjoin()して全項目を取得する
Record record = jooq.select()
.from(USER)
.join(USER_INFO).on(USER.UID.eq(USER_INFO.UID))
.where(USER.UID.eq(1))
.fetchOne();
Recordで一旦受けて、値を取得する時には、Recordからテーブルごとのクラスを取り出して使う感じです。
例えば、上記の処理でとりだしたrecordだと、USERとUSER_INFOの2つのテーブルデータがまとめてはいってますから、こんな感じで、テーブルごとにわけて、後続の処理をします。
UserRecord user = record.into(USER);
UserInfoRecord uinfo = record.into(USER_INFO);
別名を使って、記述を簡潔にする+JOIN+取得項目指定
SQLではよくやりますよね。
テーブル名をそのまま書くと、長すぎて読みづらくなるので、別名をつけて簡潔に書くあれです。
JOOQでも、同じようにできます。
// 別名を定義
User a = USER.as("a");
UserInfo b = USER_INFO.as("b");// 別名を使う。
Result<Record5<String,String,String,String,String>> results =
jooq.select(a.UID,b.UNAME,b.UNAME_KANA,b.UMAIL,b.UTEL)
.from(a)
.join(b).on(a.UID.eq(b.UID))
.orderBy(b.UNAME_KANA.asc())
.fetch();
joinするテーブルが増えたら、.join()をテーブルの数分つなげていけばいいです。
特徴的なのが、受け側の定義方法です。
selectで取得する項目が5項目なので、Record5です。
これが2項目なら、Record2です、3項目ならRecord3です。
Record22まであるみたいです。
この結果「results」も同じようにiterateできます。
このように項目指定でとりだした時は、項目にあわせたBeanクラスを別途用意して、詰めなおした方が使いやすいと,個人的には思っています。
なので、UserBeanクラスを用意して、そのリストに取得結果を詰め替えるパターンで例を書いてみます。
// 用意したBeanのListをインスタンス化する
List<UserBean> ret = NewManager.newList();
// iterateして詰め直す。
for(Record r:results){
UserBean tmp = new UserBean();
tmp.setUid(r.getValue(USER.UID));
tmp.setUserName(r.getValue(USER_INFO.UNAME));
tmp.setUserNameKana(r.getValue(USER_INFO.UNAME_KANA));
tmp.setUserMailAddr(r.getValue(USER_INFO.UNAME_KANA));
tmp.setUserTelNo(r.getValue(USER_INFO.UTEL));
ret.add(tmp);
}
あ、すいません。
いきなり、NewManager.newList(); なんて、メソッドを書いてしまいました。
補足します。
これは、自分用のユーティリティメソッドです。
中身は、これだけです。
public static <K> List<K> newList() {
return new ArrayList<K>();
}
スレッドセーフであることが問題になるような場合に、ArrayListを、CopyOnWriteArrayList<K>();に書き換えたりすることがあるので、一旦、ワンクッションおくようにしているだけです。
Where条件の色々な書き方(AND,OR,LIKEなど)
例えば、likeとorの組み合わせ。
HTTPリクエストで渡された名前が、ファーストネームかラストネームのどちらかに一致すれば取得するみたいな条件
select().from(AUTHOR).where(
AUTHOR.FIRST_NAME.like("%" + request.getParameter("author") + "%")
.or(AUTHOR.LAST_NAME .like("%" + request.getParameter("author") + "%"))
).fetch();
ANDとか 。
誕生年が1950年以降、かつ、名前が「高橋悠治」さんに一致する人を取得する例。
Result<Record> result =
jooq.select()
.from(AUTHOR.as("a"))
.join(BOOK.as("b")).on(a.ID.eq(b.AUTHOR_ID))
.where(a.YEAR_OF_BIRTH.gt(1950)
.and(a.FIRST_NAME.eq("高橋悠治")))
.orderBy(b.TITLE)
.fetch();
こんな感じで、条件を書きます。
などなど。
Selectのまとめ
代表的なパターンだけ書きました。
これは、ほんの一部です。
JOOQって機能がものすごく多いです。
しかも、同じことをするのに、複数の書き方ができます。
例えば、selectFrom(USER) と、select().from(USER)・・みたいに。
なので、書ききれません。
まあ、SQLで書けることは、ほぼJOOQでも書けると思って良いみたいではあります。
JOOQに関する記事を見ると、複雑なSQLになると、読みづらくなることを心配されている方もあるみたいです。
確かにそういう面はありますが、例えば、複雑なwhere部分だけを抜き出して別メソッドで書いて、後で組み立てることもできます(Dynamic SQL)から、個人的には、意外に見通しよく書けるなと思ってます。
何より、何をやってるかが、あとでソースを見たときにすぐわかる。
そこは利点ですね。
しかも、すばらしいマニュアルがあります。
どう書いたらいいか迷ったら、こちらのマニュアルのサンプルコードを見るのがてっとり速いです。
英語ですが、説明文は少なく、サンプルコードが大量にのってます。
だから、英語が苦手でも、SQLがある程度わかっている人なら、下手な日本語マニュアルよりわかりやすいと思います。
singleをおすすめします
マニュアルには、singleとmultipleがあります。
おすすめは1本のHTMLにすべて収まっているsingleの方です。
検索がしやすいですから。
ここで、例えば Ctrl+Fで検索ウインドウをだして、selectDistinct とか、selectCountとか、groupByとかのキーワード(何をするのかはわかりますよね)で検索すると、適当なサンプルプログラムに行き当たります。
複雑なSQLのサンプルなら、「Dynamic」で検索すると、そういうサンプルにたどり着きます。
JOOQをやってみようと思っているなら、時間のあるときに、一度、検索しながら、ざっと見てみることをオススメします。
ということで、SELECTはこのくらいにします。
あとは、挿入・更新・削除ですね。
挿入(insert)
例です
int ret = jooq.insertInto(USER, USER.UID, USER.PASSWORD, USER.START_DATE, USER.END_DATE, USER.UPDATED, USER.INFO)
.values("sampleuid", "samplePassword", null, null, Timestamp.valueOf(LocalDateTime.now()), "")
.execute();
insertIntoの最初に対象テーブルを書き、続けて、項目を書いてます。
まあ、ほぼSQLに近いですから、わかりやすいですね。
戻り値は更新件数が返ってくるので、「ret>0」のようなチェックで、挿入成功かどうかを判断できます。
更新(update)
例です。
int cnt = jooq.update(USER).set(USER.PASSWORD,"updatePasswordString").where(USER.UID.eq("sampleuid")).execute();
こちらも、SQLのイメージに近いのでわかりやすいです。
戻り値に更新件数が返るのも、insertと同じです。
削除(delete)
例です。
int ret = jooq.deleteFrom(USER).where(USER.UID.eq(uid)).execute();
こちらも、SQLをご存知の方には説明不要かと思います。
削除した件数が戻り値になります。
とりあえず、基本的なところは、こんなものですかね。
JOOQの使い方関連記事