JDBC數據庫連接池執行DDL和SQLJ存儲過程
我們曾經介紹Java四大連接池中的JDBC（Java Data Base Connectivity，Java 數據庫連接池）是一種用于執行 SQL 語句的 Java API，可以為多種關系數據庫提供統一訪問接口，它由一組用 Java 語言編寫的類和接口組成。JDBC 為數據庫應用開發人員、數據庫前臺工具開發人員提供了一個標準的 API，據此可以構建更高級的工具和接口，使數據庫開發人員能夠用純 Java API 編寫數據庫應用程序。
DDL（Data Definition Language）是指數據定義語句用于定義和管理 SQL 模式、基本表、視圖、索引和存儲過程等數據庫中的對象。
SQLJ 由一系列定義了 SQL 與 Java 之間相互作用的子句和程序擴充組成。SQLJ 是在 Java 編程語言中靜態嵌入式 SQL。本文研究的 SQLJ 存儲過程特指 DB2 數據庫提供的內嵌 SQLJ 存儲過程，例如 SQLJ.DB2_INSTALL_JAR 存儲過程，它用于創建一個新定義的 JAR 文件到特定的數據庫。
本文在系統分析利用數據庫腳本文件執行 DDL 和 SQLJ 存儲過程缺點的基礎上，提出并詳細介紹了利用 Java JDBC 執行 DDL 和 SQLJ 存儲過程的方法。
方法提出的背景
在 Java 與數據庫交互編程過程中，經常遇到這樣的場景：需要執行大量的 DDL 語句和 SQLJ 存儲過程，并且這些語句之間有著較強的依賴關系。下面是一個這樣的例子，SQL 語句中既有多條 DDL 又有對 DB2 內嵌 SQLJ 存儲過程的調用。
清單 1. DDL 和 SQLJ 存儲過程語句示例
- -- -- connect to the &database
- connect to &database user &user using &password;
- -- -- install Stored Procedure
- DROP PROCEDURE DB2TOOL.CALLDB2ADVIS;
- CALL SQLJ.REMOVE_JAR ('DB2TOOL.CALLDB2ADVIS');
- CALL SQLJ.REFRESH_CLASSES();
- CALL SQLJ.INSTALL_JAR('file:/home/luwsp.jar', 'DB2TOOL.CALLDB2ADVIS');------------- ①
- CALL SQLJ.REFRESH_CLASSES();
- -- -- create Stored Procedure
- CREATE PROCEDURE DB2TOOL.CALLDB2ADVIS ( INOUT major_version INTEGER,
- INOUT minor_version INTEGER,
- IN requested_locale VARCHAR(33),
- IN xml_input BLOB(32M),
- IN xml_filter BLOB(4K),
- OUT xml_output BLOB(4K),
- OUT xml_message BLOB(64K) )
- DYNAMIC RESULT SETS 3
- NOT DETERMINISTIC
- LANGUAGE Java
- EXTERNAL NAME 'DB2TOOL.CALLDB2ADVIS:com.ibm.datatools.ia.luw.CALLDB2ADVIS.cALLDB2ADVIS'
- FENCED
- THREADSAFE
- PARAMETER STYLE JAVA; --------------------------------------------------------- ②
- -- -- grant the execution privilege to public
- GRANT EXECUTE ON PROCEDURE DB2TOOL.CALLDB2ADVIS TO PUBLIC WITH GRANT OPTION;
- connect reset;
- terminate;
在上面的 SQL 語句中，語句之間的依賴性很強。例如，如果語句①不能正確執行，直接影響到語句②的執行，因為它們之前存在著引用關系。針對這種情況的 Java 數據庫交互編程，通常采用將這些語句封裝成一個數據庫 SQL 腳本文件去執行，主要執行過程如下：
◆準備數據庫腳本文件的執行環境，主要是對一些環境變量的設置；
◆運行數據庫腳本文件，把輸出結果定向到特定的文件；
◆Java 程序系統的分析數據庫腳本文件的輸出結果，得到每一條 SQL 的運行狀況；
◆清除數據庫腳本文件的執行環境，還原到初始狀態。
這種運行 DDL 和 SQLJ 存儲過程的方法，存在著以下幾個缺點：
◆依賴數據庫環境。需要在過程的開始階段，對腳本文件的執行環境進行初始化，在腳本文件運行結束后，必須對環境進行清除；
◆難以對執行過程進行控制。例如在清單 1 中的 SQL 語句執行過程中，如果語句①執行失敗，腳本文件不會終止運行并把錯誤信息發送給 Java 程序，而會繼續執行下一條 SQL 語句，這時可以確定語句②必定執行失敗，但是腳本文件還是強制執行語句②；
◆腳本文件的輸出結果難以處理。由于 SQL 語句在不同的數據庫環境下，輸出結果的格式信息有所變化，這就極大的影響了 Java 程序讀取輸出結果的準確性，難以精確的定位到出現問題的 SQL 語句；
本文針對執行 DDL 和 SQLJ 存儲過程數據庫腳本文件引出的這些缺點，提出了利用 Java JDBC 執行 DDL 和 SQLJ 存儲過程的方法。方法的一些簡單示例代碼如下。
清單 2. 簡單的示例代碼
- Driver dbDriver=(Driver)Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
- String url="jdbc:db2://"+host+":"+port+"/"+database+"";
- Properties p = new Properties();
- p.put("user", username);
- p.put("password",password);
- conn= dbDriver.connect(url,p);
- try{
- stat=conn.createStatement();
- stat.executeUpdate(“DROP PROCEDURE DB2TOOL.CALLDB2ADVIS”);
- stmt = conn.prepareCall(“CALL SQLJ.REFRESH_CLASSES()”);
- stmt.execute();
- }
- catch(SQLException e){
- System.out.println(e.getMessage());
- }
- stat.close();
- stmt.close();
- conn.close();
通過上面的示例代碼，可以了解到利用 Java JDBC 執行 DDL 和 SQLJ 存儲過程的基本步驟：
◆建立數據庫連接；
◆根據需要創建合適的 Java JDBC Statement 對象；
◆根據 SQL 語句選擇合適的執行方法。
在實際的 Java 與數據庫交互編程的環境中，總結分析這種方法具有下列優點：
◆容易與 Java 程序進行交互。可以獲取每一條 SQL 語句的執行情況，通知 Java 程序選擇運行對應的處理邏輯；
◆不依賴數據庫環境。完全脫離了數據庫環境的限制，能對本地或遠程的數據庫進行高效的數據操作。
◆能精確的獲取每條 SQL 語句的執行狀態。如果某一條 SQL 語句運行失敗，Java 程序能及時的捕獲到對應的異常信息。
由于 Java JDBC 是通過 Statement 對象來執行 SQL 語句的，所以它是執行 DDL 和 SQLJ 存儲過程的入口，下面將詳細介紹 JDBC 包含的幾種 Statement 對象。
執行 DDL 和 SQLJ 存儲過程的入口：Java JDBC Statement
Java JDBC Statement 對象用于將 SQL 語句發送到數據庫中。實際上有三種 Statement 對象，它們都作為在給定連接上執行 SQL 語句的包容器：Statement、PreparedStatement 和 CallableStatement。它們都專用于發送特定類型的 SQL 語句。三種 Statement 對象的關系如圖 1 所示。
圖 1. 三種 Statement 對象關系
Statement 對象用于執行不帶參數的靜態 SQL 語句，提供了執行語句和獲取結果的基本方法。它的 execute(String sql) 和 executeUpdate(String sql) 方法支持執行 DDL 語句。示例代碼如下。
清單 3. Statement 對象的示例代碼
- Statement stat=conn.createStatement();
- stat.executeUpdate(“DROP PROCEDURE DB2TOOL.CALLDB2ADVIS”);
- stat.execute(“DROP FUNCTION DB2TOOL.DEMO_LIC”);
- stat.close();
PreparedStatement 對象用于執行帶或不帶 IN 參數的預編譯 SQL 語句，它從 Statement 繼承而來，添加了處理輸入參數的方法，有防止 SQL 注入的功能，還有較好的執行效率。它的 execute() 和 executeUpdate() 方法支持執行 DDL 語句。使用示例代碼如下。
清單 4. PreparedStatement 對象的示例代碼
- PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
- SET SALARY = ? WHERE ID = ?");
- pstmt.setBigDecimal(1, 153833.00);
- pstmt.setInt(2, 110592);
- pstmt.executeUpdate();
- pstmt.close();
CallableStatement 對象用于執行對數據庫已有存儲過程的調用，它從 PreparedStatement 繼承而來，添加了處理輸出參數的方法。它的 execute() 和 executeUpdate() 方法支持執行 DDL 語句。使用示例代碼如下。
清單 5. CallableStatement 對象的示例代碼
- CallableStatement cstm = connection.prepareCall("CALL SQLJ.REMOVE_JAR(?)");
- cstmt.setString(1, “test”);
- cstmt.execute();
- cstmt.close();
Java JDBC 執行 DDL 和 SQLJ 存儲過程：實例演示
上一部分詳細介紹了 Java JDBC 的三種 Statement 對象，了解了它們之間的關系和特定的操作對象，為利用 Java JDBC 執行 DDL 和 SQLJ 存儲過程奠定了理論基礎。下面的兩個程序實例，將充分利用這三種 Statement 對象，展示這種方法的實現過程及其靈活性。
實例 1 演示利用 Java JDBC 執行 DDL 的方法。需要執行的 DDL 語句如下：
清單 6. DDL 語句示例
- DROP FUNCTION DB2TOOL.DEMO_LIC;
- CREATE FUNCTION DB2TOOL.DEMO_LIC() RETURNS VARCHAR(8) LANGUAGE SQL CONTAINS SQL
- NO EXTERNAL ACTION DETERMINISTIC RETURN VARCHAR('DEMO_V10');
- GRANT EXECUTE ON FUNCTION DB2TOOL.DEMO_LIC TO PUBLIC WITH GRANT OPTION;
清單 7. JDBC 執行 DDL 代碼示例
- Driver dbDriver=(Driver)Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
- String url="jdbc:db2://"+host+":"+port+"/"+database+"";
- Properties p = new Properties();
- p.put("user", username);
- p.put("password",password);
- conn= dbDriver.connect(url,p);
- try{
- Statement stat=conn.createStatement();
- stat.executeUpdate(“DROP FUNCTION DB2TOOL.DEMO_LIC”);
- PreparedStatement pstmt = conn.prepareStatement(
- "CREATE FUNCTION DB2TOOL.DEMO_LIC() RETURNS VARCHAR(8) LANGUAGE SQL
- CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN VARCHAR(?)");
- pstmt.setString(1,”DEMO_V10”);
- pstmt.execute();
- stat.execute(“GRANT EXECUTE ON FUNCTION DB2TOOL.DEMO_LIC TO
- PUBLIC WITH GRANT OPTION”);
- } catch(SQLException e){
- System.out.println(e.getMessage());
- }
- stat.close();
- pstmt.close();
- conn.close();
實例 2 演示利用 Java JDBC 執行 SQLJ 存儲過程的方法。需要執行的 SQLJ 語句如下：
清單 8. SQLJ 存儲過程語句示例
- CALL SQLJ.REMOVE_JAR ('DB2TOOL.CALLDB2ADVIS');
- CALL SQLJ.REFRESH_CLASSES();
- CALL SQLJ.INSTALL_JAR('file:/home/luwsp.jar', 'DB2TOOL.CALLDB2ADVIS');
- CALL SQLJ.REFRESH_CLASSES();
由于 SQLJ.INSTALL_JAR 存儲過程僅支持在本地數據庫創建一個新定義的 JAR 文件，所以在下面的 JDBC 執行代碼中使用 SQLJ.DB2_INSTALL_JAR 代替它，擴大它的使用范圍。
清單 9. JDBC 執行 SQLJ 存儲過程代碼示例
- Driver dbDriver=(Driver)Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
- String url="jdbc:db2://"+host+":"+port+"/"+database+"";
- Properties p = new Properties();
- p.put("user", username);
- p.put("password",password);
- conn= dbDriver.connect(url,p);
- CallableStatement cstmt = null;
- try{
- cstmt = conn.prepareCall(“CALL SQLJ.REMOVE_JAR (?)”);
- cstmt.setString(1,”DB2TOOL.CALLDB2ADVIS”);
- cstmt.execute();
- cstmt = conn.prepareCall(“CALL SQLJ.REFRESH_CLASSES()”);
- cstmt.execute();
- File aFile = new File(“/home/luwsp.jar”);
- FileInputStream inputStream = new FileInputStream(aFile);
- cstmt = conn.prepareCall(“Call SQLJ.DB2_INSTALL_JAR(?,?,?)”);
- cstmt.setBinaryStream(1, inputStream, (int)aFile.length());
- cstmt.setString(2,”DB2TOOL.CALLDB2ADVIS”);
- cstmt.setInt(3, 0);
- cstmt.execute();
- cstmt = conn.prepareCall(“CALL SQLJ.REFRESH_CLASSES()”);
- cstmt.execute();
- } catch(SQLException e){
- System.out.println(e.getMessage());
- }
- cstmt.close();
- conn.close();
上面的兩個實例詳細的展示了利用 Java JDBC 執行 DDL 和 SQLJ 存儲過程的方法，在執行的過程中可以確定每一條 SQL 語句的執行狀態，例如三種 Statement 對應的 execute(String sql) 和 execute() 方法能返回 boolean 類型的值，executeUpdate(String sql) 和 executeUpdate() 方法能返回 int 類型的值，可以根據這些返回值精確的確定每條 SQL 的執行狀態，另外也可以通過捕獲 SQLException 獲得執行情況。這兩個實例充分體現了本文提出的方法具有靈活性、易于控制執行過程、易于獲得 SQL 執行狀態等優點。
總結
本文主要介紹了利用 Java JDBC 執行 DDL 和 SQLJ 存儲過程的方法，描述了方法提出的背景，詳細學習了 Java JDBC 中的三種 Statement 對象，最后通過兩個實例展現了方法實現的過程，進一步證明了使用這種方法，可以使 Java 程序和 DDL,SQLJ 的交互操作變得非常靈活，提高 Java 編程的效率。
【編輯推薦】
