Handling Complex Conditions with Rules

Hint

This article is generated by AI translation.

Simple rules like @{and, name = :name} handle single conditions well, but real-world queries often need nested logic like (A AND B) OR (C AND D). This article shows how dbVisitor rules handle complex compound conditions in one line.

Scenario Challenge

Suppose we have the following query requirement:

Query users who meet any group of the following conditions:

1. age equals specified value AND sex is 1
2. name equals specified value AND id is in the specified list

The corresponding SQL logical structure is:

WHERE (age = ? AND sex = '1') OR (name = ? AND id IN (?, ?, ?))

Traditional Pain Points

If you don't use dbVisitor's advanced rules, implementing this logic in other frameworks (such as MyBatis XML) can be very painful:

• Troublesome Parenthesis Management: You need to carefully control the generation of ( and ) to prevent leaving empty brackets () when some parameters are null, which would cause SQL errors.
• Tedious Prefix Handling: You need to handle the splicing of the OR keyword. If the first group of conditions is empty, the second group cannot have OR in front; if there are already WHERE conditions before, you need to add AND.

The resulting XML might look like cryptic code:

<!-- Cumbersome XML Implementation (Counterexample) -->
<trim prefix="AND (" suffix=")" prefixOverrides="OR">
    <if test="age != null">
        (age = #{age} AND sex = '1')
    </if>
    <if test="name != null">
        OR (name = #{name} AND id IN 
        <foreach ...>...</foreach>
        )
    </if>
</trim>

dbVisitor's Elegant Solution

dbVisitor's design philosophy is "Let SQL return to SQL". Its @{and, ...} rule supports not only simple key = value, but also writing complete SQL fragments containing parentheses and logical operators.

Code Example

We just need to write the complete logic in one @{and} rule:

/* SQL Template */
select * from user_info 
where status = 'ENABLE'
@{and, (age = :age and sex = '1') or (name = :name and id in (:ids)) }

Operating Mechanism

When executing this SQL, the dbVisitor engine performs the following judgments:

1. Parameter Scanning: The engine scans all parameters referenced in the rule expression (age = :age ...) (i.e., :age, :name, :ids).
2. Dynamic Decision:
   • Case A: All key parameters are null. If :age, :name, :ids are all null, the entire @{and, ...} rule will be completely ignored. The SQL degrades to select * from user_info where status = 'ENABLE'.
   • Case B: Valid parameters exist. As long as any one of the parameters is not null (and meets the enabling conditions), the entire expression will be appended to the SQL as a whole.
3. Automatic Decoration: dbVisitor automatically handles the connector after WHERE. If this is the first condition, it automatically fills in AND (if there is already status='ENABLE' before it).

Generated Result

Assuming the input parameters are age = 18, name = "Tom", ids = [1, 2, 3], the final generated SQL is:

select * from user_info 
where status = 'ENABLE'
  AND ( (age = ? and sex = '1') or (name = ? and id in (?, ?, ?)) )

Scheme Advantages

1. High Readability: The rule you write is standard SQL syntax, including parentheses and OR logic. Anyone who knows SQL can understand it at a glance without having to decipher the nested logic of XML tags.
2. Zero Glue Code: No need for complex tags like <trim>, <if>, <choose> to handle the splicing of SQL syntax fragments.
3. Security: Although writing complex expressions is allowed, all variables (:age, etc.) are still processed through JDBC PreparedStatement, completely preventing SQL injection.

Summary

dbVisitor's rule engine is not just for simple non-null judgments. By supporting nested complex SQL expressions in rules, it allows us to handle complex dynamic query logic in a way that is most intuitive and closest to native SQL.

Next time you encounter a complex OR combination query, try writing the logic directly into @{and, ...}!