Webシステムに最適！配列を活用した賢いSQLの作り方

プログラム中でSQLを合成する場合、配列を活用されてはいかがでしょうか。SQLは、文字列なので簡単に作れるものの、それが正しく動作するかチェックするのは簡単ではありません。ミスしにくい作り方が望まれます。そして、その方法としておすすなのが、配列を使った方法です。今回は、配列を使ったSQLの作り方を詳しく解説します。

SQLをプログラムに埋め込むには

Webシステムを構築する際、データベース管理ソフトが欠かせません。そして、プログラム側からデータベース管理ソフトを利用する際、既に用意してあるのSQLをそのまま使ったり、プログラムの中でSQLを合成するなど、データベースの利用の仕方によって、埋め込み方が変わります。

次から、SQLをプログラムに埋め込む方法について解説します。

全てのデータを取得する

Webシステムでデータベース管理ソフトを使う最も簡単な方法は、格納されている全てのデータを取り出し、必要なデータの抽出や加工をすべてプログラム側で行う方法です。

このようなケースでは、テーブル内の全てのデータを取り出すSQLを1つ準備すれば十分です。プログラム中でSQLを合成する手間がかからず、プログラマーはデータの加工に専念できます。

しかし、扱うデータの量が多ければ、それだけ処理が遅くなるのが欠点です。そのため、このような取得方法が使えるのは特殊なケースのみと考えてください。

文字列のパーツを用意して合成する

SQLは、比較的簡単な文字列なので、プログラムで合成するのは、それほど難しくありません。検索条件となる文字列を変数に格納して、SQLのパーツを格納した文字列と変数を格納した文字列とを結合しれば、SQLが作れます。また、SQL合成用に、専用のファンクションを用意するケースもあります。

しかし、SQLの文字をスペースで区切っただけの書き方は、ミスしやすいフォーマットです。簡単な修正のつもりが、空白を入れ忘れてしまえば、システムは動作しません。そして、このような場合、原因が解らず、調査に時間がかかったりします。

配列を使ってSQLを合成する

先ほど説明したように、SQLの合成は意外にミスしやすく、SQLが複雑になればミスした箇所を探すのに手間がかかります。そこで、おすすめしたいのが、配列にSQLを構成するパーツを追加していき、最後に空白で区切った文字列に変換して、それをSQLとして利用する方法です。

なお、Webシステムで使われることの多いプログラム言語では、配列を追加する機能や、配列から文字列に変換する機能があります。ぜひ、配列を活用してSQLを合成し、ミスを減らしましょう。

SQLを配列で合成するメリット

プログラムの中でSQLを合成する場合、その処理自体を後から修正できるように工夫することで、SQLに関する余計なトラブルを防げます。では、どういった不具合があり、それに配列で合成する方法にメリットがあるのでしょうか。次から、簡単に解説します。

SQL作成時にミスしやすい点

SQLをよく編集する方でも、コピーとペーストを使って手を抜いたりすると、ミスして動作しないSQLを作ってしまうことがあります。そのようなミスで、よくやりがちなのが、スペースの入れ忘れと、シングルクオートまたはダブルクオートを付け忘れるケースです。

空白は、長いSQLを合成する場合、変数の前後に空白が抜けることがあります。また、SQLでは、検索条件で文字を指定する場合には、シングルクオートまたはダブルクオートを使いますが、これを付け忘れるケースもあります。

いぜれにしても、合成したSQLを、データベース管理ソフトのクライアントツールで実行すれば、エラーメッセージですぐ解る内容です。しかし、システムに組み込まれてしまうと、エラーの原因がすぐには解りません。

プログラムで作るSQLの問題点

人が書いたSQLは、サブクエリーなどを別の行にするなど、読み易いように改行されています。しかし、プログラムで作る場合、そのような手間は必要ありません。プログラムの変数を使い、正しく動くように作られた1行のSQLを合成するだけで十分です。

しかし、SQLの一部を修正することになった場合、簡単だからと言って手を抜いてしまうと、先ほど挙げたような、空白が抜けたり、シングルクオートまたはダブルクオートが抜けるといったミスが発生します。しかも、その場合、動作しない原因を調べるのはかなり面倒です。

配列を使ってSQLを合成する例

サブクエリーが複数あるような長いSQLを合成する場合、プログラムで合成する場合でも、人が読みやすいように工夫した例を次に紹介します。

サブクエリーを使ったSQLの例

SELECT 
 age, 
 count 
FROM 
(SELECT 
  age,
  COUNT(age) as count 
 FROM staffs GROUP BY age
) as ageLists;

プログラムで文字列を追加して合成する例

$sql = "SELECT ";
$sql = $sql + "age";
$sql = $sql + ", ";
$sql = $sql + "count ";
$sql = $sql + "FROM ";
$sql = $sql + "(SELECT ";
$sql = $sql + "age";
$sql = $sql + ", ";
$sql = $sql + "COUNT(age) as count ";
$sql = $sql + "FROM staffs GROUP BY age ";
$sql = $sql + ") as ageLists;";

この書き方では、SQLを構成する文字の最後に空白が入っていますが、それが抜けてしまうとエラーになります。また、ダブルクオートで文字を指定して条件を追加しようとすると、見づらいのでミスしてもなかなか気が付きません。

配列を使った場合、例えばrubyなら次のように書けます。

base = []
base.push("SELECT");
base.push("age");
base.push(",");
base.push("count");
base.push("FROM");
base.push("(SELECT");
base.push("age");
base.push(",");
base.push("COUNT(age) as count");
base.push("FROM staffs GROUP BY age");
base.push(") as ageLists;");
sql = base.join(' ')

プログラム言語別の作り方

先ほど、Rubyの例を紹介しましたが、この方法は他のプログラム言語でも使えます。次から、Webサイト作成でよく使われるプログラム言語のうち、JavaとPython、PHPの使い方を紹介します。

Javaにおける使い方の例

Javaでは、要素の数が固定の配列(Array)ではなく、後から追加できるリスト(List)を使います。

Javaのリストを使ったSQL合成の例

List list = new ArrayList();
list.add("select");
list.add("*");
list.add("from");
list.add("test");
list.add(";");
String sql = String.join(" ", list);

Pythonにおける使い方の例

Pythonは、Rubyと同じような書き方が可能です。

Pythonの配列を使ったSQL合成の例
base = []
base.append("select")
base.append("*")
base.append("from")
base.append("test")
base.append(";")
sql = " ".join(base)

PHPにおける使い方の例

PHPは、array_push()関数を使います。RubyやPythonに比べると、見づらいかもしれません。

Pythonの配列を使ったSQL合成の例
$base = array();
array_push($base, "select");
array_push($base, "*");
array_push($base, "from");
array_push($base, "test");
$sql = implode(" ", $base);

まとめ

これまで紹介したように、プログラムでSQLを合成する際、配列を活用することで、文字列に追加していく方法より見やすく、ミスしにくい合成が可能です。もし、プログラム中にSQLを書く方法を見直ししたいと考えているのでしたら、ぜひ、参考にしてください。