通用動態(tài)多條件查詢方法的研究與實現

摘要：文章從基于SQL語句查詢優(yōu)化的角度出發(fā)，對當前數據庫應用程序研發(fā)中的動態(tài)多條件查詢方法進行了探究與對比分析，進而提出了一種高效通用的動態(tài)多條件查詢方法。該方法在當前的動態(tài)多條件查詢方法的基礎上作了巧妙的改進，提高了執(zhí)行效率，所形成的SQL語句及程序代碼簡潔靈活，適用于通用查詢模塊的設計或高效搜索引擎的研發(fā)。

關鍵詞：動態(tài)多條件查詢；多條件查詢；查詢優(yōu)化；SQL語句

中圖分類號：TP311文獻標識碼：A 文章編號：1009-3044(2011)04-0738-03

Research and Implementation of a General Method for Dynamic Multi-conditional Query

ZHOU Qian-ming1， WANG Qiao-xia2， ZHU Xin-juan1

(1.School of Computer Science， Xi'an Polytechnic University， Xi'an 710048， China; 2.School of News and Media， Shaanxi Normal University， Xi'an 710054， China)

Abstract: This paper is based on the perspective of query optimization of SQL statements. After research and comparative analysis on the current methods for dynamic multi-conditional query in the development of database applications， a more efficient and general method for dynamic multi-conditional query is proposed. In this method， ingenious improvements have been made on the basis of current methods for dynamic multi-conditional query and the efficiency has been greatly increased. Concise and flexible SQL statements and code will be formed when adopting this method. It is suitable for the design of general query modules and the development of efficient search engine.

Key words: dynamic multi-conditional query; multi-conditional query; query optimization; SQL statement

基于Internet的信息技術(IT)的核心是數據庫技術。幾乎在任何一個投入運行的系統(tǒng)中，都存在一個數據庫管理系統(tǒng)(DBMS)作為基石，用以組織、存儲和處理系統(tǒng)中的各種數據。隨著信息技術(IT)的不斷進步及人們對應用系統(tǒng)功能需求的不斷提升，系統(tǒng)的性能逐漸受到軟件研發(fā)人員及用戶的普遍關注。因此，盡可能優(yōu)化系統(tǒng)的性能至關重要。數據信息查詢是任何應用系統(tǒng)最基本、最常用的數據庫操作，執(zhí)行查詢功能的SQL語句是數據庫應用的一個關鍵部分，其性能的高低將直接影響系統(tǒng)的性能。與當前的動態(tài)多條件查詢方法不同，文章提出一種高效靈活的動態(tài)多條件查詢方法。該方法具有通用性，在任何數據庫應用系統(tǒng)的研發(fā)中具有重要的實際意義。

1 動態(tài)多條件查詢方法的分析研究

在數據庫應用程序研發(fā)的軟件過程中，經常涉及到對數據庫中一個或多個數據表的多個字段進行任意形式組合查詢，這就是動態(tài)多條件查詢。在這樣的查詢界面或網頁中，用戶可以根據自己已知的多個條件信息在數據庫中進行搜索，通過已知的條件篩選數據，縮小搜索范圍，從而得到所有符合要求的結果。當前的動態(tài)多條件查詢方法可以分為查詢條件枚舉法、SQL語句比較拼接和改進的SQL語句拼接3種。為了論述方便，設在某物流信息管理系統(tǒng)數據庫中存在一張材料表(Tb_Material)，包含6個字段，該表各字段的基本信息如表1所示（數據類型基于SQL Server）。

1.1 查詢條件枚舉法

查詢條件枚舉法的思想比較簡單，其出發(fā)點為針對不同的查詢條件組合形式列出與之對應的SQL語句，最終執(zhí)行的SQL語句只是其中的一條。以材料代碼(Snum)和材料名稱(Sname) 2個查詢條件為例，源程序如下(采用C#語言，下同。其中txtSnum 、txtSname 為系統(tǒng)查詢界面或頁面的文本框，用以輸入材料代碼和名稱，@Snum、@Sname分別為接收輸入的材料代碼與名稱的參數，sqlStr為最終執(zhí)行的SQL語句)：

if ( (txtSnum.Text.Trim() == \"\") (txtSname.Text.Trim() == \"\") )

sqlStr = \" select * from Tb_Material \";

if ( (txtSnum.Text.Trim() == \"\") (txtSname.Text.Trim() != \"\") )

sqlStr = \" select * from Tb_Material where Sname like '%' + @Sname + '%' \";

if ( (txtSnum.Text.Trim() != \"\") (txtSname.Text.Trim() == \"\") )

sqlStr = \" select * from Tb_Material where Snum like '%' + @Snum + '%' \";

if ( (txtSnum.Text.Trim() != \"\") (txtSname.Text.Trim() != \"\") )

sqlStr = \" select * from Tb_Material where (Snum like '%' + @Snum + '%') and (Sname like '%' + @Sname + '%' ) \";

容易發(fā)現，對于2個條件的動態(tài)查詢，源程序中出現4次條件判斷和4條查詢語句。對于3個條件的動態(tài)查詢，將出現8次條件判斷和8條查詢語句。以此類推，若將材料表(Tb_Material)的6個字段全部納入查詢條件中，將出現64次條件判斷和64條查詢語句。因此，在查詢條件枚舉法中，條件判斷次數或查詢語句的條數Y與查詢條件的個數X的函數關系式滿足：Y=2X。當查詢條件的個數增多時，程序代碼的復雜性大大升高，其執(zhí)行效率也將急劇下降。

1.2 SQL語句比較拼接

為了克服枚舉法的缺點，在程序中定義兩個字符串：string sqlStr = \" select * from Tb_Material where \"; string oldSqlStr = \" select * from Tb_Material where \"; oldSqlStr作為參照字符串，sqlStr作為最終執(zhí)行的SQL語句。源程序如下：

if (txtSnum.Text.Trim() != \"\")

if (sqlStr == oldSqlStr)

sqlStr += \" Snum like '%' + @Snum + '%' \";

else

sqlStr += \" and Snum like '%' + @Snum + '%' \";

if (txtSname.Text.Trim() != \"\")

if (sqlStr == oldSqlStr)

sqlStr += \" Sname like '%' + @Sname + '%' \";

else

sqlStr += \" and Sname like '%' + @Sname + '%' \";

if(sqlStr == oldSqlStr)

sqlStr = \" select * from Tb_Material \";

SQL語句比較拼接以參照字符串oldSqlStr為基準，先逐一對各查詢條件作出判斷，若查詢條件非空，則將查詢條件拼接到sqlStr后面，將sqlStr與oldsqlStr進行比較是為了確定此次拼接是否為第一次拼接。若為第一次拼接，則說明即將在sqlStr后面加入第一個條件語句。若非第一次拼接，則說明將在sqlStr已有的條件語句后面通過“and”增加新的條件語句。可以發(fā)現，較查詢條件枚舉法而言，對于查詢條件個數≥3的動態(tài)查詢，只需增加少量相似的代碼，源程序的條件判斷次數與查詢語句的條數將大大縮減，在很大程度上提高了程序代碼的執(zhí)行效率。

1.3 改進的SQL語句拼接

SQL語句比較拼接采用兩個字符串，對于每個非空的查詢條件，都要將sqlStr與oldsqlStr進行比較以確定當前的拼接是否為第一次拼接。并且，在對所有的查詢條件都作出判斷之后，仍需將sqlStr與oldsqlStr進行最后的比較，用以避免因所有的查詢條件均為空而導致SQL語句出錯。為了消除這種重復的比較，可以在where子句中預先設定一個恒定成立的條件。改進的SQL語句拼接源程序如下：

string sqlStr = \" select * from Tb_Material where ''='' \";

if (this.txtSnum.Text.Trim() != \"\")

sqlStr += \" and Snum like '%' + @Snum + '%' \";

if (this.txtSname.Text.Trim() != \"\")

sqlStr += \" and Sname like '%' + @Sname + '%' \";

與SQL語句比較拼接對比，改進的SQL語句拼接無論是在代碼量還是在編程技巧上都上了一個臺階。通過在sqlStr的where子句中預先設定一個恒定成立的條件''=''，只要對每個查詢條件逐一進行非空判斷，就可以進行無限制的SQL語句自然拼接，從而較好地實現動態(tài)查詢。

2 通用動態(tài)多條件查詢方法的實現

通過對查詢條件枚舉法、SQL語句比較拼接和改進的SQL語句拼接3種動態(tài)多條件查詢方法的分析研究可知，對比枚舉法和SQL語句比較拼接兩種方法，改進的SQL語句拼接在執(zhí)行效率上明顯占優(yōu)勢，能夠帶來整個數據庫應用系統(tǒng)性能的提高。然而，美中不足的是，縱觀這3種方法，始終沒有得到改進的是源程序中繁瑣的條件判斷，即If語句。而對于程序優(yōu)化，起到條件判斷作用的If語句是編譯器優(yōu)化代碼的最大障礙。程序代碼中的If語句越多，其執(zhí)行速度和效率越低。因此，可以考慮在改進的SQL語句拼接基礎之上，通過對源程序中繁瑣的If語句作進一步的改進，從而找到一種更加高效靈活的動態(tài)多條件查詢方法。

改進的SQL語句拼接通過在where子句中預先設定一個恒定成立的條件''=''，從而較好地實現了SQL語句的自然拼接。考慮到在其最終執(zhí)行的SQL語句sqlStr中，參數@Snum、@Sname等的值直接來自獲取的用戶在相應文本框、下拉列表框等組件中輸入或選擇的值，在應用程序運行之前，這些參數的值的非空與否不能確定。但可以確定的是，這些參數的值只有兩種狀態(tài)：空或非空。這兩種狀態(tài)具有對立性。而在SQL語句的where子句中，可以采用“or”邏輯運算符連接兩個條件，將同一個參數的這兩種狀態(tài)都表征出來。對照前文闡述的內容，以參數@Snum為例，可以表示為：Snum like '%' + @Snum + '%' or @Snum=''。如果參數@Snum的值非空，則@Snum=''條件不滿足，整個條件表現為Snum like '%' + @Snum + '%'；如果參數@Snum的值為空，@Snum=''條件滿足，即''=''，這是一個恒定成立的條件，則Snum like '%' + @Snum + '%'條件“失效”，整個條件表現為''=''，恒定成立。

因此，上文中的源程序可以作如下的進一步優(yōu)化：

string sqlStr = \" select * from Tb_Material where (Snum like '%' + @Snum + '%' or @Snum='') and (Sname like '%' + @Sname + '%' or @Sname='') \";

容易發(fā)現，在優(yōu)化的SQL語句中，where子句中“and”邏輯運算符所連接的每個圓括號內的條件均包含采用“or”邏輯運算符連接兩個條件，將各參數的兩種狀態(tài)都表征出來了。當所有參數的值均為空時，最終執(zhí)行的SQL語句sqlStr等效為：

string sqlStr = \" select * from Tb_Material where (''='') and (''='') and (''='')……\";

亦等效為：

string sqlStr = \" select * from Tb_Material\"; 即對材料表(Tb_Material)的所有字段進行查詢。

這樣，無論查詢條件個數的多少，該SQL語句均可以自動地根據各參數的值進行條件匹配。經過優(yōu)化，在改進的SQL語句拼接基礎之上，巧妙地消去了源程序中繁瑣的If語句，無需對每個查詢條件逐一進行非空判斷，便可進行無限制的SQL語句自然拼接，從而實現了簡潔方便、高效靈活的動態(tài)多條件查詢。

從優(yōu)化技巧上看，該方法巧妙地利用了where子句中“or”與“and”兩種邏輯運算符，結合各參數的空與非空兩種相互對立的狀態(tài)，對where子句的結構進行了重組，從而達到了預期效果。同時，為了使系統(tǒng)更加高效地進行動態(tài)多條件查詢，可以運用該方法創(chuàng)設相應的存儲過程。上文中的源程序對應的存儲過程如下(設系統(tǒng)查詢界面或頁面中包含“材料代碼”、“材料名稱”、“單位名稱”、“價格上限”、“價格下限”、“庫存地”及“入庫時間”7個查詢條件)：

create procedure Pr_GetMaterial

(

@Snum varchar(20)，

@Sname varchar(50)，

@Unit varchar(10)，

@HighPrice decimal(18，3)，

@LowPrice decimal(18，3)，

@Place varchar(50)，

@Date varchar(20)

)

as

select Snum，Sname，Unit，Price，Place，Date

from Tb_Material

where (Snum like '%' + @Snum + '%' or @Snum='') and (Sname like '%' + @Sname + '%' or @Sname='') and (Unit like '%' + @Unit + '%' or @Unit='') and (Price >=@LowPrice or @LowPrice=-1) and (Price<=@HighPrice or @HighPrice=-1) and (Place like '%' + @Place + '%' or @Place='') and (Date=@Date or @Date='')

存儲過程“Pr_GetMaterial”包含7個參數，其中，參數@HighPrice與@LowPrice分別表示材料的價格上限與價格下限，它們的默認值均設定為-1，以保證當用戶在系統(tǒng)查詢界面或頁面中沒有指定材料的價格上限或價格下限時，@LowPrice=-1或@HighPrice=-1恒定成立，使得存儲過程能夠正常執(zhí)行。存儲過程經過編譯后存儲在數據庫中，通過數據庫應用程序的調用執(zhí)行，可以減少預處理所花費的時間，從而提高系統(tǒng)的執(zhí)行效率。

3 結束語

文章著眼于數據庫應用程序性能的提高，以SQL語句查詢優(yōu)化為出發(fā)點，對當前的動態(tài)多條件查詢方法進行了探究與對比分析，提出了一種高效通用的動態(tài)多條件查詢方法。采用該方法形成的SQL語句及程序代碼簡潔靈活，降低了算法的復雜度，能夠在很大程度上促進系統(tǒng)性能的提高。

參考文獻：

[1] 朱更明，李方敏，李和軍.基于數據庫系統(tǒng)表的通用查詢方法[J].計算機工程與應用，2001(24)

[2] 邵增珍，劉弘，劉磊.支持重用的可視化通用查詢界面的生成[J].計算機工程，2003(9).

[3] 劉桂芝.淺談SQL語句的優(yōu)化對應用程序性能的影響[J].一重技術，2007(1).

[4] 吳險峰.SQL Server環(huán)境下的SQL優(yōu)化方法探討[J].電腦知識與技術，2008(15).

[5] 姚劍芳.基于SQL Server 的多條件模糊匹配查詢[J].福建電腦，2010(4).

[6] 王云，郭外萍，陳承歡.Web項目中的SQL注入問題研究與防范方法[J].計算機工程與設計，2010，31(5).