java - 我认为我没有正确关闭连接 - Java

Question

我还是初学者，我已经成功地将我的 Java 项目链接到带有 Heroku 的在线 MySQL 数据库。这似乎比期望我的导师在标记我的作业时配置我的离线数据库更可取。

但是，我现在遇到了连接过多的问题，而且我的程序的性能非常慢。我想我已经正确地关闭了代码中的连接 - 但我相信有人可以指出我的错误:)

允许的最大连接数为 10，不知何故已经达到最大值。

public class DBConnect {

Connection dbConnection;
Statement stmt;
ResultSet rs; 

protected Connection connectToDatabase() {
    try {
        dbConnection=DriverManager.getConnection("jdbc:mysql://us-cdbr-iron-east-05.cleardb.net", "be0f2e99e68dbf", "ad1ed239");
    } catch (SQLException error) {
          System.err.println("Error connecting to database: "+ error.toString());
    }
    return dbConnection;
}

public void closeConnection(){
    try {
        if (null != dbConnection && !dbConnection.isClosed()) {
            dbConnection.close();
        }
    }
    catch (SQLException sqle)
    {
        System.out.println("Error closing connection: " + sqle.toString());
    }
}
}

我的数据库类

public class TeamDB extends DBConnect {

Team t;

public TeamDB(){
}

public void saveTeam(String teamName, int GoalsScored){
    final String insertStmt = "INSERT INTO heroku_2b89816185313b9.TEAM (TEAMNAME, GOALSSCORED) VALUES (?,?)";
    try (Connection con = connectToDatabase()) {
        try (PreparedStatement pstmt =  con.prepareStatement(insertStmt)) {
            pstmt.setString(1,teamName);
            pstmt.setInt(2, GoalsScored);
            pstmt.executeUpdate();
        }  
    } catch (SQLException sqle){ 
            System.out.println("Exception when inserting Team record: " + sqle.toString());
        }

}

public void updateTeam(String teamName, int GoalsScored){
    final String loadStmt = "SELECT * FROM heroku_2b89816185313b9.TEAM WHERE TEAMNAME = '" + teamName + "'";
    try (Connection con = connectToDatabase()) {
        try (PreparedStatement pstmt = con.prepareStatement(loadStmt)) {
            rs = pstmt.executeQuery(loadStmt);
            rs.next();
            deleteTeam(teamName);
            saveTeam(rs.getString("TEAMNAME"), (GoalsScored+rs.getInt("GOALSSCORED")));
            rs.close();
        } 
    } catch(SQLException error) { 
        System.err.println("Error updating team: " + error.toString());
    } 
}

public void deleteTeam(String teamName){
    final String deleteStmt = "DELETE FROM heroku_2b89816185313b9.TEAM WHERE TEAMNAME = '" + teamName + "'";
    try (Connection con = connectToDatabase()) {
        try (PreparedStatement pstmt = con.prepareStatement(deleteStmt)){
            pstmt.executeUpdate(deleteStmt);
        } catch (SQLException error) {
            System.err.println("Error deleting team from database: " + error.toString());
        }
    } catch (SQLException error) {
        System.out.println("Error connecting to database"+error.toString());
    }

}

public ArrayList<String> viewTeams() throws SQLException{
    ArrayList<String> teamNames = new ArrayList<>();
    String viewTeams = "SELECT TEAMNAME FROM heroku_2b89816185313b9.TEAM";
    try (Connection con = connectToDatabase()){
        try (PreparedStatement pstmt = con.prepareStatement(viewTeams)) {
            rs = pstmt.executeQuery();            
            while (rs.next()) {
                String nms = rs.getString("TEAMNAME");
                teamNames.add(nms);
            }
            rs.close();
        }

    } catch (SQLException error) {
        System.err.println("Error viewing teams from database: " + error.toString());
    }
    return teamNames;
}


public ArrayList<TeamScore> sortLeagueTable() throws SQLException {
    ArrayList<TeamScore> teamData = new ArrayList<>();
    String viewTeams = "SELECT * FROM heroku_2b89816185313b9.TEAM ORDER BY GOALSSCORED DESC";
    try (Connection con = connectToDatabase()){
        try (PreparedStatement pstmt = con.prepareStatement(viewTeams)) {
            rs = pstmt.executeQuery();
            while (rs.next()) {
            TeamScore ts = new TeamScore(rs.getString("TEAMNAME"),rs.getInt("GOALSSCORED"));
            teamData.add(ts);
            }
        }
    } catch (SQLException error) {
        System.err.println("Error sorting league table: " + error.toString());
    } 
    return teamData;
}
}

