在專案開發過程中,團隊成員的程式風格差異,加上資料庫操作語句 SQL 的高度靈活性,往往給專案帶來越來越多的操作與維護難度。
舉個例子:要從 user 表取出所有資料,有人會寫成 select * from user,有人則寫成 select all from user。雖然兩者在執行上都不會出錯,但當其他人閱讀程式時,就會產生不一致的觀感。若這種風格差異在專案中層出不窮,維護起來便會痛苦不堪。為了解決這個問題,我們可以考慮用 XML 來封裝資料庫操作語句,作為專案規範化的第一步。
為了便於說明,我們先在資料庫中建立兩張表:
TBL_USER:USERID BIGINT、USERNAME VARCHAR(20)、PASSWORD VARCHAR(20)、CREATETIME DATE
TBL_USER_INFO:USERID BIGINT、EMAIL VARCHAR(64)、MOBILE VARCHAR(13)
A) 分析 select 語句
以 SELECT USERNAME, PASSWORD FROM TBL_USER 為例,可將它描述為如下 XML:
<dbtrans name="selectUser" table="TBL_USER" method="select">
<get>
<property name="username" type="string"/>
<property name="password" type="string"/>
</get>
</dbtrans>
其中 dbtrans 為節點名稱,屬性 name 是交易名稱、table 是資料表名、method 是操作資料庫的方法(這裡為 select),子節點 <get> 表示從資料庫讀取資料,<property> 則是讀取的欄位,屬性 name 為欄位名、type 為欄位型別。
對於帶條件的 SELECT USERNAME, PASSWORD FROM TBL_USER WHERE USERID=123,可再增加一個 <key> 節點來描述查詢條件;若使用 like 操作,則可在 property 上加 match="like"。對於帶排序的查詢,可加上 <order name="username" match="desc"/>。若同一欄位要同時做區間比對(例如建立時間在某段範圍內),則可在 key 中以兩個 property 分別指定起始與結束時間,並透過 column 屬性對應到同一個實際欄位。
但即使如此,仍有少數特殊語句難以用上述結構描述,例如 SELECT COUNT(*) FROM TBL_USER。這時便可引入 special 屬性,把整條特殊語句直接寫在屬性中:
<dbtrans name="selectUser" table="TBL_USER" method="select" special="select count(*) from tbl_user">
</dbtrans>
B) 分析 INSERT 語句
INSERT 在關聯式資料庫中可以說是最麻煩的一條語句,因為當你需要在 TBL_USER 與 TBL_USER_INFO 兩張表建立對應資料時,必須先知道主鍵的值。為兼顧相容性,這裡採用另一種做法:在資料庫中建立一張 TSYS_PRIMARYKEY 表,包含 KEYID、TABLENAME 與 PRIMARYKEY 三個欄位,分別保存主鍵的值、表名與主鍵名稱。這樣一來,在執行 insert 前先取出目前主鍵值並加一,作為新的主鍵,insert 完成後再 update 這張表以保持同步。
於是有:
<dbtrans name="insertUser" table="TBL_USER" method="insert">
<primarykey name="userid">
<set>
<property name="username" type="string"/>
<property name="password" type="string"/>
</set>
</dbtrans>
C) 分析 DELETE 語句
Delete 最常用可分為兩種:按鍵值刪除與全部刪除,因此我們把它劃分為 delete 與 clear 兩種類型。前者以 <key> 指定條件,後者則直接清空整張表:
<dbtrans name="clearUserInfo" table="TBL_USER_INFO" method="clear">
</dbtrans>
D) 分析 UPDATE 語句
從 update 的常見用法可知,用 XML 表述時會出現 <key> 與 <set> 兩種 tag:
<dbtrans name="updateUserInfo" table="TBL_USER_INFO" method="update">
<key>
<property name="userid" type="long" />
</key>
<set>
<property name="email" type="string" />
</set>
</dbtrans>
分析完 XML 之後,接下來是程式設計。要實現上述操作,必須完成以下幾步:
1. 讀取 XML 文件
2. 定位相應的交易節點
3. 拼出 SQL 語句
4. 執行資料庫操作
5. 取資料
6. 返回資料
其中,第一步讀取文件可封裝所有 XML 解析與前期操作,這裡命名為 XMLLoadFile;交易處理(第 2、5、6 步)可封裝進 XMLTransaction 類別;拼裝 SQL 語句獨立建立 XMLCombine 類別(也可細分為 SelectCombine、InsertCombine 等);資料庫操作則封裝成 XMLExecuteSQL。以上所有類別統一由一個出口類別 XMLUtils 對外提供介面,例如 select、insert、delete、update,以及 setTransNode(設定交易節點)、setTransValue(設定交易資料值)、setTransKeyValue(設定交易鍵值)等方法。
完成封裝後,外部程式的呼叫就變得非常簡潔。以 select 為例:
XMLUtils util = new XMLUtils();
util.setTransNode("selectUser");
Vector vRtn = util.select( con );
帶條件的查詢則多加一行設定鍵值:
util.setTransNode("selectUserByKey");
util.setTransKeyValue("userid", 123 );
Vector vRtn = util.select( con );
insert 的呼叫如下,並可從返回結果中取出自動產生的主鍵:
util.setTransNode("insertUser");
util.setTransValue("username", "test" );
util.setTransValue("password", "test" );
Vector vRtn = util.insert( con );
long userid = ((Long)((Hashtable)vRtn.elementAt(0)).get("userid")).longValue();
delete 與 update 的呼叫 similarly 只需設定交易節點、鍵值與欲更新的資料值:
util.setTransNode("updateUserInfo");
util.setTransKeyValue("userid", 123);
util.setTransValue("email", "aaa@aaa.com");
util.update( con );
可以看到,經過這樣的封裝,所有程式員寫出來的資料庫操作都變得工整劃一、風格統一。程式員不必再費心拼寫 SQL 語句,既降低了出錯的機會,也大幅減輕了日後維護的負擔。