"BOKU"のITな日常

62歳・文系システムエンジニアの”BOKU”は日々勉強を楽しんでます

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

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

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

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

arakan-pgm-ai.hatenablog.com

 

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)から、個人的には、意外に見通しよく書けるなと思ってます。 

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

そこは利点ですね。 

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

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

www.jooq.org

 

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

だから、英語が苦手でも、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の使い方関連記事

arakan-pgm-ai.hatenablog.com 

f:id:arakan_no_boku:20170725215801j:plain