C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[2]: Where句の自動生成


(過去ログ 58 を表示中)

[トピック内 18 記事 (1 - 18 表示)]  << 0 >>

■32915 / inTopicNo.1)  Where句の自動生成
  
□投稿者/ デザパタ大好きっ子 (1回)-(2009/02/17(Tue) 16:53:31)

分類:[データベース全般] 

複数条件でデータベースを検索したい場合、
最も簡単な方法はSQL文が必要なタイミングで、条件を網羅しながら文字列連結でWhere句を構築することだと思います。
ただ、これだとUIであるFormクラスにif文と文字列連結のコードがあふれます。
if文は仕方ないにしても、SQLがUIにあるのはコーディング上(Intellisenseが効かないからコーディングミスを誘発)よろしくないと思うのでどうにかしたいです。

で、何か良い方法がないものか模索しているところです。

自前でORマッピングしているので既存のORマッピングツールに切り替えたくありません。
検索条件をツリー構造で構築してDecoratorパターンを利用してWhere句を生成?
う〜ん、実現可能なのかわからないです。

そもそもこのような考え自体が不毛なのか、皆さんどのように対処されているのかご意見を伺いたいです。
引用返信 編集キー/
■32916 / inTopicNo.2)  Re[1]: Where句の自動生成
□投稿者/ みきぬ (389回)-(2009/02/17(Tue) 17:09:05)
No32915 (デザパタ大好きっ子 さん) に返信
> 複数条件でデータベースを検索したい場合、
> 最も簡単な方法はSQL文が必要なタイミングで、条件を網羅しながら文字列連結でWhere句を構築することだと思います。
> ただ、これだとUIであるFormクラスにif文と文字列連結のコードがあふれます。

私の場合…
・検索条件を格納するための型付き DataTable を用意する。
・UI は、画面からの入力をもとに、検索条件データ(上で作ったもののインスタンス)をこしらえてサーバへ投げる。
・サーバ側では、検索条件データを使って SQL を組み立てて、DB へクエリを投げる。

引用返信 編集キー/
■32917 / inTopicNo.3)  Re[1]: Where句の自動生成
□投稿者/ επιστημη (1734回)-(2009/02/17(Tue) 17:09:24)
επιστημη さんの Web サイト
C++だと演算子の再定義が自由なので、演算子を使って構文木を生成させ、
たとえばこんなのが作れます。

Database db;
Table company = db["company"]; // company table
Table person = db["person"]; // person table
Condition cond = (company["id"] == person["company_id"]) && person["age"] > 30;
PhraseBook pb("SQLServer"); // SQLServer用の語彙表を用意
string phrase = pb.phrase(cond); // "company.id = person.company_id AND person.age > 30" なんて文字列を生成。

# てかこんなDBクラスライブラリは実在するです。

引用返信 編集キー/
■32921 / inTopicNo.4)  Re[2]: Where句の自動生成
□投稿者/ デザパタ大好きっ子 (2回)-(2009/02/17(Tue) 18:17:12)
No32916 (みきぬ さん) に返信
> ・検索条件を格納するための型付き DataTable を用意する。

回答ありがとうございます。
検索条件をどのように分解して格納しているのでしょうか?
(例「手配数 < :p手配数」なんて条件の場合、対象のカラムを表す列、演算子、パラメータ、入力された値、な感じなのかな)
括弧による優先順位の変化にどう対応するのかももう少し詳しく聞きたいところです。

入力データの型チェックとSQL文の生成タイミングが分離できていて良い感じですね。
Intellisenseも利用できますし。
引用返信 編集キー/
■32922 / inTopicNo.5)  Re[2]: Where句の自動生成
□投稿者/ デザパタ大好きっ子 (3回)-(2009/02/17(Tue) 18:36:24)
No32917 (επιστημη さん) に返信
> C++だと演算子の再定義が自由なので、演算子を使って構文木を生成させ、
> たとえばこんなのが作れます。
>
> Database db;
> Table company = db["company"]; // company table
> Table person = db["person"]; // person table
> Condition cond = (company["id"] == person["company_id"]) && person["age"] > 30;
> PhraseBook pb("SQLServer"); // SQLServer用の語彙表を用意
> string phrase = pb.phrase(cond); // "company.id = person.company_id AND person.age > 30" なんて文字列を生成。
>
> # てかこんなDBクラスライブラリは実在するです。
>

