基于SQL語句的通用數(shù)據(jù)交換方法研究與應(yīng)用

葛從兵　陳劍　嚴(yán)吉皞

摘 要：為了避免頻繁地修改數(shù)據(jù)交換系統(tǒng)程序代碼，提高數(shù)據(jù)交換系統(tǒng)的通用性，提出了基于SQL語句的通用數(shù)據(jù)交換方法。該方法根據(jù)源數(shù)據(jù)庫和目的數(shù)據(jù)庫結(jié)構(gòu)編寫SQL語句，保存至XML文件。數(shù)據(jù)交換時，數(shù)據(jù)交換系統(tǒng)從XML文件讀入SQL語句，執(zhí)行相應(yīng)操作，完成數(shù)據(jù)交換。該方法已在實踐中得到廣泛應(yīng)用。

關(guān)鍵詞：數(shù)據(jù)交換；通用數(shù)據(jù)交換方法；SQL語句；XML

中圖分類號：TP301

文獻(xiàn)標(biāo)識碼：A 文章編號：1672-7800（2015）005-0044-03

作者簡介：葛從兵（1966-），男，江蘇南京人，碩士，南京水利科學(xué)研究院教授級高級工程師，研究方向為軟件工程；陳劍（1975-），男，江蘇南京人，南京水利科學(xué)研究院工程師，研究方向為計算機(jī)應(yīng)用；嚴(yán)吉皞（1987-），男，江蘇南京人，碩士，南京水利科學(xué)研究院工程師，研究方向為計算機(jī)理論。

0 引言

隨著計算機(jī)技術(shù)的發(fā)展，各行各業(yè)對信息化的需求越來越強(qiáng)烈，信息化水平也越來越高。作為國家防洪抗旱減災(zāi)體系的重要組成部分和國民經(jīng)濟(jì)的重要基礎(chǔ)設(shè)施，水庫也在逐步開展信息化工作。在水庫信息化過程中，經(jīng)常需要在局域網(wǎng)范圍內(nèi)進(jìn)行數(shù)據(jù)共享、數(shù)據(jù)集成或匯聚，這不可避免地涉及到數(shù)據(jù)交換。但由于水庫的監(jiān)控系統(tǒng)和信息系統(tǒng)由不同時期的不同承建商建設(shè)，導(dǎo)致同一水庫的不同系統(tǒng)或不同水庫的同一類型系統(tǒng)在數(shù)據(jù)存儲上存在諸多差異，若數(shù)據(jù)交換系統(tǒng)僅針對特定水庫，則每個水庫的數(shù)據(jù)交換系統(tǒng)都要根據(jù)水庫具體情況修改其程序代碼，不僅修改工作量大，系統(tǒng)穩(wěn)定性也得不到保證。因此，本文對數(shù)據(jù)庫差異和數(shù)據(jù)交換過程進(jìn)行研究，提出通過讀入XML文件中SQL語句[1]實現(xiàn)數(shù)據(jù)交換的通用數(shù)據(jù)交換方法，并在實踐中得到應(yīng)用。

1 數(shù)據(jù)存儲差異性與交換過程

1.1 數(shù)據(jù)存儲差異性

現(xiàn)在的信息系統(tǒng)一般采用數(shù)據(jù)庫存儲數(shù)據(jù)，很少采用文件形式存儲數(shù)據(jù)，故本文僅討論采用數(shù)據(jù)庫的數(shù)據(jù)存儲形式。

在數(shù)據(jù)交換中，源數(shù)據(jù)庫和目的數(shù)據(jù)庫的數(shù)據(jù)存儲差異性主要體現(xiàn)在以下幾個方面：

（1）數(shù)據(jù)庫管理系統(tǒng)不同。常用的數(shù)據(jù)庫管理系統(tǒng)有SQL Server、Oracle、MySQL、Sybase、DB2等[2]，早期系統(tǒng)有的還采用Access。

