LittleSoft J-Programming
4.3. Java コードによる SQL 文構築(動的 SQL の構築)
この章では、動的に SQL を構築する方法について解説します。
業務アプリケーション構築で最も使用頻度が高い使用の仕方です。
業務アプリケーション構築で最も使用頻度が高い使用の仕方です。
条件が指定された場合のみ SQL に WHERE 句を追加する
バインドパラメータに値がある場合のみ、WHERE 句に条件が追加されます。
検索画面の構築での面倒な SQL の構築が IF 文なしで行えます。
対象データをSELECTする際、検索パラメータに値が設定されている場合のみ、条件によるフィルターを実施したい場合があります。
LSJでは、このような機能を簡単に実装するAPIが用意されています。
以下のコードでは、検索パラメータの所属コードに値が設定されている場合のみ、その条件フィルタを適用する例です。
WHERE句の条件式が追加されないようになります。
また、以下のように AND や OR の組み合わせも可能です。
以下は条件式に優先順位をつけた記述の例です。
検索画面の構築での面倒な SQL の構築が IF 文なしで行えます。
バインドパラメータ値 による WHERE 句 の生成
LSJでは、このような機能を簡単に実装するAPIが用意されています。
以下のコードでは、検索パラメータの所属コードに値が設定されている場合のみ、その条件フィルタを適用する例です。
select()
.whereEvaluateNonNulls(emp.deptCode).is(":deptCode").end
.orderBy(emp.name)
.prepare();
whereEvaluateNonNulls (カラム名).is (バインドパラメータ名) は、バインドパラメータ値が NULL の場合は.whereEvaluateNonNulls(emp.deptCode).is(":deptCode").end
.orderBy(emp.name)
.prepare();
WHERE句の条件式が追加されないようになります。
また、以下のように AND や OR の組み合わせも可能です。
select()
.whereEvaluateNonNulls(emp.salary).bigger(":salary")
.andEvaluateNonNulls(emp.deptCode).is(":deptCode")
.orEvaluateNonNulls(emp.empId).is(":empId").end
.prepare();
実行時には下記のようなSQLが実行されます。
.whereEvaluateNonNulls(emp.salary).bigger(":salary")
.andEvaluateNonNulls(emp.deptCode).is(":deptCode")
.orEvaluateNonNulls(emp.empId).is(":empId").end
.prepare();
// パラメータがすべて指定されている場合
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)
WHERE
T_EMP.SALARY > :salary
AND T_EMP.DEPT_CODE = :deptCode
OR T_EMP.EMP_ID = :id
// パラメータが指定されていない場合
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)
条件式の優先順位は、$() で記述する式を使用します。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)
WHERE
T_EMP.SALARY > :salary
AND T_EMP.DEPT_CODE = :deptCode
OR T_EMP.EMP_ID = :id
// パラメータが指定されていない場合
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)
以下は条件式に優先順位をつけた記述の例です。
select()
.whereEvaluateNonNulls(emp.salary).bigger(":salary")
.andEvaluateNonNulls(
$if(emp.deptCode).is(":deptCode")
.or(emp.empId).is(":empId")
).end
.prepare();
実行時には下記のようなSQLが実行されます。
.whereEvaluateNonNulls(emp.salary).bigger(":salary")
.andEvaluateNonNulls(
$if(emp.deptCode).is(":deptCode")
.or(emp.empId).is(":empId")
).end
.prepare();
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)
WHERE
T_EMP.SALARY > :salary
AND (T_EMP.DEPT_CODE = :deptCode OR T_EMP.EMP_ID = :empId)
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)
WHERE
T_EMP.SALARY > :salary
AND (T_EMP.DEPT_CODE = :deptCode OR T_EMP.EMP_ID = :empId)
IN 句を使う
IN 句による以下のような条件式の場合、NULL パラメータの判定が通常と異なります。
実行時にバインドパラメータ値が NULL の場合は、IN 句のリストから自動的に取り除かれた SQL が設定されます。
また、全てのバインドパラメータ値が NULL である場合は、この条件自体が設定されないようになります。
実行時には下記のようなSQLが実行されます。
select()
.whereEvaluateNonNulls(sql.$$.empId).in(":e1", ":e2", ":e3").end
.prepare();
指定された3つのバインドパラメータ e1, e2, e3 の全てに値を設定する必要はなく、.whereEvaluateNonNulls(sql.$$.empId).in(":e1", ":e2", ":e3").end
.prepare();
実行時にバインドパラメータ値が NULL の場合は、IN 句のリストから自動的に取り除かれた SQL が設定されます。
また、全てのバインドパラメータ値が NULL である場合は、この条件自体が設定されないようになります。
実行時には下記のような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)
WHERE
T_EMP.EMP_ID IN (:e1,:e2,:e3)
// パラメータが指定されない場合
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)
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)
WHERE
T_EMP.EMP_ID IN (:e1,:e2,:e3)
// パラメータが指定されない場合
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)
BETWEEN を使用する
BETWEEN による以下のような条件式の場合、NULL パラメータの設定により、特別な動作が行われます。
通常どおり、以下の条件式となります。
BETWEEN は使われず、以下のような条件式となります。
BETWEEN は使われず、以下のような条件式となります。
select()
.whereEvaluateNonNulls($$.enterDate).between(":from", ":to")
.prepare();
.whereEvaluateNonNulls($$.enterDate).between(":from", ":to")
.prepare();
from、to 両方のパラメータに値が設定されている場合
WHERE ENTER_DATE BETWEEN :from AND :to
fromパラメータに値が設定され、toパラメータがNULLの場合
WHERE ENTER_DATE >= :from
toパラメータに値が設定され、from パラメータがNULLの場合
WHERE ENTER_DATE <= :to
from、to 両方がNULLである場合は、実行時に条件式自体が SQL に設定されません。
LIKE 検索をする
前方一致・部分一致・後方一致のようなLIKE を使用した検索について説明します。
.startsWith(XXX) を使用することで LIKE XXX || '%' のようなSQLを生成します。
.endsWith(XXX) を使用することで LIKE '%' || XXX のようなSQLを生成します。
.contains(XXX) を使用することで LIKE '%' || XXX || '%' のようなSQLを生成します。
下記は上の3パターンのLIKEを生成する場合のコード例です。
前方一致
後方一致
部分一致
下記は上の3パターンのLIKEを生成する場合のコード例です。
select()
// 氏名が入力されている場合のみ前方一致検索します
.whereEvaluateNonNulls($$.name).startsWith(":name")
// メールアドレスが入力されている場合のみ後方一致検索します
.andEvaluateNonNulls($$.mail).endsWith(":mail")
// 氏名(カナ)が入力されている場合のみ部分一致検索します
.andEvaluateNonNulls($$.kana).contains(":kana")
.prepare();
// 氏名が入力されている場合のみ前方一致検索します
.whereEvaluateNonNulls($$.name).startsWith(":name")
// メールアドレスが入力されている場合のみ後方一致検索します
.andEvaluateNonNulls($$.mail).endsWith(":mail")
// 氏名(カナ)が入力されている場合のみ部分一致検索します
.andEvaluateNonNulls($$.kana).contains(":kana")
.prepare();
動的に条件式を設定する
実行時に動的に条件式を変更したい場合は、DynamicCondition を使用することができます。
動的に変更となる条件式には、whereDynamicCondition(条件式名) で設定を行います。
引数の条件式名は、動的条件を識別するために使用する任意のキー値となります。
以下のコードは、動的条件式を使用した例です。
動的に変更となる条件式には、whereDynamicCondition(条件式名) で設定を行います。
引数の条件式名は、動的条件を識別するために使用する任意のキー値となります。
以下のコードは、動的条件式を使用した例です。
public WebSQLTable<Employee> tableEmp = new WebSQLTable<Employee>() {{
select()
// 動的に条件式を設定します
.whereDynamicCondition("dyc")
.and($$.deptCode).is(":deptCode").end
.orderBy($$.name)
.prepare();
}};
/**
* 検索ボタン押下時のアクション
*/
public doSearch() {
// DynamicCondition の設定をリセットします
tableEmp.resetDynamicCondtions();
// 性別が 1:女性 の場合のみ "dyc" をキーに条件オブジェクトを挿入します
if (tableEmp.$$.sex == 1) {
tableEmp.setDynamicCondtion("dyc", $if(tableEmp.$$.name).startWidth("熊田") );
}
// tableEmp を activate して SQL を実行
tableEmp.activate(conn);
}
select()
// 動的に条件式を設定します
.whereDynamicCondition("dyc")
.and($$.deptCode).is(":deptCode").end
.orderBy($$.name)
.prepare();
}};
/**
* 検索ボタン押下時のアクション
*/
public doSearch() {
// DynamicCondition の設定をリセットします
tableEmp.resetDynamicCondtions();
// 性別が 1:女性 の場合のみ "dyc" をキーに条件オブジェクトを挿入します
if (tableEmp.$$.sex == 1) {
tableEmp.setDynamicCondtion("dyc", $if(tableEmp.$$.name).startWidth("熊田") );
}
// tableEmp を activate して SQL を実行
tableEmp.activate(conn);
}