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

第5回　外だしSQLの基本

Java, DBFlute

この記事を読むのに必要な時間：およそ 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を定義して利用することになるでしょう。

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

Java, DBFlute

著者プロフィール

久保雅彦（くぼまさひこ）

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