Robbie DAO Developer's Guide

このページを読まれる前に、QuickスタートでRobbie DAOの利用方法をご確認ください。その方がスムーズに理解が進みます。

ここでは、SQLの定義方法について説明します。

Robbie DAOを使用することによって、様々なSELECT文を動的に作成することが可能です。動的なSELECT文は、与えられるバインド変数へのマッピング情報から作成されるため、プログラム上でAPIを使用する必要はありません。

Robbie DAOではMap内に存在するパラメータを元に動的にSQLを作成して実行してくれます。例えば下記のように、where、var、const要素を使用してqueryを記述します。

          
    <query id="test4">
      SELECT ID, NAME, AGE FROM TEST_TABLE
      <where join="AND">
        <var key="id" req="false">ID = ?</var>
        <var key="name" req="false" like="%?%">NAME like ?</var>
        <var key="age" req="false">AGE = ?</var>
        <const><![CDATA[ ROWNUM < 100 ]]></const>
      </where>
    </query>

次に、以下のように実行します。

        
        // DAOインスタンスの生成
        XDaoFactory factory = new XDaoFactory("etc/test-config.xml");
        XDao dao = factory.createDaoInstance("TestDAO");

        // 検索条件の設定
        Map params = new HashMap();
        params.put("name", "A");
        
        // DAOの実行
        List result = dao.query("test4", params);
        
        // DB接続が全て終了したらDAOをリリース
        dao.release();

下記のようなSQLが実行されます。

 Query-ID=[test4]
 
 [Query =
  BEANS-CLASS=[null]
  Query-STRING=[
     
       SELECT ID, NAME, AGE FROM TEST_TABLE
       WHERE 
                      ← Mapにkey="id"で値が入っていなかったので展開されない
          NAME like ? ← Mapにkey="name"で値が入っていたので展開
                      ← Mapにkey="age"で値が入っていなかったので展開されない
          AND  ROWNUM < 100 ← 常に展開
       
     ]
  BIND-PARAMETERS=[
     id:1 value:%A% type:null ← Mapの値がセット
  ]
 ]

このように、パラメータMapにセットされたデータによって、動的にSQLが作成され実行されます。

また、上記のようにRobbie DAOはPreparedStatementを使用しているため、SQLインジェクションを引き起こさないセキュアなデータベースアクセスを実現していることも特徴の一つです。

上記で使用したXMLの要素と属性について説明します。

where要素

join属性

var要素

key属性

req属性

like属性

type属性

const要素

key属性

req属性

IN述語を利用する場合には、repeat要素とObjectの配列を使用します。
例えば、以下のようにSQLを定義します。


    <query id="test5">
      SELECT ID, NAME, AGE FROM TEST_TABLE
      <where>
        <repeat key="age" req="false">AGE IN (?)</repeat>
      </where>
    </query>

次に、以下のように実行します。

        
        // DAOインスタンスの生成
        XDaoFactory factory = new XDaoFactory("etc/test-config.xml");
        XDao dao = factory.createDaoInstance("TestDAO");

       // IN述語へ渡すパラメータの配列
       Object[] ages = {
            new Integer(12), 
            new Integer(13), 
            new Integer(14), 
            new Integer(15), 
            new Integer(16), 
            new Integer(17)
        };
        
        // 検索条件の設定
        Map params = new HashMap();
        params.put("age", ages);
        
        // DAOの実行
        List result = dao.query("test5", params);
        
        // DB接続が全て終了したらDAOをリリース
        dao.release();

この場合、実行されるSQLは次のようになります。

 
 Query-ID=[test5]
 
 [Query =
  BEANS-CLASS=[null]
  Query-STRING=[
     
       SELECT ID, NAME, AGE FROM TEST_TABLE
       WHERE 
          AGE IN (?, ?, ?, ?, ?, ?)
       
     ]
  BIND-PARAMETERS=[
     id:1 value:12 type:null
     id:2 value:13 type:null
     id:3 value:14 type:null
     id:4 value:15 type:null
     id:5 value:16 type:null
     id:6 value:17 type:null
  ]
 ]

上記のように、Objectの配列内のデータが、バインド変数としてセットされました。
このrepeat要素とその属性について説明します。

repeat要素

key属性

req属性

BETWEEN句はQuickスタートでも使用しましたので、ここでは違う利用方法について説明します。まず、以下のようなバインド変数を2つ持つSQLを記述します。

        
    <query id="test6">
      SELECT ID, NAME, AGE FROM TEST_TABLE
      <where>
        <between from="age_from" to="age_to" req="false"><![CDATA[AGE >= ? 
 AND AGE <= ? ]]></between>
      </where>
    </query>

このSQLを以下のように実行します。

        
        // DAOインスタンスの生成
        XDaoFactory factory = new XDaoFactory("etc/test-config.xml");
        XDao dao = factory.createDaoInstance("TestDAO");

        // 検索条件の設定
        Map params = new HashMap();
        params.put("age_from", new Integer(10));
        params.put("age_to", new Integer(16));
        
        // DAOの実行
        List result = dao.query("test6", params);
        
        // DB接続が全て終了したらDAOをリリース
        dao.release();

