はじめてのDBTools.h++ (part-4)
JOIN と WHERE
SQLによるテーブルの検索の際、多くの場合検索条件をWHERE節に記述します。
たとえば2つのテーブル:
|
|
があり、'大阪府'に住む人を探し出すSQLは
SELECT person.name
FROM person, address
WHERE person.addr_id = address.addr_id
AND
address.pref = '大阪府'
となるでしょう。
ここで、WHERE節に指定したふたつの条件文はそれぞれ別の意味を持っています。
すなわち、
person.addr_id =address.addr_id
は、ふたつのテーブルpersonとaddressをひとつのテーブルに結合する条件であり、
address.pref = '大阪府'
は、結合されたテーブルから列を得るための検索条件です。
結合条件と検索条件をひとつのWHERE節に混在させるより、JOINによる結合をFROM節に記述することで、WHERE節には検索条件だけが残り、すっきりしたSQL文となります。
SELECT person.name
FROM person INNER JOIN address
ON person.addr_id = address.addr_id
WHERE address.pref = '大阪府'
こう書けば、"addr_idで結合されたテーブルから'大阪府'を見つけ出す"という検索条件がはっきりします。DBTools.h++で書けば以下のようになります。
RWDBDatabase db = ...;
RWDBTable person = db.table("person");
RWDBTable address = db.table("address");
RWDBSelector selector = db.selector();
selector << person["name"];
selector.from(rwdbInner(person, address));
selector.on(person["addr_id"] == adress["addr_id"]);
selector.where(address["pref"] == "大阪府");
...
DBTools.h++ v4.1 でのJOIN
上述のように、テーブルの結合をFROM〜JOIN〜ONで行なうことでWHERE節をすっきり記述できるのですが、DBTools.h++ v4.0まではこのJOIN〜ONに大きな制限がありました。
|
|
|
上のようなテーブルから、全従業員の所属する部署の一覧を作ることを考えてみましょう。
従業員と所属部署との対応はt_mapによって対応づけられています(ただし従業員の中には所属部署の定まらない新入社員が存在するものとします)。
各テーブルの内容は、たとえば次のようになります('江口'さんと'大木'さんは新入社員であり、まだ所属部署が決まっていません)。
|
|
|
さてこのとき、この3つのテーブルを t_map.job_id = t.job.id かつ t_map.id = t_map.emp_idという条件で結合するには、JOINの入れ子を作ることになります。すなわち、"t_mapと t_job を条件 t_map.job_id = t_job.id で結合したもの" と t_emp を条件t_emp.id = t_map.emp_idで結合したものが求めるテーブルとなります。SQL文では
SELECT t_emp.name, t_job.name
FROM t_emp LEFT JOIN
( t_map INNER JOIN t_job
ON t_map.job_id = t_job.id)
ON t_emp.id = t_map.emp_id
ここでDBTools.h++の問題が露呈します。つまり、JOINの結合条件であるONはRWDBSelectorのメソッドon()で指定するため、JOINの入れ子が表現できないのです。
DBTools.h++ v4.1ではこの(JOINが入れ子にできない)が解消されました。JOINの結合条件を指定するメソッドon()がRWDBSelectorからRWDBJoinExprに移されました(RWDBSelector::on()も残してありますが、そのうちなくなってしまうと思われます)。
DBTools.h++ v4.1でJOINの入れ子が可能になり、以下のようなコードで記述できるようになりました。
/*
* SELECT t_emp.name, t_job.name
* FROM t_emp LEFT JOIN
* ( t_map INNER JOIN t_job
* ON t_map.job_id = t_job.id)
* ON t_emp.id = t_map.emp_id
*/
RWDBTable emp = db.table("T_EMP");
RWDBTable job = db.table("T_JOB");
RWDBTable map = db.table("T_MAP");
RWDBSelector selector = db.selector();
selector << emp["name"] << job["name"];
RWDBJoinExpr inner_join =
rwdbInner(map, job).on(map["job_id"] == job["id"]);
RWDBJoinExpr left_join =
rwdbLeftOuter(emp, inner_join).on(emp["id"] == map["emp_id"]);
selector.from(left_join);
...
sample code (only for DBTools.h++ v4.1 or later)
#include <iostream>
#include <locale>
#include <rw/db/db.h>
using namespace std;
#ifdef _DEBUG
#define SERVER_TYPE "msq15d.dll"
#else
#define SERVER_TYPE "msq12d.dll"
#endif
int trial() {
// データベースに接続
RWDBDatabase db = RWDBManager::database(
SERVER_TYPE, // serverType
"MSSQL_TRIAL", // serverName
"sa", // userName
"", // password
"TRIAL" // databaseName
);
if ( !db.isValid() ) return 1;
RWDBConnection connection = db.connection();
// 3つのテーブル 'T_EMP', 'T_JOB', 'T_EMP' を作成
RWDBTable emp = db.table("T_EMP");
RWDBTable job = db.table("T_JOB");
RWDBTable map = db.table("T_MAP");
RWDBOSql sql;
if ( !emp.exists(connection, TRUE) ) {
const char* statements[] = {
"create table T_EMP(ID int, NAME char(10))",
"insert into T_EMP values(1, '相川')",
"insert into T_EMP values(2, '井上')",
"insert into T_EMP values(3, '上田')",
"insert into T_EMP values(4, '江口')",
"insert into T_EMP values(5, '大木')",
0 };
for ( int i = 0; statements[i]; ++i ) {
sql.statement(statements[i]);
sql.execute(connection);
}
}
if ( !job.exists(connection, TRUE) ) {
const char* statements[] = {
"create table T_JOB(ID int, NAME char(10))",
"insert into T_JOB values(1, '総務')",
"insert into T_JOB values(2, '営業')",
"insert into T_JOB values(3, '広報')",
"insert into T_JOB values(4, '開発')",
"insert into T_JOB values(5, '保守')",
0 };
for ( int i = 0; statements[i]; ++i ) {
sql.statement(statements[i]);
sql.execute(connection);
}
}
if ( !map.exists(connection, TRUE) ) {
const char* statements[] = {
"create table T_MAP(EMP_ID int, JOB_ID int)",
"insert into T_MAP values(1, 1)",
"insert into T_MAP values(2, 2)",
"insert into T_MAP values(3, 3)",
0 };
for ( int i = 0; statements[i]; ++i ) {
sql.statement(statements[i]);
sql.execute(connection);
}
}
/*
* SELECT t_emp.name, t_job.name
* FROM t_emp LEFT JOIN
* ( t_map INNER JOIN t_job
* ON t_map.job_id = t_job.id)
* ON t_emp.id = t_map.emp_id
*/
RWDBSelector selector = db.selector();
selector << emp["name"] << job["name"];
RWDBJoinExpr inner_join =
rwdbInner(map, job).on(map["job_id"] == job["id"]);
RWDBJoinExpr left_join =
rwdbLeftOuter(emp, inner_join).on(emp["id"] == map["emp_id"]);
selector.from(left_join);
cout << selector.asString() << endl;
RWDBReader reader = selector.reader(connection);
while ( reader() ) {
RWCString emp_name, job_name;
reader >> emp_name >> job_name;
if ( job_name.isNull() ) {
job_name = "(NULL)";
}
cout << emp_name << '\t' << job_name << endl;
}
cout << db.version() << endl;
emp.drop();
job.drop();
map.drop();
return 0;
}
void onError(const RWDBStatus& aStatus) {
if ( aStatus.errorCode() == RWDBStatus::ok )
return;
cerr << aStatus.vendorMessage1() << " / "
<< aStatus.vendorMessage2() << endl;
aStatus.raise();
}
int main() {
locale::global(locale("japanese"));
RWDBManager::setErrorHandler(onError);
int ret = 0;
try {
ret = trial();
} catch ( RWExternalErr& er) {
cerr << er.why() << endl;
} catch ( exception& ex ) {
cerr << ex.what() << endl;
}
return ret;
}