## Sqletの開発

- SELECT文の実行
- 引数付きSELECT文の実行
- バインド変数付きSELECT文の実行
- ロジックを持ったSELECT文の実行
- where,and,orタグ
- UPDATE文の実行

## SELECT文の実行

Sqletを使って、SQL文を実行することもできます。
sqletタグのボディにSQL文を記述します。
SELECT文の場合は、フェッチした結果をJavaにマッピングするための情報(XMLのパス)を result属性に指定します。
contextName属性で、データベースへの接続に利用する SeasarContextの名前を指定します。
contextName属性を省略した場合は、seasar-context.xmlの最初のcontextが使われます。
通常は、contextName属性を省略します。

#### examples.org.seasar.nazuna.SelectSqlet

    <sqlet result="examples.org.seasar.nazuna.Employee">
        SELECT * FROM emp
        ORDER BY empno
    </sqlet>

result用のXMLでO/Rマッピングを定義します
className属性で、フェッチした1行を格納するオブジェクトのクラス名を指定します。
指定するクラスは、JavaBeansであるかjava.util.Mapを実装している必要があります。
propertyタグのname属性には、JavaBeansのプロパティか、Mapのキー名を指定します。
columnNameで指定する名前は、ResultSet.getXxx(String columnName)で指定する名前です。

sqletタグのresult属性を省略すると、classNameにorg.seasar.util.Structが指定されたとみなされ、
プロパティ名は、カラム名と同じになります。
Structの場合、プロパティ名の大文字小文字を意識することなくアクセスできます。
プロパティの型もカラム(JDBC)の型から自動的に決定されます。

#### examples.org.seasar.nazuna.Employee

    <result className="examples.org.seasar.nazuna.Employee">
        <property name="employeeNo" columnName="empno"/>
        <property name="employeeName" columnName="ename"/>
        <property name="job" columnName="job"/>
        <property name="manager" columnName="mgr"/>
        <property name="hireDate" columnName="hiredate"/>
        <property name="salary" columnName="sal"/>
        <property name="commission" columnName="comm"/>
        <property name="departmentNo" columnName="deptno"/>
    </result>

JavaBeansであるためには、引数のないデフォルトのコンストラクタを用意し、
java.io.Serializableをimplementsします。
プロパティのJavaの型は、int,long.double,BigDecimal,String,Timestamp,byte[]でなければいけません。
プロパティの型の制限は、JavaBeansの仕様ではなく、Sqletの制限です。
propertyタグで指定しない(カラムとマッピングされない)プロパティの型には、
制限はありません。

classNameにjava.util.Mapを実装したクラスを指定する場合、propertyタグのtype属性で、
型を指定することができます。
指定できる型は、Integer,Long,Double,BigDecimal,Timestamp,Binaryになります。
指定しなかった場合のデフォルトは、Stringです。

#### JDBCの型(java.sql.Types)からプロパティの型への自動変換

JDBCの型 | プロパティの型
--- | ---
TINYINT | Integer
SMALLINT | Integer
INTEGER | Integer
BIGINT | Long
REAL | Double
FLOAT | Double
DOUBLE | Double
DECIMAL | Double
NUMERIC | Double
DATE | Timestamp
TIME | Timestamp
TIMESTAMP | Timestamp
BINARY | Binary
VARBINARY | Binary
その他 | String

#### examples.org.seasar.nazuna.Employee

    package examples.org.seasar.nazuna;

    import java.io.Serializable;
    import java.math.BigDecimal;
    import java.sql.Timestamp;

    public class Employee implements Serializable {

        private int _employeeNo;
        private String _employeeName;
        private String _job;
        private int _manager;
        private Timestamp _hireDate;
        private BigDecimal _salary;
        private BigDecimal _commission;
        private int _departmentNo;

        public Employee() {
        }

        public int getEmployeeNo() {
            return _employeeNo;
        }

        public void setEmployeeNo(int employeeNo) {
            _employeeNo = employeeNo;
        }

        public String getEmployeeName() {
            return _employeeName;
        }

        public void setEmployeeName(String employeeName) {
            _employeeName = employeeName;
        }

        public String getJob() {
            return _job;
        }

        public void setJob(String job) {
            _job = job;
        }

        public int getManager() {
            return _manager;
        }

        public void setManager(int manager) {
            _manager = manager;
        }

        public Timestamp getHireDate() {
            return _hireDate;
        }

        public void setHireDate(Timestamp hireDate) {
            _hireDate = hireDate;
        }

        public BigDecimal getSalary() {
            return _salary;
        }

        public void setSalary(BigDecimal salary) {
            _salary = salary;
        }

        public BigDecimal getCommission() {
            return _commission;
        }

        public void setCommission(BigDecimal commission) {
            _commission = commission;
        }

        public int getDepartmentNo() {
            return _departmentNo;
        }

        public void setDepartmentNo(int departmentNo) {
            _departmentNo = departmentNo;
        }
    }

