SQL関数フィルタ

概要
- 基本概念
関数スタイル
目的スタイル
- 日付の切り捨て
- 日付の加算

概要

基本概念

子テーブルの導出カラム(最大値や合計値)などの値を、SQL関数を利用してSQL上でフィルタ処理を施すことができます。主にSQL上で利用が完結するような値をフィルタするのに有効です。

SQL関数フィルタの仕様は、以下の全ての機能で共通しています。

(Specify)DerivedReferrer: 子テーブルの導出カラムの取得
(Query)DerivedReferrer: 子テーブルの導出カラムで絞り込み
ColumnQuery: カラム同士の絞り込み条件

(Specify)DerivedReferrer

(Query)DerivedReferrer

ColumnQuery

それぞれの機能のオプション指定にて利用できます。

e.g. 最終ログイン日時が null の場合は 1192-01-01 として扱う {MEMBER_LOGIN} @Java
cb.query().derivedMemberLoginList().max(new SubQuery<MemberLoginCB>() {
    public void query(MemberLoginCB subCB) {
        subCB.specify().columnLoginDatetime();
        subCB.query().setMobileLoginFlg_Equal_False();
    }
}, Member.ALIAS_..., new DerivedReferrerOption().coalesce("1192-01-01"));

関数スタイル

具体的なSQL関数を意識したスタイルのメソッドが用意されています。

coalesce()

null の場合のデフォルト値を指定できます(@since 0.9.7.4)。DerivedReferrerの導出値などで利用する集計関数(max(), avg() など)は、計算対象レコードが存在しない場合に null を戻す可能性があります。特に (Query)DerivedReferrer のようなSQL上で導出値の利用が完結するような場合には、null だと条件式の結果が業務とは都合の合わないことがあるため、coalesce() 関数を使って null の場合に 0 として計算させたりすると良いでしょう。

e.g. 合計購入価格を導出する際に、そもそも購入したことのない会員は 0 に @Java
cb.query().derivedPurchaseList().max(new SubQuery<PurchaseCB>() {
    public void query(PurchaseCB subCB) {
        subCB.specify().columnPurchasePrice();
    }
}, Member.ALIAS_..., new DerivedReferrerOption().coalesce(0));

様々な型が想定されるため引数は Object 型です。バインド変数として扱われます。

round()

指定された精度で(主に数値を)丸めます。SQL上では round 関数が利用されます。厳密な丸め仕様はDBMSに依存します。基本的には数値型を想定していますが、日付型に対して round 関数が利用できるDBMSの場合は、引数に指定できる値はそのDBMSの round 関数の仕様に依存します。

DBMS ごとの違いに対応できるように引数は Object 型です。バインド変数として扱われます。

trunc()

指定された精度で切り捨てします。SQL上では trunc 関数が利用されます。DBMSごとの関数名の違いは内部的に吸収されます。基本的には数値型を想定していますが、日付型に対して trunc 関数が利用できるDBMSの場合は、引数に指定できる値はそのDBMSの trunc 関数の仕様に依存します。

DBMS ごとの違いに対応できるように引数は Object 型です。バインド変数として扱われます。

別途、目的スタイルの切り捨てメソッド(truncDay() や truncTime() など)が存在しますので、切り捨ての目的が合う場合はそちらを利用すると良いでしょう。

目的スタイル

関数は問わず、目的を意識したスタイルのメソッドが用意されています。どのようなSQL関数を使って解決するかは、DBFluteが内部的に解決します。

(DBFluteとして)正式サポートされていない DBMS では、これら機能もサポートされない可能性があります。また、正式サポートされている DBMS でも、そもそもSQL関数で実現できない、もしくは、独自性の強いSQL関数の仕様を持っている DBMS の場合はサポートされないパターンがありますので、利用する際は必ず単体テストなどでの事前検証をお奨めします。

日付の切り捨て

例えば、時分秒を切り捨てるなど、日付に対する切り取りをします(@since 0.9.8.6)。

truncMonth(): 月、日、時分秒ミリ秒を切り捨て (年初めになる)
truncDay(): 日、時分秒ミリ秒を切り捨て (月初めになる)
truncTime(): 時分秒ミリ秒を切り捨て (その日の始まりになる)

引数はありません。また、二度呼び出しは許されません。

e.g. 右辺カラムの日時に対して時分秒ミリ秒を切り捨て @Java
...
cb.columnQuery(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnBirthdate();
    }
}).greaterThan(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnFormalizedDatetime();
    }
}).convert(new ColumnConversionOption().truncTime());

日付の加算

例えば、一日進めるなど、日付に対する加算をします(@since 0.9.8.6)。

addYear(): "年" を進める (もしくは戻す)
addMonth(): "月" を進める (もしくは戻す)
addDay(): "日" を進める (もしくは戻す)
addHour(): "時" を進める (もしくは戻す)
addMinute(): "分" を進める (もしくは戻す)
addSecond(): "秒" を進める (もしくは戻す)

引数はそれぞれ Integer 型を指定します。マイナス値を指定すると減算になります。また、null を指定すると無効な呼び出しとして何も処理されません。二度呼び出しは許されません。

e.g. 右辺カラムの日時に対して 1 日進める @Java
...
cb.columnQuery(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnBirthdate();
    }
}).greaterThan(new SpecifyQuery<MemberCB>() {
    public void specify(MemberCB cb) {
        cb.specify().columnFormalizedDatetime();
    }
}).convert(new ColumnConversionOption().addDay(1));