我的 TeamDB 类处理团队查询

public class PlayerDB extends DBConnect {

Player p;

public PlayerDB(){
}

public void savePlayer(final String playerName,
        final int playerGoals, final int redCards,
        final int yellowCards, final int gamesAsCap,
        final int forward, final int center,
        final int back) {
        final String insertStmt = "INSERT INTO heroku_2b89816185313b9.PLAYER (playerName,"
                + " playerGoals, redCards, yellowCards, gamesAsCap, forward,"
                + " center, back) VALUES (?,?,?,?,?,?,?,?)";
        try (Connection con = connectToDatabase()) {
            try (PreparedStatement pstmt = con.prepareStatement(insertStmt)) {
                pstmt.setString(1, playerName);
                pstmt.setInt(2, playerGoals);
                pstmt.setInt(3, redCards);
                pstmt.setInt(4, yellowCards);
                pstmt.setInt(5, gamesAsCap);
                pstmt.setInt(6, forward);
                pstmt.setInt(7, center);
                pstmt.setInt(8, back);
                pstmt.executeUpdate();
            }
        } 
        catch (SQLException sqle){ 
            System.out.println("Exception when inserting Player record: " + sqle.toString());
        }
}

public Player updatePlayer(String pN, int goalsThis, Boolean isCap, String posPlayed, int redC, int yelC){
    final String loadStmt = "SELECT * FROM heroku_2b89816185313b9.PLAYER WHERE PLAYERNAME = '" + pN + "'";
    try (Connection con = connectToDatabase()) {
        try (PreparedStatement pstmt = con.prepareStatement(loadStmt)) {
            rs = pstmt.executeQuery(loadStmt);
            rs.next();
            deletePlayer(pN);
            p = new Player(pN, goalsThis, isCap, posPlayed, redC, yelC);
            p.playerName = rs.getString("PLAYERNAME");
            p.totPlayerGoals += rs.getInt("PLAYERGOALS");
            p.totYellowCards += rs.getInt("YELLOWCARDS");
            p.totRedCards += rs.getInt("REDCARDS");
            p.totGamesAsCap += rs.getInt("GAMESASCAP");
            p.positionNum[0] += rs.getInt("FORWARD");
            p.positionNum[1] += rs.getInt("CENTER");
            p.positionNum[2] += rs.getInt("BACK");
            rs.close();
        }           
    }
    catch(SQLException error)
    { 
        System.err.println("Error connecting to database: " + error.toString());
    }
    finally {
        p.savePlayer();
        return p;
    }    
}

public Player loadPlayer(String plrName){
    final String loadStmt = "SELECT * FROM heroku_2b89816185313b9.PLAYER WHERE PLAYERNAME = '" + plrName + "'";
    try (Connection con = connectToDatabase()) {
        try (PreparedStatement pstmt = con.prepareStatement(loadStmt)) {
            rs = pstmt.executeQuery(loadStmt);
            rs.next();
            p = new Player("",0,Boolean.FALSE,"",0,0);
            p.playerName = rs.getString("PLAYERNAME");
            p.totPlayerGoals = rs.getInt("PLAYERGOALS");
            p.totYellowCards = rs.getInt("YELLOWCARDS");
            p.totRedCards = rs.getInt("REDCARDS");
            p.totGamesAsCap = rs.getInt("GAMESASCAP");
            p.positionNum[0] = rs.getInt("FORWARD");
            p.positionNum[1] = rs.getInt("CENTER");
            p.positionNum[2] = rs.getInt("BACK");
            rs.close();
        }
    } catch(SQLException error) { 
        System.err.println("Error connecting to database: " + error.toString());
    } finally {
        return p;
    } 
}

public void deletePlayer(final String playerName){
    final String deleteStmt = "DELETE FROM heroku_2b89816185313b9.PLAYER WHERE PLAYERNAME = '" + playerName + "'";
    try (Connection con = connectToDatabase()) {
        try (PreparedStatement pstmt = con.prepareStatement(deleteStmt)) {
            pstmt.executeUpdate(deleteStmt);
        } catch (SQLException error) {
            System.err.println("Error deleting player from database: " + error.toString());
        }
    } catch (SQLException error) {
        System.out.println("Error connecting to database"+error.toString());
    }

}

public ArrayList viewPlayers(){
    ArrayList vp = new ArrayList();
    String viewPlayers = "SELECT * FROM heroku_2b89816185313b9.PLAYER";
    connectToDatabase();
    try (Connection con = connectToDatabase()) {
        try (PreparedStatement pstmt = con.prepareStatement(viewPlayers)) {
            rs = pstmt.executeQuery(viewPlayers); 
            while (rs.next()){
            vp.add((rs.getString("PLAYERNAME")));
            }
            rs.close();
        }
    } catch (SQLException error) {
        System.err.println("Error querying database for player names: " + error.toString());
    } finally {
        return vp;
    }       
}
}

