SQL Rules: Combining AND/OR Conditions

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.

Query Scenario

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 (?, ?, ?))

Condition Handling

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>

Composing SQL Rules

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; ids must be non-empty when the combined condition is enabled */
select * from user_info 
where status = 'ENABLE'
@{and, ((age = :age and sex = '1') or (name = :name and id in @{in, :ids})) }

How Rules Work

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, the entire expression will be appended to the SQL as a whole. The rule does not remove NULL comparisons inside the combined expression. Validate required parameters, especially ids. Use separate conditional rules if the groups should be independently optional.
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 SQL

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 (?, ?, ?)) )

Benefits

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, binding data values. Table names, column names, and ${...} text substitution are outside that protection and must be controlled by the application.

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, ...}!