Nazuna.executeQuery(String name)の第1引数にSqletの名前を指定して、
SQL文を実行します。
Sqletの名前は、パッケージ名 + . + ファイル名から拡張子を除いたものになります。
戻り値は、java.util.Listで、Listの各エントリは、結果用XMLのclassNameの インスタンスになります。
結果が0の場合、サイズ0のListがかえされます。

1件しか返さないことが分かっている場合は、 Nazuna.executeSingleQuery(String name)を使います。
戻り値は、java.lang.Object(JavaBeans, java.util.Map)です。
結果が0件の場合、nullが返されます。
結果が複数件あった場合は、例外(ESSR0362)が発生します。

実際にJavaのプログラムを動かす前に、Seasarの設定ができているかを確認します。
インストール、 コネクションプールの設定、 SeasarContextを確認してください。
OKならSeasarを開始させてください。

    package examples.org.seasar.nazuna;

    import java.util.List;

    import org.seasar.nazuna.Nazuna;
    import org.seasar.util.Reflector;

    public class SelectSqletClient {

        private static final String SQLET_NAME =
            "examples.org.seasar.nazuna.SelectSqlet";

        public static void main(String[] args) {
            try {
                List results = Nazuna.executeQuery(SQLET_NAME);
                for (int i = 0; i < results.size(); ++i) {
                    String s = Reflector.toStringForBean(results.get(i));
                    System.out.println(s);
                }
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

実行結果は次のようになります。

    DEBUG 2003-04-04 16:11:52,882 [main]
    SELECT * FROM emp
    ORDER BY empno
    {commission=null,departmentNo=20,employeeName=SMITH,employeeNo=7369,hireDate=1980-12-17 00:00:00.0,job=CLERK,manager=7902,salary=800}
    {commission=300,departmentNo=30,employeeName=ALLEN,employeeNo=7499,hireDate=1981-02-20 00:00:00.0,job=SALESMAN,manager=7698,salary=1600}
    {commission=500,departmentNo=30,employeeName=WARD,employeeNo=7521,hireDate=1981-02-22 00:00:00.0,job=SALESMAN,manager=7698,salary=1250}
    {commission=null,departmentNo=20,employeeName=JONES,employeeNo=7566,hireDate=1981-04-02 00:00:00.0,job=MANAGER,manager=7839,salary=2975}
    {commission=1400,departmentNo=30,employeeName=MARTIN,employeeNo=7654,hireDate=1981-09-28 00:00:00.0,job=SALESMAN,manager=7698,salary=1250}
    {commission=null,departmentNo=30,employeeName=BLAKE,employeeNo=7698,hireDate=1981-05-01 00:00:00.0,job=MANAGER,manager=7839,salary=2850}
    {commission=null,departmentNo=10,employeeName=CLARK,employeeNo=7782,hireDate=1981-06-09 00:00:00.0,job=MANAGER,manager=7839,salary=2450}
    {commission=null,departmentNo=20,employeeName=SCOTT,employeeNo=7788,hireDate=1982-12-09 00:00:00.0,job=ANALYST,manager=7566,salary=3000}
    {commission=null,departmentNo=10,employeeName=KING,employeeNo=7839,hireDate=1981-11-17 00:00:00.0,job=PRESIDENT,manager=0,salary=5000}
    {commission=0,departmentNo=30,employeeName=TURNER,employeeNo=7844,hireDate=1981-09-08 00:00:00.0,job=SALESMAN,manager=7698,salary=1500}
    {commission=null,departmentNo=20,employeeName=ADAMS,employeeNo=7876,hireDate=1983-01-12 00:00:00.0,job=CLERK,manager=7788,salary=1100}
    {commission=null,departmentNo=30,employeeName=JAMES,employeeNo=7900,hireDate=1981-12-03 00:00:00.0,job=CLERK,manager=7698,salary=950}
    {commission=null,departmentNo=20,employeeName=FORD,employeeNo=7902,hireDate=1981-12-03 00:00:00.0,job=ANALYST,manager=7566,salary=3000}
    {commission=null,departmentNo=10,employeeName=MILLER,employeeNo=7934,hireDate=1982-01-23 00:00:00.0,job=CLERK,manager=7782,salary=1300}

実行したSQL文がログに書き出されます。
ログの設定は、log4j.propertiesで変更することもできます。
Reflector#toStringForBean()は、JavaBeansのデータをリフレクションを 使って出力するユーティリティメソッドです。

## 引数付きSELECT文の実行

executeQuery()の第2引数以降に指定することにより、execute()と同様に 引数を渡すことができます。

引数や変数をSQL文で参照するには、${変数名}を使います。
その場合、値がSQL文の中にそのまま埋め込まれるので、文字列型の場合は、
シングルクォーテーション(')で囲む必要があります。

#### examples.org.seasar.nazuna.SelectArgSqlet

    <sqlet result="examples.org.seasar.nazuna.Employee">
        <input>
            <arg name="empno" className="java.lang.Integer"/>
            <arg name="ename" className="java.lang.String"/>
        </input>
        SELECT * FROM emp
        WHERE empno = ${empno}
        OR ename = '${ename}'
    </sqlet>

    package examples.org.seasar.nazuna;

    import java.util.List;

    import org.seasar.nazuna.Nazuna;
    import org.seasar.util.Reflector;

    public class SelectArgSqletClient {

        private static final String SQLET_NAME =
            "examples.org.seasar.nazuna.SelectArgSqlet";

        public static void main(String[] args) {
            try {
                List results =
                    Nazuna.executeQuery(SQLET_NAME, new Integer(7788), "SCOTT");
                String s = Reflector.toStringForBean(results.get(0));
                System.out.println(s);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

実行結果は次のようになります。

    DEBUG 2003-04-07 20:39:34,339 [main]
    SELECT * FROM emp
    WHERE empno = 7788
    OR ename = 'SCOTT'
    {commission=null,departmentNo=20,employeeName=SCOTT,employeeNo=7788,hireDate=1982-12-09 00:00:00.0,job=ANALYST,manager=7566,salary=3000}

## バインド変数付きSELECT文の実行

SelectArgSqletでは、SQL文中にパラメータの値を埋め込んでいました。
そのためパラメータの値が変わるとSQL文も変わります。RDBMSは、

    SELECT * FROM emp
    WHERE empno = 7788

と

    SELECT * FROM emp
    WHERE empno = 7369

は、違うSQL文とみなし、それぞれをコンパイルして実行します。
上記のSQL文を

    SELECT * FROM emp
    WHERE empno = ?

のように書き換えます。そして、?の部分の値を実行のたびにバインドすることで、
最初にコンパイルしたSQL文を再利用できるRDBMSもあります。
このような機能を持ったRDBMSは、?(バインド変数)を使うことで、
コンパイル済みのSQL文を再利用できる可能性が増え、パフォーマンスも向上します。
Sqletでは、?{変数名}と記述することで、バインド変数を利用することができます。
この場合、文字列でもシングルクォーテーション(')で囲む必要はありません。
基本的には、バインド変数を使った方がいいでしょう。

#### examples.org.seasar.nazuna.SelectBindVariableSqlet

    <sqlet result="examples.org.seasar.nazuna.Employee">
        <input>
            <arg name="ename" className="java.lang.String"/>
        </input>
        SELECT * FROM emp
        WHERE ename = ?{ename}
    </sqlet>

    package examples.org.seasar.nazuna;

    import java.util.List;

    import org.seasar.nazuna.Nazuna;
    import org.seasar.util.Reflector;

    public class SelectBindVariableSqletClient {

        private static final String SQLET_NAME =
            "examples.org.seasar.nazuna.SelectBindVariableSqlet";

        public static void main(String[] args) {
            try {
                List results = Nazuna.executeQuery(SQLET_NAME, "SCOTT");
                String s = Reflector.toStringForBean(results.get(0));
                System.out.println(s);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

実行結果は次のようになります。

    DEBUG 2003-04-07 21:51:26,159 [main]
    SELECT * FROM emp
    WHERE ename = 'SCOTT'
    {commission=null,departmentNo=20,employeeName=SCOTT,employeeNo=7788,hireDate=1982-12-09 00:00:00.0,job=ANALYST,manager=7566,salary=3000}

ログでは、わかりやすいように値が埋めこまれていますが、実際はバインド変数が使われています。

## ロジックを持ったSELECT文の実行

入力された値によって、SQL文を変えたいというニーズは良くあります。
例えば、empnoが入力されたいる場合はWHERE句を追加し、
empnoの入力がない場合はWHERE句をなくしたいような場合です。
Sqletでは、SQL文の中にもFlowletのタグをそのまま使えるので、
自由自在にSQL文を組み立てることができます。

#### examples.org.seasar.nazuna.SelectLogicSqlet

    <sqlet result="examples.org.seasar.nazuna.Employee">
        <input>
            <arg name="empno" className="java.lang.Integer"/>
        </input>
        SELECT * FROM emp
        <case>
            <when condition="empno is not null">
                WHERE empno = ?{empno}
            </when>
            <else>
                ORDER BY empno
            </else>
        </case>
    </sqlet>

SelectLogicSqletの場合、パラメータのempnoがnullでなければ、

    SELECT * FROM emp
    WHERE ename = ?{empno}

のSQL文が実行され、パラメータのempnoがnullなら、

    SELECT * FROM emp
    ORDER BY empno

のSQL文が実行されます。

    package examples.org.seasar.nazuna;

    import java.util.List;

    import org.seasar.nazuna.Nazuna;

    public class SelectLogicSqletClient {

        private static final String SQLET_NAME =
            "examples.org.seasar.nazuna.SelectLogicSqlet";

        public static void main(String[] args) {
            try {
                System.out.println("*** empno is null");
                List results = Nazuna.executeQuery(SQLET_NAME);
                System.out.println();
                System.out.println("*** empno is not null");
                results = Nazuna.executeQuery(SQLET_NAME, new Integer(7788));
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

実行結果は次のようになります。

    *** empno is null
    DEBUG 2003-04-08 11:46:10,687 [main]
    SELECT * FROM emp
    ORDER BY empno

    *** empno is not null
    DEBUG 2003-04-08 11:46:10,998 [main]
    SELECT * FROM emp
    WHERE empno = 7788

パラメータempnoの有無によって、異なるSQL文になっていることがわかります。

## where,and,orタグ

条件が複雑になってくると、WHERE句の組み立ても複雑になります。
例えば、引数にsalary,deptnoがあり、salaryの入力があった場合は、
sal ge ?{salary}の条件を加え、deptnoの入力があった場合は、
deptno = ?{deptno}の条件を加えるという仕様の場合、引数のパターンにより、
次のようなWHERE句になります。

salary | deptno | WHERE句
--- | --- | ---
あり | あり | WHERE sal ge ?{salary} AND deptno = ?{deptno}
あり | なし | WHERE sal ge ?{salary}
なし | あり | WHERE deptno = ?{deptno}
なし | なし |

条件が2つならまだ大丈夫ですが、これが3つ,4つとなると一挙に複雑になります。
このようなWHERE句の複雑さを軽減させるためのタグがwhere,and,orタグです。

    <where>
        <case>
            <when condition="salary is not null">
                <and>sal ge ?{salary}</and>
            </when>
        </case>
        <case>
            <when condition="deptno is not null">
                <and>deptno = ?{deptno}</and>
            </when>
        </case>
    </where>

andタグは、自分が条件に一致して出力対象になったときに、
自分の外側にあるwhereタグに含まれるand,orタグが既にあれば、
ANDをSQL文に含め、なければANDを出力しません。
例えば、<and>deptno = ?{deptno}</and>は、
自分が出力対象になった場合に、salary >= ?{salary}が出力済なら、
AND deptno = ?{deptno}となり、whereタグの中で、自分が最初に出力対象になったときには、
deptno = ?{deptno}と出力します。

orタグも同様です。

whereタグは、自分に含まれるand,orタグが1つでも対象になったら、
WHEREをSQL文に書き出し、1つも対象にならなかった場合は、何も出力しません。

#### examples.org.seasar.nazuna.SelectWhereSqlet

    <sqlet result="examples.org.seasar.nazuna.Employee">
        <input>
            <arg name="salary" className="java.math.BigDecimal"/>
            <arg name="deptno" className="java.lang.Integer"/>
        </input>
        SELECT * FROM emp
        <where>
            <case>
                <when condition="salary is not null">
                    <and>sal ge ?{salary}</and>
                </when>
            </case>
            <case>
                <when condition="deptno is not null">
                    <and>deptno = ?{deptno}</and>
                </when>
            </case>
        </where>
    </sqlet>

    package examples.org.seasar.nazuna;

    import java.math.BigDecimal;
    import java.util.List;

    import org.seasar.nazuna.Nazuna;

    public class SelectWhereSqletClient {

        private static final String SQLET_NAME =
            "examples.org.seasar.nazuna.SelectWhereSqlet";

        public static void main(String[] args) {
            try {
                System.out.println("*** salary is null AND deptno is null");
                List results = Nazuna.executeQuery(SQLET_NAME);

                System.out.println();
                System.out.println("*** salary is not null AND deptno is null");
                results = Nazuna.executeQuery(SQLET_NAME, new BigDecimal(3000));

                System.out.println();
                System.out.println("*** salary is null AND deptno is not null");
                results = Nazuna.executeQuery(SQLET_NAME, null, new Integer(30));

                System.out.println();
                System.out.println("*** salary is not null AND deptno is not null");
                results = Nazuna.executeQuery(SQLET_NAME,
                    new BigDecimal(3000), new Integer(30));
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

実行結果は次のようになります。

    *** salary is null AND deptno is null
    DEBUG 2003-04-08 21:10:53,799 [main]
    SELECT * FROM emp

    *** salary is not null AND deptno is null
    DEBUG 2003-04-08 21:10:54,099 [main]
    SELECT * FROM emp
    WHERE sal >= 3000

    *** salary is null AND deptno is not null
    DEBUG 2003-04-08 21:10:54,199 [main]
    SELECT * FROM emp
    WHERE deptno = 30

    *** salary is not null AND deptno is not null
    DEBUG 2003-04-08 21:10:54,309 [main]
    SELECT * FROM emp
    WHERE sal >= 3000
    AND deptno = 30

geは、SQL文の中では、>=に置換されます。

## UPDATE文の実行

SELECT文と同様に、INSERT,UPDATE,DELETE文を書けば、更新処理もできます。
SELECT文との違いは、Nazuna.executeUpdate()を呼び出すことです。
executeUpdate()の戻り値は更新した件数になります。
Flowletと同様に、transAttribute属性が使えます。

#### examples.org.seasar.nazuna.UpdateSqlet

    <sqlet transAttribute="Required">
        <input>
            <arg name="employee" className="examples.org.seasar.nazuna.Employee"/>
        </input>
        UPDATE emp SET ename = ?{employee.employeeName}
        WHERE empno = ?{employee.employeeNo}
    </sqlet>

#### examples.org.seasar.nazuna.UpdateSqlet

    package examples.org.seasar.nazuna;

    import org.seasar.nazuna.Nazuna;

    public class UpdateSqletClient {

        private static final String SQLET_NAME =
            "examples.org.seasar.nazuna.UpdateSqlet";

        public static void main(String[] args) {
            try {
                Employee employee = new Employee();
                employee.setEmployeeNo(7788);
                employee.setEmployeeName("SCOTT");
                int rows = Nazuna.executeUpdate(SQLET_NAME, employee);
                System.out.println("Updated " + rows + " row(s)");
            } catch (Exception ex) {
                ex.printStackTrace();
            }
        }
    }

実行結果は次のようになります。

    DEBUG 2003-04-10 17:02:26,567 [main] Transaction.begin()
    DEBUG 2003-04-10 17:02:29,602 [main]
    UPDATE emp SET ename = 'SCOTT'
    WHERE empno = 7788
    DEBUG 2003-04-10 17:02:29,782 [main] Transaction.commit()
    Updated 1 row(s)