import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class SQLTEST {
?
?public static void procedureTest(String tables,int num) {
??try{
???Connection conn = DriverManager.getConnection("jdbc:default:connection");
???Statement stmt = conn.createStatement();
???StringTokenizer stk = new StringTokenizer(tables,",");
???while(stk.hasMoreTokens()) {
????String table = stk.nextToken();
????String sql = "SELECT * FROM " + table;
????for(int i=0;i<num;i++) {
?????stmt.executeQuery(sql);
?????
????}
???}
???stmt.close();
???conn.close();
??}catch(Exception e) {
???e.printStackTrace();
??}
?}
}
將此class編譯后拷貝到DB2安裝目錄的function目錄下。
再用servlet來調用它：
package com.test;
import java.io.IOException;
import java.util.StringTokenizer;
import java.sql.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class SQLServlet extends HttpServlet {
?public void doGet(HttpServletRequest req, HttpServletResponse resp)
??throws ServletException, IOException {
???doPost(req,resp);
?}
?public void doPost(HttpServletRequest req, HttpServletResponse resp)
??throws ServletException, IOException {
???CallableStatement callStmt = null;
???Connection conn = null;
???Statement stmt = null;
???String tables = "shgt_auth,shgt_master,trx_inbox";
???long start = 0;
???long end = 0;
???long lastTime = 0;
???int num = 100;
???try {
????
????//存儲過程調用
????conn = getConnection("CET","eximtrx","Standard00");
????String procName = "SQLTEST";
????String sql = "CALL " + procName + "(?,?)";
????callStmt = conn.prepareCall(sql);
????callStmt.setString(1,tables);
????callStmt.setInt(2,num);
????start = System.currentTimeMillis();
????callStmt.execute();
????end = System.currentTimeMillis();
????lastTime = end - start;
????System.out.println("###########java store procedure Last time : " + lastTime/1000? + "s" + lastTime%1000 + "ms");
????
????
????
????//SQL 調用
????conn = getConnection("CET","eximtrx","Standard00");
????stmt = conn.createStatement();
????StringTokenizer strTok = new StringTokenizer(tables,",");
????start = System.currentTimeMillis();
????while(strTok.hasMoreTokens()) {
?????String table = strTok.nextToken();
?????String sql2 = "select * from " + table;
?????for(int i=0;i<num;i++){
??????stmt.executeQuery(sql2);
?????}
????}
????end = System.currentTimeMillis();
????lastTime = end - start;
????System.out.println("###########SQL Last time : " + lastTime/1000? + "s" + lastTime%1000 + "ms");
???} catch (Exception e) {
????e.printStackTrace();
???}
?}
?private Connection getConnection(String ds, String user, String pwd)
? throws Exception {
?? Connection con = null;
?? InitialContext ctx = new InitialContext();
?? DataSource fDS = (DataSource)ctx.lookup(ds);
?? con = fDS.getConnection(user, pwd);
?? return con;
?}
}
運行此servlet就可成功調用存儲過程。
其中有個注意點，就是大小寫的問題，剛開始時建存儲過程是這樣CREATEPROCEDURE SQLTest(IN tables VARCHAR(200),IN num INTEGER) ，java class也用的是SQLTest，但是在調存儲過程時會抱錯，說找不到SQLTEST.procedureTest方法，原來數據庫在創建存儲過程時，即使你使用SQLTest，但也會被執行為SQLTEST,所以只有都改成大寫。
但有點意外的是，通過這個測試出來的數據，存儲過程既然比sql跑得慢，不知道為什么，難道程序有問題？請大家看一下