（2）實體-聯(lián)系模型不同。相同一組信息，源數(shù)據(jù)庫存儲在兩個表中，目的數(shù)據(jù)庫存儲在一個表中，或源數(shù)據(jù)庫存儲在一個表中，目的數(shù)據(jù)庫存儲在兩個表中。

（3）字段類型不同。相同信息，源數(shù)據(jù)庫以數(shù)值型存儲，目的數(shù)據(jù)庫以字符型存儲，或源數(shù)據(jù)庫以字符型存儲，目的數(shù)據(jù)庫以日期型存儲。

（4）字段長度不同。相同信息在源數(shù)據(jù)庫和目的數(shù)據(jù)庫里字符串長度、數(shù)值長度或小數(shù)位數(shù)不一樣。

（5）標(biāo)示符命名規(guī)則不同。表名和字段名差異較大。

（6）同一對象，如測點，其名稱或編碼在源數(shù)據(jù)庫和目的數(shù)據(jù)庫里不同。

（7）同一組信息，源數(shù)據(jù)庫里存儲的屬性比目的數(shù)據(jù)庫里少。

這些差異性要求數(shù)據(jù)交換時需進(jìn)行必要的轉(zhuǎn)換。

1.2 數(shù)據(jù)交換過程

數(shù)據(jù)交換一般包括3個步驟：數(shù)據(jù)提取、數(shù)據(jù)轉(zhuǎn)換、數(shù)據(jù)寫入。數(shù)據(jù)提取先連接源數(shù)據(jù)庫，然后從相關(guān)表中提取所需數(shù)據(jù)，數(shù)據(jù)提取需要知道源數(shù)據(jù)庫的數(shù)據(jù)庫管理系統(tǒng)以及源數(shù)據(jù)庫表結(jié)構(gòu)；數(shù)據(jù)轉(zhuǎn)換是將來自源數(shù)據(jù)庫的數(shù)據(jù)轉(zhuǎn)換為符合目的數(shù)據(jù)庫要求的數(shù)據(jù)，包括數(shù)據(jù)拆分、數(shù)據(jù)重組、數(shù)據(jù)類型及長度變換等，數(shù)據(jù)轉(zhuǎn)換需要知道源數(shù)據(jù)庫與目的數(shù)據(jù)庫的表結(jié)構(gòu)以及它們之間的對應(yīng)關(guān)系；數(shù)據(jù)寫入先連接目的數(shù)據(jù)庫，然后將轉(zhuǎn)換后的數(shù)據(jù)寫入相關(guān)表，數(shù)據(jù)寫入需要知道目的數(shù)據(jù)庫的數(shù)據(jù)庫管理系統(tǒng)以及目的數(shù)據(jù)庫表結(jié)構(gòu)。

數(shù)據(jù)寫入時需考慮目的數(shù)據(jù)庫中已有數(shù)據(jù)的更新問題。由于數(shù)據(jù)交換一般針對時間序列數(shù)據(jù)，即數(shù)據(jù)元組中含有時間戳，因此可以根據(jù)時間段來處理數(shù)據(jù)更新問題。數(shù)據(jù)更新有兩種方式：①先檢測目的數(shù)據(jù)庫是否已有該時間段數(shù)據(jù)，如果有，用更新語句更新數(shù)據(jù)，否則用插入語句插入數(shù)據(jù)；②先直接刪除目的數(shù)據(jù)庫中可能有的該時間段數(shù)據(jù)，然后用插入語句插入數(shù)據(jù)。后一方式操作相對簡單。

2 通用數(shù)據(jù)交換方法

通用數(shù)據(jù)交換方法將一次完整的數(shù)據(jù)交換分為若干個數(shù)據(jù)交換過程，每個交換過程可以針對數(shù)據(jù)庫里的一個表，也可以針對數(shù)據(jù)庫一個表中的特定數(shù)據(jù)。數(shù)據(jù)交換過程由保存在XML文件中的SQL語句控制，SQL語句根據(jù)源數(shù)據(jù)庫和目的數(shù)據(jù)庫及其對應(yīng)關(guān)系編寫。

