第7回　応用編「エスケープ付き曖昧検索」

はじめに

今回からは、とうとう応用編です。 ConditionBean/OutsideSql(外だしSQL)両方における「⁠エスケープ付き曖昧検索」を見て行きます。

エスケープ付き曖昧検索

エスケープ付き曖昧検索とは？

まずは基本的な概念から説明します。

曖昧検索は、条件値に含まれているワイルドカード(%や_)を使って、前方一致・中間一致等を実現します。例えば、条件値が'ス%'であれば「⁠スで始まるもの⁠」⁠、'%ス%'であれば「スを含むもの」という検索になります。しかし、もしデータベース上に「100%ジュースの飲み物」という文字が格納されていて、「⁠'100%ジュース'を含むもの」という検索をしたい場合に、条件値が'%100%ジュース%'では正確な検索をすることができません。

リスト1　曖昧検索-'100%ジュース'を含むもの

-- 「'100%ジュース'を含むもの」
where xxx like '%100%ジュース%'

なにがまずいかというと、もしデータベース上に「100回回ってから飲むジュース⁠」⁠ というデータが入ってたら、このデータも検索対象になってしまうのです。「⁠'100%ジュース'を含むもの」という条件からは外れたデータです。つまりは、ワイルドカード(%や_)も通常の文字として扱いたいこともあるのです。

SQLでは、文字として扱いたい'%'や'_'がある場合に、それらを条件値の中でエスケープします。そのときエスケープ文字を明示的に指定します。

リスト2　エスケープ付き曖昧検索-ワイルドカード文字をエスケープ

-- 「'100%ジュース'を含むもの」
where xxx like '%100|%ジュース%' escape '|'

こうすることで、エスケープ文字でエスケープされた'%'や'_'はワイルドカードではなく、通常の文字として扱われます。エスケープ文字をエスケープすることも可能です。

リスト3　エスケープ付き曖昧検索-エスケープ文字をエスケープ

-- 「'aaa|bbb'を含むもの」
where xxx like '%aaa||bbb%' escape '|'

この処理は、実業務において結構忘れられがちです。それは、やらなくても支障のない場合も多いからです。しかし、全く考慮せずに実装すると思わぬところでトラブることもあります。商品の品番など複雑なコード体系では、'%'や'_'がコード上に存在することもあります。その場合に、「⁠検索で余計なデータもたくさんでてきて、探せないんだけど⁠」⁠ とユーザに怒られてしまうこともあります。また、主に前方一致検索を想定している検索で、先頭に'%'を入れられて中間一致検索になってしまい、インデックスが利用されずに SQLが全然帰ってこないという事態が発生する可能性もあります (前方一致にする理由としてインデックスを利用したいからって場合もあるのです)。

「第3回　ConditionBeanで色々な条件組み立て」で紹介したConditionBeanの PrefixSearchでは、実はエスケープ処理が施されません(簡易な前方一致)。エスケープをする必要のないケースもたくさんあるので、大抵はこれでもよいのですが、上記のようなシビアな検索の場合にどうしたら良いのか、 DBFluteの高機能曖昧検索「LikeSearch」をご紹介致します。

ConditionBeanのLikeSearch

ConditionBeanのLikeSearchの基本的な使い方を説明します。

query()メソッドの後、PrefixSearchではなくLikeSearchを選びます。そのとき、第二引数が必須です。LikeSearchOptionというクラスを生成して、 likePrefix()というメソッドを呼び出すことで前方一致になります。

リスト4　LikeSearchの基本的な使い方

/**
  * 会員名称が'ス'で始まる会員を検索。
  * 
  * @throws Exception
  */
 public void test_ConditionBean_query_LikeSearch_likePrefix_Tx() throws Exception {
     // ## Arrange ##
     String prefix = "ス";
     MemberCB cb = new MemberCB();
     
     LikeSearchOption option = new LikeSearchOption().likePrefix();
     cb.query().setMemberName_LikeSearch(prefix, option);
     
     // これと同じ
     // cb.query().setMemberName_PrefixSearch(prefix);

     // ## Act ##
     List<Member> memberList = memberBhv.selectList(cb);

     // ## Assert ##
     assertNotNull(memberList);
     assertNotSame(0, memberList.size());
     for (Member member : memberList) {
         log.debug("memberName=" + member.getMemberName());
         if (!member.getMemberName().startsWith(prefix)) {
             fail();
         }
     }
 }

リスト5　LikeSearchの基本的な使い方-SQL

select ...
  from MEMBER dflocal 
 where dflocal.MEMBER_NAME like 'ス%'

likePrefix()の部分をlikeContain()やlikeSuffix()にすることで中間一致や後方一致にすることも可能です。

リスト6　LikeSearchの一致の種別

new LikeSearchOption().likePrefix(); // 前方一致
new LikeSearchOption().likeContain(); // 中間一致
new LikeSearchOption().likeSuffix(); // 後方一致

それでは、本題のエスケープ処理です。

このLikeSearchOptionにescapeBy[エスケープ文字]()というメソッドが幾つか存在します。それを呼び出すことでエスケープ処理が可能になります。

リスト7　ConditionBeanによるエスケープ付き曖昧検索

/**
 * 会員名称に'100%ジュース_テ'が含まれる会員を検索。
 * 
 * @throws Exception
 */