回答ありがとうございます。
なるほど演算子を使って構文木を生成ですか、これは思いつきませんでした。
すごい理想的な形ですね。
どうパラメタライズするか考えないといけないっぽいですが、検討してみたいと思います。
DB周りのクラスライブラリって検索しても全然出てこないです。
キーワードを教えてもらえるとありがたいです。
引用返信 編集キー/
■32925 / inTopicNo.6)  Re[3]: Where句の自動生成
□投稿者/ επιστημη (1736回)-(2009/02/17(Tue) 19:55:53)
επιστημη さんの Web サイト
僕が使ったヤツなら Rogue Wave http://www.roguewave.com/ の DBTools.h++ ですねー
↓ここにいくつかアーティクルを書きました。
http://www.s34.co.jp/cpptechdoc/index.html

現在は SourcePro DB と名前を変えてます。

引用返信 編集キー/
■32935 / inTopicNo.7)  Re[3]: Where句の自動生成
□投稿者/ やじゅ (943回)-(2009/02/17(Tue) 22:57:11)
やじゅ さんの Web サイト
No32921 (デザパタ大好きっ子 さん) に返信

>SQLがUIにあるのはコーディング上よろしくないと思うのでどうにかしたいです。

今の私のプロジェクトでは、
UIクラス(Form)、BizLogicクラス(ビジネスロジック)、DacLogicクラス(データアクセスロジック)
の3つのクラスに分けてます。

SQL句は、データアクセスロジッククラスにしか記述しません。
各クラスに渡すパラメータ(Form上での入力値)は、コレクション型またはDataTable型などを使って
渡します。
引用返信 編集キー/
■32949 / inTopicNo.8)  Re[4]: Where句の自動生成
□投稿者/ デザパタ大好きっ子 (4回)-(2009/02/18(Wed) 08:56:45)
No32925 (επιστημη さん) に返信
> 僕が使ったヤツなら Rogue Wave http://www.roguewave.com/ の DBTools.h++ ですねー
> ↓ここにいくつかアーティクルを書きました。
> http://www.s34.co.jp/cpptechdoc/index.html
>
> 現在は SourcePro DB と名前を変えてます。
>

情報ありがとうございます。
このライブラリの考え方を参考にC#版でも作ってみたいところです。


No32935 (やじゅ さん) に返信
> ■No32921 (デザパタ大好きっ子 さん) に返信
>
> >SQLがUIにあるのはコーディング上よろしくないと思うのでどうにかしたいです。
>
> 今の私のプロジェクトでは、
> UIクラス(Form)、BizLogicクラス(ビジネスロジック)、DacLogicクラス(データアクセスロジック)
> の3つのクラスに分けてます。
>
> SQL句は、データアクセスロジッククラスにしか記述しません。
> 各クラスに渡すパラメータ(Form上での入力値)は、コレクション型またはDataTable型などを使って
> 渡します。

回答ありがとうございます。
みきぬさんのやり方と似ていますね。
やはり気になるのはデータアクセスロジッククラスにおけるSQLの構築方法です。
例えば、Form上である条件に対して曖昧検索か否かを選べるCheckBoxがあって、
この情報を元に'LIKE'で結ぶか'='で結ぶかを判別したいのですが、この情報をどのように渡しているかです。
引用返信 編集キー/
■32959 / inTopicNo.9)  Re[5]: Where句の自動生成
□投稿者/ επιστημη (1737回)-(2009/02/18(Wed) 10:36:01)
επιστημη さんの Web サイト
2009/02/18(Wed) 13:32:55 編集(投稿者)

