java.sql.SQLException：在第一次数据库查询后连接已经关闭
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38015582/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-11-03 03:01:58 来源:igfitidea点击:
java.sql.SQLException: Connection has already been closed after first database query
提问by Devendra Singh
My configurations are : Application Server: Weblogic 10gR3 Database : Oracle 10g IDE : Netbeans Java: version 7
我的配置是：应用程序服务器：Weblogic 10gR3 数据库：Oracle 10g IDE：Netbeans Java：版本 7
I am facing exception while getting connection from pool. I have used try with resources to avoid burden to wrongly closing connections and statements.
从池中获取连接时遇到异常。我使用 try with resources 来避免错误关闭连接和语句的负担。
Here is the stack trace.
这是堆栈跟踪。
[INFO] 2016-06-24 19:01:08,304 org.nmdc.incentivepackage.databaseFiles.DepositMDao checkData 184 - Executing query SELECT count(*) count FROM dep_m
[INFO] 2016-06-24 19:01:08,304 org.nmdc.incentivepackage.databaseFiles.DepositMDao checkData 188 - Executing query SELECT count(*) count FROM dep_m
[INFO] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.databaseFiles.DepositMDao checkData 194 - Relinquish Database connection ...
[INFO] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.databaseFiles.DepositMDao checkData 206 - return 1###success###
[INFO] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.controllerFiles.CheckDepositMDataController processRequest 35 - result 1###success### .........save
[INFO] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.controllerFiles.CheckDepositMDataController processRequest 46 - update deposit master table
[INFO] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.controllerFiles.GetDepositMDataController processRequest 33 - Fetching deposit master data
[ERROR] 2016-06-24 19:01:08,320 org.nmdc.incentivepackage.databaseFiles.DepositMDao getDepositMData 239 - Exception
java.sql.SQLException: Connection has already been closed.
at weblogic.jdbc.wrapper.PoolConnection.checkConnection(PoolConnection.java:55)
at weblogic.jdbc.wrapper.Connection.preInvocationHandler(Connection.java:92)
at weblogic.jdbc.wrapper.Connection.createStatement(Connection.java:441)
at org.nmdc.incentivepackage.databaseFiles.DepositMDao.getDepositMData(DepositMDao.java:219)
at org.nmdc.incentivepackage.controllerFiles.GetDepositMDataController.processRequest(GetDepositMDataController.java:35)
at org.nmdc.incentivepackage.controllerFiles.GetDepositMDataController.doPost(GetDepositMDataController.java:74)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:292)
at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:175)
at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3498)
at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
at weblogic.security.service.SecurityManager.runAs(Unknown Source)
at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2180)
at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2086)
at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1406)
at weblogic.work.ExecuteThread.execute(ExecuteThread.java:201)
at weblogic.work.ExecuteThread.run(ExecuteThread.java:173)
The DBUtility class that gives the connection object is
提供连接对象的 DBUtility 类是
public class DBUtility {
private static Connection conn;
private static Context ctx ;
final static Logger logger = LoggerFactory.getLogger(DBUtility.class);
public DBUtility() {
ctx = null;
conn = null;
}
public static Connection getConnection() throws Exception {
if (conn != null)
return conn;
else {
//Hashtable ht = new Hashtable();
// ht.put(Context.INITIAL_CONTEXT_FACTORY,"weblogic.jndi.WLInitialContextFactory");
// ht.put(Context.PROVIDER_URL,"t3://localhost:7001"); //weblogic server
try {
ctx = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup ("incentivepool");
conn = ds.getConnection();
}
catch(Exception e) {
logger.error("Error in establishing connection...",e);
}
}
return conn;
}
}
The class that is throwing exception is
抛出异常的类是
ublic class DepositMDao extends HttpServlet {
private Connection connection = null;
final static Logger log = LoggerFactory.getLogger(DepositMDao.class);
private String deposit,category,period,query;
private float amount;
private String errors,status;
private int errorCount;
private Statement statement;
private ResultSet rs;
JSONObject jobject;
public DepositMDao() throws Exception {
// connection = DBUtility.getConnection();
if(connection != null) {
log.info("Received Database connection ...");
}
errors ="";
status = "success";
errorCount =0;
statement = null;
}
public String checkData() throws SQLException {
int count=0;
query = "SELECT count(*) count FROM dep_m" ;
log.info("Executing query "+query);
try ( Connection connection = DBUtility.getConnection();
Statement statement = connection.createStatement();
) {
log.info("Executing query "+query);
try (ResultSet rs = statement.executeQuery(query)) {
if(rs.next()) {
count = rs.getInt("count");
}
}
log.info("Relinquish Database connection ...");
} catch(Exception ex) {
errorCount++;
errors = errors + " "+ex.getMessage();
log.error("Exception ",ex);
}
if(errorCount == 0) {
status = "success";
} else {
status = "failure";
}
log.info("return "+count+"###"+status + "###"+errors);
return count+"###"+status + "###"+errors;
}
public List<DepositMBean> getDepositMData() throws SQLException {
List<DepositMBean> depositList = new ArrayList();
DepositMBean depositBean;
String dep14,dep11C,dep11B;
depositBean = null;
depositList = null;
query = "SELECT c_dep14,c_dep11C,c_dep11B FROM dep_m ";
try ( Connection connection1 = DBUtility.getConnection();
Statement statement1 = connection1.createStatement();
)
{
log.info("Executing query "+query);
try (ResultSet rs1 = statement1.executeQuery(query)) {
while (rs1.next()) {
depositBean = new DepositMBean();
dep14 = rs1.getString("c_dep14");
dep11C = rs1.getString("c_dep11C");
dep11B = rs1.getString("c_dep11B");
depositBean.setcDep14(dep14);
depositBean.setcDep11B(dep11B);
depositBean.setcDep11C(dep11C);
depositList.add(depositBean);
}
depositBean.display();
log.info("Relinquish Database connection ...");
}
} catch(Exception ex ) {
log.error("Exception ",ex);
}
return depositList;
}
Pooling configuration on server is :
服务器上的池配置是：
回答by CSK
it is becasue the connection is static object which means there is ONLY ONE connection instance can exist.
这是因为连接是静态对象，这意味着只能存在一个连接实例。
private static Connection conn
so the first time you call it works but the second time doesn't because the connection is already closed.
所以你第一次调用它时可以，但第二次不行，因为连接已经关闭。
you need to recreate the connection if it's closed
如果连接已关闭，则需要重新创建连接
if (conn != null && !conn.isClosed())
return conn;
