データベースのデータの挿入、更新、および削除の例

データベース用 Anypoint Connector (Database Connector) では、データベースのデータを管理できるようにする Insert、Update、Delete 操作がサポートされています。

次の例では、electronic という名前のテーブルが含まれる Products という名前のデータベーススキーマのデータを挿入、更新、削除します。このテーブルには、id、name、description、price、discount という列が含まれます。

次の SQL ステートメントでは、このテーブルを作成します。

CREATE TABLE electronic(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    description VARCHAR(255),
    price SMALLINT,
    discount TINYINT
)

Studio で Insert 操作を設定する

テーブル electronic がすでに作成されていて、dbConfig という名前の Database Connector 接続設定がある場合、テーブルに新しいレコードを挿入する Insert 操作を設定できます。

Studio のフローで、[Insert] 操作を選択します。
コネクタ設定画面で、実行する SQL クエリを [SQL Query Text (SQL クエリテキスト)] 項目に設定します。例:

    INSERT INTO electronic(name, description, price, discount)
    VALUES ('Coffee Machine', 'Model: XYZ99. Uses small size capsules.', 120, 5)

id パラメーターは、データベースによって自動的に生成される自動増分キーであるため、クエリに渡されません。

次のスクリーンショットは、Studio の設定を示しています。

XML エディターでは、<db:insert> 設定は次のように記述されます。

<db:insert config-ref="dbConfig">
  <db:sql>
    INSERT INTO electronic(name, description, price, discount)
    VALUES ('Coffee Machine', 'Model: XYZ99. Uses small size capsules.', 120, 5)
  </db:sql>
</db:insert>

Insert 操作の [Input Parameters (入力パラメーター)] 項目を設定する

入力パラメーターの利点を得るには、次のように同等の SQL クエリを構築します。

Studio のフローで、[Insert] 操作を選択します。
コネクタ設定画面で、実行する SQL クエリを [SQL Query Text (SQL クエリテキスト)] 項目に設定します。例:

INSERT INTO electronic(name, description, price, discount)
VALUES (:name, :description, :price, :discount)

[Input Parameters (入力パラメーター)] 項目で、次のパラメーターを設定します。

{
    name: 'Coffee Machine',
    description: 'Model:XYZ99. Uses small size capsules.',
    price: 120,
    discount: 5
}

次のスクリーンショットは、[Input Parameter (入力パラメーター)] 項目の設定を示しています。

XML エディターでは、<db:input-parameters> 設定は次のように記述されます。

<db:insert config-ref="dbConfig">
    <db:sql>
        INSERT INTO electronic(name, description, price, discount)
        VALUES (:name, :description, :price, :discount)
    </db:sql>
    <db:input-parameters>#[{
        name: 'Coffee Machine',
        description: 'Model:XYZ99. Uses small size capsules.',
        price: 120,
        discount: 5}]
    </db:input-parameters>
</db:insert>

Insert 操作の [Auto Generate Keys (自動生成キー)] 項目を設定する

Insert 操作は、2 つの項目 (クエリの影響を受けた行数を示す整数 affectedRows と、自動生成されたキーが含まれるマップ generatedKeys) が含まれる Statement Result オブジェクトを出力します。

次の例の出力では、行は正常にテーブルに追加されているが、マップ generatedKeys が空であることが示されています。マップが空になるのは、オーバーヘッドを回避するためにこの情報がデフォルトで無効になっているためです。

{
  "affectedRows": 1,
  "generatedKeys": {

  }
}

情報をアクティブ化してキーを生成する手順は、次のとおりです。

Studio のフローで、[Insert] 操作を選択します。
[Advanced (詳細)] タブで [Auto generate keys (自動生成キー)] 項目を [True] に設定します。

次のスクリーンショットは、Studio の設定を示しています。

XML エディターでは、<autoGenerateKeys> 設定は次のように記述されます。

<db:insert config-ref="dbConfig" autoGenerateKeys="true">
...
</db:insert>

ステートメントで影響を受けた行ごとに複数のキーが生成される場合、目的の列名が含まれるリストを設定して、返される列を指定できます。例:

Studio のフローで、[Insert] 操作を選択します。
[Advanced (詳細)] タブで、[Auto generate column names (列名を自動生成)] 項目を [Expression or Bean reference (式または bean 参照)] に設定して、項目を 1 つの文字列要素が含まれる DataWeave リストに設定します。
[expression (式)] 項目を ['id'] に設定して、id 列のみが返されるようにします。

次のスクリーンショットは、Studio の設定を示しています。

「Auto generate column names (列名を自動生成)」項目の設定

XML エディターでは、<autoGeneratedKeysColumnNames> 設定は次のように記述されます。

<db:insert config-ref="dbConfig" autoGenerateKeys="true" autoGeneratedKeysColumnNames="#[['id']]">
    ...
</db:insert>

Studio で Update 操作を設定する

electronic テーブルが事前に定義されている場合、テーブルのデータを更新する (たとえば、価格値が 100 を越えているすべての項目の割引を 10 % に設定する) には、次のように Update 操作を設定します。

Studio のフローで、[Update] 操作を選択します。
コネクタ設定画面で、"UPDATE electronic SET discount = :discount WHERE price > :price" のように、実行する SQL クエリを [SQL Query Text (SQL クエリテキスト)] 項目に設定します。
[Input parameters (入力パラメーター)] 項目を次のパラメーターに設定します。

{
      discount: 10,
      price: 100
}

次のスクリーンショットは、Studio の設定を示しています。

XML エディターでは、<db:update> 設定は次のように記述されます。

<db:update doc:name="Update">
			<db:sql ><![CDATA["UPDATE electronic SET discount = :discount WHERE price > :price"]]></db:sql>
			<db:input-parameters ><![CDATA[#[{
        discount: 10,
        price: 100
    }]]]></db:input-parameters>
		</db:update>

<![CDATA[…]]> ラッパーにより、クエリで > や " などの特殊文字を使用できます。ラッパーがない場合、これらの文字の XML でエスケープされたバージョン (> や " など) を使用する必要があります。

Studio で Delete 操作を設定する

electronic テーブルが事前に定義されている場合、id: 1 のレコードを削除するには、次のように Delete 操作を設定します。

Studio のフローで、[Update] 操作を選択します。
コネクタ設定画面で、DELETE FROM electronic WHERE id = :id のように、実行する SQL クエリを [SQL Query Text (SQL クエリテキスト)] 項目に設定します。
[Input parameters (入力パラメーター)] 項目を {id: 1} に設定します。

次のスクリーンショットは、Studio の設定を示しています。

XML エディターでは、<db:delete> 設定は次のように記述されます。

<db:delete config-ref="dbConfig">
    <db:sql>DELETE FROM electronic WHERE id = :id</db:sql>
    <db:input-parameters>#[{
        id: 1
    }]</db:input-parameters>
</db:delete>

データベースのデータの挿入、更新、および削除の例

Studio で Insert 操作を設定する

Insert 操作の [Input Parameters (入力パラメーター)] 項目を設定する

Insert 操作の [Auto Generate Keys (自動生成キー)] 項目を設定する

Studio で Update 操作を設定する

Studio で Delete 操作を設定する

関連情報