再論 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。應該也是可以達成一樣的效果。