文系プログラマによるTIPSブログ

文系プログラマ脳の私が開発現場で学んだ事やプログラミングのTIPSをまとめています。

トップ > Jooq > Spring batch, Flyway, Jooq code generatorからJooq DSLでSQLを発行する！

Spring batch, Flyway, Jooq code generatorからJooq DSLでSQLを発行する！

Jooq java springboot

Spring boot、Spring batch、Flyway、Jooq code generator、Jooq DSLの組み合わせですよ〜
f:id:treeapps:20180802010416p:plain

Jooqとは

www.jooq.org
詳細は公式サイトを見た方が早いのですが、簡単に言うとJavaでタイプセーフにSQL発行できるライブラリです。

言葉で説明しても伝わりにくいので、実際のjavaのコードを見るとイメージできると思います。

List<PrefectureRecord> results = dsl.selectFrom(Prefecture.PREFECTURE)
    .where(Prefecture.PREFECTURE.PREFECTURE_CD.eq(Byte.parseByte("13")))
    .fetchInto(PrefectureRecord.class);
results.forEach(System.out::println);
↓
+-------+-------------+---------------+
|area_cd|prefecture_cd|prefecture_name|
+-------+-------------+---------------+
|      3|           13|東京都          |
+-------+-------------+---------------+

※ static importすればもっとシンプルな記述ができます。

こんな感じでSQLファイルにSQLを記述するタイプではなく、javaのコードでSQLを発行する事ができます。

S2JDBCのような見た目ですが、S2JDBCと違い、group byやhaving等、高度なSQLを扱う事ができます。その変わり、SQLファイルにSQLを書く事は基本的にできません。

Jooqのサンプル

今回も例によって既にモノはできています。

github.com

環境

環境 バージョン
OS mac el capitan
IDE Spring tool suite(STS) 3.7.3
docker 1.10.0
MySQL 5.7
java 1.8
gradle 2.10
Spring boot 1.3.3
Spring batch 1.3.3
Flyway 3.2.1
Jooq 3.7.3

プロジェクト構成

以下のように、マルチプロジェクト構成で、base（共通親プロジェクト）に自動生成したクラスを配置するようにしています。実際のプロジェクトではマルチプロジェクトの場合がほとんでしょうから、そうしてみました。

root
├ master（gradle, docker関連）
└ base（共通親プロジェクト）
　　└ batch（バッチプロジェクト）

このサンプルで行われる事

このサンプルコードは、Spring batchを起動すると、javaから任意のタイミングでFlywayでマイグレーションを行ってテーブル作成＋データ登録をし、Jooq code generatorでモデルクラス等を自動生成し、そのままjooq DSLでSQLを発行しています。FlywayもJooq code generatorもJooq DSLも全てjavaから任意のタイミングで実行しているサンプルです。

DBについてはdocker上のMySQL5.7を使っています。docker上にMySQLサーバを作成する手順は前述のgithubを見るか、以下の記事をご覧下さい。
www.bunkei-programmer.net

最初はgradleからflywayとjooq code generatorを実行しようとしましたが、DB接続情報をgradleでも管理する必要があり、2重管理を避けたいと思ったので、全てjavaから実行しています。また、Spring bootを使っているのは、DB接続に必要なデータソース系のオブジェクトを簡単にDIできるためです。

Jooqの簡単なexampleについては、以下のソースをご覧下さい（この記事で詳細な解説はしません）
spring-boot-flyway-jooq-example/GenerateMain.java at master · treetips/spring-boot-flyway-jooq-example · GitHub

Flaywayの場合
    @Autowired
    private DataSource dataSource;

・・・中略・・・
    Flyway flyway = new Flyway();
    flyway.setDataSource(dataSource);
Jooq DSL の場合
    @Autowired
    private DSLContext dsl;

・・・中略・・・
    List<PrefectureRecord> results = dsl.selectFrom(Prefecture.PREFECTURE)
        .where(Prefecture.PREFECTURE.PREFECTURE_CD.eq(Byte.parseByte("13")))
        .fetchInto(PrefectureRecord.class);

Jooqを触ってみて

といっても業務で使っているわけではなく、私個人でちょっと触った程度なのですが。。。

良かった点

集約関数

distinctやgroup byやhavingが書けてしまうのは凄いと思いました。例えば

List<Record2<Byte, Integer>> results =
    dsl.select(Prefecture.PREFECTURE.AREA_CD, DSL.count())
    .from(Prefecture.PREFECTURE)
    .groupBy(Prefecture.PREFECTURE.AREA_CD).fetch();

