再論 Store Procedure

上一篇中，討論了 JDBC 提供了一個規範，讓呼叫不同 DB 都可以用統一的語法來呼叫 Store Procedure...
而在 Hibernate中要呼叫 Store Procedure，則設定方法如下：

<sql-query name="getBankVO" callable="true">
  <return class="storeprocedure.BankVO">
    <return-property name="bankId" column="BANK_ID" />
    <return-property name="bankName" column="BANK_NAME" />
    <return-property name="regNo" column="REG_NO" />
  </return>
  { ? = call test_sp ? }
</sql-query>

如此我們就可以使用以下程式來取得結果
    Query query = session.getNamedQuery("getBankVO");
    query.setParameter(0, "008");
    List list = query.list();

原本這段程式碼是應當可以運作的, 但是事實不是如此。
這就引發我的好奇心了，就連Hibernate的教學手冊也是這樣寫的，只不過教學手冊寫的是For Oracle資料庫，而我們跑的是Sql Server，難道就因為DB不同而有不同寫法嗎?
那就失去 JDBC 規範出一套通用語法的好處了...
雖然後來我們把 { ? = call test_sp ? } 改成sql server的語法呼叫 exec test_sp ? 之後就可以跑了.

雖然問題有解決, 但是心中總是有一個疑問，有問題沒有獲得解答，是蠻難過的。

所以，我就開始追蹤 Hibernate 是怎樣處理 Store Procedure。
最後追蹤到 /hibernate/src/org/hibernate/loader/Load.java 裡面關於註冊輸出參數的解法:
if (callable) {
    col = dialect.registerResultSetOutParameter( (CallableStatement)st, col );
}
看到這裡，我又想到，JDBC規範的兩種語法中，若使用無輸出參數的寫法(即{call test_sp ?})，那這邊似乎會有問題發生，再看一次 Hibernate 的教學手冊中寫道:
The stored procedure/function must return a resultset as the first out-parameter to be able to work with Hibernate.
嗯，hibernate 目前只能支援有宣告輸出參數的寫法(事後實驗結果也一致)。

回到剛剛追蹤的程式碼，發現到他會轉交給 dialect 去處理，這就聯想到在設定 hibernate 時所設定的 dialect 物件，於是追蹤到 org.hibernate.dialect.SQLServerDialect 的父類別找到如下程式：
public int registerResultSetOutParameter(CallableStatement statement, int col) throws SQLException {
    return col; // sql server just returns automatically
}

問題就在這邊了，這個 SQL Dialect 實作並沒有處理任何事項，就回傳了。
發現問題之後，就要動手改了，改成如下:
public int registerResultSetOutParameter(CallableStatement statement, int col) throws SQLException {
    statement.registerOutParameter(col, Types.INTEGER);
    col++;
    return col;
}

改完之後，將hibernate重新包成一包再跑一遍，看到執行結果時真是令人感到興奮，成功了。
終於能呼叫 { ? = call test_sp ? } 看到執行結果，就跟直接使用 JDBC 的呼叫方法一樣。

雖然成功的跑過一遍，但心中還是不踏實，為什麼 hibernate 沒有好好處理到這一段呢?
或許是目前 hibernate 對 Store Procedure 還沒有很完善，但是沒有完善的處理可以不實作(Dialect會拋出UnsupportedOperationException).但是不能說實作了不能用。

再歸納了所有有實作此方法的物件如下：

1. MySQLDialect - 及其子類別 (MySQLInnoDBDialect, MySQLMyISAMDialect)


2. DB2Dialect - 及其子類別 (DB2390Dialect, DB2400Dialect, DerbyDialect)


3. SybaseDialect - 及其子類別 (SQLServerDialect, Sybase11Dialect, SybaseAnywhereDialect)


4. Oracle9Dialect - 及其子類別 (OracleDialect)

後來再想想，由於我使用的是 jdts 的 JDBC Driver，換成 SQL Server 官方版的 Driver試試看，於是在抓了最新版的 MS JDBC Driver 之後，跑一般的 JDBC 程式，根本就不能使用 {? = call test_sp ?} 的語法來呼叫 store procedure...只能使用 exec test_sp ? 的語法來跑。

我想這大概就是 hibernate 會這樣實作的原因了，對於 Oracle 等有提供正確語法的支援就正常處理，若遇到其他的DB，則實作方式就是不管輸出參數....如此就以該 DB 的語法來使用。

再實際去追蹤 jdts 0.8的 source code，證實 jdts 會額外去處理 call 開頭或 ? 開頭的語法，解析出 store procedure 的名稱，再去與 Sql Server 溝通。

終於把心中所有的疑惑解決了...

發現到，雖然 sun 提出了一個規範讓實作廠商實作，但是廠商的配合度真是不夠。

微軟是個大廠商，實在應該大大方方的實作標準的 JDBC 規範(這小小的解析大括弧和 call關鍵字說實在並不是很難)，而不是交差了事，反而還比不上open source的程式。

