用DB2的Java存储过程结合Tomcat连接池开发Web项目
来源:互联网 发布:全国存量房数据 编辑:程序博客网 时间:2024/06/05 20:28
第一步:
在开发目录D:/DB2ExpV82ProcSampleWeb/Proc里创建几个文件:
1、
DuYiProcCreate.db2
内容:
CREATE PROCEDURE RtnGoodsInfo(IN GoodName char(16))
DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'DuYiProc.RtnGoodsInfo'@
CREATE PROCEDURE RtnAllUserInfo()
DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'DuYiJavaProc.RtnAllUserInfo'@
CREATE PROCEDURE ONE_RESULT_SET (IN salValue DOUBLE)
DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'DuYiJavaProc.resultSetToClient'@
CREATE PROCEDURE TWO_RESULT_SETS (IN salary DOUBLE)
DYNAMIC RESULT SETS 2
NOT DETERMINISTIC
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
READS SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'DuYiJavaProc.twoResultSets'@
2、
用来删除存储过程的DuYiProcDrop.db2
内容:
DROP PROCEDURE RtnGoodsInfo (CHAR(16))@
DROP PROCEDURE RtnAllUserInfo()@
DROP PROCEDURE ONE_RESULT_SET (DOUBLE)@
DROP PROCEDURE TWO_RESULT_SETS (DOUBLE)@
3、被调用的java类:DuYiJavaProc.java
内容:
import java.sql.*; // JDBC classes
///////
// Java stored procedure is in this class
///////
//返回货物信息
//返回全部已注册的用户信息
public class DuYiJavaProc
{
public static void RtnGoodsInfo(char[] goodName,ResultSet[] outRs)
throws SQLException
{
int errorCode = 0; // SQLCODE = 0 unless SQLException occurs
String errorLabel = null;
try
{
// get caller's connection to the database
errorLabel = "GET CONNECTION";
Connection con = DriverManager.getConnection("jdbc:default:connection");
errorLabel = "SELECT STATEMENT";
// set the SQL statement that will return the desired result set
String query = "SELECT GoodsName, StockNums,Description,Price" +
" FROM GoodsInfo where GoodsName like ? ";
// prepare the SQL statement
PreparedStatement stmt = con.prepareStatement(query);
stmt.setString(1, "%"+goodName+"%");
// get the result set that will be returned to the client
outRs[0] = stmt.executeQuery();
// to return a result set to the client, do not close ResultSet
con.close();
}
catch (SQLException sqle)
{
errorCode = sqle.getErrorCode();
throw new SQLException( errorCode + " : " + errorLabel + " FAILED" );
}
}//RtnGoodsInfo
public static void RtnAllUserInfo(ResultSet[] outRs)
throws SQLException
{
int errorCode = 0; // SQLCODE = 0 unless SQLException occurs
String errorLabel = null;
try
{
// get caller's connection to the database
errorLabel = "GET CONNECTION";
Connection con = DriverManager.getConnection("jdbc:default:connection");
errorLabel = "SELECT STATEMENT";
// set the SQL statement that will return the desired result set
String query = "SELECT name, password " +
" FROM users ";
// prepare the SQL statement
PreparedStatement stmt = con.prepareStatement(query);
// get the result set that will be returned to the client
outRs[0] = stmt.executeQuery();
// to return a result set to the client, do not close ResultSet
con.close();
}
catch (SQLException sqle)
{
errorCode = sqle.getErrorCode();
throw new SQLException( errorCode + " : " + errorLabel + " FAILED" );
}
}//RtnAllUserInfo
//*************************************************************************
// Stored Procedure: resultSetToClient
//
// Purpose: Returns a result set to the caller that identifies employees
// with salaries greater than the value of input parameter
// inSalaryThreshold.
//
// Parameters:
//
// IN: inSalaryThreshold - salary
// OUT: outRs - ResultSet
//
//*************************************************************************
public static void resultSetToClient(double inSalaryThreshold,
ResultSet[] outRs)
throws SQLException
{
int errorCode = 0; // SQLCODE = 0 unless SQLException occurs
String errorLabel = null;
try
{
// get caller's connection to the database
errorLabel = "GET CONNECTION";
Connection con = DriverManager.getConnection("jdbc:default:connection");
errorLabel = "SELECT STATEMENT";
// set the SQL statement that will return the desired result set
String query = "SELECT name, job, CAST(salary AS DOUBLE) " +
" FROM staff " +
" WHERE salary > ? " +
" ORDER BY salary";
// prepare the SQL statement
PreparedStatement stmt = con.prepareStatement(query);
// set the value of the parameter marker (?)
stmt.setDouble(1, inSalaryThreshold);
// get the result set that will be returned to the client
outRs[0] = stmt.executeQuery();
// to return a result set to the client, do not close ResultSet
con.close();
}
catch (SQLException sqle)
{
errorCode = sqle.getErrorCode();
throw new SQLException( errorCode + " : " + errorLabel + " FAILED" );
}
} // resultSetToClient
//*************************************************************************
// Stored Procedure: twoResultSets
//
// Purpose: Return two result sets to the caller. One result set
// consists of employee data of all employees with
// salaries greater than inSalaryThreshold. The other
// result set contains employee data for employees with salaries
// less than inSalaryThreshold.
//
// Parameters:
//
// IN: inSalaryThreshold - salary
// OUT: outRs1 - first ResultSet
// outRs2 - second ResultSet
//
//*************************************************************************
public static void twoResultSets(double inSalaryThreshold,
ResultSet[] outRs1,
ResultSet[] outRs2)
throws SQLException
{
int errorCode = 0; // SQLCODE = 0 unless SQLException occurs
String errorLabel = null;
try
{
// get caller's connection to the database
errorLabel = "GET CONNECTION";
Connection con = DriverManager.getConnection("jdbc:default:connection");
errorLabel = "SELECT STATEMENT 1";
// set the SQL statement that will return the desired result set
String query1 =
"SELECT name, job, CAST(salary AS DOUBLE) FROM staff " +
" WHERE salary > ? " +
" ORDER BY salary";
// prepare the SQL statement
PreparedStatement stmt1 = con.prepareStatement(query1);
// set the value of the parameter marker (?)
stmt1.setDouble(1, inSalaryThreshold);
// get the result set that will be returned to the client
outRs1[0] = stmt1.executeQuery();
errorLabel = "SELECT STATEMENT 2";
// set the SQL statement that will return the desired result set
String query2 =
"SELECT name, job, CAST(salary AS DOUBLE) FROM staff " +
" WHERE salary < ? " +
" ORDER BY salary DESC";
// prepare the SQL statement
PreparedStatement stmt2 = con.prepareStatement(query2);
// set the value of the parameter marker (?)
stmt2.setDouble(1, inSalaryThreshold);
// get the result set that will be returned to the client
outRs2[0] = stmt2.executeQuery();
// to return the result sets to the client, do not close the ResultSets
con.close();
}
catch (SQLException sqle)
{
errorCode = sqle.getErrorCode();
throw new SQLException( errorCode + " : " + errorLabel + " FAILED" );
}
} // twoResultSets
} //DuYiJavaProc
第二步:
一、将db2java.zip从DB2安装目录下的SQLLIB/java复制到Tomcat5.5安装目录下的common/lib中。
二、将config.xml复制到Tomcat安装目录下的conf中。
登录sample数据库的用户名和密码要重新指定。
sample样本数据库可通过运行db2fs命令创建。
三、用命令db2fs创建样本数据库:sample 。
1、创建相应的表:
create table users(name varchar(30) not null primary key , password varchar(16) not null,description varchar(60) ,email varchar(60),phone varchar(16))
insert into users values('杜毅 ','aaa','杜毅是四川托普学院计科系教师于08年9月29日开发该Web项目','duyi@163.com','66666666666')
CREATE TABLE "ADMIN "."GOODSINFO" (
"GOODSID" CHAR(20) NOT NULL ,
"GOODSNAME" CHAR(50) NOT NULL ,
"STOCKNUMS" BIGINT NOT NULL ,
"DESCRIPTION" CHAR(100) ,
"PRICE" DOUBLE NOT NULL )
IN "USERSPACE1" ;
插入一些记录。
2、用“数据源(ODBC)”建立一个系统数据源DB2sampleJDBC-ODBC。
3、编辑Tomcat5.5的conf目录下的context.xml,在“</Context>”前添加:
<Resource
auth="Container"
name="jdbc/DB2sampleJDBC-ODBC"
type="javax.sql.DataSource"
driverClassName="sun.jdbc.odbc.JdbcOdbcDriver"
url="jdbc:odbc:DB2sampleJDBC-ODBC"
maxIdle="60"
maxActive="1000"
maxWait="5000"
username="admin"
password="XXXXXX"
/>
四、在NB61或“计算机管理”内重启Tomcat。
五、创建存储过程:(假定DB2的安装目录是:C:/IBMDB2UDBExpressV82)
在DB2的命令窗口中依次执行以下各行命令:
cd C:/IBMDB2UDBExpressV82/java/jdk/bin
copy/y D:/DB2ExpV82ProcSampleWeb/Proc/*.*
db2 connect to sample
db2 -td@ -vf duyiprocdrop.db2
db2 -td@ -vf duyiproccreate.db2
javac duyijavaproc.java
dir *.class
...
2008-09-29 14:39 2,623 DuYiJavaProc.class
...
copy/y duyijavaProc.class C:/IBMDB2UDBExpressV82/Function
(如果没有最后一步,会出现42724错误;即,找不到存储过程对应的java类。)
六、创建和运行使用存储过程的(Web)项目即可。
七、servlet代码举例:
/**
* 返回全部已注册的用户信息
*/
package CallProc;
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;
public class CallRtnAllUserInfoProc extends HttpServlet {
private InitialContext initCtx;
private DataSource ds;
private Connection con;
// <editor-fold defaultstate="collapsed" desc="各个调用存储过程的方法。单击左侧的 + 号以编辑代码。">
static double outMedian = 0;
public static void callRtnAllUserInfo(Connection con,PrintWriter out)
{
try
{
// prepare the CALL statement for ONE_RESULT_SET
//调用SpServer.resultSetToClient
String procName = "RtnAllUserInfo";
String sql = "CALL " + procName+"()";
CallableStatement callStmt = con.prepareCall(sql);
// call the stored procedure
out.println("<p>存储过程<B>" + procName+"</B></font>的返回值:");
callStmt.execute();
ResultSet rs = callStmt.getResultSet();
fetchAll(rs,out);
// close ResultSet and callStmt
rs.close();
callStmt.close();
}
catch (SQLException e)
{
out.println(e.getMessage());
}
} // callOneResultSet
// ======================================================
// Method: fetchAll -- returns all rows from a result set
// ======================================================
public static void fetchAll(ResultSet rs,PrintWriter out)
{
try
{
out.println(
"<br>=============================================================");
// retrieve the number, types and properties of the
// resultset's columns
ResultSetMetaData stmtInfo = rs.getMetaData();
int numOfColumns = stmtInfo.getColumnCount();
out.print("<br>共有"+numOfColumns+"列。<br>");
out.print("<table><tr><td>用户名</td><td>密码</td><td>备注</td><td>EMAIL</td><td>电话号码</td>");
while (rs.next())
{
for (int i = 1; i <= numOfColumns; i++)
{
out.print("<tr>");
out.print("<td>");
out.print(rs.getString(i));
out.print("</td>");
if (i == numOfColumns)
out.print("</tr>");
}
}
out.print("</table>");
}
catch (Exception e)
{
out.println("fetchALL方法发生异常:"+e.getMessage());
out.println("</body></html>");
}
} // fetchAll
// </editor-fold>
protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html;charset=GB2312");
PrintWriter out = response.getWriter();
try {
//使用连接池技术
initCtx=new InitialContext();
ds = (DataSource)initCtx.lookup("java:comp/env/jdbc/DB2sampleJDBC-ODBC");
con = ds.getConnection();
out.println("<html>");
out.println("<head>");
out.println("<title>调用存储过程</title>");
out.println("</head>");
out.println("<body>");
out.println("<center><font color=blue><B>---全部已注册的用户信息---</B></font></center>");
callRtnAllUserInfo(con,out);
//回滚全部对sample数据库的可能更改
con.rollback();
con.close();
out.println("</body>");
}
catch (Exception e)
{
out.println("异常:"+e.toString());
try
{
con.rollback();
con.close();
}
catch (Exception x)
{
}
}
finally {
out.close();
}
}
// <editor-fold defaultstate="collapsed" desc="HttpServlet 方法。单击左侧的 + 号以编辑代码。">
/**
* Handles the HTTP <code>GET</code> method.
* @param request servlet request
* @param response servlet response
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Handles the HTTP <code>POST</code> method.
* @param request servlet request
* @param response servlet response
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}
/**
* Returns a short description of the servlet.
*/
public String getServletInfo() {
return "Short description";
}
// </editor-fold>
}
- 用DB2的Java存储过程结合Tomcat连接池开发Web项目
- tomcat部署java web项目的过程
- DB2 存储过程开发
- java调用db2存储过程的例子
- DB2的存储过程
- java EE IDE web项目使用 tomcat 调试的过程
- 部署在tomcat上的java web项目连接SQLservices
- DB2存储过程开发探秘
- 在linux下用tomcat部署java web项目的过程与注意事项
- 在linux下用tomcat部署java web项目的过程与注意事项
- 在linux下用tomcat部署java web项目的过程与注意事项(原创)
- 在linux下用tomcat部署java web项目的过程与注意事项(原创)
- 在linux下用tomcat部署java web项目的过程与注意事项
- 在linux下用tomcat部署java web项目的过程与注意事项
- 在linux下用tomcat部署java web项目的过程与注意事项
- 在linux下用tomcat部署java web项目的过程与注意事项
- 在linux下用tomcat部署java web项目的过程与注意事项
- 在linux下用tomcat部署java web项目的过程与注意事项
- 爆笑中沉思:股市欢迎你(老张经典版)
- asp.net页面间数据传递
- 原创Oracle数据库导入导出工具
- 500元征集网站域名和网站名称
- 系统的横向结构和Websharp AOP
- 用DB2的Java存储过程结合Tomcat连接池开发Web项目
- InStr([start,]string1,string2[,compare])
- 随身店---店随身
- 如何用VB实现半透明控件
- 配置Tomcat5.5.4数据库连接池
- 将自己的代码添加版权信息
- C#日期格式化
- SQL statement 中由单引号时
- Excel常用枚举常量列表对应的数字