SQL Writing Guidelines

Dynamic SQL Tag Syntax

Dynamic SQL functionality allows us to construct SQL statements dynamically based on conditions. By using specific tags, we can easily implement complex SQL logic. Below are the commonly used dynamic SQL tags and their usage.

1. if Tag

The if tag is used for conditional evaluation, similar to an if statement in Java.

SELECT * FROM BLOG WHERE state = 'ACTIVE'
<if test="title != null">
  AND title like #{title}
</if>

Parameter Example

{
  "title": "MyBlog"
}

Resulting SQL Query

SELECT * FROM BLOG WHERE state = 'ACTIVE' AND title like ?

2. choose, when, otherwise Tags

These tags function similarly to a switch statement in Java.

SELECT * FROM BLOG WHERE state = 'ACTIVE'
<choose>
  <when test="title != null">
    AND title = #{title}
  </when>
  <when test="author != null and author.name != null">
    AND author_name = #{author.name}
  </when>
  <otherwise>
    AND featured = 1
  </otherwise>
</choose>

Parameter Example

{
  "title": "MyBlog",
  "author": {
    "name": "AuthorName"
  }
}

Resulting SQL Query

SELECT * FROM BLOG WHERE state = 'ACTIVE' AND title = ?

3. trim, where, set Tags

These tags are used to handle prefixes, suffixes, and conjunctions in SQL queries.

trim Tag

The trim tag allows for customized string trimming.

SELECT * FROM BLOG 
<trim prefix="WHERE" prefixOverrides="and |or ">
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null">
    AND author_name like #{author.name}
  </if>
</trim>

Parameter Example

{
  "title": "MyBlog",
  "author": {
    "name": "AuthorName"
  }
}

Resulting SQL Query

SELECT * FROM BLOG WHERE title like ? AND author_name like ?

where Tag

The where tag dynamically adds the WHERE keyword at the beginning of the generated SQL statement and removes the first AND or OR keyword.

SELECT * FROM BLOG 
<where>
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null">
    AND author_name like #{author.name}
  </if>
</where>

Parameter Example

{
  "title": "MyBlog",
  "author": {
    "name": "AuthorName"
  }
}

Resulting SQL Query

SELECT * FROM BLOG WHERE title like ? AND author_name like ?

set Tag

The set tag is used for dynamically updating SQL statements, intelligently adding the SET keyword and removing the final comma.

UPDATE BLOG
<set>
  <if test="title != null">title = #{title},</if>
  <if test="author != null">author_name = #{author.name},</if>
</set>
WHERE id = #{id}

Parameter Example

{
  "title": "MyBlog",
  "author": {
    "name": "AuthorName"
  },
  "id": 123
}

Resulting SQL Query

UPDATE BLOG SET title = ?, author_name = ? WHERE id = ?

4. foreach Tag

The foreach tag is primarily used to iterate over collections, often employed to build IN clauses.

SELECT * FROM BLOG
WHERE id in
<foreach item="item" index="index" collection="list" open="(" separator="," close=")">
  #{item}
</foreach>

Parameter Example

{
  "list": [1, 2, 3, 4, 5]
}

Resulting SQL Query

SELECT * FROM BLOG WHERE id in (?, ?, ?, ?, ?)

5. bind Tag

The bind tag creates a variable and binds the value of an OGNL expression to that variable.

<bind name="pattern" value="'%' + title + '%'" />
SELECT * FROM BLOG
WHERE title LIKE #{pattern}

Parameter Example

{
  "title": "MyBlog"
}

Resulting SQL Query

SELECT * FROM BLOG WHERE title LIKE ?

SQL Parameter Injection

In dynamic SQL, both #{} and ${} are used for parameter substitution, but they differ significantly in how they handle parameters.

Usage of #{}

1. Precompiled Processing: #{} is preprocessed, equivalent to the ? placeholder in JDBC. It replaces #{} with ?, performs precompilation, and sets parameters via PreparedStatement.
2. Automatic Escaping: #{} effectively prevents SQL injection by automatically escaping input parameters.
3. Type Handling: #{} handles type conversions for incoming parameters.

Example:

SELECT * FROM BLOG WHERE title LIKE #{title}

With title = "MyBlog", the resulting SQL becomes:

SELECT * FROM BLOG WHERE title LIKE ?

Usage of ${}

1. Direct Substitution: ${} directly substitutes the parameter value into the SQL statement without precompilation. Therefore, caution is required when using ${} to avoid SQL injection risks.
2. No Escaping: ${} does not escape input parameters, making it suitable for dynamically generating table names or column names.
3. No Type Conversion: ${} bypasses type handling and uses the parameter's toString() method directly.

Example:

SELECT * FROM BLOG WHERE title LIKE '${title}'

With title = "MyBlog", the resulting SQL becomes:

SELECT * FROM BLOG WHERE title LIKE 'MyBlog'

Recommended Usage Scenarios

• Use #{}: In most cases, especially when dealing with user inputs, prioritize #{} to prevent SQL injection.
• Use ${}: Use ${} when dynamically generating table or column names, ensuring that the input parameters are safe.

System Built-in Variables

DBAPI internally includes the variable __clientId, which retrieves the current client ID and can be directly used as an SQL parameter.

Usage Example:

SELECT * FROM users WHERE client_id = #{__clientId}

Special Character Escaping

Escaping Less-than Signs

Less-than signs < in SQL statements must be escaped as <.

Incorrect Example:

-- Incorrect: Causes XML parsing errors
SELECT * FROM users WHERE age < 18

Correct Example:

-- Correct: Uses escaped characters
SELECT * FROM users WHERE age < 18