mysql连接问题

Question

出现此异常：
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:888)class test1.db:com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException: No operations allowed after connection closed.我的代码如下：public void insertOutLineStatus(String sitename,String sitestate,String tablename) throws SQLException
{
//Statement stmt;
if(conn!=null)
conn.close();
java.text.SimpleDateFormat  df   =   new   SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String changetime=df.format(new Date());
try{
Statement stmt = conn.createStatement();
System.out.println("insert into  "+tablename+"(site_name,out_site_state,change_time,alert_count) values('"+sitename+"','"+sitestate+"','"+changetime+"',"+0+")");
stmt.executeUpdate("insert into  "+tablename+"(site_name,out_site_state,change_time,alert_count) values('"+sitename+"','"+sitestate+"','"+changetime+"',"+0+")");
//stmt.close();
} catch (Exception ex) {
 ex.printStackTrace();
}finally{

}
}

public void updataOutLineStatue(String sitename,String sitestate,String tablename) throws SQLException
{
if(conn!=null)
conn.close();
java.text.SimpleDateFormat  df   =   new   SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String changetime=df.format(new Date());
try{
Statement stmt = conn.createStatement();
System.out.println("updata "+tablename+"set out_site_state='"+sitestate+"',change_time='"+changetime+"' where site_name='"+sitename+"'");
stmt.executeUpdate("updata "+tablename+"set out_site_state='"+sitestate+"',change_time='"+changetime+"' where site_name='"+sitename+"'");
//stmt.close();
} catch (Exception ex) {
 ex.printStackTrace();
}
}

@SuppressWarnings("finally")
public Boolean selectOutLineStatue(String tablename,String sitename) throws SQLException
{
if(conn!=null)
conn.close();
Boolean flag=false;
ResultSet res;
try{
Statement stmt = conn.createStatement();
res=stmt.executeQuery("select * from "+tablename+" where site_name='"+sitename+"'");
if(res.next()){
flag=true;
}
//stmt.close();
}catch(Exception e1)
{
e1.printStackTrace();
System.out.println(this.getClass()+":"+e1.toString());
}finally{
return flag;
}
}

