This is an issue when a database PreparedStatement object is created inside a loop with a constant or loop-invariant SQL string
argument.
Creating a PreparedStatement repeatedly adds object allocation overhead and may repeat SQL parsing and preparation, depending on the
driver and its caching configuration. Reusing the statement avoids repeated creation.
The application may experience degraded performance, with operations that should be fast becoming noticeably slow. This is especially problematic when the number of iterations in the loop is very large, or when the same pattern is repeated across many places in the codebase, as the cumulative overhead can significantly affect the application’s ability to handle production workloads.
Beyond performance, this can also lead to resource exhaustion, for example when repeatedly creating parameterized query objects exhausts server-side cursor or statement handle limits and causes connection failures, as well as increased infrastructure costs due to higher CPU usage and longer execution times.
Prepare the statement once before the loop and reuse it with different parameters.
for (int id : ids) {
PreparedStatement ps = connection.prepareStatement("SELECT * FROM t WHERE id = ?"); // Noncompliant
ps.setInt(1, id);
ps.execute();
ps.close();
}
PreparedStatement ps = connection.prepareStatement("SELECT * FROM t WHERE id = ?");
for (int id : ids) {
ps.setInt(1, id);
ps.execute();
}