SQL記述規則

動的SQLタグの構文

動的SQL機能により、条件に応じてSQL文を動的に構築できます。特定のタグを使用することで、複雑なSQLロジックを容易に実現できます。以下は、動的SQLでよく使用されるタグとその使い方です。

1. ifタグ

ifタグは条件判定に使用され、Javaのif文に似ています。

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タグ

これらのタグはJavaのswitch文に似ています。

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タグは生成されるSQL文の先頭に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システムには、現在のクライアントIDを取得するための__clientIdという内蔵変数が用意されており、これを直接SQLパラメータとして利用できます。

使用例：

SELECT * FROM users WHERE client_id = #{__clientId}

特殊文字のエスケープ

小なり記号のエスケープ

SQL文内の小なり記号<は、<にエスケープする必要があります。

誤った例：

-- エラー：XML解析エラーを引き起こす
SELECT * FROM users WHERE age < 18

正しい例：

-- 正しい：エスケープ文字を使用
SELECT * FROM users WHERE age < 18