//帮你稍微优化了一下: (未经测试)package cnc;import java.sql.*;
import javax.naming.*;public class DBConnection { private static String strDBUrl="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=*****"; //可共享生成多个Statement对象
private Connection conn=null; //在多个方法中共用stmt对象容易产生object has been closed.异常。
private Statement stmt = null; private CallableStatement cs; private ResultSet rs = null; public DBConnection(){
conn = getConnection();
} // 专门用来获得数据库连接
private Connection getConnection() throws Exception {
try{
Context initCtx = new InitialContext();
if(initCtx==null)
throw new Exception("没有匹配的环境");
Context ctx = (Context) initCtx.lookup("java:comp/env");
//获取连接池对象
Object obj = (Object) ctx.lookup("jdbc/mysql");
//类型转换
javax.sql.DataSource ds = (javax.sql.DataSource)obj;
if(ds==null)
throw new Exception("没有匹配数据库");
return ds.getConnection();
}catch(Exception ex){
ex.printStackTrace();
}
return null;
} public ResultSet executeQuery(String sql) throws Exception {
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
} catch (SQLException ex) {
throw ex;
}
return rs;
} //<!--定义数据操作-->
public void executeUpdate(String sql) throws Exception {
try {
stmt = conn.createStatement();
stmt.executeUpdate(sql);
} catch (SQLException ex) {
throw ex;
}finally {
}
}
//存储过程的调用函数
public CallableStatement prepareCall(String str) throws Exception {
try {
cs=conn.prepareCall(str);
} catch (SQLException ex) {
throw ex;
}
return cs;
}
//带有输出结果集的存储过程的调用函数
public CallableStatement prepareCall(String str,int rsType,int rsConcurrency) throws Exception {
try {
cs = conn.prepareCall(str,rsType,rsConcurrency);
}catch(SQLException ex){
throw ex;
}
return cs;
} //关闭与数据库连接有关的资源,
//在完成所有的数据库操作之后调用此方法
public void close(){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(cs!=null){
try {
cs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}//End method: close()}
//验证的BEAN的方法:(name 和password是页面上提取的)
public static UserBean userValidate(String name, String password) throws Exception {
UserBean user = null;
try {
String sql = "{call userLogin(?,?)}";//存储过程的检查是否有该用户
CallableStatement cs = db.prepareCall(sql);
cs.setString(1, name);
cs.setString(2, password);
cs.execute();
ResultSet rs = cs.getResultSet(); //取得存储过程查询值
if(rs.next()) {
user = new UserBean();
user.setUserID(rs.getString("用户编号"));
user.setUsername(rs.getString("用户名称"));//去用户ID
user.setDepartmentID(rs.getString("部门编号"));
user.setDepartmentName(rs.getString("部门名称"));//用户部门
user.setRoleID(rs.getInt("角色编号"));
user.setRoleName(rs.getString("角色名称"));//用户角色权限
}
}catch (Exception e){
e.printStackTrace();
}finally { //关闭数据库连接
db.close();
}
return user;
}
import javax.naming.*;public class DBConnection { private static String strDBUrl="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=*****"; //可共享生成多个Statement对象
private Connection conn=null; //在多个方法中共用stmt对象容易产生object has been closed.异常。
private Statement stmt = null; private CallableStatement cs; private ResultSet rs = null; public DBConnection(){
conn = getConnection();
} // 专门用来获得数据库连接
private Connection getConnection() throws Exception {
try{
Context initCtx = new InitialContext();
if(initCtx==null)
throw new Exception("没有匹配的环境");
Context ctx = (Context) initCtx.lookup("java:comp/env");
//获取连接池对象
Object obj = (Object) ctx.lookup("jdbc/mysql");
//类型转换
javax.sql.DataSource ds = (javax.sql.DataSource)obj;
if(ds==null)
throw new Exception("没有匹配数据库");
return ds.getConnection();
}catch(Exception ex){
ex.printStackTrace();
}
return null;
} public ResultSet executeQuery(String sql) throws Exception {
try {
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
} catch (SQLException ex) {
throw ex;
}
return rs;
} //<!--定义数据操作-->
public void executeUpdate(String sql) throws Exception {
try {
stmt = conn.createStatement();
stmt.executeUpdate(sql);
} catch (SQLException ex) {
throw ex;
}finally {
}
}
//存储过程的调用函数
public CallableStatement prepareCall(String str) throws Exception {
try {
cs=conn.prepareCall(str);
} catch (SQLException ex) {
throw ex;
}
return cs;
}
//带有输出结果集的存储过程的调用函数
public CallableStatement prepareCall(String str,int rsType,int rsConcurrency) throws Exception {
try {
cs = conn.prepareCall(str,rsType,rsConcurrency);
}catch(SQLException ex){
throw ex;
}
return cs;
} //关闭与数据库连接有关的资源,
//在完成所有的数据库操作之后调用此方法
public void close(){
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(cs!=null){
try {
cs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}//End method: close()}
//验证的BEAN的方法:(name 和password是页面上提取的)
public static UserBean userValidate(String name, String password) throws Exception {
UserBean user = null;
try {
String sql = "{call userLogin(?,?)}";//存储过程的检查是否有该用户
CallableStatement cs = db.prepareCall(sql);
cs.setString(1, name);
cs.setString(2, password);
cs.execute();
ResultSet rs = cs.getResultSet(); //取得存储过程查询值
if(rs.next()) {
user = new UserBean();
user.setUserID(rs.getString("用户编号"));
user.setUsername(rs.getString("用户名称"));//去用户ID
user.setDepartmentID(rs.getString("部门编号"));
user.setDepartmentName(rs.getString("部门名称"));//用户部门
user.setRoleID(rs.getInt("角色编号"));
user.setRoleName(rs.getString("角色名称"));//用户角色权限
}
}catch (Exception e){
e.printStackTrace();
}finally { //关闭数据库连接
db.close();
}
return user;
}
先谢谢,但是我按你的程序测试了,好有点问题,不过确实你比我写的程序好多了,我还要好好努力了.
也许我别的代码还写的有些问题:我再附加一下以下代码:
//验证的BEAN的方法:(name 和password是页面上提取的)---现在我加上该类还有其变量:
package cnc;import java.sql.*;
import java.util.*;public class UserBean {
private String username; //用户姓名
private String userID; //用户编号
private String departmentID; //部门编号
private String departmentName; //部门名称
private int roleID; //角色编号
private String roleName;
private String levelName;
private String re;
private static DBConnection db = new DBConnection();
public UserBean() {
} public void setUsername(String username) {
this.username = username;
} public void setUserID(String userID) {
this.userID = userID;
} public void setDepartmentID(String departmentID) {
this.departmentID = departmentID;
} public void setDepartmentName(String departmentName) {
this.departmentName = departmentName;
} public void setRoleID(int roleID) {
this.roleID = roleID;
} public void setRoleName(String roleName) {
this.roleName = roleName;
} public void setLevelName(String levelName) {
this.levelName = levelName;
} public void setRe(String re) {
this.re = re;
} public String getUsername() {
return username;
} public String getUserID() {
return userID;
} public String getDepartmentID() {
return departmentID;
} public String getDepartmentName() {
return departmentName;
} //检验用户是否存在 public int getRoleID() {
return roleID;
} public String getRoleName() {
return roleName;
} public String getLevelName() {
return levelName;
} public String getRe() {
return re;
} public static UserBean userValidate(String name, String password) throws Exception {
UserBean user = null;
try {
String sql = "{call userLogin(?,?)}";
CallableStatement cs = db.prepareCall(sql);
cs.setString(1, name);
cs.setString(2, password);
cs.execute();
ResultSet rs = cs.getResultSet();
if(rs.next()) {
user = new UserBean();
user.setUserID(rs.getString("用户编号"));
user.setUsername(rs.getString("用户名称"));
user.setDepartmentID(rs.getString("部门编号"));
user.setDepartmentName(rs.getString("部门名称"));
user.setRoleID(rs.getInt("角色编号"));
user.setRoleName(rs.getString("角色名称"));
}
}catch (Exception e){
e.printStackTrace();
}finally {
db.close();
}
return user;
}
-------------------------
在连接的时候还还报错:
错误如下, 写主要的错误
1.
java.sql.SQLException: Connection is closed.
at cnc.DBConnection.prepareCall(DBConnection.java:72)
是:
public CallableStatement prepareCall(String str) throws Exception {
try {
cs=conn.prepareCall(str);--72行
} catch (SQLException ex) {
throw ex;
}
return cs;
}
at cnc.UserBean.userValidate(UserBean.java:87)
是:
public static UserBean userValidate(String name, String password) throws Exception {
UserBean user = null;
try {
String sql = "{call userLogin(?,?)}";
CallableStatement cs = db.prepareCall(sql); --87行
cs.setString(1, name);
cs.setString(2, password);
cs.execute();
ResultSet rs = cs.getResultSet();
if(rs.next()) {
2.
[Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
at cnc.DBConnection.close(DBConnection.java:100)
是:
if(cs!=null){
try {
cs.close();--100行
} catch (SQLException e) {
e.printStackTrace();
}
}
at cnc.UserBean.userValidate(UserBean.java:104)
是:
}finally {
db.close();--104行
}
return user;
}
2.
[Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
at cnc.DBConnection.close(DBConnection.java:100)
是:
if(cs!=null){
try {
cs.close();--100行
} catch (SQLException e) {
e.printStackTrace();
}
}
at cnc.UserBean.userValidate(UserBean.java:104)
是:
}finally {
db.close();--104行
}
return user;
}
2.
[Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
at cnc.DBConnection.close(DBConnection.java:100)
是:
if(cs!=null){
try {
cs.close();--100行
} catch (SQLException e) {
e.printStackTrace();
}
}
at cnc.UserBean.userValidate(UserBean.java:104)
是:
}finally {
db.close();--104行
}
return user;
}
2.
[Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
at cnc.DBConnection.close(DBConnection.java:100)
是:
if(cs!=null){
try {
cs.close();--100行
} catch (SQLException e) {
e.printStackTrace();
}
}
at cnc.UserBean.userValidate(UserBean.java:104)
是:
}finally {
db.close();--104行
}
return user;
}
2.
[Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
at cnc.DBConnection.close(DBConnection.java:100)
是:
if(cs!=null){
try {
cs.close();--100行
} catch (SQLException e) {
e.printStackTrace();
}
}
at cnc.UserBean.userValidate(UserBean.java:104)
是:
}finally {
db.close();--104行
}
return user;
}
2.
[Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
at cnc.DBConnection.close(DBConnection.java:100)
是:
if(cs!=null){
try {
cs.close();--100行
} catch (SQLException e) {
e.printStackTrace();
}
}
at cnc.UserBean.userValidate(UserBean.java:104)
是:
}finally {
db.close();--104行
}
return user;
}
2.
[Microsoft][SQLServer 2000 Driver for JDBC]Object has been closed.
at cnc.DBConnection.close(DBConnection.java:100)
是:
if(cs!=null){
try {
cs.close();--100行
} catch (SQLException e) {
e.printStackTrace();
}
}
at cnc.UserBean.userValidate(UserBean.java:104)
是:
}finally {
db.close();--104行
}
return user;
}
3.
Connection is closed.
at cnc.DBConnection.close(DBConnection.java:114)
是:
if(conn!=null){
try {
conn.close();--114行
} catch (SQLException e) {
e.printStackTrace();
}
}
at cnc.UserBean.userValidate(UserBean.java:104)
是:
}finally {
db.close();--104行
}
return user;
}
---------
为什么还没有执行存储过程,connection就关闭了呢?
程序哪里有问题啊?DBConnection.java是按你说的方法执行的,我仅仅在构造函数里加了try{}.
import cnc.DBConnection;//生成实例
DBConnection dbc = new DBConnection();dbc.prepareCall("sql sentence");
....//最后关闭,在此之前不调用
dbc.close();
//在此之后的代码中没有针对实例dbc的任何操作
private static DBConnection db = new DBConnection();是不是这句话的问题啊?
怎么现在感觉写开程序,理论就忘完了啊!
private static DBConnection db = new DBConnection();
我把以上代码去掉,在每一个函数里写上了
DBConnection db = new DBConnection();是sataic 本身的问题吗?
但是我有点想不通!
谁能帮我解释一下啊