DBアクセスを定番化しよう DBFlute入門

第5回外だしSQLの基本

はじめに

今回は、いざというときに大活躍する外だしSQL(OutsideSql)の基本をみていきましょう。

外だしSQLとは?

まずは第1回 DBFluteの概要の簡単なおさらいになります。

外だしSQLは、その名の通りSQLをプログラムの外に出して、そのSQLをプログラムから実行する機能のことを言います。

SQLは「.sql」という拡張子のテキストファイルに記述し、そのSQLファイルへのパスとパラメータをプログラムで指定して実行します。

リスト1:外だしSQLのサンプル-SQLファイル
-- 会員IDと名称の前方一致で絞り込んで、会員一覧と購入最大価格を検索。
-- 絞り込み条件はそれぞれ値がnullじゃければ評価する。
select member.MEMBER_ID
     , member.MEMBER_NAME
     , (select max(purchase.PURCHASE_PRICE)
          from PURCHASE purchase
         where purchase.MEMBER_ID = member.MEMBER_ID) as MAX_PURCHASE_PRICE
  from MEMBER member
 /*BEGIN*/where
   /*IF pmb.memberId != null*/member.MEMBER_ID = /*pmb.memberId*/3/*END*/
   /*IF pmb.memberName != null*/and member.MEMBER_NAME like /*pmb.memberName*/'ス' || '%'/*END*/
 /*END*/

SQLは、⁠アプリケーションで実行するときもツールで実行するときも同じSQLを利用」するということが実現可能な「2Way-SQL」と呼ばれる形式で記述が可能です。IF文やバインド変数などの動的ロジックは全て標準SQLのコメント形式で記述します。

開発者は、実装したSQLをアプリケーションを介さずにツール上で文法の確認をすることが可能です。これまでの開発でよく見られた「アプリケーションをデプロイして画面を実行して初めて'右括弧が足りてないこと'に気づき、修正してまた画面を実行して確認」という非効率な開発がなくなります。SQLプログラマとの分業も非常にやりやすくなります。

