Java Store_java store procedure 的使用（DB2）

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

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

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就可成功调用存储过程。

其中有个注意点，就是大小写的问题，刚开始时建存储过程是这样CREATEPROCEDURESQLTest(INtablesVARCHAR(200),INnumINTEGER) ，java class也用的是SQLTest，但是在调存储过程时会抱错，说找不到SQLTEST.procedureTest方法，原来数据库在创建存储过程时，即使你使用SQLTest，但也会被执行为SQLTEST,所以只有都改成大写。

但有点意外的是，通过这个测试出来的数据，存储过程既然比sql跑得慢，不知道为什么，难道程序有问题？请大家看一下