PrepareStatement In Loop

ID

java.prepare_stmt_in_loop

Severity

low

Remediation Complexity

trivial

Remediation Risk

low

Remediation Effort

low

Resource

Efficiency

Language

Java

Tags

efficiency

Description

Reports Connection.prepareStatement(…​) calls executed inside a loop body.

Rationale

prepareStatement round-trips to the database to parse and plan the SQL. Doing that once per loop iteration multiplies the latency by the iteration count and defeats the entire point of using a prepared statement (parameter binding without re-parsing).

// Bad — re-parses on every iteration
for (int id : ids) {
    PreparedStatement ps = c.prepareStatement("SELECT * FROM t WHERE id = ?");
    ps.setInt(1, id);
    ps.executeQuery();
}

// Good — prepare once, bind per iteration
PreparedStatement ps = c.prepareStatement("SELECT * FROM t WHERE id = ?");
for (int id : ids) {
    ps.setInt(1, id);
    ps.executeQuery();
}

Remediation

Hoist the prepareStatement call out of the loop. Close the statement after the loop (typically in a try-with-resources block).

References