こう書くと、

select
        prefecture.area_cd
        ,count(*)
    from
        prefecture prefecture
    group by
        prefecture.area_cd

というSQLになります。これがDSLで書けるのは嬉しいですね。

DDL

また、create table、create index、drop table、drop index、alter table等のDDLも書けてしまいます。ストアドファンクション・ストアドプロシージャにも勿論対応しています。

sysout

例えば

List<PrefectureRecord> results = dsl.selectFrom(Prefecture.PREFECTURE)
    .where(Prefecture.PREFECTURE.PREFECTURE_CD.eq(Byte.parseByte("13")))
    .fetchInto(PrefectureRecord.class);
results.forEach(System.out::println);

こう書くと、以下のようにコンソールに出力されます。

+-------+-------------+---------------+
|area_cd|prefecture_cd|prefecture_name|
+-------+-------------+---------------+
|      3|           13|東京都          |
+-------+-------------+---------------+

ただし、1件毎にリッチな出力がされてしまうので、複数件sysoutするとヘッダが何回も出力されてちょっとウザいです。

悪かった点

複雑なSQL

やはり複雑なSQLを書こうとした場合の懸念です。

select
        sum(cnt)
        ,
    from (
        select
                count(*) as 'cnt'
                ,xxx_cd
            from
                aaa
            group by
                xxx_cd
    ) tmp
    inner join bbb
        on tmp.xxx = bbb.xxx
;

例えばこんな感じの「グルーピングした件数の合計値を区分毎に取得したい」といったSQLを書きたい場合、Jooqでどうやって記述するんだろう？と悩んでしまいます。書けるのかもしれませんが、パッと思いつきません。リファレンスをじっくり読んでようやく書けるという感じになると思います。また、joinの数が増えた時に、「これ、実行するとどんなSQLが発行されのだろう？」と悩んでしまいそうです。

DSLを書くときにインデントを頑張って調整すれば、きっとどんなSQLが発行されるか想像しやすくなると思いますが、フォーマッターの自動整形でお目当ての整形をさせるのは無理でしょうから、可読性は心配です。

Jooq code generatorの設定
Configuration configuration = new Configuration().withJdbc(new Jdbc() //
        .withDriver(dataSourceProperties.getDriverClassName()) //
        .withUrl(dataSourceProperties.getUrl()) //
        .withUser(dataSourceProperties.getUsername()) //
        .withPassword(dataSourceProperties.getPassword()) //
).withGenerator(new Generator().withDatabase(new Database() //
        .withName(generatorClassName) //
        .withIncludes(".*") //
        .withExcludes("(batch_.*|schema_version)") //
        .withInputSchema(dataSourceProperties.getSchema()) //
).withTarget(new Target() //
        .withPackageName("com.github.treetips.domain.model") //
        .withDirectory("../base/src/main/java")));
GenerationTool.generate(configuration);

こんな感じでcodeをgenerateするのですが、withDriver・withUrl ・withUser・withPassword の部分のDB接続情報はDIしたDataSourceをセットする等して簡略化できないものですかね。このためだけにDataSourcePropertiesをDIしたりする必要があるし、コード量が無駄に増えるのでスマートではないなあと思いました。

未知数な点

今後メンテされてアップデートし続けていくのかどうかと、SQLのパフォーマンス（SQLからモデルクラスへのマッピング速度）等ですね。

おまけ

MySQLとjavaの型のマッピング一覧

今回githubにアップしたサンプルはMySQLを使っています。MySQLのそれぞれの型は、Jooq code generatorで自動生成した場合、javaだとどの型に変換されるのか？を簡単にまとめてみます。

使用するDDLは以下の通りです。

drop table if exists java_type;
create table java_type (
	char_type char(10) default 'char'
	,varchar_type varchar(10) default 'varchar'
	,tinytext_type tinytext
	,text_type text
	,mediumtext_type mediumtext
	,longtext_type longtext
	,tinyint_type tinyint default 1
	,smallint_type smallint default 1
	,mediumint_type mediumint default 1
	,int_type int default 1
	,bigint_type bigint default 1
	,float_type float default 1.1
	,double_type double default 1.1
	,date_type date default '2001-01-01'
	,datetime_type datetime default '2001-01-01 00:00:00'
	,timestamp_type timestamp default '2001-01-01 00:00:00'
	,time_stype time default '00:00:00'
	,year_type year default '2001'
	,binary_type binary
	,varbinary_type varbinary(10)
	,tinyblob_type tinyblob
	,blob_type blob
	,mediumblob_type mediumblob
	,longblog_type longblob
	,enum_type enum('red','blue','yellow') default 'red'
	,set_type set('green', 'orange', 'pink') default 'green'
	,geometry_type geometry
	,point_type point
	,linestring_type linestring
	,polygon_type polygon
	,multipoint_type multipoint
	,multilinestring_type multilinestring
	,multipolygon_type multipolygon
	,geometrycollection_type geometrycollection
) engine=innodb charset=utf8mb4 row_format=dynamic comment='java型確認マスタ';

