Why this matters
Using `SELECT *` retrieves more data than necessary, increasing database load, network traffic, and memory usage. Query only required fields to optimize performance.
Ensure that SQL queries specify only the necessary fields instead of using `SELECT *` to optimize database performance.
Using `SELECT *` retrieves more data than necessary, increasing database load, network traffic, and memory usage. Query only required fields to optimize performance.
Side-by-side examples engineers can pattern-match during review.
public class OrderRepository {
public record OrderSummary(String title, String orderId, BigDecimal price) { }
public List<OrderSummary> queryOrderSummaries(Connection conn) {
String sql = "SELECT * " + // Noncompliant
"FROM Orders JOIN Customers ON Orders.customerId = Customers.id ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
return convertResultToOrderSummaryList(rs);
}
}public class OrderRepository {
public record OrderSummary(String title, String orderId, BigDecimal price) { }
public List<OrderSummary> queryOrderSummaries(Connection conn) {
String sql = "SELECT Customers.title, Orders.id, Orders.price " + // Compliant
"FROM Orders JOIN Customers ON Orders.customerId = Customers.id ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
return convertResultToOrderSummaryList(rs);
}
}public class OrderRepository {
public record OrderSummary(String title, String orderId, BigDecimal price) { }
public List<OrderSummary> queryOrderSummaries(Connection conn) {
String sql = "SELECT * " + // Noncompliant
"FROM Orders JOIN Customers ON Orders.customerId = Customers.id ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
return convertResultToOrderSummaryList(rs);
}
}public class OrderRepository {
public record OrderSummary(String title, String orderId, BigDecimal price) { }
public List<OrderSummary> queryOrderSummaries(Connection conn) {
String sql = "SELECT Customers.title, Orders.id, Orders.price " + // Compliant
"FROM Orders JOIN Customers ON Orders.customerId = Customers.id ";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);
return convertResultToOrderSummaryList(rs);
}
}From the same buckets as this rule.
Review SQL/database migrations for operations that can lock large tables or cause downtime. Examples: creating indexes without CONCURRENTLY (Postgres), ALTER COLUMN TYPE on big tables, adding NOT NULL without backfill, long-running updates without batching. Require an online migration strategy (CONCURRENTLY, backfill in batches, dual-write/expand-contract) and a rollback plan.