抽象SQL查詢：SQL-MAP技術的使用

什么是參數化查詢？我們來看百度百科對此的定義和示例：

一，定義

------------------------------------------------------------------

　參數化查詢（Parameterized Query 或 Parameterized Statement）是指在設計與數據庫鏈接并訪問數據時，在需要填入數值或數據的地方，使用參數 (Parameter) 來給值，這個方法目前已被視為最有效可預防SQL注入攻擊 (SQL Injection) 的攻擊手法的防御方式。

　　有部份的開發人員可能會認為使用參數化查詢，會讓程序更不好維護，或者在實現部份功能上會非常不便，然而，使用參數化查詢造成的額外開發成本，通常都遠低于因為SQL注入攻擊漏洞被發現而遭受攻擊，所造成的重大損失。

原理

　　在使用參數化查詢的情況下，數據庫服務器不會將參數的內容視為SQL指令的一部份來處理，而是在數據庫完成 SQL 指令的編譯后，才套用參數運行，因此就算參數中含有具有損的指令，也不會被數據庫所運行。

SQL 指令撰寫方法

　　在撰寫 SQL 指令時，利用參數來代表需要填入的數值，例如：

Microsoft SQL Server

　　Microsoft SQL Server 的參數格式是以 "@" 字符加上參數名稱而成，SQL Server 亦支持匿名參數 "?"。

　　SELECT * FROM myTable WHERE myID = @myID

　　INSERT INTO myTable (c1, c2, c3, c4) VALUES (@c1, @c2, @c3, @c4)

Microsoft Access

　　Microsoft Access 不支持具名參數，只支持匿名參數 "?"。

　　UPDATE myTable SET c1 = ?, c2 = ?, c3 = ? WHERE c4 = ?

MySQL

　　MySQL 的參數格式是以 "?" 字符加上參數名稱而成。

　　UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4

Oracle

　　Oracle 的參數格式是以 ":" 字符加上參數名稱而成。

　　UPDATE myTable SET c1 = :c1, c2 = :c2, c3 = :c3 WHERE c4 = :c4

PostgreSQL

　　PostgreSQL 的參數格式是以 "$" 字符加上參數順序號而成。

　　UPDATE myTable SET c1 = $1, c2 = $2, c3 = $3 WHERE c4 = $4

???? PostgreSQL也支持Oracle的參數表示形式

--------------------------------------------------------------------------------

總結一下各數據庫對于參數符號的定義：

SQLSERVER @

Access，MySQL ？

Oracle ：

PostgreSQL $

上面的這些符號是各數據庫內部原生支持的方式，但是具體到ADO.NET調用的時候，

采用各數據庫原生的.NET驅動程序，發現除了Oracle，各種數據庫都可以在SQL語句中用@符號表示參數；

采用各數據庫的OleDB或者ODBC驅動程序，都要求使用？符號表示參數。

還有其它本文未說到的數據庫，他們的SQL語句表示參數的符號可能都是不一樣的，怎么樣在程序里面統一處理呢？本文主題開始了：

二，抽象SQL參數化查詢

在PDF.NET（PWMIS數據開發框架）中，對參數的定義統一采用##來處理，具體格式如下：

#參數名字[:參數類型],[數據類型],[參數長度],[參數輸出輸入類型]#

上面定義當中，中括號里面的內容都是可選的。

詳細內容，請參看“SQL-MAP” 或者“PDF.NET（PWMIS數據開發框架）之SQL-MAP目標和規范”

對本文第一部分的示例，可以改寫成下面的方式：

 UPDATE?myTable?SET?
 c1?=?#c1#,?
 c2?=?#c2:String#,?
 c3?=?#c3:String,Sring,50#?
WHERE?c4?=?#c4:Int32# 

如果不指定參數的類型，默認為String類型，例如c1參數。

程序在運行時，會根據當前具體的數據庫訪問程序實例，將##內部的參數替換成合適的參數內容。