↓あそんでみた。C++なら実用レベルまでもっていけそ。
http://blogs.wankuma.com/episteme/archive/2009/02/18/168331.aspx

↓C#だとこんな感じ?
http://blogs.wankuma.com/episteme/archive/2009/02/18/168350.aspx

引用返信 編集キー/
■32964 / inTopicNo.10)  Re[5]: Where句の自動生成
□投稿者/ みきぬ (390回)-(2009/02/18(Wed) 10:46:26)
No32949 (デザパタ大好きっ子 さん) に返信
> やはり気になるのはデータアクセスロジッククラスにおけるSQLの構築方法です。
> 例えば、Form上である条件に対して曖昧検索か否かを選べるCheckBoxがあって、
> この情報を元に'LIKE'で結ぶか'='で結ぶかを判別したいのですが、この情報をどのように渡しているかです。

私の場合は、CheckBox のチェック状態の bool 値と、検索語句の string 値をロジック側へ渡して、
ロジック側で bool 値をもとに分岐させる感じですね。

επιστημη さんのは、検索の仕方も含めて(クエリを抽象化したものを)UI 側から渡すイメージですね。
そっちのほうがいいのかなー?
引用返信 編集キー/
■32996 / inTopicNo.11)  Re[6]: Where句の自動生成
□投稿者/ デザパタ大好きっ子 (5回)-(2009/02/18(Wed) 15:59:10)
No32959 (επιστημη さん) に返信
> 2009/02/18(Wed) 13:32:55 編集(投稿者)
>
> ↓あそんでみた。C++なら実用レベルまでもっていけそ。
> http://blogs.wankuma.com/episteme/archive/2009/02/18/168331.aspx
>
> ↓C#だとこんな感じ?
> http://blogs.wankuma.com/episteme/archive/2009/02/18/168350.aspx
>

こ、これは・・・
ここまでサンプルができてると夢が広がりますねぇ。
ちょっと試してみようと思います。
ありがとうございます。


No32964 (みきぬ さん) に返信
> 私の場合は、CheckBox のチェック状態の bool 値と、検索語句の string 値をロジック側へ渡して、
> ロジック側で bool 値をもとに分岐させる感じですね。

UIから得た情報を分解してロジック側に渡すのですね。
となるとロジック側での情報の取捨選択という点で、コードがごちゃごちゃしてしまうのはいたしかたないというところですか。
意外とこのような自前でごりごり的なアプローチをされている人が多いようで安心しています。
ただ、その割には世間で紹介されるのはO/RマッピングツールやTableAdapterの利用例ばかりで、
こういったアプローチがいまいち具体化されていないように思うんですよね。
参考書も前者ばかりですし。
ほんとは■32917でεπιστημηさんが示されたような具体例なんかも見てみたかったですが、
基本のアプローチ方法は似ていることがわかっただけでもよしとします。


> επιστημη さんのは、検索の仕方も含めて(クエリを抽象化したものを)UI 側から渡すイメージですね。
> そっちのほうがいいのかなー?

処理と役割が明確に決まって分けられていればどちらでも良さそうで、あとは好みの問題でしょうかね。
私はεπιστημη さんのサンプルに感銘を受けてちょっと予定を変更してこちらを試してみようと企んでいるところですが。

皆さんご意見どうもありがとうございました。
解決済み
引用返信 編集キー/
■33015 / inTopicNo.12)  Re[7]: Where句の自動生成
□投稿者/ επιστημη (1746回)-(2009/02/18(Wed) 21:48:59)
επιστημη さんの Web サイト
2009/02/18(Wed) 22:20:53 編集(投稿者)
> ちょっと試してみようと思います。

おもろいのできたらくださいな♪

