DBAPI

# Dynamic SQL Tag Syntax

The dynamic SQL feature allows us to dynamically construct SQL statements 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 judgment, similar to the if statement in Java.

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

# Parameter Example

{
  "title": "MyBlog"
}

# Parsed Real SQL

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

# 2. choose, when, otherwise Tags

These tags are similar to the 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"
  }
}

# Parsed Real SQL

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 statements.

# trim Tag

The trim tag can be used 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"
  }
}

# Parsed Real SQL

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

# where Tag

The where tag will dynamically add the where keyword to the generated SQL statement and delete 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"
  }
}

# Parsed Real SQL

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

# set Tag

The set tag is used for dynamic update statements, can intelligently add the SET keyword, and remove the last 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
}

# Parsed Real SQL

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

# 4. foreach Tag

The foreach tag is mainly used to iterate over collections, commonly used to build IN conditions.

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]
}

# Parsed Real SQL

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

# 5. bind Tag

The bind tag is used to create a variable and bind the value of the OGNL expression to that variable.

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

# Parameter Example

{
  "title": "MyBlog"
}

# Parsed Real SQL

SELECT * FROM BLOG WHERE title LIKE ?

# SQL Parameter Introduction

In dynamic SQL, both #{} and ${} are used for parameter referencing, but they have some key differences in parameter handling.

# Usage of #{}

  1. Precompiled Processing: #{} is used for precompiled processing, equivalent to the ? placeholder in JDBC. It will replace #{} with ?, perform precompilation, and then set parameters through PreparedStatement.
  2. Automatic Escaping: #{} can effectively prevent SQL injection because it automatically escapes incoming parameters.
  3. Type Handling: #{} can handle type conversion of incoming parameters.

Example:

SELECT * FROM BLOG WHERE title LIKE #{title}

After passing parameter title = MyBlog, the parsed SQL is:

SELECT * FROM BLOG WHERE title LIKE ?

# Usage of ${}

  1. Direct Replacement: ${} directly replaces the parameter value into the SQL statement without precompilation processing. Therefore, when using ${}, you need to be careful to avoid SQL injection risks.
  2. No Escaping: ${} does not escape incoming parameters, suitable for dynamic table names or column names.
  3. No Type Conversion: ${} does not go through type handlers, directly uses the toString() method of the parameter.

Example:

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

After passing parameter title = MyBlog, the parsed SQL is:

SELECT * FROM BLOG WHERE title LIKE 'MyBlog'

# Usage Scenario Recommendations

  • Use #{}: In most cases, especially scenarios involving user input, #{} should be prioritized to prevent SQL injection.
  • Use ${}: Use ${} when you need to dynamically generate table names or column names and ensure the safety of incoming parameters.