数据库连接未关闭

Question

我的数据库连接不会关闭的原因是什么？错误表明，当我执行几个查询时，连接太多

提前感谢，这是代码：

   try {
        st = DBConnector.getConnection().prepareStatement(sqlQuery);
        st.setString(1, userID);
        result = st.executeQuery();
        checker = !result.first();
    }catch(Exception e){
        e.printStackTrace();
    }
    finally
    {
        try {
            DBConnector.getConnection().close();
        }catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }   
    }

顺便说一下，这是我的DBConnector类

public class DBConnector {
    private static Connection;
    private static String host;
    private static String username;
    private static String password ;

    public static void setUsername(String newusername){
        username = newusername;
    }
    public static void setPassword(String newpassword){
        password = newpassword;
    }
    public static void setHost(String newHost){
        host = newHost;
    }
    public static void setConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = (Connection) DriverManager.getConnection(host, username , password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static String getUsername(){
        return username;
    }
    public static String getPassword(){
        return password;
    }
    public static String getHost(){
        return host;
    }
    public static Connection getConnection(){
        setConnection();
        return conn;
    }
    public static void disconnect(){
        conn = null;
    }
}

Answer 1

如果我正确阅读了您的类，您将在finally块中创建一个立即关闭的新连接，而不是在执行DBConnector.getConnection().close()时关闭原始连接。

将您的代码更改为：

 Connection connection = DBConnector.getConnection();
 try {        
     st = connection .prepareStatement(sqlQuery);
     // more code
  } catch (...) {
     // more code
  finally
    {
        try {
            connection.close(); // 
        }catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }   
    }

更好的方法是使用try-and-resources（Java7）：

 try (Connection connection = DBConnector.getConnection()) {        
     st = connection .prepareStatement(sqlQuery);
     // more code
 } catch (...) {
     // more code
 }

Answer 2

这就是正确打开和关闭连接的方法。

/**
 * Creates a connection to database if there isn't any established.
 * 
 * @return {@link Connection} to database.
 */
protected Connection getConnection() throws ServiceException{
    try{
        if(this.conn == null || this.conn.isClosed()){
            // Here you have to create your connection
            // this.conn = ...
        }
        if(conn==null){
            // Retry to connect in case that the connection is still null!
            Thread.sleep(250);
            getConnection();
        }
    }catch(SQLException e ){
        e.printStackTrace();
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
    return conn;
}

/**
 * Closes the {@link Connection} to database.
 */
protected void closeConnection(){
    try{
        if(this.conn != null && !this.conn.isClosed()){
            this.conn.close();
        }
    }catch(SQLException e){
        e.printStackTrace();
    }
    finally{
        this.conn = null;
    }
}