[Seasar-user:8886] Re: [DBFlute] 動的にSQLを生成する方法

[kubo]

久保です。

久保です。

丸岡さんへ

> DBFluteを利用しています。
> 現在、プログラムロジックでSQLを自動生成する要件があります。
> 
> 例：名称を「あ」,「い」で曖昧検索を行ないたい場合、下記のように
> UNIONでSQLを連結したいのです。
> この検索条件の個数は不定であるため、UNIONの個数も不定です。


ある列の値に「あ」が含まれている(or で始まっている)

    もしくは

そのある列の値に「い」が含まれている(or で始まっている)


というように、
A. LikeのOR条件をしたいということでしょうか？
B. かつ、そのOR条件が不定数である
C. かつ、そのOR条件はUNIONで実現されてなければならない




DBFluteで可能は範囲を説明しておきます。

Table -- BOOK(本)
Column -- BOOK_ID(本ID)
Column -- BOOK_NAME(本の名前)


【ConditionBeanによるLike検索のOR条件】
もし、「A」と「B」だけであれば、以下のように実現することができます。

ex) 本の名前に「S2Dao」もしくは「DBFlute」が含まれているレコードを検索
    (論理削除されていないものだけを対象とする)
    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
    final LikeSearchOption likeSearchOption = new LikeSearchOption();
    likeSearchOption.likeContain().splitByPipeLine();// ★部分一致＆区切り文字'|'
    final LikeSearchOption.LikeAsOrCallback bookNameCallback = new LikeSearchOption.DefaultLikeAsOrCallback() {
        public String getAdditionalTargetPropertyName() {
            return BookDbm.COLUMN_UNCAP_PROP_NAME_OF_BookName;
        }
    };
    likeSearchOption.addLikeAsOrCallback(bookNameCallback);
    cb.query().setBookName_LikeSearch("S2Dao|DBFlute", likeSearchOption);// ★Like検索
    cb.query().setDeleteFlg_Equal_NonDelete();
    final List<Book> bookList = bookBhv.selectList(cb);
    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 

出力されるSQLのイメージは以下です。(実際にはBind変数が利用されます)

 where (BOOK.BOOK_NAME like '%S2Dao%' or BOOK.BOOK_NAME like '%DBFlute%')
   and BOOK.DELETE_FLG = 0

自分も実業務で、
画面で入力された“空白区切りの文字列”「S2Dao DBFlute JDBC ...」を使って
ある列を曖昧検索するという要件があり、上記のやり方を利用してリリースしました。


【ConditionBeanによるUnionの不定数設定】
「C」も含めた場合は、「ConditionBeanによるUnion」を利用します。
例えば曖昧検索が前方一致の場合で、OR句ではなくUNION句を利用することによって、
INDEXの恩恵を得たい場合、以下のように実現することができます。

    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 
    final String[] bookNames = new String[] {"S2Dao", "DBFlute"};
    final BookCB cb = new BookCB();
    final LikeSearchOption likeSearchOption = new LikeSearchOption();
    likeSearchOption.likePrefix();// ★前方一致
    for (String bookName : bookNames) {
        final BookCB unionCB = new BookCB();
        unionCB.query().setBookName_LikeSearch(bookName, likeSearchOption);// ★Like検索
        cb.query().setDeleteFlg_Equal_NonDelete();
        cb.union(unionCB.query());
    }
    cb.query().setDeleteFlg_Equal_NonDelete();
    * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 

出力されるSQLのイメージは以下です。(実際にはBind変数が利用されます)

select ...
  from BOOK
 where like '%S2Dao%'
   and BOOK.DELETE_FLG = 0
 union
select ...
  from BOOK
 where like '%DBFlute%'
   and BOOK.DELETE_FLG = 0



外だしSQLでは、確かにこのような不定数条件付与は「埋め込み変数コメントを利用する」
しかありません。少なくとも自分が知っている限りはですが。
(S2DaoのSQLコメントにfor文のようなものは存在しないので)
そして、その場合はBind変数を利用した条件設定ができません。

自分が実業務でこの問題に直面したときは、５つくらいそういう検索が存在したのですが、
４つは上記のConditionBeanにて実現し、１つは外だしSQLで、ベタベタに「20個のBind変数」
を用意して凌ぎました(たまたま画面入力の個数に限度が設けられていたので)。


丸岡さんの実業務では、上記のConditionBeanを利用した方法では
実現は難しいでしょうか？

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
ConditionBeanは、
  「親の親までのSelect句指定(結合)」
  「子テーブルの列での条件絞込み」
など装備しており、大抵のSQLは実現可能であります。
但し、GroupByやSQL関数を利用したい場合などは利用できません。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -