In MyBatis 3, dynamic SQL lets a mapper include, omit, or choose SQL fragments at runtime. Use <if> for optional conditions, <choose> for mutually exclusive branches, <where> and <set> to format conditional clauses, and <foreach> to build collection-based fragments. Keep values in #{} prepared parameters; ${} inserts raw text and must not receive untrusted input.
“MyBatis dynamic SQL” can also mean the separate MyBatis Dynamic SQL Java DSL. That library builds whole statements in Java rather than conditionally composing fragments in a mapper. The right choice depends on where your project keeps SQL and how its existing mappers are structured.
How MyBatis dynamic SQL works
MyBatis evaluates dynamic elements in a mapped statement and combines the resulting fragments into SQL. The main documented elements are <if>, <choose>, <trim>, <where>, <set>, <foreach>, and <bind>. You can use them in XML mapper files or inside a <script> element in an annotation-based mapper. See the MyBatis 3 dynamic SQL documentation.
Choose the right tag for the query
Use <if> for independent optional filters
An <if> element emits its contents when its OGNL test expression evaluates to true. This suits filters that can be combined independently, such as title and author criteria:
Recommended Free Tools
<select id="findPosts" resultType="Post">
SELECT * FROM BLOG
<where>
<if test="title != null">
AND title like #{title}
</if>
<if test="author != null and author.name != null">
AND author_name = #{author.name}
</if>
</where>
</select>Each predicate is included only when its condition passes. Use the predicate order and conditions that reflect the search behavior your application intends.
Use <choose> when only one branch should apply
<choose> evaluates alternatives in order and emits a matching <when> branch; if none matches, <otherwise> supplies a fallback. It is useful when the query should prefer one search key rather than combine every supplied key:
<where>
<choose>
<when test="id != null">
AND id = #{id}
</when>
<when test="email != null">
AND email = #{email}
</when>
<otherwise>
AND status = 'ACTIVE'
</otherwise>
</choose>
</where>Decide explicitly what should happen when none of the preferred inputs is present; the fallback is query logic, not merely formatting.
Rank #2
Use <where> to manage optional predicates
<where> emits WHERE only when its body produces content and removes a leading AND or OR. This avoids a dangling clause when all optional conditions are absent and removes the conjunction from the first emitted predicate.
For custom formatting, <trim prefix="WHERE" prefixOverrides="AND |OR "> offers equivalent control. Whitespace in prefixOverrides matters because MyBatis matches the specified text.
Use <set> for partial updates
<set> conditionally emits update assignments, adds the SET keyword, and removes a trailing comma. For custom formatting, use a trim element with prefix="SET" and suffixOverrides=",".
<update id="updatePost">
UPDATE BLOG
<set>
<if test="title != null">title = #{title},</if>
<if test="author != null">author = #{author},</if>
</set>
WHERE id = #{id}
</update>Make sure the application’s inputs and validation rules prevent an update with no assignments from becoming an unintended operation; formatting tags do not define that business rule.
Use <foreach> for collections
<foreach> iterates over an iterable, map, or array and can add opening text, separators, and closing text. It is commonly used for an IN predicate:
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
<select id="findPostsByIds" resultType="Post">
SELECT * FROM BLOG
WHERE id IN
<foreach item="id" collection="list" open="(" separator="," close=")">
#{id}
</foreach>
</select>Choose and test the application’s behavior for null and empty collections. Verify the rendered SQL and expected result for both; do not assume that an empty collection has the same meaning as an omitted filter.
Rank #4
Use <bind> for a derived parameter
<bind> creates a variable from an OGNL expression. For example, a mapper can form a LIKE pattern and still pass it as a bound value:
<bind name="pattern" value="'%' + title + '%'"/>
AND title like #{pattern}The resulting value remains a parameter rather than raw SQL text.
Keep parameters safe
#{value} creates a prepared-statement parameter that MyBatis binds through JDBC. Use it for data values, including values inside dynamic fragments and <foreach>.
Quick wins for a faster PC:
Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Best Value
${value} substitutes a string directly into SQL without escaping it. It can be useful when SQL must include dynamic metadata such as a column identifier, but accepting arbitrary user input there creates SQL injection risk. If a sort column or identifier must vary, map an application-controlled choice to an allow-listed identifier; do not pass raw user text to ${}. MyBatis explains the distinction in its SQL map XML documentation.
Choose between mapper scripting and the Java DSL
The names are similar, but they refer to different ways of constructing SQL.
| Approach | Where SQL is authored | What it does |
|---|---|---|
| MyBatis 3 dynamic SQL | Mapper XML, or a <script> element in an annotation |
Conditionally assembles fragments within a mapped statement. |
| MyBatis Dynamic SQL library | Java code | A separate DSL builds complete DELETE, INSERT, SELECT, and UPDATE statements and parameter objects. Its WHERE support includes equality and other comparisons, IN, LIKE, BETWEEN, and null checks. See its introduction and WHERE clause documentation. |
| MyBatis SQL Builder | Java code | A core MyBatis facility for building SQL strings; it is distinct from the separately maintained MyBatis Dynamic SQL library. See the SQL Builder documentation. |
There is no established universal winner or performance ranking between XML scripting and the separate Java DSL. Compare where your team wants to author queries, how much type guidance it wants from a DSL, how each option fits existing mapper interfaces and infrastructure, and whether the generated SQL and parameter handling behave correctly for your database. The DSL’s quick start describes its table and column representation, mapper setup, and use with MyBatis.
Handle database and project-specific behavior
SQL syntax can vary by database. When a databaseIdProvider is configured, MyBatis exposes _databaseId for choosing database-specific fragments. Treat each branch as dialect-specific SQL and validate it against the database the application actually uses.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →The default MyBatis scripting language is XML. A custom language driver is an extension point if a project needs another scripting approach; ordinary dynamic queries do not require one.
Before adopting syntax or a library feature, check it against the MyBatis and library versions in the application’s dependencies. Generated SQL should be inspected or tested with the project’s actual database and expected inputs, especially for absent filters, empty collections, and partial updates.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