public void test_ConditionBean_query_LikeSearch_likePrefix_escapeByPipeLine_Tx() throws Exception {
    // ## Arrange ##
    String expectedMemberName = "果汁100%ジュース_テスト";
    String keyword = "100%ジュース_テ";

    // escape処理の必要な会員がいなかったので、ここで一時的に登録
    Member escapeMember = new Member();
    escapeMember.setMemberName(expectedMemberName);
    escapeMember.setMemberAccount("temporaryAccount");
    escapeMember.classifyMemberStatusCodeFormalized();
    memberBhv.insert(escapeMember);

    // escape処理をしない場合にHITする会員も登録
    Member nonEscapeOnlyMember = new Member();
    nonEscapeOnlyMember.setMemberName("果汁100パーセントジュースAテスト");
    nonEscapeOnlyMember.setMemberAccount("temporaryAccount2");
    nonEscapeOnlyMember.classifyMemberStatusCodeFormalized();
    memberBhv.insert(nonEscapeOnlyMember);

    // 一時的に登録した会員が想定しているものかどうかをチェック
    MemberCB checkCB = new MemberCB();
    checkCB.query().setMemberName_LikeSearch(keyword, new LikeSearchOption().likeContain());
    assertEquals("escapeなしで2件ともHITすること", 2, memberBhv.selectList(checkCB).size());

    MemberCB cb = new MemberCB();
    LikeSearchOption option = new LikeSearchOption().likeContain().escapeByPipeLine(); 
    cb.query().setMemberName_LikeSearch(keyword, option); 

    // ## Act ##
    List<Member> memberList = memberBhv.selectList(cb);

    // ## Assert ##
    assertNotNull(memberList);
    assertEquals(1, memberList.size());// このキーワードにHITする人は1人しかいない
    Member actualMember = memberList.get(0);
    log.debug(actualMember);
    assertEquals(expectedMemberName, actualMember.getMemberName());
}

リスト8　ConditionBeanによるエスケープ付き曖昧検索-SQL

select ...
  from MEMBER dflocal 
 where dflocal.MEMBER_NAME like '%100|%ジュース|_テ%' escape '|'

見事に'%'や'_'がエスケープされています。このエスケープ処理を手動で行い場合に一番間違え易く忘れ易いのが、「⁠条件値にエスケープ文字を埋め込む作業」です。本当のワイルドカードを付ける前に文字列置換でエスケープ文字を埋め込む必要があるのですが、 DBFluteはこれを内部的に自動で行います。よって、非常に「安全に⁠」⁠ エスケープ付き曖昧検索を実現することが可能です。

どのエスケープ文字でエスケープするかは、ご利用のデータベースが採用しているエスケープ文字を選んで下さい。(データベース固有の仕様をご確認下さい)

リスト9　選択できるエスケープ文字の種類

new LikeSearchOption().escapeByAtMark();    // '@'でエスケープ
new LikeSearchOption().escapeByBackSlash(); // '\'でエスケープ
new LikeSearchOption().escapeByPipeLine();  // '|'でエスケープ
new LikeSearchOption().escapeBySlash();     // '/'でエスケープ

条件値にエスケープ文字が格納されていたらどうしよう！？と考える必要はありません。

条件値内のエスケープ文字も内部的に自動でエスケープされます。

リスト10　ConditionBeanでエスケープ文字をエスケープ-SQL

-- '100|ジュース'という条件値だった場合
 where dflocal.MEMBER_NAME like '%100||ジュース%' escape '|'

OutsideSql(外だしSQL)のLikeSearch

それでは、今度はOutsideSql(外だしSQL)におけるLikeSearchを見て行きましょう。こちらに関しては、やろうと思えばエスケープ文字の指定をSQLにベタに書くことはとても簡単です。SQLに「escape '|'」と自分で書いてしまえば良いのです。しかし、条件値のエスケープ文字の埋め込み処理は自前でやる必要がありますし、また、エスケープ文字の指定がSQLとプログラムで冗長化します。

DBFluteでは、ParameterBeanにLikeSearchOptionを指定することでConditionBeanのときのようなエスケープ処理の自動化を提供しています。

ParameterBeanの宣言時に、曖昧検索対象のプロパティに「:like」というオプションを付けてあげて、Sql2Entityを実行します。すると、ParameterBeanのSetterメソッドにて、LikeSearchOptionが指定できるようになります。

リスト11　ParameterBeanでLikeSearchOption指定-SQL

-- !MemberWithMaxPurchasePricePmb!
-- !!Integer memberId!!
-- !!String memberName:like!!

リスト12　ParameterBeanのSetterでLikeSearchOption

LikeSearchOption option = new LikeSearchOption().likeContain().escapeByPipeLine();
pmb.setMemberName_LikeSearch("100%ジュース", option);

リスト13　LikeSearchOption利用時の外だしSQLでの条件設定-SQL

MEMBER_NAME like /*pmb.memberName*/'%テスト値%'

リスト14　LikeSearchOption利用時の実行後SQL

MEMBER_NAME like '%100|%ジュース%' escape '|'

まとめ

エスケープ付き曖昧検索を徹底してみてみました。実務でぜひ利用してみて下さい。

次回は、とうとう「ページング検索」をみていきます！