演習 5: テーブル結合を使用した、より複雑なデータの取得

これまでは、それぞれのデータ・アクセス関数が 1 つのテーブルからのみ行を取得していました。この演習では、EGL で SQL テーブル結合を使用して、一度に複数のテーブルを処理する方法を学習します。

1. 新規 SQLRecord パーツを保持する新規 EGL ソース・ファイルを作成します。
   1. 「プロジェクト・エクスプローラー」ビューで、EGLSQL プロジェクトを右クリックし、「新規」 > 「EGL ソース・ファイル」とクリックします。
   2. 「新規 EGL ソース・ファイル」ウィンドウで、「パッケージ」フィールドに名前として data を入力します。
   3. 「EGL ソース・ファイル名」フィールドに、records と入力します。
   4. 「終了」をクリックします。
   新規 EGL ソース・ファイルが作成されてエディターで開きます。
2. 新規ファイルの package ステートメントの下に、CUSTOMER と ORDERS の両方のテーブルを結合する新規 SQLRecord の名前を入力します。

   record OrderCustomerJoin type SQLRecord
     {tableNames = [["EGL.CUSTOMER", "C"], ["EGL.ORDERS", "O"]]}
   end

   このレコードの 1 行目はもう一方の SQLRecord パーツと同じで、レコードの名前と SQLRecord ステレオタイプを指定しています。2 行目は、このレコードが関連するテーブルを、tableNames プロパティーの値として指定しています。前の演習で使用した単一テーブル・レコードでは、このプロパティーに、Orders レコードの ORDERS テーブルなど 1 つのテーブルのみがリストされました。

   record Orders type sqlRecord { 
     tablenames=[["EGL.ORDERS"]]
   ...

   作成している OrderCustomerJoin レコードでは、2 つのテーブルからデータを取得するために、tableNames プロパティーに 2 つのテーブルがリストされる必要があります。わかりやすくするために、レコードにそれぞれのテーブル名の別名 (ORDERS を表す「O」と CUSTOMERS を表す「C」) を含めます。後で説明しますが、これらの別名により、レコード内のテーブル名を簡単に参照できるようになります。

   これでレコード・パーツの基本情報が指定されました。残りの情報は EGL によって埋められます。ただし、EGL がどのデータベースから接続情報を取得するのかを最初に指定する必要があります。

3. 次のようにして、EGL のデフォルトの SQL データベースとして Derby データベースを設定します。
   1. 「ウィンドウ」 > 「設定」とクリックします。
   2. 「設定」ウィンドウで、「EGL」を 展開して「SQL データベース接続」をクリックします。
   3. 「接続」リストで、使用するデータベース接続 (EGLDerbyR7 または EGLDerbyR71) を選択します。
   4. 「OK」をクリックします。
4. records.egl ファイルに戻り、レコード名の上にカーソルを置きます。
5. OrderCustomerJoin レコードの名前の上にカーソルを置いて右クリックし、「SQL レコード」 > 「SQL を検索」とクリックします。 EGL SQL 取得機能
6. パスワード・プロンプトで、ユーザー名およびパスワードのデフォルト値をそのまま使用します。 サンプル・データベースでは、特定のユーザー名またはパスワードは必要ありません。 EGL SQL 取得機能により、SQLRecord パーツの残りの部分、例えば、テーブル内の行や keyItems プロパティーに対するフィールドなどが作成されます。

   record OrderCustomerJoin type SQLRecord
     {tableNames = [["EGL.CUSTOMER", "C"], ["EGL.ORDERS", "O"]], 
     keyItems=[CUSTOMER_ID, ORDER_ID]}
   
     CUSTOMER_ID int
       {column="C.CUSTOMER_ID", isReadOnly=yes};
     FIRST_NAME string
       {column="C.FIRST_NAME", isReadOnly=yes, 
         isSqlNullable=yes, sqlVariableLen=yes, maxLen=30};
     LAST_NAME string
       {column="C.LAST_NAME", isReadOnly=yes, 
         isSqlNullable=yes, sqlVariableLen=yes, maxLen=30};
   ...
     ORDER_ID int
       {column="O.ORDER_ID", isReadOnly=yes};
     orders_CUSTOMER_ID int
       {column="O.CUSTOMER_ID", isReadOnly=yes, 
         isSqlNullable=yes};
     ORDER_AMOUNT decimal(8,2)
       {column="O.ORDER_AMOUNT", isReadOnly=yes, 
         isSqlNullable=yes};
     ORDER_DETAILS string
       {column="O.ORDER_DETAILS", isReadOnly=yes, 
         isSqlNullable=yes, sqlVariableLen=yes, maxLen=111};
   end

   各フィールドの column プロパティーの値が、tableNames プロパティーで指定したテーブルの別名で始まることに注意してください。 この別名は、特定のフィールドが関連するテーブルを追跡するのに便利です。
7. records.egl ファイルを保存しますが、開いたままにします。
8. printCustomerOrders という名前のプログラム・パッケージ内に、新規プログラム・パーツを作成します。
9. 新規プログラムからデフォルト・コードを除去します。
10. プログラムの main 関数で、上で作成した OrderCustomerJoin レコードに基づいて配列変数を作成します。

    allCustomerOrders OrderCustomerJoin[0];

    Ctrl + スペースを押すことにより、コンテンツ・アシストを使用してレコード・パーツ・タイプを挿入できることを覚えておいてください。 コンテンツ・アシストを使用しない場合は、手動で import ステートメントをファイル上部の package ステートメントのすぐ下に追加します。

    import data.OrderCustomerJoin;

11. open および forEach ステートメントを使用して、データベースから行を取得し、値をコンソールに出力します。

    tempRec OrderCustomerJoin;
    open resultSet for tempRec;
    forEach (tempRec)
      SysLib.writeStdout(tempRec.CUSTOMER_ID :: " " :: 
        tempRec.LAST_NAME :: " " :: tempRec.ORDER_ID);
    end

12. プログラムを保存し、生成して実行します。 テーブル結合で陥りやすいエラーについての知識がある場合は、このテーブル結合のエラーに気付き、なぜ出力があまり有用でないかにお気付きになると思われます。

    1 Filibuster 1
    1 Filibuster 2
    1 Filibuster 3
    ...
    1 Filibuster 22
    1 Filibuster 23
    2 Lundquist 1
    2 Lundquist 2
    2 Lundquist 3
    2 Lundquist 4
    ...
    2 Lundquist 22
    2 Lundquist 23
    3 Kingman 1
    3 Kingman 2
    ...

    データベースは、テーブル間でロジックの相互参照を行いませんでした。代わりに、CUSTOMERS テーブル内の各行と ORDERS テーブル内の各行とを可能な限りの組み合わせでペアにしました。この結果、それぞれの顧客があらゆるオーダーを行ったものとして記録されましたが、もちろんこれは誤っています。 open ステートメントの背後にある SQL コードを明示すると、SQL が Where 文節を使用することなく、両方のテーブルからすべての行を選択していることがわかります。

    open resultSet for tempRec with
      #sql{
        select
          C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, C.PASSWORD, 
          C.PHONE, C.EMAIL_ADDRESS, C.STREET, C.APARTMENT, 
          C.CITY, C.STATE, C.POSTALCODE, C.DIRECTIONS, 
          O.ORDER_ID, O.CUSTOMER_ID, O.ORDER_AMOUNT, 
          O.ORDER_DETAILS, O.ORDER_DATE, O.ORDER_STATUS
        from EGL.CUSTOMER C, EGL.ORDERS O
        order by
          C.CUSTOMER_ID, O.ORDER_ID asc
      };

    意味を成すようにテーブルを結合するには、単にテーブルの結合を返すのではなく、論理的にテーブルをマージする方法をデータベースに指示する必要があります。 明示的な SQL コードを編集してこれを行うことができますが、この場合、レコードを使用するたびに編集を行わなければなりません。そうしないと、不正確なデータを取得する危険が生じます。代わりに、デフォルトの選択条件をこのレコードに設定し、暗黙的な SQL コードが常に意味のあるテーブル結合を実行するようにします。

