Hint

This article is generated by AI translation.

Rules System

In dbVisitor, Rules are a set of lightweight DSL (Domain Specific Language) embedded in SQL, invoked via the @{...} syntax. Its emergence is intended to replace cumbersome XML tags (such as <if>, <where>), allowing developers to write dynamic queries in a way that is closer to native SQL.

Scope of Application

Since rule parsing is the underlying core capability of the dbVisitor execution engine, you can use it wherever SQL is defined:

• Java Annotations: Written directly in annotations such as @Query, @Insert, @Update, @Delete.
• Code Construction: Arbitrary SQL strings executed via LambdaTemplate or JdbcTemplate.
• XML Mapper: Defining complex queries in Mapper .xml files.

Hint

Rule names are case-insensitive. @{AND}, @{And}, and @{and} are equivalent.

Usage Restrictions

Do not use rules in SQL string literals.

When scanning SQL, the dbVisitor parser identifies and skips string content wrapped in single quotes '. This means that if you write @{...} inside single quotes, it will be output as plain text and will not be executed as a rule.

Incorrect Example:

-- The @{uuid} here will not be parsed, and the database will eventually receive the string '... @{uuid}'
SELECT * FROM users WHERE name = 'user_@{uuid}'

Correct Practice:

Rules should be placed outside quotes, using the database's string concatenation function (such as concat) or via parameter placeholders.

-- Method 1: Splicing using the concat function
SELECT * FROM users WHERE name = concat('user_', @{uuid})

How it Works

When dbVisitor receives a SQL with @{...}, it goes through the following steps:

1. Parsing: Scan the SQL text and extract all rule tags.
2. Evaluation: Calculate the conditional expression (if any) in the rule based on the passed parameter context.
3. Transformation: Generate corresponding SQL fragments based on the type and parameters of the rule.

Syntax Structure

The general syntax format of the rule is as follows:

@{ RuleName [, Condition Expression [, Rule Content ]] }

Component	Required	Description	Example
RuleName	✅ Yes	The identifier of the rule, case-insensitive.	and, or, uuid32
Condition Expression	❌ No	An OGNL boolean expression. The rule takes effect when the result is true. If omitted or empty, it defaults to true.	age > 18, name != null
Rule Content	❌ No	The SQL fragment or parameter to be operated by the rule. Some rules (such as UUID) do not need this part.	age = :age, order by id

Common Variations

1. No-argument call: Call the rule to generate data only.

   -- Generate UUID
   @{uuid32}

2. Call with content: Usually used for dynamic SQL assembly, implying null check logic.

   -- When :name is not empty, automatically append AND name = :name
   @{and, name = :name}

3. Call with condition: Explicitly specify the effective condition (rule names usually start with if).

   -- When age > 18, append SQL
   @{ifand, age > 18, age = :age}

User Guide

dbVisitor provides a wealth of built-in rules to meet the needs of different scenarios:

• Statement Generation Rules
  • Includes Dynamic SQL Assembly (@{and}, @{or}, @{set}), Parameter Processing (@{md5}, @{uuid}), and Macro and Text Injection (@{macro}).
• Result Processing Rules
  • Used for post-processing of query result sets, as well as auxiliary configurations for stored procedures and multi-result set queries (@{resultSet}).
• Rule Nesting
  • Learn how to combine multiple rules to implement parameter pre-processing and complex logic (such as @{and, col = @{md5, :val}}).
• Custom Rules
  • When built-in rules cannot meet your needs, extend with custom rules by implementing the SqlRule interface.