我的 PlayerDB 类处理播放器查询。

非常感谢任何建议，

祝大家新年快乐

更新了代码，现在实现了 Try with Resource block ，我仍然有同样的问题，事实上现在更糟了:(。

Answer 1

您正在打开连接，不一定要关闭它们。除了使用连接池，您还可以在每个操作中创建一个新连接。

最好使用 try-with-resources 和 prepared statements(而不是分段组成 SQL 字符串 - 转义单引号并防止 SQL 注入(inject))。

List<Product> list = new ArrayList<>();
try (Connection connection = openConnection()) {
    try (PreparedStatement preparedStatement = connection.prepareStatement(query)) {
        preparedStatement.setInt(1, appId);
        try (resultSet = preparedStatement.executeQuery()) {
            while (resultSet.next()) {
                Product item = getProductById(resultSet.getInt("prodId"));
                list.add(item);
            } 
            return list;
        }
    }
} catch (Exception e) {
    e.printStackTrace();
}

try-with-resources 确保自动关闭，包括 Statement 和 ResultSet。

Class.forName 不再需要查找驱动程序。

---

进一步检查

麻烦

我已经看到 ResultSet 的变量 rs 也是可自动关闭的。也可以使用 try-with-resources。我确实认为代码有点困惑； rs 不应该是一个字段，而应该是一个纯粹的局部变量。否则，两种方法可能会出于不同目的使用相同的 rs。请参阅下面的代码示例。

小费使用 exception.getMessage() 或 exception.getLocalizedMessage()(取决于语言)而不是 toString()。

也许是一个改进

一般来说，当你需要所有字段时不一定像这里这样:而不是 SELECT * 更好地列出你需要的列，并通过索引获取它们( rs.getInt(1) 等等)。

仅改进

替换

"jdbc:mysql://us-cdbr-iron-east-05.cleardb.net"

通过

"jdbc:mysql://us-cdbr-iron-east-05.cleardb.net/heroku_2b89816185313b9"

会缩短查询，因为 heroku_2b89816185313b9. 可以在其他地方删除。

仅改进

public Player loadPlayer(String plrName) {
    final String loadStmt = "SELECT * FROM PLAYER WHERE PLAYERNAME = ?";
    try (Connection con = connectToDatabase()) {
        try (PreparedStatement pstmt = con.prepareStatement(loadStmt)) {
            pstmt.setString(1, plrName);
            try (ResultSet rs = pstmt.executeQuery(loadStmt)) {
                if (!rs.next()) {
                    throw new SQLException("Player does not exist: " + plrName);
                }
                Player p = new Player("",0,Boolean.FALSE,"",0,0);
                p.playerName = rs.getString("PLAYERNAME");
                p.totPlayerGoals = rs.getInt("PLAYERGOALS");
                p.totYellowCards = rs.getInt("YELLOWCARDS");
                p.totRedCards = rs.getInt("REDCARDS");
                p.totGamesAsCap = rs.getInt("GAMESASCAP");
                p.positionNum[0] = rs.getInt("FORWARD");
                p.positionNum[1] = rs.getInt("CENTER");
                p.positionNum[2] = rs.getInt("BACK");
                return p;
            }
        }
    } catch(SQLException error) { 
        System.err.println("Error connecting to database: " + error.getMessage());
        throw new IllegalStateException("Loading player", error);
    } 
}

return 可以在最里面使用。在 return/break/throw 时，将调用隐式 final 来关闭。

我自己并没有捕捉到SQLException，而是在方法头中添加了throws SQLException。这允许 delete...(...); save...(); 以安全的方式完成。渔获物只是在调用位置移动。在这里我被迫抛出另一个(运行时)异常，因为当没有播放器可加载时不会返回任何内容。删除捕获并添加 throws SQLException 会更好。

我没有看到更多的问题；只是 rs 很奇怪，并且没有处理失败的 rs.next() 返回 false。对于数据问题，如 mysql 允许的字符集、SQL 数据类型等，会收到 SQLExceptions。