public ArrayList getOutIp()
{
ResultSet res = null;
ArrayList<String> site_out_ip=new ArrayList();
String str[]=new String[2];
try{
Statement stmt=conn.createStatement();
res=stmt.executeQuery("select * from out_line_info");
while(res.next()){
str[0]=res.getString("site_out_ip");
str[1]=res.getString("site_name");
siteurl=str[0];
sitename=str[1];
System.out.println(str[1]+":"+str[0]+"\n"+"1111111111");
getReturnMSG("http://"+str[0]);
site_out_ip.add(str[0]);
}
/*String[] adrs = (String[])site_out_ip.toArray(new String[site_out_ip.size()]);
for(String s : adrs) {   
     System.out.println("s:"+s);
     test1.urltest.this.getReturnMSG("http://"+s);
}*/

}catch(Exception e){
e.printStackTrace();
System.out.println(this.getClass()+":"+e.toString());
}finally{
try {
res.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return site_out_ip;
}

private String getReturnMSG(String url)
{
HttpURLConnection con=null;
StringBuffer document = new StringBuffer();
try{
URL url1=new URL(url);
con=(HttpURLConnection)url1.openConnection();
con.setConnectTimeout(10000);
BufferedReader reader = new BufferedReader(new InputStreamReader(con.getInputStream()));
String line = null;
while ((line = reader.readLine()) != null)
{
     document.append(line + "\n");
}
System.out.println("55555555555555555555"+"\n"+con.getResponseMessage());
System.out.println(con.getContentType());
System.out.println(con.getURL());
sitestate=con.getResponseMessage();
reader.close();
System.out.println(tablename);
System.out.println(sitename);
flag=selectOutLineStatue(tablename, sitename);
if(!flag){
insertOutLineStatus(sitename,sitestate,tablename);
}else{
updataOutLineStatue(sitename, sitestate, tablename);
}

}catch(Exception ex){
System.out.println("1111111111111111"+ex.getMessage());
/*flag=db1.selectOutLineStatue(tablename, sitename);
if(!flag){
db1.insertOutLineStatus(sitename, ex.getMessage(), tablename);
}else{
db1.updataOutLineStatue(sitename, ex.getMessage(), tablename);
}*/
}
return document.toString();
}多数据库操作，connection开关是否存在问题？

Answer 1

你的程序中数据库连接关闭了，就不能再做数据库相关的操作了：
if(conn!=null) 
conn.close(); 
Boolean flag=false; 
ResultSet res; 
try{ 
Statement stmt = conn.createStatement(); 
res=stmt.executeQuery("select * from "+tablename+" where site_name='"+sitename+"'"); 
if(res.next()){ 
flag=true; 
} 
建议你修改一下代码，执行结束后关闭连接。

Answer 2

package test1;
import java.sql.*;
import java.io.*;
import java.util.*;
import java.net.*;
import java.text.SimpleDateFormat;
import java.util.Date;public class db {
public Connection conn;
private static ArrayList site_out_ip=new ArrayList();ResultSet rs=null;
String Username="";
String Password="";
String Email="";
String Homepage="";String sitename=null;
String siteurl=null;
String sitestate=null;
String tablename="out_line_state";
Boolean flag=false;
private Statement stmt;public db(){
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
// 建立到MySQL的连接
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/monitor?useUnicode=true&characterEncoding=gb2312", "root", "mysql");
} catch (Exception ex) {
System.out.println("Error : " + ex.toString());
}
}private void insertOutLineStatus(String sitename,String sitestate,String tablename) throws SQLException
{
Statement stmt=null;
if(conn!=null)
conn.close();
java.text.SimpleDateFormat  df   =   new   SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String changetime=df.format(new Date());
try{
stmt = conn.createStatement();
System.out.println("insert into  "+tablename+"(site_name,out_site_state,change_time,alert_count) values('"+sitename+"','"+sitestate+"','"+changetime+"',"+0+")");
stmt.executeUpdate("insert into  "+tablename+"(site_name,out_site_state,change_time,alert_count) values('"+sitename+"','"+sitestate+"','"+changetime+"',"+0+")"); } catch (Exception ex) {
 ex.printStackTrace();
}
}

private void updataOutLineStatue(String sitename,String sitestate,String tablename) throws SQLException
{
if(conn!=null)
conn.close();
Statement stmt=null;
java.text.SimpleDateFormat  df   =   new   SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String changetime=df.format(new Date());
try{
stmt = conn.createStatement();
System.out.println("updata "+tablename+"set out_site_state='"+sitestate+"',change_time='"+changetime+"' where site_name='"+sitename+"'");
stmt.executeUpdate("updata "+tablename+"set out_site_state='"+sitestate+"',change_time='"+changetime+"' where site_name='"+sitename+"'");
} catch (Exception ex) {
 ex.printStackTrace();
}
}

@SuppressWarnings("finally")
private Boolean selectOutLineStatue(String tablename,String sitename) throws SQLException
{
if(conn!=null)
conn.close();
Boolean flag=false;
ResultSet res=null;
try{
stmt = conn.createStatement();
res=stmt.executeQuery("select * from "+tablename+" where site_name='"+sitename+"'");
if(res.next()){
flag=true;
}
}catch(Exception e1)
{
e1.printStackTrace();
System.out.println(this.getClass()+":"+e1.toString());
}finally{
return flag;
}
}

public ArrayList getOutIp()
{
Statement stmt=null;
ResultSet res = null;
ArrayList<String> site_out_ip=new ArrayList();
String str[]=new String[2];
try{
stmt=conn.createStatement();
res=stmt.executeQuery("select * from out_line_info");
while(res.next()){
str[0]=res.getString("site_out_ip");
str[1]=res.getString("site_name");
siteurl=str[0];
sitename=str[1];
System.out.println(str[1]+":"+str[0]+"\n"+"1111111111");
getReturnMSG("http://"+str[0]);
site_out_ip.add(str[0]);
}
}catch(Exception e){
e.printStackTrace();
System.out.println(this.getClass()+":"+e.toString());
}finally{
try {
res.close();
stmt.close();
if(conn!=null)
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return site_out_ip;
}

private String getReturnMSG(String url)
{
HttpURLConnection con=null;
StringBuffer document = new StringBuffer();
try{
URL url1=new URL(url);
con=(HttpURLConnection)url1.openConnection();
con.setConnectTimeout(10000);
BufferedReader reader = new BufferedReader(new InputStreamReader(con.getInputStream()));
String line = null;
while ((line = reader.readLine()) != null)
{
     document.append(line + "\n");
}
System.out.println("55555555555555555555"+"\n"+con.getResponseMessage());
System.out.println(con.getContentType());
System.out.println(con.getURL());
sitestate=con.getResponseMessage();
reader.close();
System.out.println(tablename);
System.out.println(sitename);
flag=selectOutLineStatue(tablename, sitename);
if(!flag){
insertOutLineStatus(sitename,sitestate,tablename);
}else{
updataOutLineStatue(sitename, sitestate, tablename);
}

}catch(Exception ex){
System.out.println("1111111111111111"+ex.getMessage());
}
return document.toString();
}
}