控制數(shù)據(jù)交換過程的SQL語句存儲格式如下：

DataExchange元素表示一個數(shù)據(jù)交換過程，它包含一個交換過程所需的SQL語句。

SourceConnection元素和TargetConnection元素為源數(shù)據(jù)庫和目的數(shù)據(jù)庫的連接字符串。連接字符串中必須包含Provider（提供程序）的值，其它值因數(shù)據(jù)庫管理系統(tǒng)和連接方式不同而不同，如Data Source、User ID、Password等。以O(shè)LEDB為例，典型連接字符串如下：

（1）Oracle："Provider=MSDAORA；Data Source=；User ID=myUsername；Password=myPassword"

（2）SQL Server："Provider=SQLOLEDB；Data Source=（local）； Initial Catalog=mydb；User ID=myUsername；Password=myPassword"

（3）MySQL："Provider=MySQLProv；Data Source=mydb；User ID=myUsername；Password=myPassword"

（4）Access："Provider=Microsoft.Jet.OLEDB.4.0；Data Source=c：＼＼bin＼＼LocalAccess40.mdb"

SelectSQL元素為從源數(shù)據(jù)庫提取數(shù)據(jù)的Select語句。Select語句可以提取一個表里一個時間段內(nèi)全部數(shù)據(jù)，也可以提取一個表里一個時間段內(nèi)的特定數(shù)據(jù)，如某個測點的數(shù)據(jù)，Select語句還可以通過多表連接提取數(shù)據(jù)。同時，Select語句中的Select子句還承擔(dān)數(shù)據(jù)轉(zhuǎn)換工作，包括數(shù)據(jù)拆分、數(shù)據(jù)重組、數(shù)據(jù)類型及長度變換等，這些轉(zhuǎn)換可能用到數(shù)據(jù)庫管理系統(tǒng)提供的函數(shù)。Where子句中開始時間和結(jié)束時間采用保留字符串“_STTM”和“_ENTM”的方式。由于Select語句里可能包含標(biāo)點符號，故采用CDATA段[3]，即以“<！[CDATA[”標(biāo)記開始，以“]]”標(biāo)記結(jié)束。

DeleteSQL元素為從目的數(shù)據(jù)庫刪除數(shù)據(jù)的Delete語句，它可以避免插入數(shù)據(jù)時違反主鍵約束。Delete語句中Where子句一般跟上述Select語句中Where子句存在一致性。

ColumnNum元素表示Select語句的Select子句中前ColumnNum個列的數(shù)據(jù)將傳遞至Insert語句。

InsertSQL元素為將數(shù)據(jù)寫入目的數(shù)據(jù)庫的Insert語句。其Values子句包含“_Column0”、“_Column1”、“_Column2”、“_Column3”等保留字符串，用于傳遞Select語句中的數(shù)據(jù)，分別對應(yīng)于Select子句中第1列、第2列、第3列、第4列等。Values子句也可以承擔(dān)數(shù)據(jù)轉(zhuǎn)換工作。

3 應(yīng)用

通用數(shù)據(jù)交換系統(tǒng)是一個采用基于SQL語句的通用數(shù)據(jù)交換方法開發(fā)，并廣泛應(yīng)用于水庫局域網(wǎng)信息共享和數(shù)據(jù)集成的數(shù)據(jù)交換系統(tǒng)。其集成開發(fā)環(huán)境為Microsoft Visual Studio 2010，開發(fā)語言為C#，數(shù)據(jù)庫訪問采用微軟應(yīng)用程序接口OLEDB，支持常用的數(shù)據(jù)庫管理系統(tǒng)。雖然OLEDB的效率比其它專用數(shù)據(jù)庫接口效率低，但數(shù)據(jù)交換系統(tǒng)的通用性提高。