これをJooq code generatorはどのようにjavaの型にマッピングしたのでしょうか。

MySQL Java
char(10) String
varchar(10) String
tinytext String
text String
mediumtext String
longtext String
tinyint Byte
smallint Short
mediumint Integer
int Integer
bigint Long
float Double
double Double
date java.sql.Date
datetime java.sql.Timestamp
timestamp java.sql.Timestamp
time java.sql.Time
year java.sql.Date
binary byte[]
varbinary(10) byte[]
tinyblob byte[]
blob byte[]
mediumblob byte[]
longblob byte[]
enum('red''blue''yellow') JavaTypeEnumType
set('green', 'orange', 'pink') String
geometry Object
point Object
linestring Object
polygon Object
multipoint Object
multilinestring Object
multipolygon Object
geometrycollection Object

概ね想像通りです。（位置情報系は全部Object）

enum型に関しては、javaのenumクラスが生成されていました。

/**
 * This class is generated by jOOQ.
 */
@Generated(
    value = {
        "http://www.jooq.org",
        "jOOQ version:3.7.3"
    },
    comments = "This class is generated by jOOQ"
)
@SuppressWarnings({ "all", "unchecked", "rawtypes" })
public enum JavaTypeEnumType implements EnumType {

    red("red"),
    blue("blue"),
    yellow("yellow");

    private final String literal;

    private JavaTypeEnumType(String literal) {
        this.literal = literal;
    }

    @Override
    public Schema getSchema() {
        return null;
    }

    @Override
    public String getName() {
        return "java_type_enum_type";
    }

    @Override
    public String getLiteral() {
        return literal;
    }
}

bigint型とLong型

しかし、どのORMの自動生成のマッピングを見ても、Bigint -> Long とマッピングする事が多いように思えます。

MySQL Bigint型 符号なし 18446744073709551615
Java Long型 9223372036854775807

このように、javaがLong型だと、bigintの符号無しの場合は桁あふれしてしまうんですよね。そう考えるとjava側はBigDecimalにしてもいいんじゃないかと思います。auto_incrementなカラムは大抵bigint unsignedで定義するので、いつか桁あふれするよなこれ・・・でも桁あふれする程このシステム使われないから大丈夫なんだろうけど・・・等といつも思っています。

雑感

複雑なSQLを書こうとすると大変になりそうなデメリットを持つ一方で、DDLが書けてしまうメリットもある。そんな良い点と悪い点の両方を併せ持つJooq。使い所さえ間違わなければかなり使えそうな感じですね。

私は業務で未だにS2JDBCを使っていますが、DSLで集約関数は書けないし、truncateも書けないし、SQLファイル内でDDLは書けない、それができてしまうJooqは凄いなと思いました（ｺﾅﾐｶﾝ
ただ、一方でJooqはSQLファイルによる記述ができないので、そこが業務ではデメリットになりそうです。そう考えるとS2JDBCはDSLもそこそこ書けるしSQLファイルで2way SQLもいけるし、結構バランスがいいんだなあと思いました（更にｺﾅﾐｶﾝ

Jooqの記事を書いておいてアレですが、seasarプロダクトのDomaはSQLファイルオンリーで高機能なORMですが、個人的には次に業務で使うならdoma2がいいなと思っています。例えばS2JDBCの場合、generatorでentityとnamesクラスを自動生成しますが、カラムが変更された時にコンパイルエラーが出るから変更に追従し易い、といったメリットがありますが、私は一度もカラム名変更に追従できて助かった記憶が無いのです。そもそもカラム名を変更する機会が無いのです。そう考えると、もういっそ全部SQLファイルの方がいいんじゃない？とか思いました。

ただ、全部SQLファイルになると、どんなSQLを発行しようとしてるのかはSQLファイルを1件づつ開いてみないと確認できない点だけが不安ですね。


ん？Hibernate？なにそれぼくしらない