ちょこちょこいぢって いまここ↓

    // 電話帳にある名前とアドレス帳にある名前が一致し、
    // かつ 
    // "えぴXXX"であるか または20才以上
    DB.Exp exp = phone["name"] == address["name"] 
               & (phone["name"].like("えぴ%") | address["age"] >= 20).brace;

出力:
phonebook.name = addressbook.name AND (phonebook.name LIKE 'えぴ%' OR addressbook.age >= 20)

解決済み
引用返信 編集キー/
■33017 / inTopicNo.13)  Re[1]: Where句の自動生成
□投稿者/ Oakbow (1回)-(2009/02/19(Thu) 00:00:39)
2009/02/19(Thu) 00:02:55 編集(投稿者)
2009/02/19(Thu) 00:01:40 編集(投稿者)
2009/02/19(Thu) 00:01:24 編集(投稿者)

はじめまして。
私の場合はみきぬさんややじゅさんと同じかな?

今のプロジェクトではJavaで、Spring+Struts+Hibernateって環境です。
Struts配下のActionから検索条件を引数に持つServiceのメソッドが呼ばれ、その中で条件分岐を行い、like条件用のDaoのメソッドか、
=検索用のDaoのメソッドが呼ばれます。
ビジネスロジックやデータアクセス層はUIから分離されているってのは常套手段だし、UIで直にSQL文生成ってのは避けた方がよいと思います。

O/Rマッパーってたいていは単にオブジェクトとテーブルのマッピングを行うだけではなく、SQL生成用の仕組みが用意されていると思います。
Hibernateの場合はCriteriaってのがそれで、επιστημηさんが試していらっしゃるようなことができるようになっています。
どのような構造になっているか研究してみてはいかがでしょうか?

とはいいつつO/Rマッパー嫌いな私は、DaoでもSQL生成せずにストアド呼んじゃいたいところですけれど。

>最も簡単な方法はSQL文が必要なタイミングで、条件を網羅しながら文字列連結でWhere句を構築することだと思います。
これはDynamicSQL(動的SQL)と呼ばれる、あまり好ましくないとされる手法で、MSなんかはヤメテーヤメテーと連呼してます。
あと、対応しているRDBMSであればPrepared Statementを使用するべきかなあ。

例えばユーザー名での部分一致検索か、ユーザーIDでの完全一致での検索を行いたい場合。

DECLARE @CONDITION_TYPE bit; 
DECLARE @USER_NAME varchar(20);
DECLARE @USER_ID int;

SET @USER_NAME = '%' + REPLACE(@USER_NAME, '%', '%%') + '%';

SELECT *
FROM WANKUMA_USER
WHERE (1 = @CONDITION_TYPE AND USER_NAME LIKE @USER_NAME) OR (0 = @CONDITION_TYPE AND USER_ID = @USER_ID);

こんな感じで組んでしまえば、検索条件が異なる場合でもひとつの静的SQLですんだりもします。

DECLARE @USER_NAME varchar(20);
DECLARE @USER_ID int;

IF(@USER_NAME IS NOT NULL)
  BEGIN
    SET @USER_NAME = '%' + REPLACE(@USER_NAME, '%', '%%') + '%';
  END;

SELECT *
FROM WANKUMA_USER
WHERE (@USER_NAME IS NOT NULL AND USER_NAME LIKE @USER_NAME) OR (@USER_ID IS NOT NULL AND USER_ID = @USER_ID)

でもいけたかなあ?
条件が複雑になると組むのもデバッグも大変になるので、素直に分けたほうがいいとは思いますけれど。
※コードは適当なので感じだけつかんでもらえれば。。LIKEのメタ文字は%だけじゃなかったと思うし。
※※投稿なれてなくて編集ばっかでごめんなさい。。

引用返信 編集キー/
■33021 / inTopicNo.14)  Re[2]: Where句の自動生成
□投稿者/ 魔界の仮面弁士 (1002回)-(2009/02/19(Thu) 09:36:52)
No33017 (Oakbow さん) に返信
> SET @USER_NAME = '%' + REPLACE(@USER_NAME, '%', '%%') + '%';
> LIKEのメタ文字は%だけじゃなかったと思うし。

