アラカン"BOKU"のITな日常

文系システムエンジニアの”BOKU”が勉強したこと、経験したこと、日々思うこと。

SQLライクなORマッパ。JOOQの使い方2回目。基本構文と戻り値の受取り。STS3.9.0+SpringBoot2.0

今回は、SQLで頻繁に使う基本構文をJOOQで書いたら、どんな感じになるのかを整理してみます。

 

なお、事前にテーブル定義を生成(CodeGenerator)できている前提の話になります。

 

テーブル定義の生成方法やセットアップについては、前回の記事を参照してください。

arakan-pgm-ai.hatenablog.com

 

まずは、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();

 

こんな感じで、条件を書きます。

 

などなど。

 

代表的なパターンだけ書きました。

 

これは、ほんの一部です。

 

JOOQって機能がものすごく多いです。

 

しかも、同じことをするのに、複数の書き方ができます。

 

例えば、selectFrom(USER) と、select().from(USER)・・みたいに。

 

なので、書ききれません。

 

まあ、SQLで書けることは、ほぼJOOQでも書けると思って良いみたいではあります。

 

JOOQに関する記事を見ると、複雑なSQLになると、読みづらくなることを心配されている方もあるみたいです。

 

確かにそういう面はありますが、例えば、複雑なwhere部分だけを抜き出して別メソッドで書いて、後で組み立てることもできます(Dynamic SQL)から、個人的には、意外に見通しよく書けるなと思ってます。

 

何より、何をやってるかが、あとでソースを見たときにすぐわかる。

 

そこは利点ですね。

 

しかも、すばらしいマニュアルがあります。

 

どう書いたらいいか迷ったら、こちらのマニュアルのサンプルコードを見るのがてっとり速いです。

www.jooq.org

 

英語ですが、説明文は少なく、サンプルコードが大量にのってます。

 

だから、英語が苦手でも、SQLがある程度わかっている人なら、下手な日本語マニュアルよりわかりやすいと思います。

 

マニュアルには、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の使い方関連記事

arakan-pgm-ai.hatenablog.com

 

 

f:id:arakan_no_boku:20170725215801j:plain