JDBC數(shù)據(jù)庫連接池執(zhí)行DDL和SQLJ存儲(chǔ)過程
我們曾經(jīng)介紹Java四大連接池中的JDBC（Java Data Base Connectivity，Java 數(shù)據(jù)庫連接池）是一種用于執(zhí)行 SQL 語句的 Java API，可以為多種關(guān)系數(shù)據(jù)庫提供統(tǒng)一訪問接口，它由一組用 Java 語言編寫的類和接口組成。JDBC 為數(shù)據(jù)庫應(yīng)用開發(fā)人員、數(shù)據(jù)庫前臺(tái)工具開發(fā)人員提供了一個(gè)標(biāo)準(zhǔn)的 API，據(jù)此可以構(gòu)建更高級的工具和接口，使數(shù)據(jù)庫開發(fā)人員能夠用純 Java API 編寫數(shù)據(jù)庫應(yīng)用程序。
DDL（Data Definition Language）是指數(shù)據(jù)定義語句用于定義和管理 SQL 模式、基本表、視圖、索引和存儲(chǔ)過程等數(shù)據(jù)庫中的對象。
SQLJ 由一系列定義了 SQL 與 Java 之間相互作用的子句和程序擴(kuò)充組成。SQLJ 是在 Java 編程語言中靜態(tài)嵌入式 SQL。本文研究的 SQLJ 存儲(chǔ)過程特指 DB2 數(shù)據(jù)庫提供的內(nèi)嵌 SQLJ 存儲(chǔ)過程，例如 SQLJ.DB2_INSTALL_JAR 存儲(chǔ)過程，它用于創(chuàng)建一個(gè)新定義的 JAR 文件到特定的數(shù)據(jù)庫。
本文在系統(tǒng)分析利用數(shù)據(jù)庫腳本文件執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程缺點(diǎn)的基礎(chǔ)上，提出并詳細(xì)介紹了利用 Java JDBC 執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程的方法。
方法提出的背景
在 Java 與數(shù)據(jù)庫交互編程過程中，經(jīng)常遇到這樣的場景：需要執(zhí)行大量的 DDL 語句和 SQLJ 存儲(chǔ)過程，并且這些語句之間有著較強(qiáng)的依賴關(guān)系。下面是一個(gè)這樣的例子，SQL 語句中既有多條 DDL 又有對 DB2 內(nèi)嵌 SQLJ 存儲(chǔ)過程的調(diào)用。
清單 1. DDL 和 SQLJ 存儲(chǔ)過程語句示例
- -- -- 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 語句中，語句之間的依賴性很強(qiáng)。例如，如果語句①不能正確執(zhí)行，直接影響到語句②的執(zhí)行，因?yàn)樗鼈冎按嬖谥藐P(guān)系。針對這種情況的 Java 數(shù)據(jù)庫交互編程，通常采用將這些語句封裝成一個(gè)數(shù)據(jù)庫 SQL 腳本文件去執(zhí)行，主要執(zhí)行過程如下：
◆準(zhǔn)備數(shù)據(jù)庫腳本文件的執(zhí)行環(huán)境，主要是對一些環(huán)境變量的設(shè)置；
◆運(yùn)行數(shù)據(jù)庫腳本文件，把輸出結(jié)果定向到特定的文件；
◆Java 程序系統(tǒng)的分析數(shù)據(jù)庫腳本文件的輸出結(jié)果，得到每一條 SQL 的運(yùn)行狀況；
◆清除數(shù)據(jù)庫腳本文件的執(zhí)行環(huán)境，還原到初始狀態(tài)。
這種運(yùn)行 DDL 和 SQLJ 存儲(chǔ)過程的方法，存在著以下幾個(gè)缺點(diǎn)：
◆依賴數(shù)據(jù)庫環(huán)境。需要在過程的開始階段，對腳本文件的執(zhí)行環(huán)境進(jìn)行初始化，在腳本文件運(yùn)行結(jié)束后，必須對環(huán)境進(jìn)行清除；
◆難以對執(zhí)行過程進(jìn)行控制。例如在清單 1 中的 SQL 語句執(zhí)行過程中，如果語句①執(zhí)行失敗，腳本文件不會(huì)終止運(yùn)行并把錯(cuò)誤信息發(fā)送給 Java 程序，而會(huì)繼續(xù)執(zhí)行下一條 SQL 語句，這時(shí)可以確定語句②必定執(zhí)行失敗，但是腳本文件還是強(qiáng)制執(zhí)行語句②；
◆腳本文件的輸出結(jié)果難以處理。由于 SQL 語句在不同的數(shù)據(jù)庫環(huán)境下，輸出結(jié)果的格式信息有所變化，這就極大的影響了 Java 程序讀取輸出結(jié)果的準(zhǔn)確性，難以精確的定位到出現(xiàn)問題的 SQL 語句；
本文針對執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程數(shù)據(jù)庫腳本文件引出的這些缺點(diǎn)，提出了利用 Java JDBC 執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程的方法。方法的一些簡單示例代碼如下。
清單 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 執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程的基本步驟：
◆建立數(shù)據(jù)庫連接；
◆根據(jù)需要?jiǎng)?chuàng)建合適的 Java JDBC Statement 對象；
◆根據(jù) SQL 語句選擇合適的執(zhí)行方法。
在實(shí)際的 Java 與數(shù)據(jù)庫交互編程的環(huán)境中，總結(jié)分析這種方法具有下列優(yōu)點(diǎn)：
◆容易與 Java 程序進(jìn)行交互?？梢垣@取每一條 SQL 語句的執(zhí)行情況，通知 Java 程序選擇運(yùn)行對應(yīng)的處理邏輯；
◆不依賴數(shù)據(jù)庫環(huán)境。完全脫離了數(shù)據(jù)庫環(huán)境的限制，能對本地或遠(yuǎn)程的數(shù)據(jù)庫進(jìn)行高效的數(shù)據(jù)操作。
◆能精確的獲取每條 SQL 語句的執(zhí)行狀態(tài)。如果某一條 SQL 語句運(yùn)行失敗，Java 程序能及時(shí)的捕獲到對應(yīng)的異常信息。
由于 Java JDBC 是通過 Statement 對象來執(zhí)行 SQL 語句的，所以它是執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程的入口，下面將詳細(xì)介紹 JDBC 包含的幾種 Statement 對象。
執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程的入口：Java JDBC Statement
Java JDBC Statement 對象用于將 SQL 語句發(fā)送到數(shù)據(jù)庫中。實(shí)際上有三種 Statement 對象，它們都作為在給定連接上執(zhí)行 SQL 語句的包容器：Statement、PreparedStatement 和 CallableStatement。它們都專用于發(fā)送特定類型的 SQL 語句。三種 Statement 對象的關(guān)系如圖 1 所示。
圖 1. 三種 Statement 對象關(guān)系
Statement 對象用于執(zhí)行不帶參數(shù)的靜態(tài) SQL 語句，提供了執(zhí)行語句和獲取結(jié)果的基本方法。它的 execute(String sql) 和 executeUpdate(String sql) 方法支持執(zhí)行 DDL 語句。示例代碼如下。
清單 3. Statement 對象的示例代碼
- Statement stat=conn.createStatement();
- stat.executeUpdate(“DROP PROCEDURE DB2TOOL.CALLDB2ADVIS”);
- stat.execute(“DROP FUNCTION DB2TOOL.DEMO_LIC”);
- stat.close();
PreparedStatement 對象用于執(zhí)行帶或不帶 IN 參數(shù)的預(yù)編譯 SQL 語句，它從 Statement 繼承而來，添加了處理輸入?yún)?shù)的方法，有防止 SQL 注入的功能，還有較好的執(zhí)行效率。它的 execute() 和 executeUpdate() 方法支持執(zhí)行 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 對象用于執(zhí)行對數(shù)據(jù)庫已有存儲(chǔ)過程的調(diào)用，它從 PreparedStatement 繼承而來，添加了處理輸出參數(shù)的方法。它的 execute() 和 executeUpdate() 方法支持執(zhí)行 DDL 語句。使用示例代碼如下。
清單 5. CallableStatement 對象的示例代碼
- CallableStatement cstm = connection.prepareCall("CALL SQLJ.REMOVE_JAR(?)");
- cstmt.setString(1, “test”);
- cstmt.execute();
- cstmt.close();
Java JDBC 執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程：實(shí)例演示
上一部分詳細(xì)介紹了 Java JDBC 的三種 Statement 對象，了解了它們之間的關(guān)系和特定的操作對象，為利用 Java JDBC 執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程奠定了理論基礎(chǔ)。下面的兩個(gè)程序?qū)嵗?，將充分利用這三種 Statement 對象，展示這種方法的實(shí)現(xiàn)過程及其靈活性。
實(shí)例 1 演示利用 Java JDBC 執(zhí)行 DDL 的方法。需要執(zhí)行的 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 執(zhí)行 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();
實(shí)例 2 演示利用 Java JDBC 執(zhí)行 SQLJ 存儲(chǔ)過程的方法。需要執(zhí)行的 SQLJ 語句如下：
清單 8. SQLJ 存儲(chǔ)過程語句示例
- 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 存儲(chǔ)過程僅支持在本地?cái)?shù)據(jù)庫創(chuàng)建一個(gè)新定義的 JAR 文件，所以在下面的 JDBC 執(zhí)行代碼中使用 SQLJ.DB2_INSTALL_JAR 代替它，擴(kuò)大它的使用范圍。
清單 9. JDBC 執(zhí)行 SQLJ 存儲(chǔ)過程代碼示例
- 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();
上面的兩個(gè)實(shí)例詳細(xì)的展示了利用 Java JDBC 執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程的方法，在執(zhí)行的過程中可以確定每一條 SQL 語句的執(zhí)行狀態(tài)，例如三種 Statement 對應(yīng)的 execute(String sql) 和 execute() 方法能返回 boolean 類型的值，executeUpdate(String sql) 和 executeUpdate() 方法能返回 int 類型的值，可以根據(jù)這些返回值精確的確定每條 SQL 的執(zhí)行狀態(tài)，另外也可以通過捕獲 SQLException 獲得執(zhí)行情況。這兩個(gè)實(shí)例充分體現(xiàn)了本文提出的方法具有靈活性、易于控制執(zhí)行過程、易于獲得 SQL 執(zhí)行狀態(tài)等優(yōu)點(diǎn)。
總結(jié)
本文主要介紹了利用 Java JDBC 執(zhí)行 DDL 和 SQLJ 存儲(chǔ)過程的方法，描述了方法提出的背景，詳細(xì)學(xué)習(xí)了 Java JDBC 中的三種 Statement 對象，最后通過兩個(gè)實(shí)例展現(xiàn)了方法實(shí)現(xiàn)的過程，進(jìn)一步證明了使用這種方法，可以使 Java 程序和 DDL,SQLJ 的交互操作變得非常靈活，提高 Java 編程的效率。
【編輯推薦】
