Согласованность написания SQL‑запросов

Синтаксис тегов динамического SQL

Функция динамического SQL позволяет динамически формировать SQL‑запросы на основе заданных условий. С помощью специальных тегов можно легко реализовать сложную логику запроса. Ниже представлены основные теги динамического SQL и их использование.

1. Тег if

Тег if используется для проверки условий, аналогично оператору if в Java.

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

Пример параметров

{
  "title": "MyBlog"
}

Реальный SQL‑запрос после обработки

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

2. Теги choose, when, otherwise

Эти теги аналогичны оператору switch в 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>

Пример параметров

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

Реальный SQL‑запрос после обработки

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

3. Теги trim, where, set

Эти теги используются для обработки префиксов, суффиксов и союзов в SQL‑запросах.

Тег trim

Тег trim позволяет настраивать удаление лишних символов из строки.

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>

Пример параметров

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

Реальный SQL‑запрос после обработки

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

Тег where

Тег where динамически добавляет ключевое слово WHERE в начало сформированного запроса и удаляет первое ключевое слово AND или OR.

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>

Пример параметров

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

Реальный SQL‑запрос после обработки

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

Тег set

Тег set используется для динамического обновления запроса: он автоматически добавляет ключевое слово SET и удаляет последнюю запятую.

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

Пример параметров

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

Реальный SQL‑запрос после обработки

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

4. Тег foreach

Тег foreach используется для перебора коллекций и часто применяется при формировании условий типа IN.

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

Пример параметров

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

Реальный SQL‑запрос после обработки

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

5. Тег bind

Тег bind создает переменную и привязывает к ней значение выражения OGNL.

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

Пример параметров

{
  "title": "MyBlog"
}

Реальный SQL‑запрос после обработки

SELECT * FROM BLOG WHERE title LIKE ?

Подключение параметров в SQL

В динамическом SQL как #{}, так и ${} используются для подключения параметров, однако между ними существуют важные различия.

Использование #{}

1. Предварительная компиляция: #{} используется для предварительной компиляции, что соответствует символу ? в JDBC. Значение #{} заменяется на ?, затем выполняется предварительная компиляция, а параметры устанавливаются через PreparedStatement.
2. Автоматическая экранировка: #{} эффективно защищает от SQL‑инъекций, поскольку автоматически экранирует передаваемые данные.
3. Обработка типов: #{} умеет преобразовывать типы передаваемых данных.

Пример:

SELECT * FROM BLOG WHERE title LIKE #{title}

После подстановки параметра title = MyBlog получается следующий SQL‑запрос:

SELECT * FROM BLOG WHERE title LIKE ?

Использование ${}

1. Прямая подстановка: ${} просто подставляет значение параметра в SQL‑запрос без предварительной компиляции. Поэтому при использовании ${} необходимо быть осторожным, чтобы избежать риска SQL‑инъекций.
2. Без экранирования: ${} не экранирует передаваемые данные, что делает его подходящим для использования с динамическими именами таблиц или столбцов.
3. Отсутствие преобразования типов: ${} не проходит через обработчик типов и напрямую использует метод toString() передаваемого значения.

Пример:

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

После подстановки параметра title = MyBlog получается следующий SQL‑запрос:

SELECT * FROM BLOG WHERE title LIKE 'MyBlog'

Рекомендации по выбору

• Используйте #{} — в большинстве случаев, особенно при работе с пользовательскими данными, следует отдавать предпочтение #{} для предотвращения SQL‑инъекций.
• Используйте ${} — применяйте ${} только тогда, когда требуется динамическое формирование имен таблиц или столбцов, при этом гарантируя безопасность передаваемых данных.

Встроенные системные переменные

В системе DBAPI уже встроена переменная __clientId, которая используется для получения ID текущего клиента и может напрямую применяться в качестве параметра SQL‑запроса.

Пример использования:

SELECT * FROM users WHERE client_id = #{__clientId}

Экранирование специальных символов

Экранирование знака «меньше»

Знак «меньше» < в SQL‑запросах должен быть экранирован как <.

Неправильный пример:

-- Ошибка: приводит к ошибке разбора XML
SELECT * FROM users WHERE age < 18

Правильный пример:

-- Правильно: использовать экранированный символ
SELECT * FROM users WHERE age < 18