通用數(shù)據(jù)交換系統(tǒng)界面如圖1所示。通用數(shù)據(jù)交換系統(tǒng)有兩種交換方式：定時交換和即時交換。定時交換每隔一段時間進(jìn)行一次數(shù)據(jù)交換，交換的數(shù)據(jù)時間段為上次交換截止時間至當(dāng)前時間，兩次交換之間的時間間隔可以是幾分鐘，也可以是幾個小時；即時交換可任意指定本次交換的數(shù)據(jù)時間段，點擊交換按鈕后，立即進(jìn)行交換。定時交換用于實時數(shù)據(jù)自動交換，即時交換用于歷史數(shù)據(jù)交換。

通用數(shù)據(jù)交換系統(tǒng)的數(shù)據(jù)提取和插入代碼如下：

tstr = select[i].Replace（"_STTM"， _STTM.ToString（））.Replace（"_ENTM"， _ENTM.ToString（））；

oledbAdapt.SelectCommand = new OleDbCommand（tstr， oledbConn1）；

oledbAdapt.Fill（dataSet， "temp"）；

for （int j = 0； j < dataSet.Tables["temp"].Rows.Count； j++）

{

tstr = insert[i]；

for （int p = 0； p < ColumnNum[j]； p++）

{

_Column = dataSet.Tables["temp"].Rows[j][p].ToString（）；

tstr=tstr.Replace（"_Column"+p.ToString（），_Column）；

}

try

{

oledbComm = new OleDbCommand（tstr， oledbConn2）；

oledbConn2.Open（）；

oledbComm.ExecuteNonQuery（）；

}

catch { }

finally

{

oledbConn2.Close（）；

}

}

dataSet.Tables.Remove（"temp"）；

上述代碼中，字符串?dāng)?shù)組select和insert分別存放Select語句和Insert語句，oledbConn1和oledbConn2分別連接源數(shù)據(jù)庫和目的數(shù)據(jù)庫，變量_STTM和_ENTM分別存儲開始時間和結(jié)束時間。數(shù)據(jù)提取時，均將數(shù)據(jù)轉(zhuǎn)換為字符串，寫入時由Insert語句將其轉(zhuǎn)換成目的數(shù)據(jù)庫里的類型。

通用數(shù)據(jù)交換系統(tǒng)用于不同水庫時，不需要對通用數(shù)據(jù)交換系統(tǒng)程序進(jìn)行任何修改，僅需編寫XML文件中的SQL語句。典型XML文件內(nèi)容如下：

<？xml version="1.0" encoding="utf-8"？>

2015-1-7 15：55：56

Provider=MSDAORA；data source=ORCL1；user id=DSMIS；password=dsmis

Provider=MSDAORA；data source=；user id=SRE；password=sre

<！[CDATA[select MPCD，MSDT，SPPR from MI_SPPR_M where MSDT>to_date（'_STTM'，'yyyy-mm-dd hh24：mi：ss'） and MSDT<=to_date（'_ENTM'，'yyyy-mm-dd hh24：mi：ss'）]] >

<！[CDATA[delete from EGS_SPPR_R where MSTM>to_date（'_STTM'，'yyyy-mm-dd hh24：mi：ss'） and MSTM<=to_date（'_ENTM'，'yyyy-mm-dd hh24：mi：ss'）]] >

<_ColumnNum>3

<！[CDATA[insert into EGS_SPPR_R（HYCNCD，MPCD，MSTM，SPPRWL） values（'BFA89001782F8'，'_ Column0'，to_date（'_ Column1'，'yyyy-mm-dd hh24：mi：ss'），_ Column2）]] >

Provider=MSDAORA；data source= ORCL2；user id=WATER；password=water

Provider=MSDAORA；data source=；user id=SRE；password=sre