上面這種參數形式是寫在SQL-MAP配置文件里面的，例如下面的一個實際的SQL-MAP查詢腳本：

 ?<Select?CommandName="GetStatisticsAnalysis_SalerRoleStatistics"?CommandType="Text"?Method=""?Description=""?ResultClass="DataSet">
 ????????<![CDATA[
 ?????SELECT?a.角色,a.銷售金額/10000?銷售金額,a.占比?FROM?[GetStatisticsAnalysis_SalerRoleStatistics]?(
 ????#manageid:Int32#,?#min:String#,?#max:String#)?a]]>
 ??????</Select> 

如果想使用動態SQL語句，即SQL語句中有一個“假參數”，在運行時由另外一個字符串來替換的，例如非常復雜的查詢條件拼接過程，請參看：

在SQLMAP中使用動態SQL

通過這種方式，完全屏蔽了不同種類的數據庫查詢的參數問題，將SQL參數化查詢抽象了出來。

看到這里本文似乎該結束了，但本文的標題“參數化”加了一個括號，說明我們抽象的不僅僅是參數，我們還可以抽象整個SQL查詢。

三，抽象SQL查詢：SQL-MAP技術

在本文第二部分，我們將SQL中的參數“抽象化”了，我們還可以進一步抽象整個SQL，看下面的抽象過程：

編寫任意形式的合法SQL查詢語句；
抽象SQL中的參數；
將整個SQL語句抽象成一個唯一名字為CommandName；
將一組CommandName映射到一個DAL類文件；
將這個CommandName映射到一個DAL類的方法名稱；
將SQL語句中的參數名稱映射到該DAL類的當前方法中的參數名稱；
將整個SQL腳本文件映射到一個DAL程序集。

這個思想，就是SQL-MAP，將SQL語句映射為程序!

下面我們介紹一下PDF.NET數據開發框架對于存儲過程的操作思路，對于單條SQL也是如此。當然，單條SQL語句的操作我們不必請出SQL-MAP這種“重量級”的方式，還是使用框架中的ORM技術OQL吧，但這不是本文討論的話題。

首先，在SQL-MAP配置文件里面寫下面的腳本：

<Select?CommandName="GetProductManage_FundSaleAndAIP"?Method=""?CommandType="Text"?Description="獲取XXX列表"?ResultClass="DataSet">

`2`	`???????<![CDATA[`

`3`	`????????select * from GetProductManage_FundSaleAndAIP(#Type:String#,#Name:String#,#isAIP:String#)`

`4`	`???????]]>`

`5`	`?????</Select>`

注意腳本中的ResultClass屬性，它可以將查詢映射成為單值，DataSet，實體類，實體類集合。

有了這個SQL-MAP文件，我們可以使用代碼工具自動生成下面的代碼（當然你也可以手寫）：

 01/// <summary> 
???/// 獲取XXXXX列表  
???/// </summary>  
???/// <param name="Type"></param>  
???/// <param name="Name"></param>  
???/// <param name="isAIP"></param>  
???/// <returns></returns>  
???public?DataSet GetProductManage_FundSaleAndAIP(String Type? , String Name? , String isAIP?? )?  
???{?  
???????????//獲取命令信息  
???????????CommandInfo cmdInfo=Mapper.GetCommandInfo("GetProductManage_FundSaleAndAIP");  
???????????//參數賦值，推薦使用該種方式；  
???????????cmdInfo.DataParameters[0].Value = Type;  
???????????cmdInfo.DataParameters[1].Value = Name;  
???????????cmdInfo.DataParameters[2].Value = isAIP;  
???????????//參數賦值，使用命名方式；  
???????????//cmdInfo.SetParameterValue("@Type", Type);  
???????????//cmdInfo.SetParameterValue("@Name", Name);  
???????????//cmdInfo.SetParameterValue("@isAIP", isAIP);  
???????????//執行查詢  
???????????return?CurrentDataBase.ExecuteDataSet(CurrentDataBase.ConnectionString, cmdInfo.CommandType, cmdInfo.CommandText , cmdInfo.DataParameters);  
???????//  
???}//End Function 

從上面的過程可以看出，框架采用SQL-MAP技術，將SQL語句（包括各種查詢的單條SQL語句和存儲過程等）映射成了DAL層代碼，整個過程不需要了解.NET開發技術，所以DAL層的代碼完全可以由DBA來寫，而業務開發人員只要調用DAL代碼即可。

采用這種技術，DBA可以寫高效的有數據庫特性的SQL，如果要換數據庫，只需要換一個配置文件即可，不需要重寫程序。

題外話：

SQL-MAP思想并非PDF.NET數據開發框架獨有，實際上，該思想也是從著名的iBatis框架借鑒而來的，但與iBatis不同的是，PDF.NET的SQL-MAP參數不需要定義專門的“參數類”，也不需要寫額外的XML文件指明查詢結果如何與實體類映射，所以整個開發過程大大簡化，簡化到你只需要會寫SQL語句，就可以寫DAL代碼。

? ? 本文轉自深藍醫生博客園博客，原文鏈接：http://www.cnblogs.com/bluedoctor/archive/2011/05/06/2038727.html，如需轉載請自行聯系原作者