SQL関数フィルタ

• 概要
  • 基本概念
• 関数スタイル
  • coalesce()
  • round()
  • trunc()
• 目的スタイル
  • 日付の切り捨て
  • 日付の加算

概要

基本概念

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

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

(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));

SiteMap Author's BlogBTS