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

第5回 外だしSQLの基本

この記事を読むのに必要な時間:およそ 4 分

はじめに

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

 サイト更新中にEMechaのバージョンがあがり,利用し易さの向上やDIコンテナの選択の追加など幾つかの改良がされました。それに伴い,第2回の環境構築の内容も多少修正させて頂いております。

外だし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を定義して利用することになるでしょう。

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

著者プロフィール

久保雅彦(くぼまさひこ)

DBFluteメインコミッタ。主にオープン系の開発に従事。DB設計・DB周りの実装などを担当することが多い。

コメント

コメントの記入