最近做项目,要用到两个库里的两个表。
自己在用JDBC对另一个库中的表进行操作的时候,第一次修改密码很成功(两个表中的数据都进行了修改),然而当我再次修改密码的时候,却出现:
NonTransientConnectionException: No operations allowed after connection closed
这样的错误,好几天都不知道怎么解决。代码如下:
/**
* modify cloudStack user password(JDBC)
* @author xxxx
* time:2012-03-13
* updateTime:2012-03-19
*/
@Repository
public class CloudStackUserDAO implements ICloudStackUserDAO{
private static final Log logger = LogFactory.getLog(CloudStackUserDAO.class);
//get jdbc connection
//DBUtil 获得 connection
private static Connection conn = DBUtil.conn();
private static Statement stmt = null;
public CloudStackUserDAO() {
// do nothing
}
/*
* (non-Javadoc)
* @see com.cloud.dao.cloudstack.ICloudStackUserDAO#modifyCloudStackUserPassword(long, java.lang.String, java.lang.String)
*/
@Override
public void modifyCloudStackUserPassword(long cloudUserId, String userName, String password){
logger.debug("modify cloudStack user password");
/*try {
// 取消掉自动提交
//conn.setAutoCommit(false);
} catch (SQLException e1) {
e1.printStackTrace();
}*/
String sql = "update cloud.`user` u set password='" + password + "' where u.account_id="
+ cloudUserId + " and u.username='" + userName +"'";
logger.info("modify cloudStack user password sql: " + sql);
try {
stmt = conn.createStatement();
int i = stmt.executeUpdate(sql);
//conn.commit(); // commit
logger.info("modify cloudStack user password success , update rows = " + i);
} catch (SQLException e) {
logger.info("modify cloudStack user password failed");
e.printStackTrace();
/*try {
//conn.rollback(); //rollback
} catch (SQLException e1) {
e1.printStackTrace();
}*/
}finally{
try {
if(stmt != null) {
stmt.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
当第二次进行操作的时候就会出现上面所说的问题。
后来经过尝试对代码做了相应的修改,如下:
/**
* modify cloudStack user password(JDBC)
* @author wanggang
* time:2012-03-13
* updateTime:2012-03-19
*/
@Repository
public class CloudStackUserDAO implements ICloudStackUserDAO{
private static final Log logger = LogFactory.getLog(CloudStackUserDAO.class);
//get jdbc connection 在这里做了相应的修改
private static Connection conn = null;
private static Statement stmt = null;
public CloudStackUserDAO() {
// do nothing
}
/*
* (non-Javadoc)
* @see com.cloud.dao.cloudstack.ICloudStackUserDAO#modifyCloudStackUserPassword(long, java.lang.String, java.lang.String)
*/
@Override
public void modifyCloudStackUserPassword(long cloudUserId, String userName, String password){
logger.debug("modify cloudStack user password");
/*try {
// 取消掉自动提交
//conn.setAutoCommit(false);
} catch (SQLException e1) {
e1.printStackTrace();
}*/
conn = DBUtil.conn();
String sql = "update cloud.`user` u set password='" + password + "' where u.account_id="
+ cloudUserId + " and u.username='" + userName +"'";
logger.info("modify cloudStack user password sql: " + sql);
try {
stmt = conn.createStatement();
int i = stmt.executeUpdate(sql);
//conn.commit(); // commit
logger.info("modify cloudStack user password success , update rows = " + i);
} catch (SQLException e) {
logger.info("modify cloudStack user password failed");
e.printStackTrace();
/*try {
//conn.rollback(); //rollback
} catch (SQLException e1) {
e1.printStackTrace();
}*/
}finally{
try {
if(stmt != null) {
stmt.close();
}
if(conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
至于为什么这样做,我也在思考中,希望大家给予指点。