対 Oracle や 対 SQL Server の場合で言うと、
Like のエスケープは % → %% では無いですよ。

例えば、『"Bio_100%" で始まる文字列』という条件であれば、
  WHERE USER_NAME LIKE 'Bio\_100\%%' ESCAPE '\'
と書けます。また、対象列が(VARCHAR/VARCHAR2 ではなく) NVARCHAR/NVARCHAR2 の場合は、
  WHERE USER_NAME LIKE N'Bio\_100\%%' ESCAPE N'\'
の記述に変化します。

# NVARCHAR2 列の時に、ESACPE 句に N 指定を行わないと、
# Oracle の場合、ORA-01425 で弾かれたりします。
引用返信 編集キー/
■33068 / inTopicNo.15)  Re[3]: Where句の自動生成
□投稿者/ Oakbow (2回)-(2009/02/20(Fri) 00:26:51)
> 対 Oracle や 対 SQL Server の場合で言うと、
> Like のエスケープは % → %% では無いですよ。

ギャーーー

SET @USER_NAME = '%' + REPLACE(@USER_NAME COLLATE JAPANESE_BIN, '%', '\%') + '%';
:
WHERE (1 = @CONDITION_TYPE AND USER_NAME LIKE @USER_NAME ESCAPE '\') OR (0 = @CONDITION_TYPE AND USER_ID = @USER_ID);

こんな感じでしょうか?
http://msdn.microsoft.com/ja-jp/library/ms179859.aspx
にあるように他にもメタ文字あるので面倒なところですが。

Oracleの場合は。。
引用返信 編集キー/
■33274 / inTopicNo.16)  Re[2]: Where句の自動生成
□投稿者/ デザパタ大好きっ子 (6回)-(2009/02/24(Tue) 18:46:33)
No33017 (Oakbow さん) に返信

> >最も簡単な方法はSQL文が必要なタイミングで、条件を網羅しながら文字列連結でWhere句を構築することだと思います。
> これはDynamicSQL(動的SQL)と呼ばれる、あまり好ましくないとされる手法で、MSなんかはヤメテーヤメテーと連呼してます。

Oakbowさん、回答ありがとうございます。

私も静的SQLを発行する方が好ましいと考えていますが、複数の検索条件を伴うSQLはどうしても長く見にくくなってしまいます。
加えて検索条件の数が変化する仕様ですと、SQLの中でNULLを考慮するための分岐が入り乱れ複雑化します。

2つ3つならともかく6つ8つとなってくるととても見れたSQLではなくなるとともに、
条件の数が少なかったり、単純である場合でもパフォーマンスをあげることが難しいなどの問題も生じます。

このような経験からWhere句自動生成を検討した次第です。

解決済み
引用返信 編集キー/
■33276 / inTopicNo.17)  Re[8]: Where句の自動生成
□投稿者/ デザパタ大好きっ子 (7回)-(2009/02/24(Tue) 20:24:40)
No33015 (επιστημη さん) に返信
> 2009/02/18(Wed) 22:20:53 編集(投稿者)
>
> > ちょっと試してみようと思います。
>
> おもろいのできたらくださいな♪

う〜ん、期待はしないでください・・orz
うpろだにでも置いときます。パスはwankumaです。
ttp://www1.axfc.net/uploader/File/so/18796.txt
解決済み
引用返信 編集キー/
■33278 / inTopicNo.18)  Re[9]: Where句の自動生成
□投稿者/ επιστημη (1777回)-(2009/02/24(Tue) 22:03:54)
επιστημη さんの Web サイト
いただきましたー

さんきゅぅですー♪

解決済み
引用返信 編集キー/


トピック内ページ移動 / << 0 >>

このトピックに書きこむ

過去ログには書き込み不可

管理者用

- Child Tree -