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跑得慢，不知道为什么，难道程序有问题？请大家看一下