Normas de escritura SQL

Sintaxis de las etiquetas de SQL dinámico

La funcionalidad de SQL dinámico nos permite construir sentencias SQL de forma condicional y dinámica. Mediante el uso de etiquetas específicas, podemos implementar fácilmente lógicas SQL complejas. A continuación se presentan las etiquetas más utilizadas en SQL dinámico y sus respectivos usos.

1. Etiqueta if

La etiqueta if se utiliza para realizar evaluaciones condicionales, similar a la instrucción if en Java.

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

Ejemplo de parámetros

{
  "title": "MyBlog"
}

SQL real después del procesamiento

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

2. Etiquetas choose, when y otherwise

Estas etiquetas funcionan de manera similar a la instrucción switch en 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>

Ejemplo de parámetros

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

SQL real después del procesamiento

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

3. Etiquetas trim, where y set

Estas etiquetas permiten gestionar prefijos, sufijos y conectores en las sentencias SQL.

Etiqueta trim

La etiqueta trim se utiliza para personalizar la truncación de cadenas.

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>

Ejemplo de parámetros

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

SQL real después del procesamiento

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

Etiqueta where

La etiqueta where añade automáticamente la palabra clave WHERE al inicio de la consulta generada y elimina el primer AND o 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>

Ejemplo de parámetros

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

SQL real después del procesamiento

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

Etiqueta set

La etiqueta set se emplea para actualizar dinámicamente las sentencias, añadiendo inteligentemente la palabra clave SET y eliminando la última coma.

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

Ejemplo de parámetros

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

SQL real después del procesamiento

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

4. Etiqueta foreach

La etiqueta foreach se utiliza principalmente para recorrer colecciones, siendo muy útil para construir condiciones IN.

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

Ejemplo de parámetros

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

SQL real después del procesamiento

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

5. Etiqueta bind

La etiqueta bind permite crear una variable y vincular el valor de una expresión OGNL a dicha variable.

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

Ejemplo de parámetros

{
  "title": "MyBlog"
}

SQL real después del procesamiento

SELECT * FROM BLOG WHERE title LIKE ?

Incorporación de parámetros en SQL

En SQL dinámico, tanto #{} como ${} se utilizan para referenciar parámetros, pero presentan diferencias clave en su manejo.

Uso de #{}

1. Procesamiento precompilado: #{} se utiliza para el procesamiento precompilado, equivalente al placeholder ? en JDBC. Reemplaza los símbolos #{} por ?, realiza la precompilación y luego establece los parámetros mediante PreparedStatement.
2. Escapado automático: #{} previene eficazmente la inyección SQL al aplicar un escapado automático a los valores ingresados.
3. Manejo de tipos: #{} puede realizar conversiones automáticas de tipo para los parámetros recibidos.

Ejemplo:

SELECT * FROM BLOG WHERE title LIKE #{title}

Tras ingresar el parámetro title con valor MyBlog, la consulta resultante es:

SELECT * FROM BLOG WHERE title LIKE ?

Uso de ${}

1. Reemplazo directo: ${} sustituye directamente el valor del parámetro en la sentencia SQL sin realizar ningún procesamiento previo. Por lo tanto, debe emplearse con precaución para evitar riesgos de inyección SQL.
2. Sin escapado: ${} no aplica ningún tipo de escapado a los valores introducidos, siendo adecuado para nombres de tablas o columnas dinámicos.
3. Sin conversión de tipos: ${} no realiza ninguna transformación de tipo; simplemente utiliza el método toString() del parámetro.

Ejemplo:

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

Tras ingresar el parámetro title con valor MyBlog, la consulta resultante es:

SELECT * FROM BLOG WHERE title LIKE 'MyBlog'

Recomendaciones sobre el uso

• Utilice #{}: En la mayoría de los casos, especialmente cuando se trata de entradas procedentes del usuario, es preferible usar #{} para prevenir la inyección SQL.
• Utilice ${}: Emplee ${} únicamente cuando sea necesario generar dinámicamente nombres de tablas o columnas y esté seguro de que los valores introducidos son seguros.

Variables internas del sistema

El sistema DBAPI incorpora internamente la variable __clientId, que permite obtener el ID del cliente actual y puede utilizarse directamente como parámetro en consultas SQL.

Ejemplo de uso:

SELECT * FROM users WHERE client_id = #{__clientId}

Escapado de caracteres especiales

Escapado del signo menor <

El signo menor < utilizado en las sentencias SQL debe ser escapado como <.

Ejemplo incorrecto:

-- Error: Provoca un error de análisis XML
SELECT * FROM users WHERE age < 18

Ejemplo correcto:

-- Correcto: Utiliza el carácter escapado
SELECT * FROM users WHERE age < 18