JDBC 连接数据库报Object has been closed

Question

[code="java"]
package DB;

import java.sql.Connection;
import java.sql.DriverManager;

public class MeDBConn
{
private Connection conn;

private  String driver="com.microsoft.jdbc.sqlserver.SQLServerDriver";

private  String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=BW_KJ222";

private  static MeDBConn  dbc=new MeDBConn();

public static MeDBConn getInstance()
{
    return dbc;
}

public Connection getConnection()
{
try
{
Class.forName(driver);

    conn=DriverManager.getConnection(url,"sa","kj222");

 }catch(Exception ex)
 {
    ex.printStackTrace();
 }
 return conn;

}
}

[/code]

[code="java"]
package service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import models.v_Personnel_VO;

import DB.MeDBConn;

public class v_PersonnelService {
public List allUser(){
Connection con=null;
Statement stmt = null;
ResultSet rs = null;
con=MeDBConn.getInstance().getConnection();
try {
String sql = "select * from v_Personnel";
stmt = con.createStatement();
rs = stmt.executeQuery(sql);
List list = new ArrayList();
while(rs.next()){
v_Personnel_VO t = new v_Personnel_VO();
t.setID(rs.getInt("id"));
t.setPersonID(rs.getInt("personid"));
t.setI_deptid(rs.getInt("DeptCode"));
t.setUserName(rs.getString("姓名"));
t.setCardNO(rs.getString("卡号"));
t.setFrequency(rs.getString("班次"));
t.setDeptName(rs.getString("部门"));
t.setDutyName(rs.getString("职务"));
t.setJobs(rs.getString("工种"));
t.setInduction(rs.getString("上岗证有效期"));
t.setMonthlyProvisionOrii(rs.getString("每月规定下井次数"));
t.setMinimumDurationOrii(rs.getString("最小下井时长"));
t.setMaxDurationOrii(rs.getString("最大下井时长"));
t.setBanMeans(rs.getString("计班方式"));
t.setMostHours(rs.getString("最小时长不计班"));
t.setCrossTwoClass(rs.getString("跨2个班的时长"));
t.setCrossThreeClass(rs.getString("跨3个班的时长"));
t.setCrossFourClass(rs.getString("跨4个班的时长"));
t.setMemo(rs.getString("备注"));
list.add(t);
}
//System.out.println("list size is :"+list.size());

        return list;
    } catch (SQLException e) {
        e.printStackTrace();
        return null;
    }finally{
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(stmt!=null){
        try {
            stmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        }
        try {
            con.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}
}

[/code]

[code="java"]

public JsonView getAll() throws Exception {
v_PersonnelService v_p = new v_PersonnelService();
List list = v_p.allUser();
int total = list.size();
Map result = new HashMap();
result.put("total", total);
result.put("demo", list);
System.out.println("返回的数据："+new JsonView(result));
return new JsonView(result);
}

[/code]

执行到
System.out.println("返回的数据："+new JsonView(result));
return new JsonView(result);
打印的超慢，有时还会出现这里的时候出现（rs里）

                            t.setInduction(rs.getString("上岗证有效期"));
            t.setMonthlyProvisionOrii(rs.getString("每月规定下井次数"));
            t.setMinimumDurationOrii(rs.getString("最小下井时长"));
            t.setMaxDurationOrii(rs.getString("最大下井时长"));
            t.setBanMeans(rs.getString("计班方式"));
            t.setMostHours(rs.getString("最小时长不计班"));
            t.setCrossTwoClass(rs.getString("跨2个班的时长"));
            t.setCrossThreeClass(rs.getString("跨3个班的时长"));

Object has been closed 这个异常

Answer 1

"Object has been closed"是指你在多次操作数据库连接的时候将连接给关闭了，你检查下这段代码是否有重复操作数据而没有重新进行连接
[code="java"]
System.out.println("返回的数据："+new JsonView(result)); return new JsonView(result);

/**

• JDBCTemplet

• @author BAOSJ
  */
  public class JDBCTemplet {
  private static final Log LOG = LogFactory.getLog(JDBCTemplet.class);
  private static String DRIVER = "";
  private static String URL = "";
  private static String USERNAME = "";
  private static String PASSWORD = "";
  static {
  try {
  Properties props = new Properties();
  props.load(JDBCTemplet.class.getResourceAsStream("db.properties"));
  DRIVER = props.get("driver").toString();
  URL = props.get("url").toString();
  USERNAME = props.get("userName").toString();
  PASSWORD = props.get("passWord").toString();
  } catch (IOException e) {
  LOG.error("Error: " + e.getMessage());
  }
  }

  /**

  • execute
  • @author BAOSJ
  • @param sql
  • @param values */ public final Long execute(String sql, Object value) throws Exception { Object[] values = { value }; return execute(sql, values); }

  /**

  • execute
  • @author BAOSJ
  • @param sql
  • @param values */ public final Long execute(String sql, Object[] values) throws Exception { PreparedStatement ps = null; ResultSet rs = null; Connection conn = null; Long id = null; try { conn = getConnection(); ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); for (int i = 0; i < values.length; i++) { ps.setObject(i + 1, values[i]); } ps.executeUpdate(); rs = ps.getGeneratedKeys(); while (rs.next()) { id = rs.getLong(1); } } catch (Exception e) { throw e; } finally { destroy(null, ps, conn); } return id; }

  /**

  • connection
  • @author BAOSJ
  • @return */ public final Connection getConnection() throws Exception { Connection conn = null; try { Class.forName(DRIVER).newInstance(); conn = DriverManager.getConnection(URL, USERNAME, PASSWORD); } catch (Exception e) { throw e; } return conn; }

  /**

  • destroy
  • @author BAOSJ
  • @param rs
  • @param ps
  • @param conn */ public final void destroy(ResultSet rs, Statement ps, Connection conn) throws Exception { try { if (rs != null) { rs.close(); } if (ps != null) { ps.close(); } if (conn != null) { conn.close(); } } catch (Exception e) { throw e; } }

  /**

  • query count
  • @author BAOSJ
  • @param sql
  • @param values
  • @return
  • @throws Exception */ public Long queryCont(String sql, Object[] values) throws Exception { PreparedStatement ps = null; ResultSet rs = null; Connection conn = null; Long count = 0L; try { conn = getConnection(); ps = conn.prepareStatement(sql); for (int i = 0; i < values.length; i++) { ps.setObject(i + 1, values[i]); } rs = ps.executeQuery(); while (rs.next()) { count = rs.getLong(1); } } catch (Exception e) { throw e; } finally { destroy(rs, ps, conn); } return count; }

}

[/code]