LittleSoft J-Programming
4.2. Java コードによる SQL 文構築
SQL 文字列を直接 Java クラス記述せずに Java プログラミングだけで SQL を構築できるようになりました。
従来の SQL プログラミングでは、SQL 文を文字列として編集するのが一般的でしたが、
・SQL インジェクションのリスクが混入しやすい
・SQL 文字列の編集が面倒…
といった問題点がありました。
そこで、より スマートな SQL プログラミングを実現するべく、新しい API を用意しました。
型付けされたテーブルオブジェクトに対して、シンプルなオブジェクト・リレーショナルマッピング機能を提供します。
これらの機能により、データベーステーブルアプリケーションが より コンパクトなプログラミングで開発できるようになります。
※マッピングの定義方法については、「型付けされたテーブルオブジェクト - データベーステーブルとのマッピング」を参照してください。
従来の SQL プログラミングでは、SQL 文を文字列として編集するのが一般的でしたが、
・SQL インジェクションのリスクが混入しやすい
・SQL 文字列の編集が面倒…
といった問題点がありました。
そこで、より スマートな SQL プログラミングを実現するべく、新しい API を用意しました。
型付けされたテーブルオブジェクトに対して、シンプルなオブジェクト・リレーショナルマッピング機能を提供します。
これらの機能により、データベーステーブルアプリケーションが より コンパクトなプログラミングで開発できるようになります。
※マッピングの定義方法については、「型付けされたテーブルオブジェクト - データベーステーブルとのマッピング」を参照してください。
※動的 SQL についても改善(未実装)
※ベンダー依存 SQL を回避、主要 RDB 製品の SQL 自動的に対応(未実装)
型付けされた WebSQL Table を使う
型付けされた WebSQLTable を使うには、jp.littlesoft.SQLActionPage を使用します。
以下はサンプルコードでは、SQLActionPage を継承した EmpListPage を作成します。
以下はサンプルコードでは、SQLActionPage を継承した EmpListPage を作成します。
public class EmpListPage extends SQLActionPage {
WebSQLTable<Employee> tableEmp = new WebSQLTable<Employee>(){{
// SELECT 文を追加します
select().prepare();
}};
…
…
…
}
WebSQLTable<Employee> tableEmp = new WebSQLTable<Employee>(){{
// SELECT 文を追加します
select().prepare();
}};
…
…
…
}
SELECT 文を実行する
型付けされた WebSQLTable を使って、SELECT 文を実行します。
以下はサンプルコードでは、SELECT * FROM EMPLOYEE が実行されます。
※ "$$" は、Employeeクラスのダミーオブジェクトです。
※ ダミーオブジェクトとは、SQL を構築するために用意された各フィールドが null にならないよう適当な値が設定された
一時的なオブジェクトです。
以下はサンプルコードでは、SELECT * FROM EMPLOYEE が実行されます。
// 型付けされた WebSQLTable を用意
// カッコを使って拡張しします
WebSQLTable<Employee> tableEmp = new WebSQLTable<Employee>(){{
// SELECT 文を追加します
select().prepare();
// activate して、SELECT 文を実行します
tableEmp.activate(conn);
}};
※ 上の例は、select().from($$).prepare(); の省略形です。// カッコを使って拡張しします
WebSQLTable<Employee> tableEmp = new WebSQLTable<Employee>(){{
// SELECT 文を追加します
select().prepare();
// activate して、SELECT 文を実行します
tableEmp.activate(conn);
}};
※ "$$" は、Employeeクラスのダミーオブジェクトです。
※ ダミーオブジェクトとは、SQL を構築するために用意された各フィールドが null にならないよう適当な値が設定された
一時的なオブジェクトです。
WHERE 句を使う
次に WHERE 句を追加して条件を指定します。
条件の追加は、where.($$.フィールド名).is(固定値) のように記述します。
下のコードでは、社員 ID が 「12」を指定する条件を追加しています。
バインドパラメータ化するには、先ほどの固定値の部分をコロン(:)付きのフィールド名に変更します。
固定の条件を追加
下のコードでは、社員 ID が 「12」を指定する条件を追加しています。
WebSQLTable<Employee> tableEmp = new WebSQLTable<Employee>() {{
select()
// 社員ID = 12 を条件に指定します
.where($$.empId).is(12).end
.prepare();
}};
tableEmp.activate(conn);
※ WHERE 句の終わりには end を記述します。
select()
// 社員ID = 12 を条件に指定します
.where($$.empId).is(12).end
.prepare();
}};
tableEmp.activate(conn);
バインドパラメータで条件を追加
WebSQLTable<Employee> tableEmp = new WebSQLTable<Employee>() {{
select()
// 社員ID をバインドパラメータで条件に指定します
.where($$.empId).is(":empId").end
.prepare();
}};
// バインドパラメータに値をセットします
tableEmp.getParams().setValue("empId", 12);
tableEmp.activate(conn);
実行時には下記のようなSQLが実行されます。
select()
// 社員ID をバインドパラメータで条件に指定します
.where($$.empId).is(":empId").end
.prepare();
}};
// バインドパラメータに値をセットします
tableEmp.getParams().setValue("empId", 12);
tableEmp.activate(conn);
SELECT * FROM T_EMP WHERE EMP_ID = :id
JOIN する
続いて、EMPLOYEE テーブルの内容に加え DEPARTMENT テーブルから所属名を取得するようにします。
まずは、所属名を表示するようなエンティティクラスを Employee クラスを継承して作成します。
JOIN を行う場合、innerJoin.(エンティティクラス).on 条件… のように記述します。
下のコードでは、dept を emp.deptCode と dept.code で JOIN して、dept.name を取得しています。
実行時には下記のようなSQLが実行されます。
所属名を追加したビュークラス(エンティティクラス)の作成
public static class EmployeeView extends Employee {
// 所属名をフィールドに加えます
public String deptName;
}
// 所属名をフィールドに加えます
public String deptName;
}
JOINを含む SELECT 文の定義
下のコードでは、dept を emp.deptCode と dept.code で JOIN して、dept.name を取得しています。
WebSQLTable<EmployeeView> tableEmp = new WebSQLTable<EmployeeView>() {{
// 所属(Departmentクラス)のダミーオブジェクトを用意
Department dept = $useDummy(Department.class);
// 社員(Employee)のダミーオブジェクトを用意
Employee emp = $useDummy(Employee.class);
select(
$(emp).into($$),// $$ は EmployeeView のダミーオブジェクトを表します
$(dept.name).into($$.deptName)// into でマッピング先を指定
)
.from(emp)
// innerJoin にて emp.DeptCode と dept.code を結合します
.innerJoin(dept).on(emp.deptCode).is(dept.code).end
.prepare();
}};
※ $(…) は SQL 中の式を意味します。// 所属(Departmentクラス)のダミーオブジェクトを用意
Department dept = $useDummy(Department.class);
// 社員(Employee)のダミーオブジェクトを用意
Employee emp = $useDummy(Employee.class);
select(
$(emp).into($$),// $$ は EmployeeView のダミーオブジェクトを表します
$(dept.name).into($$.deptName)// into でマッピング先を指定
)
.from(emp)
// innerJoin にて emp.DeptCode と dept.code を結合します
.innerJoin(dept).on(emp.deptCode).is(dept.code).end
.prepare();
}};
実行時には下記のようなSQLが実行されます。
SELECT T_EMP.*
,M_DEPT.DEPT_NAME DEPT_NAME
FROM T_EMP
INNER JOIN M_DEPT ON (T_EMP.DEPT_CODE = M_DEPT.DEPT_CODE)
,M_DEPT.DEPT_NAME DEPT_NAME
FROM T_EMP
INNER JOIN M_DEPT ON (T_EMP.DEPT_CODE = M_DEPT.DEPT_CODE)
ORDER BY を指定する
ORDER BY を指定するには、orderBy(カラム名, カラム名…) のように記述します。
デフォルトは昇順です。
orderBy(カラム名…) に記述された順で昇順ソートを行います。
昇順または降順を指定する場合は、以下のように記述します。
実行時には下記のようなSQLが実行されます。
デフォルトは昇順です。
ORDER BY を指定
// 所属コード、社員 ID とも昇順の場合
select() …
.orderBy(emp.deptCode, emp.empId)
.prepare();
select() …
.orderBy(emp.deptCode, emp.empId)
.prepare();
昇順・降順の指定
// 所属コードの降順、社員 ID の昇順の場合
select() …
.orderBy($(emp.deptCode).descending(), $(emp.empId).ascending())
.prepare();
※ $(カラム名).descending() で降順、ascending() で昇順を指定します。select() …
.orderBy($(emp.deptCode).descending(), $(emp.empId).ascending())
.prepare();
実行時には下記のようなSQLが実行されます。
SELECT T_EMP.*
,T_EMP.DEPT_CODE DEPT_CODE
,M_DEPT.DEPT_NAME DEPT_NAME
FROM T_EMP
INNER JOIN M_DEPT ON (T_EMP.DEPT_CODE = M_DEPT.DEPT_CODE)
ORDER BY T_EMP.DEPT_CODE DESC
,T_EMP.EMP_ID ASC
,T_EMP.DEPT_CODE DEPT_CODE
,M_DEPT.DEPT_NAME DEPT_NAME
FROM T_EMP
INNER JOIN M_DEPT ON (T_EMP.DEPT_CODE = M_DEPT.DEPT_CODE)
ORDER BY T_EMP.DEPT_CODE DESC
,T_EMP.EMP_ID ASC
GROUP BY を使用する
グループ関数を使用する場合、count(), sum() などの集計関数で集計を行います。
グループ化は、groupBy(カラム名, カラム名…) を使用します。
以下は、GROUP BY を使用するコードです。
集計結果を保持するエンティティクラスを定義します。
サマリー用エンティティクラスに GROUP BY を使用した SQL を設定します。
以下のコードでは、所属コード、所属名、所属社員数、所属社員給与の合計を取得するようなSQLを設定しています。
グループ化は、groupBy(カラム名, カラム名…) を使用します。
以下は、GROUP BY を使用するコードです。
サマリー用エンティティクラスの定義
// 社員給与を所属ごとにサマリーするエンティティクラス
public static class DeptEmpSummary {
public Long deptCode;
public String deptName;
public Long empoyeeNumber;
public Long salaryTotal;
}
public static class DeptEmpSummary {
public Long deptCode;
public String deptName;
public Long empoyeeNumber;
public Long salaryTotal;
}
GROUP BY を使用する
以下のコードでは、所属コード、所属名、所属社員数、所属社員給与の合計を取得するようなSQLを設定しています。
// 作成したエンティティに 社員給与を所属ごとにサマリーする SQL を設定する
WebSQLTable<DeptEmpSummary> summary = new WebSQLTable<DeptEmpSummary>() {{
Department dept = $useDummy(Department.class);
Employee emp = $useDummy(Employee.class);
select(
$(emp.deptCode).into($$.deptCode),
$(dept.name).into($$.deptName),
$(emp.id).count().into($$.empoyeeNumber), // 所属社員数
$(emp.salary).sum().into($$.salaryTotal) ) // 給与の合計
.from(emp)
.innerJoin(dept).on(emp.deptCode).is(dept.code).end
// 所属コード、所属名でグループ化
.groupBy(emp.deptCode, dept.name)
.prepare();
}};
実行時には下記のようなSQLが実行されます。
WebSQLTable<DeptEmpSummary> summary = new WebSQLTable<DeptEmpSummary>() {{
Department dept = $useDummy(Department.class);
Employee emp = $useDummy(Employee.class);
select(
$(emp.deptCode).into($$.deptCode),
$(dept.name).into($$.deptName),
$(emp.id).count().into($$.empoyeeNumber), // 所属社員数
$(emp.salary).sum().into($$.salaryTotal) ) // 給与の合計
.from(emp)
.innerJoin(dept).on(emp.deptCode).is(dept.code).end
// 所属コード、所属名でグループ化
.groupBy(emp.deptCode, dept.name)
.prepare();
}};
SELECT
T_EMP.DEPT_CODE DEPT_CODE
,M_DEPT.DEPT_NAME DEPT_NAME
,COUNT(T_EMP.EMP_ID) EMPOYEE_NUMBER
,SUM(T_EMP.SALARY) SALARY_TOTAL
FROM T_EMP
INNER JOIN M_DEPT ON (T_EMP.DEPT_CODE = M_DEPT.DEPT_CODE)
GROUP BY
T_EMP.DEPT_CODE
,M_DEPT.DEPT_NAME
T_EMP.DEPT_CODE DEPT_CODE
,M_DEPT.DEPT_NAME DEPT_NAME
,COUNT(T_EMP.EMP_ID) EMPOYEE_NUMBER
,SUM(T_EMP.SALARY) SALARY_TOTAL
FROM T_EMP
INNER JOIN M_DEPT ON (T_EMP.DEPT_CODE = M_DEPT.DEPT_CODE)
GROUP BY
T_EMP.DEPT_CODE
,M_DEPT.DEPT_NAME
集合・差分演算子
ここでは、UNION や MINUS を使用して、結果の集合・差分を取得するような SQL を設定します。
以下のコードでは、所属コードが 1 または 所属コードが 3 の社員で、給与が¥450,000 の社員を除く SQL を設定します。
minusSelect() では上の結果から munusSelect() で取得した結果を除きます。
※ bigger(値) は比較演算子で、値 より大きい 場合に true となります。
実行時には下記のようなSQLが実行されます。
以下のコードでは、所属コードが 1 または 所属コードが 3 の社員で、給与が¥450,000 の社員を除く SQL を設定します。
WebSQLTable<Employee> unionEmp = new WebSQLTable<Employee>() {{
// Employee から所属コード=1 の社員を取得
select().from($$)
.where($$.deptCode).is(1).end
// 所属コード=3 の社員を集合する
.unionSelect().from($$)
.where($$.deptCode).is(3).end
// 給与が 450,000 以上の社員を除く
.minusSelect().from($$)
.where($$.salary).bigger(450000).end
// 所属コード、社員ID でソート
.orderBy($$.deptCode, $$.empId)
.prepare();
}};
unionSelect() は重複を含まない結果を返します。重複を含む結果を返す場合は unionAllSelect() を使用します。// Employee から所属コード=1 の社員を取得
select().from($$)
.where($$.deptCode).is(1).end
// 所属コード=3 の社員を集合する
.unionSelect().from($$)
.where($$.deptCode).is(3).end
// 給与が 450,000 以上の社員を除く
.minusSelect().from($$)
.where($$.salary).bigger(450000).end
// 所属コード、社員ID でソート
.orderBy($$.deptCode, $$.empId)
.prepare();
}};
minusSelect() では上の結果から munusSelect() で取得した結果を除きます。
※ bigger(値) は比較演算子で、値 より大きい 場合に true となります。
実行時には下記のようなSQLが実行されます。
SELECT * FROM T_EMP
WHERE DEPT_CODE = :deptCode1
UNION
SELECT * FROM T_EMP
WHERE DEPT_CODE = :deptCode2
MINUS
SELECT * FROM T_EMP
WHERE SALARY > :deptCode3
ORDER BY
DEPT_CODE
,EMP_ID
WHERE DEPT_CODE = :deptCode1
UNION
SELECT * FROM T_EMP
WHERE DEPT_CODE = :deptCode2
MINUS
SELECT * FROM T_EMP
WHERE SALARY > :deptCode3
ORDER BY
DEPT_CODE
,EMP_ID
寛容な数値の扱いについて
JDBC 結果セットで返される値が BigDecimal で、格納すべき Java クラスが Integer の場合など
値や精度を失う可能性がある場合でも、可能なかぎり寛容なマッピングを行うようになっています。
数値の値や精度に対して厳格な処理を行う場合は、以下のような2通りの設定方法があります。
値や精度を失う可能性がある場合でも、可能なかぎり寛容なマッピングを行うようになっています。
数値の値や精度に対して厳格な処理を行う場合は、以下のような2通りの設定方法があります。
エンティティクラスの ColumnDef アノテーションで設定する
public static class DeptEmpSummary {
:
@ColumnDef(lenientConvert=false)
public Long salaryTotal;
}
:
@ColumnDef(lenientConvert=false)
public Long salaryTotal;
}
Column クラスのメソッドで指定する
WebSQLTable<DeptEmpSummary> summary = new WebSQLTable<DeptEmpSummary>() {{
$column($$.salaryTotal).setLenientConvert(false);
:
}
以上の設定を行うと、数値型が異なる場合、jp.littlesoft.data.Column$TypeMismatchException がスローされるようになります。
$column($$.salaryTotal).setLenientConvert(false);
:
}