以下のような、SQLが実行されました。

 
 Query-ID=[test6]
 
 [Query =
  BEANS-CLASS=[null]
  Query-STRING=[
     
       SELECT ID, NAME, AGE FROM TEST_TABLE
       WHERE 
          AGE >= ? AND AGE <= ? 
       
     ]
  BIND-PARAMETERS=[
     id:1 value:10 type:null
     id:2 value:16 type:null
  ]
 ]

このSQLによって取得できる結果リストは、BETWEEN句を使用して場合と全く同じになります。この結果からわかるように、このSQLを生成しているXQueryオブジェクトは全くSQLの構文チェックや内容を確認してはいません。あくまでも適切な構文と適切な数のバインド変数が存在していることを前提に処理を進めています。これは、Oracleのヒントなどベンダ依存のSQLの利用を可能にするために、敢えてそのような仕様となっています。

このbetween要素とその属性について説明します。

between要素

from属性

to属性

req属性

相関副問い合わせなどの副問い合わせを行いたい場合には、subquery要素を使用します。例えば、以下のようにSQLを記述することも可能です。

        
    <query id="test7">
      SELECT ID, NAME, AGE FROM TEST_TABLE T1
      <where>
        <subquery>
          EXISTS (
            SELECT * FROM TEST_TABLE T2
            <where>
              <const>T1.AGE = T2.AGE</const>
              <repeat key="age" req="false">AGE IN (?)</repeat>
            </where>)
        </subquery>
      </where>
    </query>