事後覺得當時並不用大費周章去改 hibernate 的程式碼，只要把原本的 SQLServerDialect copy 出來改類別名稱..如 MySQLServerDialect, 然後在 hibernate.cfg.xml 指定使用 MySQLServerDialect。應該也是可以達成一樣的效果。

JDBC Store Procedure

今天研究了在 JDBC 中如何呼叫 DB 的 Store Procedure, 在 Java Doc裡面說到, JDBC 提供了一種通用的寫法來呼叫 Store Procedure, 以因應各種不同的 DB(由於目前各 DB 呼叫 Stroe Procedure 的方法不一). 而其寫法如下:

• {?= call <procedure-name>[<arg1>,<arg2>...]}

• {call <procedure-name>[<arg1>,<arg2>...]}

以上方法二擇一. 若選擇第一種, 則必需 registered 成 OUT 參數.

而若不使用上述標準寫法, 也可以直接打指令, 例如 SQL Server 為

• exec <procedure-name> [<arg1>,<arg2>...]

如此則要看使用的 Database 為何, 來下相對應的 SQL.
總的來說, 若 SQL 有用 { } 包起來, 則代表下的命令是 JDBC 的規範. 語法就如上所述..
若沒有用 { } 包起來, 則就要寫符合所用的 Database 之語法規範

第一種的範例片段如下:

CallableStatement stmt = conn.prepareCall("{? = call test_sp ?, ?}");
stmt.registerOutParameter(1, Types.INTEGER);
stmt.setString(2, "008");
stmt.setString(3, "008");
ResultSet rs = stmt.executeQuery();

JDBC 密碼問題

又是很久沒更新 Blog 了~

現在來說說在使用 JDBC Driver 時常常會被要求密碼明文不要直接寫在檔案裡的解決方式...

我們在設定 JDBC Resource 時, 通常都需要將帳號密碼存在設定檔內. 不過有些客戶會因為安全需求強烈要求密碼的部份不應該直接存在設定檔內~

在之前的系統我是採用 proxool connection pool, 此套件會去讀取一個設定檔. 於是我的做法是將讀取設定檔時, 我先parse出裡面的密碼, 然後再轉成真正的密碼給 proxool~

當時的做法是治標不治本~由於很多系統是透過 Container 去管理 data source...
不像之前是寫獨立的系統, 所以根本無法使用上述的方法~
例如 JBoss 上的 ejb 所使用的連線都是 container 去讀取設定檔並管理連線, 若依前述做法, 則要去改 JBoss 的 source code 在讀設定檔時多一層轉換以符合需求. 工程耗大.

想了一下之後, 覺得可以做一個 JDBC Driver Proxy...
處理帳號密碼之後, 將真正的請求連線交給真正的 JDBC Driver...

所以後來的使用方法就像一般的 JDBC 的用法, 而 Driver 與 URL 的地方則會有一些變化..
如下:

• JDBC Driver: idv.allen.jdbc.Driver
• JDBC URL: allen:{driver}:{url}

此 Driver 會宣告接受 allen 開頭的 URL, 而我們的 Driver 需要知道所代理的 Driver 與 URL,
所以後面接著這兩個資訊, 其中, {driver} 就是真正的 JDBC Driver, 而 {url} 就是真正的 URL.
設定帳號密碼時, 就用加密過的帳號密碼..
而此 JDBC Driver 會將所傳入的帳號密碼轉回明碼之後, 再交給真的 Driver 去取得連線...

由上面的 URL 樣式可以知道, 並沒有指定要用什麼方式解碼~
其實此 Driver 預設會採用 hex 來還原密碼, 而帳號則不處理...

後來, 我又擴充此 JDBC Driver, 其 URL 樣式如下

• JDBC URL: allen[.{decrypt}]:{driver}:{url}

其中, [.{decrypt}] 是可有可無的..若沒有的話, 則像上述採用預設的方式~

若我們想用其他的解碼方式..則必須實作 idv.allen.jdbc.decrypt.DecryptIF
其中有兩個方法需要實作:

• String getUserName(String userName): 若不需解密帳號, 則直接回傳即可.
• String getPassword(String password): 會將所設定的 password 傳入, 依自己想要的解碼方式解碼後回傳真正的 password.
  

此實作物件之 Class 為了方便起見, 要放在 idv.allen.jdbc.decrypt 的 package 裡面.
假設我們實作了一個 Class 為 idv.allen.jdbc.decrypt.OctalDecrypt...
若要採用此解碼方式, 則 URL 如下:

• JDBC URL: allen.OctalDecrypt:{driver}:{url}

這樣此Driver會動態去建構此解碼物件並將轉換事宜交給此 Class 去處理...

這樣一來. 往後不論是什麼系統, 要將 JDBC 設定之帳號密碼設為密文, 都可以透過此自製的 JDBC Driver 解決...且不會影響到原本系統的程式與架構...

以上的概念是從 proxool connection pool 中得到的靈感, 由於 proxool connection pool 也實作了自己的 JDBC Driver, 使得要採用 proxool connection pool 的方式也變得比較簡單..

善用設計模式, 可以很漂亮的解決許多問題 :)