<！[CDATA[select RECORD_TIME，WATER_VALUE from MSE_WATER where RECORD_TIME>to_date（'_STTM'，'yyyy-mm-dd hh24：mi：ss'） and RECORD_TIME<=to_date（'_ENTM'，'yyyy-mm-dd hh24：mi：ss'） and DEVICE_ID='W0005']] >

<！[CDATA[delete from ST_RSVR_R where MSTM>to_date（'_STTM'，'yyyy-mm-dd hh24：mi：ss'） and MSTM<=to_date（'_ENTM'，'yyyy-mm-dd hh24：mi：ss'） and MPCD='UPWL'）]] >

<_ColumnNum>2

<！[CDATA[insert into ST_RSVR_R（HYCNCD，MPCD，MSTM，WL） values（'BFA89001782F8'，'UPWL'，to_date（'_Column0'，'yyyy-mm-dd hh24：mi：ss'），_ Column1）]] >

上述XML文件中包括兩個數(shù)據(jù)交換過程，它們的源數(shù)據(jù)庫和目的數(shù)據(jù)庫的數(shù)據(jù)庫管理系統(tǒng)均為Oracle，源數(shù)據(jù)庫服務(wù)名分別為ORCL1和ORCL2，目的數(shù)據(jù)庫均為本地數(shù)據(jù)庫。由于數(shù)據(jù)庫管理系統(tǒng)為Oracle，所以XML文件中SQL語句均遵循Oracle的SQL語法[4]。第一個交換過程是將源數(shù)據(jù)庫中表MI_SPPR_M中的數(shù)據(jù)寫入目的數(shù)據(jù)庫中的表EGS_SPPR_R，同時將HYCNCD字段內(nèi)容設(shè)置為“BFA89001782F8”，即補(bǔ)充字段內(nèi)容。第二個交換過程是將源數(shù)據(jù)庫中表MSE_WATER中儀器編號為“'W0005”的數(shù)據(jù)寫入目的數(shù)據(jù)庫中的表ST_RSVR_R。除了將HYCNCD字段內(nèi)容設(shè)置為“BFA89001782F8”外，還要將MPCD字段內(nèi)容設(shè)為“UPWL”，即進(jìn)行編號轉(zhuǎn)換。

4 結(jié)語

本文介紹了一種基于SQL語句的通用數(shù)據(jù)交換方法。它從XML文件中讀取SQL語句，包括數(shù)據(jù)庫連接字符串與Select、Delete、Insert語句，根據(jù)這些SQL語句執(zhí)行數(shù)據(jù)交換過程。采用該方法開發(fā)的數(shù)據(jù)交換系統(tǒng)，不需要修改程序代碼，僅需編寫XML文件中的SQL語句，即可用于不同環(huán)境下不同系統(tǒng)之間的信息共享和信息集成。基于該方法的通用數(shù)據(jù)交換系統(tǒng)現(xiàn)已廣泛應(yīng)用于水庫局域網(wǎng)內(nèi)信息共享和數(shù)據(jù)集成。

參考文獻(xiàn)：

[1] 王珊，薩師煊.數(shù)據(jù)庫系統(tǒng)概論[M].第5版.北京：高等教育出版社，2014：91-117.

[2] ABRAHAM SILBERSCHATZ，HENRY F KORTH，S.SUDARSHAN.數(shù)據(jù)庫系統(tǒng)概念[M].第6版.楊冬青，李紅燕，唐世渭，譯.北京：機(jī)械工業(yè)出版社，2012：633-719.

[3] JOE FAWCETT，LIAM R E QUIN，DANNY AYERS.XML入門經(jīng)典[M].第5版.劉云鵬，王超，譯.北京：清華大學(xué)出版社，2013：32-33.

[4] 李興華，馬云濤.Oracle開發(fā)實戰(zhàn)經(jīng)典[M].北京：清華大學(xué)出版社，2014：56-138.

（責(zé)任編輯：黃 ?。?