実行方法はrepeetの場合と同じなので省略します。実行されるSQLは以下のようになります。

        
 Query-ID=[test7]
 
 [Query =
  BEANS-CLASS=[null]
  Query-STRING=[
     
       SELECT ID, NAME, AGE FROM TEST_TABLE T1
       WHERE 
           ( 
           EXISTS (
             SELECT * FROM TEST_TABLE T2
             WHERE 
                T1.AGE = T2.AGE
                AND AGE IN (?, ?, ?, ?, ?, ?)
              )
          ) 
       
     ]
  BIND-PARAMETERS=[
     id:1 value:12 type:null
     id:2 value:13 type:null
     id:3 value:14 type:null
     id:4 value:15 type:null
     id:5 value:16 type:null
     id:6 value:17 type:null
  ]

上記のように、subquery要素内でもquery要素内と同じようにwhere要素などが展開されています。実際、subqueryとqueryは同一処理を行っています。

ただし、このsubquery要素はwhere要素と後述するlist要素の中でしか利用できません。なぜなら、インラインビューの副問い合わせやHAVING句での副問い合わせは、そのまま文字列として記述することが可能であるからです。逆にwhere要素やlist要素では、動的に他の要素と文字列で結合する必要があるために、このようなsubqueryという要素が必要になったのです。

このsubquery要素について説明します。なお、subquery要素には属性はありません。

subquery要素

Robbie DAOでは、C言語やJAVAの条件多分岐を行える、switch、case、default要素を用意しています。例えば、以下のように前方一致、中間一致、後方一致を動的に変更するようなことも可能になります。

        
    <query id="test8">
      SELECT ID, NAME, AGE FROM TEST_TABLE
      <where>
        <switch key="select_flg">
          <case value="forward">
            <var key="name" req="true" like="?%">NAME LIKE ?</var>
          </case>
          <case value="middle">
            <var key="name" req="true" like="%?%">NAME LIKE ?</var>
          </case>
          <case value="back">
            <var key="name" req="true" like="%?">NAME LIKE ?</var>
          </case>
          <default>
            <var key="name" req="true">NAME = ?</var>
          </default>
        </switch>
      </where>
    </query>

次に以下のようにプログラムを記述します。このプログラムによって、後方一致で検索を実行するように指定されます。

          
        XDaoFactory factory = new XDaoFactory("etc/test-config.xml");
        XDao dao = factory.createDaoInstance("TestDAO");

        Map params = new HashMap();
        params.put("name", "B");
        params.put("select_flg","back");
        
        List result = dao.query("test8", params);
        
        dao.release();

以下のようなSQLが実行されました。

        
 Query-ID=[test8]
 
 [Query =
  BEANS-CLASS=[null]
  Query-STRING=[
     
       SELECT ID, NAME, AGE FROM TEST_TABLE
       WHERE 
           ( 
             NAME LIKE ?
            ) 
       
     ]
  BIND-PARAMETERS=[
     id:1 value:%B type:null
  ]
 ]

なお、上記の例ではwhere句内の一つの条件式だけを変更しましたが、実際にはcaseおよびdefaultには、subqueryと同様に自由なSQLが記述することが可能であるため、SQL自体をすげ替えることも可能です。

上記で使用したXMLの要素と属性について説明します。

switch要素

key属性

case要素

value属性

default要素

通常、単一のレコードのINSERT文はそれほど複雑にはなりません。下記のような実装を行うことにで、ほとんどのケースに対応できるでしょう。

        
    <query id="test9">
      INSERT INTO TEST_TABLE (ID, NAME, AGE) VALUES (
          <var key="id" req="true">?</var>
          <var key="name" req="true">, ?</var>
          <var key="age" req="true">, ?</var>
      )
    </query>

const要素はバインド変数を持たない条件式ですが、展開の有無を指定するために、key属性とreq属性を使用しています。NSERTする値がない場合には、データベース属性名と条件式を同時に展開しないように指定しているのです。
従って、プライマリキーとなるIDは必須項目なので、req="true"に指定してます。

次に以下のようなJavaコードを実行します。

        
        XDaoFactory factory = new XDaoFactory("etc/test-config.xml");
        XDao dao = factory.createDaoInstance("TestDAO");
        
        Map params = new HashMap();
        params.put("id", new Integer(4));
        params.put("name", "EEE");
        params.put("age", new Integer(19));
        
        int count = dao.update("test9", params);
        
        System.out.println("update count=" + count);
        
        if (count == -1) {
            // INSERTに失敗した場合
            System.out.println(dao.getSQLException());
        }
        
        dao.release();

実行されるSQLは以下のようになります。

        
 Query-ID=[test9]
 
 [Query =
  Query-STRING=[
     
       INSERT INTO TEST_TABLE (
         
            ID
            , NAME
            , AGE
         
       ) VALUES (
         
            ?
            , ?
            , ?
         
       )
     ]
  BIND-PARAMETERS=[
     id:1 value:4 type:null
     id:2 value:EEE type:null
     id:3 value:19 type:null
  ]
 ]

XDao#update()の戻り値は、データベースの更新件数になります。従って、1件も更新できなければ0になります。また、一意制約違反などでSQLExceptionが発生した場合には、その理由によらず-1が返されます。その場合、発生したSQLExceptionはXDao#getSQLException()で取得することが可能です。

試しに前述のJavaコードをもう一度実行した場合、既に同じプライマリーキーのレコードが登録済みであるため、以下のような実行結果が画面に表示されます。

          
 update count=-1
 java.sql.SQLException: ORA-00001: 一意制約(TEST.SYS_C001547)に反しています

なお、一意制約違反かどうかを確認するにはjava.sql.SQLException#getErrorCode()を使用しますが、データベース製品によってこの値は違うため、十分に確認して利用してください。

今回の例には、これまでに登場していない新しいlist属性が使用されています。

list要素

join属性

UPDATE文も特に複雑にはならないでしょう。下記のようにSQLを記述します。

        
    <query id="test10">
      UPDATE TEST_TABLE SET
        <var key="name" req="true">NAME = ?</var>
        , <var key="age" req="true">AGE = ?</var>
      WHERE
        <var key="id" req="true">ID = ?</var>
    </query>

これまで、where要素やlist要素で、const要素やsubquery要素を結合してきましたが、もしすべての要素が必須であるならば、where要素やlist要素は必要ありません。したがって、上記のようなシンプルなSQLの記述になります。

次に以下のようにJavaコードを実行します。今回は、Mapでバインド変数を指定するのではなく、Quickスタートで使用したexample.TestBeanを使用して更新する値とプライマリキーの値を指定します。

          
        // DAOインスタンスの生成
        XDaoFactory factory = new XDaoFactory("etc/test-config.xml");
        XDao dao = factory.createDaoInstance("TestDAO");

        // 検索条件の設定
        TestBean bean = new TestBean();
        bean.setId(4);
        bean.setName("EEE");
        bean.setAge(19);
        
        // DAOの実行
        int count = dao.update("test10", bean);
        
        // DB接続が全て終了したらDAOをリリース
        dao.release();
        
        System.out.println("update count=" + count);

実行するSQLは次のようになります。


Query-ID=[test10]

 [Query =
  Query-STRING=[
     
       UPDATE TEST_TABLE SET
         NAME = ?
         , AGE = ?
       WHERE
         ID = ?
     ]
  BIND-PARAMETERS=[
     id:1 value:EEE type:null
     id:2 value:19 type:null
     id:3 value:4 type:null
  ]
 ]

var要素のkey属性とのマッピングは、TestBeanのイントロスペクション命名規則に応じたプロパティ名で行っています。この場合は大文字・小文字を区別するので、dataSourceNameのようならくだ文字の場合には注意してXMLのkey属性に値を記述してください。

DELETE文もWHERE句に削除対象のレコードを選択する条件を記述するのみですので、SELECT文やUPDATE文のWHERE句の記述が理解できていれば特に問題なく記述できるでしょう。


    <query id="test11">
      DELETE TEST_TABLE WHERE <var key="id" req="true">ID = ?</var>
    </query>

以下のようなJavaコードを実行します。

        
        XDaoFactory factory = new XDaoFactory("etc/test-config.xml");
        XDao dao = factory.createDaoInstance("TestDAO");
        
        Map params = new HashMap();
        params.put("id", new Integer(4));
        
        int count = dao.update("test11", params);
        
        dao.release();
        
        System.out.println("update count=" + count);

以下のようなSQLが実行されます。

 
 Query-ID=[test11]
 
 [Query =
  Query-STRING=[
     
       DELETE TEST_TABLE WHERE ID = ?
     ]
  BIND-PARAMETERS=[
     id:1 value:4 type:null
  ]
 ]