はじめに
この記事は、最先端のテクノロジーを紹介するものではありません。セキュリティ対策やパフォーマンス改善、正規化…そういう高度な（？）話題ですらありません。そんなものとは無縁の、泥のような開発現場で腐った魚のような目をした日々を送っているVBプログラマーの皆様にお送りする、「毎日をちょっとだけ楽しくする」TIPS記事です。
あなたは今、SQLのSELECT句を文字列で生成するプログラムを書いているとします。「今どき生SQLとかだっさｗｗ」とか思われるかもしれませんが、そんな現場は今でも普通にゴロゴロしています。
もしあなたが古いVB6プログラマーだと、このようなソースを書いているかもしれません（Qiitaの熱心な読者の皆さんは「まさかｗ」と思われるかもしれませんが、まさに今私がメンテナンスしているソースコードがこういうソースのオンパレードなのです）。
Dim sql As String
sql = "SELECT "
sql += " ID," '社員ID
sql += " NAME," '社員名
sql += " AGE," '年齢
しかし、この書き方は、「フィールドIDを列挙する」という目的と、「それをカンマ区切りで結合してSQLとして整形する」という2つの事を混在させている為、メンテナンス性が大きく下がっています。カンマの有無で面倒なバグを混入させやすいといった問題もあります。
リストを使って構造を切り分ける
私は最近、SQLの構築はこのように書いています（巷にある素敵なSQL構築ライブラリを使いたいのですが、そういうものを勝手に使うことは許されない環境なのです。皆さんも、そうですよね？）。
Dim sqlSelect = {
"ID", '社員ID
"NAME", '社員名
"AGE" '年齢
}
Dim sql = "SELECT " + String.Join(",", sqlSelect)
String.Joinは、第二引数のリストを、第一引数の文字列を間に挟んで全て連結するStringのstaticメソッドです。
つまり、「SELECT句のフィールドリストを作る」事と、「それをカンマ区切りで繋げてSQLの形にする」事を、分けているわけです。こうする事で、様々なメリットが生じます。まずなんといっても、カンマの有無を気にしなくて良いのが最高です。
ローカル変数の宣言を省略したい人は、次のように書く事もできるでしょう。
Dim sql = "SELECT " + String.Join(",", {
"ID", '社員ID
"NAME", '社員名
"AGE" '年齢
})
古いIDEで、リスト中のコメントがエラーになる場合
但し、最初に書いたやり方もどちらも、この書き方には一つ問題があって、それはVS2010のような古いIDEでは、リストの初期化子の途中にコメントを書くとエラーになってしまうという点です。コメントが不要な人はそれでも良いかもしれませんが、実際の現場では、「ちょっとこの行だけコメントアウト」といった使い方もする為、現実的な運用で「sql += 」の形に劣ってしまいます。（VS2018ならば、大丈夫です）
そこで、ちょっと面倒ですが、古いIDEを使っている場合には、次のように書くと良いでしょう（実は私も、VS2010とかの古いIDEで仕事をしなければならない事が多いです）。
Dim sqlSelect As New List(Of String)
With sqlSelect
.Add("ID") '社員ID
.Add("NAME") '社員名
.Add("AGE") '年齢
End With
Dim sql = "SELECT " + String.Join(",", sqlSelect)
このやり方なら、各行ごとにコメントを付ける事ができます。
「.Addを連ねるのとsql += を連ねるのと何が違うんだ、むしろ記述量が増えているじゃないか」と思われるかもしれませんが、やってみると分かるのですが、カンマの有無のバグから解放されるというのは、とても気持ちの良いものですし、ちょっと1行コメントアウト、等も気軽に行えるようになって開発効率も格段に向上します。
FROM句、WHERE句、GROUP BY句、ORDER BY句
同様のやり方でFROM句やWHERE句、GROUP BY句、ORDER BY句等も書く事ができます。基本はSELECT句と同様のやり方ですが、最もシンプルなやり方はこんな感じになります。（古いIDEだとコメント部分でエラーが出ます）
FROM句やWHERE句なども、ただの文字列ではなく意味のある情報の単位として扱ってやる必要があります。その為にはやはり、項目単位でリスト化して扱うのが良い方法です。
最初に定義しているStringJoin拡張メソッドは、String.Joinをリスト型の拡張メソッドとして使える形にするものです。{"a","b","c"}というリストを" AND "というセパレータ文字列で連結して"a AND b AND c"という文字列にしたい、というようなケースで活躍します。
<System.Runtime.CompilerServices.Extension()>
Function StringJoin(Of T)(list As IEnumerable(Of T), separator As String) As String
Return String.Join(separator, list)
End Function
Sub Main()
Dim sql =
"SELECT " + {
"tbl_a.col1", 'テーブルA
"tbl_b.col1" 'テーブルB
}.StringJoin(",") +
" FROM " + {
"tbl_a LEFT JOIN tbl_b ON " + {
"tbl_a.id = tbl_b.id",
"tbl_b.num1 <> 0"
}.StringJoin(" AND ")
}.StringJoin(",") +
" WHERE " + {
"tbl_a.type1 = 5",
"tbl_a.val1 >= 12",
"tbl_a.val1 <= 0"
}.StringJoin(" AND ") +
" ORDER BY " + {
"tbl_a.val1",
"tbl_b.val1"
}.StringJoin(",")
End Sub
LEFT JOINあたりがちょっと面倒ですが、Oracleを使っている方は、(+)を使って全部WHERE句に書くというのも手でしょう（それができない事もあると思いますが）。
List型変数を使って管理するパターン
上述のようにスッキリ書く事もできますが、先に紹介したList(Of String)型の変数に.Addしていくやり方も充分利点があって、最近は、少し複雑なSQL文字列の構築をする際には、最初からこちらのやり方を使っています。なぜなら、このやり方だと、SELECT句のリストやWHERE句の条件式などを、If文を使って簡単に編集できる為です。
<System.Runtime.CompilerServices.Extension()>
Function StringJoin(Of T)(list As IEnumerable(Of T), separator As String) As String
Return String.Join(separator, list)
End Function
Function MakeSQL(param1 As Integer) As String
Dim sqlSelect As New List(Of String)
With sqlSelect
.Add("ID") '社員ID
.Add("NAME") '社員名
.Add("AGE") '年齢
If param1 > 0 Then
.Add("DATA1") '関連データ1
End If
End With
Dim sql = "SELECT " + sqlSelect.StringJoin(",")
:
Return sql
End Sub
こんな風にしておけば、長大なSELECT句を複雑なIF文で構築させた挙句、「SELECT句にDATA1列がある場合はORDER BYにもそれ追加しといて」みたいな面倒な事を言われても、「あいよ～」の掛け声と共に30秒で支度完了です。
Dim sqlOrderBy As New List(Of String)
With sqlOrderBy
.Add("ID")
If sqlSelect.Contains("DATA1") Then
.Add("DATA1")
End If
End With
SQL文字列を構築するライブラリには多種多様なものがありますが、仕事で勝手に使うことができないケースも多いでしょう。しかし、このように自分で多少工夫する程度なら問題ない場合には、気持ちよく仕事ができるようにしていきたいですね。
追記: WHERE句にOR条件があった場合
記事を読んでくださった@asuki_syobonさんから、「WHERE句のAND条件にORを含めたい時の為に、()で囲うオプションもあった方が良い」というご指摘を頂きました。現場からの声がとても嬉しいです。
私もこのやり方で大量のSQLを日々捌いているので、仰るようなケースにもたくさん遭遇しています。今回の記事では「考え方のご紹介」と思って省略したのですが、そういう場合に「どうすればいいんだろう」と自分で考えなければならないのも申し訳ないので、私のやり方をご紹介します。といっても、asukiさんと同じやり方です。
一度、かなりたくさんの拡張メソッド（LEFT JOINを作る為の専用ヘルパーメソッドとか）を作り始めた事もあったのですが、結局そんな事をすれば、独自のソースコードすぎて自分以外の人にはメンテナンスできなくなってしまうので、この程度に留め、「どんな場合でも対応できるようにする」のは諦めています。
参考にして頂ければ幸いです。
Sub Main()
Dim sql =
"SELECT " + {
"tbl_a.col1", 'テーブルA
"tbl_b.col1" 'テーブルB
}.StringJoin(",") +
" FROM " + {
"tbl_a LEFT JOIN tbl_b ON " + {
"tbl_a.id = tbl_b.id",
"tbl_b.num1 <> 0"
}.StringJoin(" AND ")
}.StringJoin(",") +
" WHERE " + {
"tbl_a.type1 = 5",
"tbl_a.val1 >= 12",
"tbl_a.val1 <= 0",
"( tbl_a.val2 is null OR tbl_a.val2 > 100 )", '直接()の中にORを書く（これが一番シンプル）
{
"tbl_a.val3 is null",
"tbl_a.val3 > 100"
}.StringJoin(" OR ").QuoteParentheses() 'ORの部分をリスト化する（括弧で囲う拡張メソッドを使って可読性を上げる）
}.StringJoin(" AND ") +
" ORDER BY " + {
"tbl_a.val1",
"tbl_b.val1"
}.StringJoin(",")
End Sub
<System.Runtime.CompilerServices.Extension()>
Function StringJoin(Of T)(list As IEnumerable(Of T), separator As String) As String
Return String.Join(separator, list)
End Function
<System.Runtime.CompilerServices.Extension()>
Function QuoteParentheses(str As String) As String
Return str.Quote("(", ")")
End Function
<System.Runtime.CompilerServices.Extension()>
Function Quote(str As String, Optional quotation As String = "'", Optional after As String = Nothing) As String
If after Is Nothing Then
after = quotation
End If
Return quotation + str + after
End Function
List(Of String)型の変数を用いた典型的なパターン
古いIDEをお使いの方や、If文などを使って複雑なSQLを構築する方、または、単純にこちらの方が好きだという方向けに、List(Of String)型の変数を用いた典型的なパターンについてもこちらに置いておきます。自由にお使い下さい。コツは、「あまりテクニカルに書こうとしすぎない」ことですｗ メンテナンスするのは自分だけではないですからね…。これでも、一般的なVBのソースから見れば十分テクニカルだとは思うのですが、深い知識がなくともなんとか読めるのではないかと思います。
Sub Main()
'SELECT句
Dim sqlSelect As New List(Of String)
With sqlSelect
.Add("tbl_a.col1")
.Add("tbl_b.col1")
End With
'FROM句
Dim sqlFrom As New List(Of String)
With sqlFrom
.Add("tbl_a LEFT JOIN tbl_b ON " + {
"tbl_a.id = tbl_b.id",
"tbl_b.num1 <> 0"
}.StringJoin(" AND ")
)
End With
'WHERE句
Dim sqlWhere As New List(Of String)
With sqlWhere
.Add("tbl_a.type1 = 5")
.Add("tbl_a.val1 >= 12")
.Add("tbl_a.val1 <= 0")
.Add("( tbl_a.val2 is null OR tbl_a.val2 > 100 )")
.Add(
{
"tbl_a.val3 is null",
"tbl_a.val3 > 100"
}.StringJoin(" OR ").QuoteParentheses()
)
End With
'GROUP BY句
Dim sqlGroupBy As New List(Of String)
With sqlOrderBy
.Add("tbl_a.val1")
.Add("tbl_b.val1")
End With
'ORDER BY句
Dim sqlOrderBy As New List(Of String)
With sqlOrderBy
.Add("tbl_a.val1")
.Add("tbl_b.val1")
End With
Dim sql = "SELECT " + sqlSelect.StringJoin(",") +
" FROM " + sqlFrom.StringJoin(",") +
If(sqlWhere.Count > 0, " WHERE " + sqlWhere.StringJoin(" AND "), "") +
If(sqlGroupBy.Count > 0, " GROUP BY " + sqlGroupBy.StringJoin(","), "") +
If(sqlOrderBy.Count > 0, " ORDER BY " + sqlOrderBy.StringJoin(","), "")
End Sub
この記事に出てきたヘルパー拡張メソッドの定義
最後に、これらのヘルパー拡張メソッドをまとめたモジュール定義を下に貼っておきますので、ご自由に改変するなりしてお使い下さい。
Module MySQLBuilder
<System.Runtime.CompilerServices.Extension()>
Function StringJoin(Of T)(list As IEnumerable(Of T), separator As String) As String
Return String.Join(separator, list)
End Function
<System.Runtime.CompilerServices.Extension()>
Function QuoteParentheses(str As String) As String
Return str.Quote("(", ")")
End Function
<System.Runtime.CompilerServices.Extension()>
Function Quote(str As String, Optional quotation As String = "'", Optional after As String = Nothing) As String
If after Is Nothing Then
after = quotation
End If
Return quotation + str + after
End Function
End Module
おまけ: SQLに見やすさの為の改行を入れなくて良い
SQL文字列の構築の際に、1行ごとに改行コードを入れている人、又はそういうプログラムを見た事のある人も多いかもしれません。
これはデバッグの際にSQLのソースが読みやすい為そうしているものと思いますが、本質であるVBのソースコードの可読性や記述性が低くなってしまいます。
そんなことをしなくても、SQL自動整形ツールがたくさんあります。OracleのSQL Developerであれば、コードを張り付けてCtrl+Shit+F7で整形できますし、SQL Serverにもそういうアドインがあるようです。Common SQL Environment(CSE)をお使いならば、Ctrl+Qで一発です。
そういうツールを使うようにして、わざわざVBのソースコードで改行を入れるなどという前時代的な事をしないようにしましょう。
おまけ: パラメータ付きクエリ
@jnchito さん、@2celeb さんよりアドバイス頂きまして、パラメータ付きクエリのことを追記させて頂きます。記事への言及ありがとうございます！ 記事書いて良かったなぁ。
ひょっとすると、本当に古いVB6の頃の頭で停まってしまっているプログラマだと、次のようなコードを書いてしまうかもしれません。
'WHERE句
Dim sqlWhere As New List(Of String)
With sqlWhere
.Add("tbl_a.type1 = " + type1Int.toString())
.Add("tbl_a.type2 = '" + type2String + "'")
End With
もちろん、ダメです。type1Int.toString() はまだ良いとしても、type2Stringはダメです。しっかりサニタイジング（無害化）が必要です。ただ、サニタイジングについては普通、プロジェクトで規約が出来ていると思いますので、一般的な方法を探す前に、プロジェクトリーダー等に確認をするべきでしょう。あなたが自由なプログラミングをしている場合には、それこそQiitaなどで「SQL サニタイジング .NET」などで検索すれば山ほど良い方法が見つかるはずです。
サニタイジングをすれば、もちろんこのプラクティスはそのまま使えます。
そして、もっと良い方法は、パラメータ付きクエリを使う方法です。例えばSQL Serverを利用してSqlConnectionでコネクションをオープンしている場合、
'WHERE句
Dim sqlWhere As New List(Of String)
With sqlWhere
.Add("tbl_a.type1 = @type1")
.Add("tbl_a.type2 = @type2")
End With
こうしておき、以下のようにパラメータを後から追加します。
command.CommandText = sql;
command.Parameters.Add(new SqlParameter("@type1", type1));
command.Parameters.Add(new SqlParameter("@type2", type2));
Dim reader As SqlDataReader = command.ExecuteReader()
:
OracleのAPIを使っている場合は、@type1 ではなく :type1 とします。
パラメータ付きクエリの話は、さすがに今の現場では無視される事は無いと思いましたので書いておりませんでしたが、「一言書いた方が良い」というアドバイスを頂き、追記致しました。私はこの所ずっと、プロジェクトで提供されているDBライブラリを使ってOracle用のパラメータ付きクエリを作ったり、サニタイジングしたりしていますので、標準的な方法については今、ネットで調べて追記しています。何か間違っている箇所などありましたらご指摘頂ければ幸いです。
皆様のコーディング環境が少しでも良くなることを祈っております。