PreparedStatement resource leak while preparing a statement

Question

I suspect this may be a false positive, but I can't be sure, so I'm somewhat confused. I'm using Eclipse Neon and the issue is appearing at the third time I prepare a statement. I do something almost identical down below, with no errors.

try{
        Connection con = MySQL.connection;
        PreparedStatement ps = con.prepareStatement("SELECT * from UsernameData "
                + "WHERE UUID = '" + player.getUniqueId() + "'");
        ResultSet rs = ps.executeQuery();
        if(rs.next() == true){
            ps = con.prepareStatement("update UsernameData set UUID = ?, Username = ? where UUID = ?");
            ps.setString(1, uuid);
            ps.setString(2, username);
            ps.setString(3, uuid);
            ps.execute();
            ps.close();
            rs.close();
            return;
        }
        ps = con.prepareStatement("insert into UsernameData(UUID, Username)"
                + " values (?, ?)");
        ps.setString(1, uuid);
        ps.setString(2, username);
        ps.execute();
        ps.close();
        rs.close();
        return;
    }catch(SQLException e){
        Bukkit.getServer().getLogger().warning("SQL Error: " + e);
    }

score 0 · Answer 1 · answered Nov 10 '16 at 00:40

0

You don't close the first set of resources when you stomp on the ps for your insert.

You should also consider using try-with-resources:

    try (Connection con = MySQL.connection;
            PreparedStatement ps = con.prepareStatement("SELECT * from UsernameData "
                    + "WHERE UUID = '" + player.getUniqueId() + "'");
            PreparedStatement ps2 = con.prepareStatement("update UsernameData set UUID = ?, Username = ? where UUID = ?");
            PreparedStatement ps3 = con.prepareStatement("insert into UsernameData(UUID, Username)"
                    + " values (?, ?)");
            ResultSet rs = ps.executeQuery()) {
        if (rs.next() == true) {
            ps2.setString(1, uuid);
            ps2.setString(2, username);
            ps2.setString(3, uuid);
            ps2.execute();
            return;
        }
        ps3.setString(1, uuid);
        ps3.setString(2, username);
        ps3.execute();
        return;
    } catch (SQLException e) {
        Bukkit.getServer().getLogger().warning("SQL Error: " + e);
    }

Yes, the second and third PreparedStatement are potentially wasted. You could wrap them in its own try-with-resources if you like.

But the crux of the problem is your stomping on the ps variable.

answered Nov 10 '16 at 00:40

Will Hartung

115,893
19
128
203

Mmm that makes sense, thank you. – Haydenman2 Nov 10 '16 at 00:42
not necessarily. I do not think its like any kind of best practise or an issue reusing same statement or PreparedStatement variable. I will site this http://stackoverflow.com/questions/850878/does-setting-java-objects-to-null-do-anything-anymore – Acewin Nov 10 '16 at 00:50
simply closing the statement using ps.close(); is fine. Potential leak is not because you are doing ps.execute(); or ps.close(); it will be when you are not able to close them. And it is the connection which is much more important because until you have made call to connection.close database assumes your connection is open even if u have de-referenced it by setting the connection variable to null. – Acewin Nov 10 '16 at 00:53
Another reference http://stackoverflow.com/questions/22889075/do-unclosed-streams-cause-memory-leaks-in-java – Acewin Nov 10 '16 at 00:55
@Will Hartung your answer is not very accurate – Acewin Nov 10 '16 at 00:55

PreparedStatement resource leak while preparing a statement

1 Answers1