現場ではよく「外だしSQL」という言葉が利用されます。S2Daoでは本来「SQLファイル」という名前が正式な機能名であるのですが、実際の開発現場で「外だしSQL」と呼ばれることが多いようです(SQLをプログラムの外に出すという意味と思われます⁠⁠。

DBFluteでは、より現場に近くわかりやすい言葉を重視したいので、この「外だしSQL」という名前を正式な機能名とし、プログラム上の物理名を「OutsideSql」としています。

また、この制御ロジックを示すSQLのコメントを「パラメータコメント」と呼びます。

S2Daoでは本来「SQLコメント」という名前が正式な機能名であるのですが、実際の開発現場で会話上の紛らわしさが存在していたため(一般的なSQLのコメントと混同⁠⁠、DBFluteでは違う呼び方をしています。

実行の仕方

では、早速外だしSQLを実行してみましょう!

SQLは、先ほどの「会員一覧と最大購入価格を検索」するSQLを利用します。そのSQLを次のパスのテキストファイルとして配置します(src/main/resourcesをリソースファイル用のソースフォルダとします⁠⁠。

  • src/main/resources/sql/member/selectMemberWithMaxPurchasePrice.sql

以下のサンプルをご覧下さい。

リスト2:実行の仕方のサンプル
/**
 * 会員一覧と最大購入価格をリスト検索。
 * 会員名称が「ス」で始まる会員を対象に検索する。
 * 
 * @throws Exception
 */
public void test_OutsideSql_selectList_BasicExecution_Tx() throws Exception {
    // ## Arrange ##
    final String path = "sql/member/selectMemberWithMaxPurchasePrice.sql";
    final MemberWithMaxPurchasePricePmb pmb = new MemberWithMaxPurchasePricePmb();
    pmb.setMemberName("ス");
    final Class entityType = MemberWithMaxPurchasePrice.class;

    // ## Act ##
    final List memberList 
            = memberBhv.outsideSql().selectList(path, pmb, entityType);

    // ## Assert ##
    for (MemberWithMaxPurchasePrice member : memberList) {
        final String memberName = member.getMemberName();
        final Integer maxPurchasePrice = member.getMaxPurchasePrice();
        log.debug(memberName + " - " + maxPurchasePrice);
        assertTrue(memberName.startsWith("ス"));
    }
}

public static class MemberWithMaxPurchasePrice {
    protected Integer memberId;
    protected String memberName;
    public Integer getMemberId() {
        return memberId;
    }
    public void setMemberId(Integer memberId) {
        this.memberId = memberId;
    }
    public String getMemberName() {
        return memberName;
    }
    public void setMemberName(String memberName) {
        this.memberName = memberName;
    }
}

public static class MemberWithMaxPurchasePrice {
    protected Integer memberId;
    protected String memberName;
    protected Integer maxPurchasePrice;
    public Integer getMemberId() {
        return memberId;
    }
    public void setMemberId(Integer memberId) {
        this.memberId = memberId;
    }
    public String getMemberName() {
        return memberName;
    }
    public void setMemberName(String memberName) {
        this.memberName = memberName;
    }
    public Integer getMaxPurchasePrice() {
        return maxPurchasePrice;
    }
    public void setMaxPurchasePrice(Integer maxPurchasePrice) {
        this.maxPurchasePrice = maxPurchasePrice;
    }
}

BehaviorのoutsideSql()メソッドに続いてselectList()というメソッドを呼び出しています。ここで、⁠SQLファイルへのパス⁠⁠、⁠パラメータ⁠⁠、⁠戻り値の型」を指定して実行します。

引数は、基本的にBean形式で指定します。ただし、引数の数が1つの場合は、その値をそのまま指定することが可能です。

上記の例では実は会員Entityをそのまま引数として利用することも可能です。つまり、SQL上で利用しているプロパティが存在しているBeanなら何でも良いのです。

しかし、実際の業務で利用するSQLでは、パラメータはSQLによって独自の形をすることが多いため、習慣的にここでは独自のBeanを定義して利用しています。

戻り値の型は、そのSQLのSelect句に定義したカラムと同じ構成のEntityを指定します。それがもし会員テーブルそのままであれば、会員Entityをそのまま利用するが可能です。

しかし、実際の業務で利用するSQLでは、Select句の構成は独自の形をすることが多いため、ほとんどの場合独自のEntityを定義して利用することになるでしょう。

上記の例においても、最大購入価格という独自のカラムがあるため、独自のクラスを定義して利用しています。

Sql2Entityによる外だし支援

「実行の仕方」をみて、ちょっと面倒だと思いませんでしたか? 具体的には、独自の引数のBeanと独自の戻り値のEntityを定義するのが煩雑に思えませんでしょうか? 煩雑なだけでなく、Select句に定義した名前とEntityに定義したプロパティ名がスペルミスなどで食い違う可能性がバグの温床ではないかと思えるのではないでしょうか?

その通りです。S2Daoで外だしSQLを実装する際の弱点はまさしくここでした。

DBFluteはこういったところを見逃しません。おおいに支援します。

Sql2Entityと呼ばれる支援機能により、これら「引数のBean、戻り値のBean」を自動生成します。

CustomizeEntityの自動生成

SQLの行コメントとして、戻り値Entityのクラス名を定義します。

「--」に続いて、⁠#Xxx#」という形式でクラス名を記述します。

リスト3:Sql2Entityで戻り値Entityを自動生成するSQLのサンプル-SQLファイル
-- #MemberWithMaxPurchasePrice#

-- 会員IDと名称の前方一致で絞り込んで、会員一覧と購入最大価格を検索。
-- 絞り込み条件はそれぞれ値がnullじゃければ評価する。
select member.MEMBER_ID
     , member.MEMBER_NAME
...(以下変わらず)

この一行を足して、DBFluteクライアントに置いてあるsql2entity.bat(.sh)を実行して下さい。

すると、なにやらコンソールにログが流れて見事成功すると、⁠Select句の構成と同じ構成のEntityクラス」が指定された名前で生成されます。そのクラスは「xxx.exentity.customize」配下に生成されます。そのクラスを先ほどの「手動で独自に定義したEntity」に代わって利用することが可能です。

このSql2Entityでの生成を利用することにより、煩雑さが解消されると同時に「Select句の名称とEntityの名称が食い違うことがない」という安全性を得ることができます。

実はSql2Entityは、該当のSQLを実際にDBに実行しています。

実行することにより、JDBC経由でそのSQLの結果セットのメタ情報が取得でき、Select句の構成を知ることができます。その情報を使って自動生成しているのです。よって、Select句の名称とEntityの名称が食い違うことがないのです。

これは、2Way-SQLを採用することによる一番の恩恵とも言えるかもしれません。2Way-SQLでなければこのようなことは実現できません。

また、SQLの文法が正しくなければこのSql2Entityは失敗します。先ほど、2Way-SQLの説明で、ツール上でSQLの文法のチェックを行うということを説明いたしましたが、このSql2EntityがまさしくSQLの文法チェックにもなり得ます。

このSql2Entityで自動生成されたEntityをDBFluteでは「CustomizeEntity」と呼びます(これに対して、テーブル単位で自動生成されたEntityを「DomainEntity」と呼びます⁠⁠。

ParameterBeanの自動生成

さらに、引数のBeanも自動生成してしまいましょう。

SQLの行コメントとして、引数のBeanのクラス名を定義します。⁠--」に続いて、⁠!Xxx!」という形式でクラス名を記述します。

また、パラメータのプロパティを行コメントで定義します。⁠--」に続いて、⁠!![Type] [Name]!!」という形式でプロパティの型と名前を記述します。

リスト4:Sql2Entityで引数Beanを自動生成するSQLのサンプル-SQLファイル
-- #MemberWithMaxPurchasePrice#

-- !MemberWithMaxPurchasePricePmb!
-- !!Integer memberId!!
-- !!String memberName!!

-- 会員IDと名称の前方一致で絞り込んで、会員一覧と購入最大価格を検索。
-- 絞り込み条件はそれぞれ値がnullじゃければ評価する。
select member.MEMBER_ID
     , member.MEMBER_NAME
...(以下変わらず)

で、同様にSql2Entityを実行します。

すると、指定されたプロパティの構成のBeanクラスが指定された名前で生成されます。そのクラスは「xxx.exdao.pmbean」配下に生成されます。そのクラスを先ほどの「手動で独自に定義したBean」に代わって利用することが可能です。

また、このParameterBeanのString型のプロパティは空文字「""」をnullとして扱います。

実は、先ほどの「手動で独自に定義したBean」では会員名称に空文字「""」を入れた場合、パラメータコメントの「/*IF pmb.memberName != null*/」がtrueになります。

実際の業務では、nullなのか空文字なのかを意識したくないことが多いかと思われます。プレゼンテーション層のフレームワークに依存もします。意外にこういった非常に細かい部分でのトラブルが結構現場では多いのです。

そのため、ParameterBeanにて空文字「""」を設定してもSQLのパラメータコメント上は、nullと扱われるようになっています(空白1文字以上「" "」は値として扱われます⁠⁠。無論、空文字をちゃんとした値として扱いたい場合もあると思いますので、生成されたParameterBeanのGapクラスの方でGetterをOverrideすることで空文字を活かすことも可能です。

しかし、個人的な見解になりますが、プログラム上またDB上において、空文字に意味を持たせる設計は何かとトラブルを呼びやすいためあまりおすすめはしません(筆者の経験上、そういった設計のシステムで細かいトラブルが多発したのを見たことがあります⁠⁠。

このSql2Entityで自動生成された引数のBeanをDBFluteでは「ParameterBean」と呼びます。

まとめ

複雑なSQLを実装するのに大活躍する外だしSQLの基本利用をご理解頂けましたでしょうか?

DBFluteはConditionBeanだけが「ウリ」なわけではありません。複雑なSQLはそのアプリケーションにとって肝になるロジックです。

そのSQLの実装はとても大事です。DBFluteはSql2Entityでより安全なOutsideSql(外だしSQL)の実装を提供します。

その他、DBFluteは用途に合わせた支援機能を備えています。

次回はそれら機能を紹介します。

おすすめ記事

記事・ニュース一覧