13. open ステートメントの背後にある SQL コードを明示した場合は、カーソルをレコード名の上に置いて右クリックし、「SQL ステートメント」 > 「削除」とクリックして、再度コードを暗黙的にします。
14. records.egl ファイルで、レコード・パーツの定義に戻ります。
15. defaultSelectCondition プロパティーをレコード・パーツの定義に追加します。

    record OrderCustomerJoin type SQLRecord
      {tableNames = [["EGL.CUSTOMER", "C"], ["EGL.ORDERS", "O"]], 
      keyItems=[CUSTOMER_ID, ORDER_ID],
      defaultSelectCondition = #sqlCondition{ condition }}

    defaultSelectCondition プロパティーでは、ユーザーがこのレコードで get または open を使用するときに用いられる、暗黙的な SQL コードとデフォルトの明示的な SQL コードの Where 文節を指定します。
16. #sqlCondition{} ブロック内で、デフォルトの「condition」テキストの代わりに、これら 2 つのテーブル間の SQL テーブル結合で使用する正しい Where 文節を入力します。この際、完全なテーブル名の代わりにテーブル別名を使用するようにします。

    defaultSelectCondition = #sqlCondition{ O.CUSTOMER_ID = C.CUSTOMER_ID }}

    キーワード WHERE を追加する必要はありません。この場合、EGL が自動的に SQL コードに挿入します。
17. ファイルを保管します。 ここで、open ステートメントの背後にある SQL コードを明示すると、次のようになります。

    open resultSet for tempRec with
      #sql{
        select
          C.CUSTOMER_ID, C.FIRST_NAME, C.LAST_NAME, C.PASSWORD, 
          C.PHONE, C.EMAIL_ADDRESS, C.STREET, C.APARTMENT, 
          C.CITY, C.STATE, C.POSTALCODE, C.DIRECTIONS, 
          O.ORDER_ID, O.CUSTOMER_ID, O.ORDER_AMOUNT, 
          O.ORDER_DETAILS, O.ORDER_DATE, O.ORDER_STATUS
        from EGL.CUSTOMER C, EGL.ORDERS O
        where
          O.CUSTOMER_ID = C.CUSTOMER_ID
        order by
          C.CUSTOMER_ID, O.ORDER_ID asc
      };

    これで、CUSTOMER と ORDERS の両方のテーブルに同じカスタマー番号がある行のみを返すように制限する Where 文節が SQL コードに含まれました。
18. プログラムを保存し、生成して実行します。

1 Filibuster 1
1 Filibuster 2
1 Filibuster 3
...
1 Filibuster 11
1 Filibuster 18
2 Lundquist 7
2 Lundquist 8
2 Lundquist 12
2 Lundquist 15
2 Lundquist 20
3 Kingman 13
4 Liebowitz 14
6 Springsteen 22
7 St. Louis 16
8 Sharov 17
9 Hudak 23