第三章 系統設計與架構
3.4 設計方法
3.4.5 產生具 SQL 語法改寫功能之 JDBC 驅動程式
國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
advice 使用 around 方式，會將 SQL 執行命令以參數 s 帶入並且指定傳出值為 object 型態。SELECT 語法經過轉換後會交由 proceed 以新的 SQL 語句取代原本的來執 行，查詢結果也會以轉換後的 SQL 語句執行結果回傳。
AOP 最後會再透過織入(weaving)的機制將 aspect 的程式碼整合到 JDBC 功 能模組中，而不會更動到原有的軟體架構。
3.4.5 產生具 SQL 語法改寫功能之 JDBC 驅動程式
本工具利用 3.3.1 所產生的 AspectＪ程式，利用 AspectJ 的編譯工具(AJK)來 進行編譯，並將其他所需程式一併加入。其執行語法如下：
ajc -inpath c:\ mysql-connector-java-5.1.17-bin.jar c:\jsqlparser.jar nccu\Rewrite.aj -outjar nccu\ mysql-connector-java-5.1.17-bin.jar
-inpath ， 首 先 指 定 原 廠 的 JDBC 驅 動 程 式 (mysql-connector-java-5.1.17 -bin.jar)。之後將 SQL Parser 工具(jsqlparser.jar)與 3.4.3 所產生的 AspectJ 程式 (Rewrite.aj)一起編譯。-outjar，指定編譯完成後，產出的 jar 檔名稱。編譯架構圖，
如圖 3.11 所示。
圖 3.11 本研究編譯流程
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
本研究產生的 JDBC 驅動程式，只需在執行應用程式的 Java VM 環境中，將 原廠的 JDBC 驅動程式置換，並配合上 schema-mapping table 的設定檔，即可使 用。當資料庫 schema 架構改變時本研究保留彈性，可依照不同 schema 設計不同 SQL 語句轉換機制。而系統將來需要改回原本資料庫 schema 架構時，亦可直接 將 JDBC 驅動程式置換回原廠 JDBC 驅動程式。
‧
就是一個有效的 AspectJ 程式。AspectJ 的編譯器產生出符合 Java byte-code 規範，可執行在 Java 的 virtual machine (VM)。它具備所有的 Java 優點，也 方便 Java 程式設計師去學習 AspectJ。 Eclipse：Eclipse[10]是一個開放程式碼的計畫，其宗旨是希望能夠增進軟體 開發的效率。Eclipse 是 Java IDE 工具，提供了強大的功能，可以幫助開發 人員編寫、編譯與部署 Java 程式，並將版本控制和 XML 編輯等多功都融入 其中。
Java 程式語言：Java[11]是源自 Sun Microsystems 在 1991 年的 the Green Project。Java 的跨平台特性且可產生更動態的網頁功能都受到歡迎。
Jsqlparser：Jsqlparser[12]可以方便程式設計人員在程式中加入 SQL Parser 機 制，並可將 SQL 語句分類。
Mysql 資料庫：Mysql[8] 是一個快速穩定多執行緒多使用者的資料庫伺服
‧
在本章節中，我們先以 private table 資料庫 schema 架構的多租戶實例來介紹本研 究。之後，介紹將 JDBC 驅動程式加上 SQL 語句自動轉換功能的實作與展示，
展示將資料庫 schema 架構修改成 chunk table 之後程式操作的案例。
4.2.1 實例情境說明與轉換機制
在實作之前，系統開發人員必需先事先準備完成 private table schema 資料庫 架構的多租戶應用程式，並安裝搭配 MySQL 的 JDBC 驅動程式。在本節，我們 以 private table 多租戶應用程式為基礎，以各種 SQL 實例來說明本系統的設計，
實際展示 SQL 語句轉換機制如何將 DDL (CREATE、ALTER、DROP)及 DML (SELECT、INSERT、UPDATE、DELETE)的等動作轉換成 chunk table schema 架 構的多租戶應用程式。
‧
AccountID Name
1 林小萱
2 周小芳
圖 4.1 一租戶一表格之範例
根據以上的表格設計，如果我們要查詢 Tenant 31 裡 AccountID 為 1 的 Major 欄位資料，可用 SQL 指令「SELECT Major FROM Tenant31 WHERE AccountID = 1」來完成。接著我們以此範例為基礎，介紹幾種常見的一租戶一表格使用情境。
1. 實例情境說明與分析
本節第一個部分先說明 private table 架構下，常見的 DDL 如 CREATE、
ALTER、DROP 等，SQL 語句的實際操作例子及語法作用，第二個部分再說明 DML 常見的操作語法，如 SELECT、INSERT、UPDATE、DELETE 等，SQL 語 句實際操作例子及語法作用。
第一部份，我們將 DDL 操作語法依下列幾種常見的陳述式來做討論：
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
CREATE 陳述式：CREATE TABLE tenant32_student (address varchar(50), city varchar(50), country varchar(25), age integer(8))。語法執行的目的是要新增一個資 料表名稱為 tenant32_student，資料表欄位包含有 address 屬於 varchar 型別、city 屬於 varchar 型別、country 屬於 varchar 型別及 age 屬於 integer 型別，在 private table schema 架構下執行的結果如圖 4.2，會協助我們建立一個新的資料表。
圖 4.2 CREATE 語法執行結果
ALTER add 陳述式：ALTER table tenant32_student add project varchar(10)。
語法執行的目的是要在資料表 tenant32_student 新增一個欄位名稱為 project 屬於 varchar 型別的欄位，在 private table schema 架構下執行的結果如圖 4.3，會協助 我們新增一個欄位。
圖 4.3 ALTER add 語法執行結果
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
ALTER modify 陳 述 式 ： ALTER table tenant32_student modify project integer(3)。語法執行的目的是要在資料表 tenant32_student 修改欄位名稱為 project 的欄位型別，更改為 Integer 型別，在 private table schema 架構下執行的結果如圖 4.4，會協助我們修改欄位型別。
圖 4.4 ALTER modify 語法執行結果
ALTER change 陳述式：ALTER table tenant32_student change project proj varchar(50)。語法執行的目的是要在資料表 tenant32_student 修改欄位名稱為 project 的欄位名稱與型別，更改名稱為 proj 型別為 varchar，在 private table schema 架構下執行的結果如圖 4.5，會協助我們修改欄位名稱與型別。
圖 4.5 ALTER change 語法執行結果
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
ALTER drop 陳述式：ALTER table tenant32_student drop proj。語法執行的目 的是要在資料表 tenant32_student 刪除欄位名稱為 proj 的欄位，在 private table schema 架構下執行的結果如圖 4.6，會協助我們移除指定的欄位。
圖 4.6 ALTER drop 語法執行結果
DROP 陳述式：DROP TABLE tenant32_student。語法執行的目的是要刪除資 料表 tenant32_student 包含所有欄位名稱與型別，會協助我們移除指定的資料表。
第二部份，我們將說明 DML 操作語法在 private table 上的實例可依下列幾 種常見的陳述式來做討論：
SELECT 陳述式，依照不同使用情境共提出了多欄位查詢、SELECE *查詢、
多 Table 查詢、JOIN 查詢和多對多關係查詢等，幾種常用的查詢關係來做說明。
首 先 ， 說 明 多 欄 位 查 詢 語 句 ： SELECT tenant31.accountid, tenant31.name, tenant31.major, tenant31.grade FROM tenant31 WHERE tenant31.accountid= 1。語 法執行的目的是要查詢資料表 tenant31 裡，accountid 是 1 的 accountid、name、
major 與 grade 等，指定的欄位資料，在 private table schema 架構下執行的結果如 圖 4.7，會協助我們查詢指定的資料表與欄位。
圖 4.7 多欄位查詢語法執行結果
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
SELECE *查詢語句：select * from tenant31。語法執行的目的是要查詢資料 表 tenant31 裡，所有欄位資料，在 private table schema 架構下執行的結果如圖 4.8，
會協助我們查詢指定的資料表所有欄位。
圖 4.8 SELECT *查詢語法執行結果
多 Table 查詢語句：SELECT tenant31.major, tenant31_class.accountid FROM tenant31,tenant31_class WHERE tenant31.accountid= 1。語法執行的目的是要查詢 資料表 tenant31 裡 major 欄位與 tenant31_class 裡 accountid 欄位的資料，並限制 條件是資料表 tenant31 中 accountid 欄位為 1 的資料才取出，在 private table schema 架構下執行的結果如圖 4.9，會協助我們查詢指定的資料表與欄位的資料。
圖 4.9 多 Table 查詢語法執行結果
JOIN 查詢語句 ： select name, classname, room from tenant31 inner join tenant31_class ON tenant31.accountid = tenant31_class.accountid。語法執行的目的 是要查詢符合指定等值連接的條件 accountid 相等的兩個資料表 tenant31 與 tenant31_class 裡 name、classname、room 欄位，並連接資料表 tenant31 與
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
tenant31_class，在 private table schema 架構下執行的結果如圖 4.10，會協助我們 連接不同資料表並查詢指定的欄位資料。
圖 4.10 JOIN 查詢語法執行結果
多對多關係查詢我們可以先建立如圖 4.11 的關聯表格，除了 Tenant31 原有 的資料表之外，另外又加上 tenant31_class 與 tenant31_score 資料表，如果我們今 天 要 查 詢 tenant31 學 生 所 修 過 的 課 及 每 堂 課 的 修 業 分 數 ， 就 需 要 依 靠 tenant31_score 做為我們的連接資料表，將另兩份資料表的主索引作為自己的外 部索引鍵。例如，[學生] 資料表 tenant31 和 [開課資料] 資料表 tenant31_class 有多對多關聯性。一位學生可選修多堂課，而每堂課程可出現在許多學生的選課
圖 4.11 多對多資料表關聯圖
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
單上。連接資料表 tenant31_score 對另外 2 個資料表都有一對多的關聯性，靠內 含參照資訊即可與其他資料表發生關聯產生多對多的關係。
多 對 多 關 係 查 詢 語 句 ： select tenant31.`name`, tenant31_class.classname, tenant31_score.score from tenant31 inner join tenant31_score on tenant31.accountid
= tenant31_score.accountid inner join tenant31_class on tenant31_score.classid = tenant31_class.classid 。 語 法 執 行 的 目 的 是 要 查 詢 兩 個 資 料 表 tenant31 與 tenant31_class 多 對 多 關 聯 的 name 、 classname 、 score 欄 位 ， 並 將 資 料 表 tenant31_score 連接資料表 tenant31 與 tenant31_class，在 private table schema 架構 下執行的結果如圖 4.12，會協助我們連接不同資料表並查詢指定的欄位資料。
圖 4.12 多對多查詢語法執行結果
INSERT 陳述式：INSERT INTO tenant32 (`accountid`, `name`, `age`) VALUES (2, '林小白', 25)。語法執行的目的是要新增一筆資料列到資料表 tenant32 裡，欄 位 accountid、name、age 的值分別是 2、林小白、25，在 private table schema 架 構下執行的結果如圖 4.13，會協助我們查詢指定的資料表與欄位的資料。
圖 4.13 INSERT 語法執行結果
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
UPDATE / DELETE 陳述式：UPDATE tenant31 SET major=‘資訊管理系' WHERE tenant31.accountid=2。語法執行的目的是要修改一筆資料表 tenant31 裡 的資料列，當 accountid=2 時欄位 major 的值由應用數學系更改為資訊管理系，
執行結果會協助我們修改指定的資料表與欄位的資料。DELETE 語法：DELETE FROM tenant31 WHERE tenant31.accountid = 2 語法執行的目的是要刪除一筆資 料表 tenant31 裡的資料列，當欄位 accountid=2 時即刪除該筆資料，執行結果會 協助我們刪除指定的資料表與欄位的資料。
4.2.2. JDBC 驅動程式 with SQL Rewriting 實作展示
本節會將上一節所介紹的所有情境，利用第 3.4 章提出的 SQL 語句轉換機 制轉換為適合用在 chunk table schema 架構的多租戶應用程式 SQL 語句，Chunk Table 這個作法是參考 Pivot 與 Universal table 的方式並加以改良。首先，也是將 所有租戶的資料放置於同一個資料庫表格，但它改良 Universal table，加入了資 料型別的考量，將租戶資料依其型別存放於不同的欄位內，一欄位一型別，稱之 為 chunk，而不是像 Pivot table，一型別一表格的作法。並以新增的 TenantID 欄 位、Table 欄位、ChunkNo 欄位以及 Row 欄位來區分不同租戶的資料。其餘的欄 位用來放置該租戶的資料。圖 4.14 是以上述的基礎設計出來的 schema，在本例 中，有整數與字串兩種資料型別，所以有 Int1 與 Str1 兩個 Chunks，同一筆資料 紀錄的相同型別的資料以 ChunkNo 來區別。例如，Tenant 31 的 AccountId 與 Grade 欄位皆為整數型別，但它們的資料可以透過 ChunkNo 的值來區別。
‧ 國
立 政 治 大 學
‧
N a tio na
l C h engchi U ni ve rs it y
ChunkTable (Int and Str)
TenantID Table ChunkNo Row Int1 Str1
31 0 0 0 1 李小白
31 0 1 0 2 資訊科學系
31 0 0 1 2 黃小明
31 0 1 1 3 應用數學系
32 1 0 0 1 陳小春
32 1 1 0 21 (空)
33 2 0 0 1 林小萱
33 2 0 1 2 周小芳
圖 4.14 Chunk Table 之多租戶資料綱要
本節第一個部分先說明如何系統化轉換 DDL 語法的執行，第二個部分再說 明 DML 語句的轉換規則與步驟，並證明本研究提出的 SQL 語句自動轉換機制 適用於這幾種常見的語法操作。
第一部份，我們先說明 DDL 操作語法是如何轉換應用於 chunk table schema
第一部份，我們先說明 DDL 操作語法是如何轉換應用於 chunk table schema