Java 连接关闭后不允许操作 MYSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45252205/
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
No operations allowed after connection closed MYSQL
提问by Whatever
Basically I have:
基本上我有：
String query = "SELECT * FROM table WHERE UNIQUEID=? AND DIR IS NOT NULL AND NAME IS NOT NULL AND PAGETYPE IS NOT NULL";
DBConnect Database = new DBConnect();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs=null;
try {
con = Database.getcon();
ps = con.prepareStatement(query);
ps.setString(1, URI);
rs=ps.executeQuery();
if(rs.next()){
}
} finally {
if(ps != null)
ps.close();
if(rs != null)
rs.close();
if(con != null)
con.close();
}
query = "SELECT COUNTCOMMENTS FROM videosinfos WHERE UNIQUEID=?";
try {
con = Database.getcon();
ps = con.prepareStatement(query); // Getting error here
rs=ps.executeQuery();
ps.setString(1, URI);
rs=ps.executeQuery();
if(rs.next()){
comments = rs.getInt(1);
}
} finally {
if(ps != null)
ps.close();
if(rs != null)
rs.close();
if(con != null)
con.close();
}
Note: The line I get the error, have a comment on it.
注意：我收到错误的那一行，请对其发表评论。
Connecting to Database:
连接数据库：
public DBConnect(){
try{
Class.forName("com.mysql.jdbc.Driver");
String unicode="useSSL=false&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8";
con = DriverManager.getConnection("jdbc:mysql://localhost:15501/duckdb?"+unicode, "root", "_PWD");
st = con.createStatement();
}catch(Exception ex){
System.out.println(ex.getMessage());
System.out.println("couldn't connect!");
}
}
public Connection getcon(){
DBConnect condb = new DBConnect();
Connection connect = con;
return con;
}
But in compilation I get this error:
但在编译时我收到此错误：
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed. at sun.reflect.GeneratedConstructorAccessor18.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1246) at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1241) at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4102) at com.mysql.jdbc.ConnectionImpl.prepareStatement(ConnectionImpl.java:4071) at duck.reg.pack.DBConnect.getitemfull_details(DBConnect.java:686) at duck.reg.pack.index.doPost(index.java:73) at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80) at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:799) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1455) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:748)
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException：连接关闭后不允许操作。在 sun.reflect.GeneratedConstructorAccessor18.newInstance(Unknown Source) 在 sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) 在 java.lang.reflect.Constructor.newInstance(Constructor.java:423) 在 com.mysql.jdbc .Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.Util.getInstance(Util.java:387) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917) at com.mysql。 jdbc.SQLError.createSQLException(SQLError.java:896) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) at com.mysql .jdbc.ConnectionImpl。
I'm Using Debian with MariaDB v10.x/Mysql
我将 Debian 与 MariaDB v10.x/Mysql 一起使用
采纳答案by Darshan Mehta
It's because of two reasons:
这是因为两个原因：
- Connection is closed after the execution of first
PreparedStatement getconuses aninstancevariable instead oflocalvariable. Due to this, sameconnvariable (the one that was closed earlier) gets returned whengetconis called next time.
- 执行 first 后连接关闭
PreparedStatement getcon使用instance变量而不是local变量。因此，下次调用conn时会返回相同的变量（之前关闭的变量）getcon。
To fix this, getconand DBConnectneed to be modified to declare a local connvariable and return it (in fact, you don't need DBConnectat all), e.g.:
为了解决这个问题，getcon并且DBConnect需要进行修改，以声明局部conn变量并返回它（实际上，你并不需要DBConnect在所有的），例如：
public Connection getcon(){
try{
Class.forName("com.mysql.jdbc.Driver");
String unicode="useSSL=false&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8";
return DriverManager.getConnection("jdbc:mysql://localhost:15501/duckdb?"+unicode, "root", "_PWD");
}catch(Exception ex){
System.out.println(ex.getMessage());
System.out.println("couldn't connect!");
throw new RuntimeException(ex);
}
}
回答by Daniel C.
A good approach is to use one try - finally block like this
一个好方法是使用一次 try - finally 块像这样
String query = "SELECT * FROM table WHERE UNIQUEID=? AND DIR IS NOT NULL AND NAME IS NOT NULL AND PAGETYPE IS NOT NULL";
DBConnect Database = new DBConnect();
Connection con = null;
PreparedStatement ps = null;
ResultSet rs=null;
try {
con = Database.getcon();
ps = con.prepareStatement(query);
ps.setString(1, URI);
rs=ps.executeQuery();
if(rs.next()){
}
query = "SELECT COUNTCOMMENTS FROM videosinfos WHERE UNIQUEID=?";
ps = con.prepareStatement(query); // Getting error here
rs=ps.executeQuery();
ps.setString(1, URI);
rs=ps.executeQuery();
if(rs.next()){
comments = rs.getInt(1);
}
} finally {
if(ps != null)
ps.close();
if(rs != null)
rs.close();
if(con != null)
con.close();
}
回答by zaarour
This is a MySQL settings issue. The response time of the MySQL server (60,621 ms in the example above) exceeds the MySQL server's configured wait_timeout value.
这是一个 MySQL 设置问题。MySQL 服务器的响应时间（上例中为 60,621 毫秒）超过了 MySQL 服务器配置的 wait_timeout 值。
Solution
解决方案
To resolve this issue, work with the MySQL database administrator to increase the value of the wait_timeout parameter.
要解决此问题，请与 MySQL 数据库管理员合作以增加 wait_timeout 参数的值。
Align Hibernate Configuration with you MySQL server to fix this problem.
将 Hibernate 配置与您的 MySQL 服务器对齐以解决此问题。
This setting is configurable in the my.cnf file. By default, mysql sets this value to "28800" seconds. If this value has been modified from the default, consider reverting it back to the default to restore connectivity to the Datameer server.
此设置可在 my.cnf 文件中配置。默认情况下，mysql 将此值设置为“28800”秒。如果此值已从默认值修改，请考虑将其恢复为默认值以恢复与 Datameer 服务器的连接。
