NonTransientConnectionException: No operations allowed after connection closed

最近做项目，要用到两个库里的两个表。

自己在用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();
			}
		}
	}
}

 至于为什么这样做，我也在思考中，希望大家给予指点。

评论

1 楼 freerambo 2013-11-07  

我也遇到这个问题了，主要是数据库线程池的空闲线程关闭了,你需要配置空闲时间，还有是否测试

		<property name="timeBetweenEvictionRunsMillis" value="${timeBetweenEvictionRunsMillis}" />
			<property name="minEvictableIdleTimeMillis" value="${minEvictableIdleTimeMillis}" />
			
			<property name="validationQuery" value="${validationQuery}" />
			<property name="testWhileIdle" value="${testWhileIdle}" />
			<property name="testOnBorrow" value="${testOnBorrow}" />

#set to 'SELECT 1'
validationQuery = SELECT 1
#set to 'true'
testWhileIdle = true
#some positive integer
timeBetweenEvictionRunsMillis = 3600000
#set to something smaller than 'wait_timeout'
minEvictableIdleTimeMillis = 18000000
#if you don't mind a hit for every getConnection(), set to "true"
testOnBorrow=true