String customerName = request.getParameter("customerName");
String status = request.getParameter("status");
String startDate = request.getParameter("startDate");
String sql = "SELECT o.order_id, o.amount, o.status, c.name " +
"FROM orders o JOIN customers c ON o.customer_id = c.id " +
"WHERE 1=1 ";
if (customerName != null && !customerName.isEmpty()) {
sql += "AND c.name LIKE '%" + customerName + "%' ";
}
if (status != null && !status.isEmpty()) {
sql += "AND o.status = '" + status + "' ";
}
if (startDate != null && !startDate.isEmpty()) {
sql += "AND o.created_at >= '" + startDate + "' ";
}
sql += "ORDER BY o.created_at DESC";
这段代码有至少三个问题。首先,SQL注入风险直接暴露——customerName和status被原封不动地拼进SQL字符串里,一个有恶意企图的用户可以在参数里写' OR '1'='1来绕过所有条件。其次,可读性差,字符串拼接的语法噪音盖过了SQL语句本身的结构。第三,维护成本高,每次新增查询条件都要在if判断和拼串两个地方改,容易漏掉单引号或百分号。
String sql = """
SELECT o.order_id, o.amount, o.status, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE 1=1
%s
%s
%s
ORDER BY o.created_at DESC
""".formatted(nameCondition, statusCondition, dateCondition);
import java.util.ArrayList;
import java.util.List;
import java.lang.StringTemplate;
public class SqlProcessor implements StringTemplate.Processor {
public static final SqlProcessor SQL = new SqlProcessor();
private SqlProcessor() {}
@Override
public ParameterizedSql process(StringTemplate st) {
StringBuilder sb = new StringBuilder();
List
import static com.example.template.SqlProcessor.SQL;
public List queryOrders(String customerName, String status, String startDate) {
String baseSql = """
SELECT o.order_id, o.amount, o.status, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE 1=1
""";
// 动态条件
String nameCondition = customerName != null && !customerName.isEmpty()
? "AND c.name LIKE " + SQL."%{customerName}%"
: "";
// 这里注意:nameCondition 本身已经是 SQL 模板处理后的结果的一部分
// 实际使用时可以把条件也纳入模板中
// 更优雅的做法是把整个SQL写在一个模板里
// 为了让动态条件也能处理,可以分段构建
ParameterizedSql sql;
if (customerName != null && status != null) {
sql = SQL."""
SELECT o.order_id, o.amount, o.status, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE c.name LIKE {'%' + customerName + '%'}
AND o.status = {status}
ORDER BY o.created_at DESC
""";
} else if (customerName != null) {
sql = SQL."""
SELECT o.order_id, o.amount, o.status, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE c.name LIKE {'%' + customerName + '%'}
ORDER BY o.created_at DESC
""";
} else {
sql = SQL."""
SELECT o.order_id, o.amount, o.status, c.name
FROM orders o JOIN customers c ON o.customer_id = c.id
ORDER BY o.created_at DESC
""";
}
// 用 PreparedStatement 执行
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql.sql())) {
for (int i = 0; i < sql.parameters().size(); i++) {
ps.setObject(i + 1, sql.parameters().get(i));
}
ResultSet rs = ps.executeQuery();
// 映射结果集...
}
}
现在SQL语句的结构清晰明了,变量通过{}直接嵌入在SQL文本中,可读性大幅提升。而且变量值是通过?占位符传入PreparedStatement的,和SQL语句本身完全分离,注入风险从语法层面被消除了。就算customerName传了'; DROP TABLE orders; --,它也只被当作一个普通的字符串参数值,永远不会变成SQL的一部分。