Prepared statements should not be created within a loopJAVA-P1003

This method calls Connection.prepareStatement() inside a loop with constant arguments. This is inefficient; move this call outside the loop.

Prepared statements can be costly to create (depending on the server implementation) because both client side and server side actions may be required to successfully create a prepared statement. For example, the server may cache the query, or generate an execution plan for the query before-hand. If a prepared statement is repeatedly created, there is a risk of various side effects occurring, such as memory or resource exhaustion, and unnecessary CPU utilization. Though modern implementations tend to cache such statements to prevent this kind of exhaustion from occurring (Oracle DB for example), this behavior must not be relied on.

Bad Practice

for (...) {
    PreparedStatement statement = connection.prepareStatement("SELECT * FROM users WHERE user = ?;");

    // Use the statement.
}

Recommended

Move this call outside the loop.

PreparedStatement statement = connection.prepareStatement("SELECT * FROM users WHERE user = ?;");

for (...) {
    // Use the statement.
}

References

• SpotBugs - IIL_PREPARE_STATEMENT_IN_LOOP