JSP调用SQL Server 2000的存储过程

创建存储过程:

use mydata;

create proc proc_members_1
as
select * from members where id between 1000 and 1005
return

package test;

import java.sql.*;

public class SqlBean {
private Connection conn;
private Statement stmt;
private ResultSet rs;
private CallableStatement cstmt;
private synchronized Connection getConnection() throws SQLException{
try {
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance();
String url = "jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=mydata";
Connection conn = DriverManager.getConnection(url, "sa", "");
return conn;
} catch (ClassNotFoundException e) {
e.printStackTrace();
throw new SQLException(e.getMessage());
} catch (InstantiationException e) {
e.printStackTrace();
throw new SQLException(e.getMessage());
} catch (IllegalAccessException e) {
e.printStackTrace();
throw new SQLException(e.getMessage());
}
}
public ResultSet doCallableSelect(String sql) throws SQLException {
cstmt = conn.prepareCall(sql);
return cstmt.executeQuery();
}
public SqlBean() throws SQLException{
conn = getConnection();
stmt = conn.createStatement();
}
public ResultSet doSelect(String sql) throws SQLException{
ResultSet rs = stmt.executeQuery(sql);
return rs;
}
public boolean doUpdate(String sql) throws SQLException {
int i = stmt.executeUpdate(sql);
return 0 != i;
}
public void close() throws SQLException {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (conn != null) conn.close();
}
}

rs = sqlBean.doCallableSelect("execute proc_members_1");
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String sex = rs.getString("sex");
long age = rs.getLong("age");
String createTime = rs.getString("createTime");
out.println("<h2>" + id + "," + name + "," + sex + "," + age + "," + createTime + "</h2>");
}
sqlBean.close();

结果：

1000,member_994 ,ML,994,2005-03-14 17:44:57.217

1001,member_995 ,FM,995,2005-03-14 17:44:57.217

1002,member_996 ,ML,996,2005-03-14 17:44:57.217

1003,member_997 ,FM,997,2005-03-14 17:44:57.217

1004,member_998 ,ML,998,2005-03-14 17:44:57.217

1005,member_999 ,FM,999,2005-03-14 17:44:57.217