When to Use #{} vs ${} in MyBatis? A Deep Dive for Java Interviews

Interview Focus Points

Basic Syntax Mastery: Ability to clearly explain the roles and syntax differences of #{} and ${} in MyBatis.

Security and SQL‑Injection Risks: Understanding the underlying principle (prepared statements vs string concatenation) and the importance of security in real‑world development.

Performance and Underlying Mechanism: Knowledge of how #{} leverages PreparedStatement for pre‑compilation and caching of execution plans.

Applicable Scenarios and Best Practices: Knowing when to use #{} for most cases and when ${} is acceptable (e.g., dynamic table or column names) with strict validation.

Dynamic SQL Understanding: Distinguishing parameter passing ( #{}) from SQL fragment generation ( ${}).

Core Answers

#{}

is MyBatis's parameter placeholder; it is pre‑compiled, prevents SQL injection, and is suitable for all value‑passing scenarios. ${} is a string replacement token; it directly injects the provided string into the SQL, carries injection risk, and should only be used for dynamic SQL structure such as table or column names.

In‑Depth Analysis

Principle / Mechanism

#{} (pre‑compiled placeholder): MyBatis replaces #{} with ? during SQL parsing, creating a PreparedStatement. At execution time, the driver safely sets the parameter values, handling type conversion and quoting automatically. This provides high security (prevents injection) and better performance through cached execution plans.

${} (string concatenation): MyBatis directly substitutes the content of ${} into the SQL string, equivalent to manual string concatenation in Java (e.g., "SELECT * FROM " + tableName). This introduces SQL‑injection risk if the substituted value originates from untrusted input.

Comparison and Best Practices

Handling Method: #{} uses prepared statements; ${} uses plain string concatenation.

Security: #{} offers high security; ${} is low security and prone to injection.

Performance: #{} benefits from statement caching; ${} has relatively lower performance.

Parameter Type: #{} automatically adds quotes for strings; ${} inserts the raw value.

Main Use Cases: #{} for passing values in WHERE/INSERT/UPDATE clauses; ${} for dynamically specifying SQL keywords such as table names, column names, or ORDER BY clauses.

Best Practices

Prefer #{} wherever possible; avoid ${} unless the SQL structure itself must change.

Use ${} only when dynamically constructing parts of the SQL syntax (e.g.,

<select id="findByTable" resultType="map">SELECT * FROM ${tableName}</select>

).

When using ${}, enforce strict whitelisting or validation of the input to ensure it is safe and trusted.

Common Pitfalls

Misconception: Treating ${} as a subset of #{} —they serve fundamentally different purposes.

Dangerous LIKE Queries: Using ${} for pattern matching (e.g., LIKE '%${name}%') can lead to injection; the correct approach is LIKE CONCAT('%', #{name}, '%') (MySQL) or LIKE '%' || #{name} || '%' (Oracle).

ORDER BY Misuse: Applying #{} in ORDER BY adds quotes, turning the column name into a constant string and breaking sorting; ${} must be used with proper validation for dynamic column ordering.

Code Examples

<!-- Safe usage with #{} -->
<select id="selectBySafe" resultType="User">
  SELECT * FROM user WHERE id = #{userId}
</select>

<!-- Dynamic ORDER BY with ${} (ensure orderByColumn is trusted) -->
<select id="selectByOrder" resultType="User">
  SELECT * FROM user ORDER BY ${orderByColumn}
</select>

<!-- Dangerous misuse of ${} for value parameters -->
<select id="selectByDanger" resultType="User">
  SELECT * FROM user WHERE id = ${userId}
</select>

Conclusion

In a nutshell, #{} is the safe way to pass parameter values, while ${} should be reserved for dynamic SQL fragments and used only after rigorous validation. Mastering this distinction is essential for writing secure and efficient MyBatis code.