Contact Free trial Login

Insert, Update, and Delete Data with the Database Connector - Mule 4

The Database connector supports insert, update, and delete operations. Similar to the select operation, these operations support dynamic queries and parameterization by using embedded DataWeave transformations while also supporting fetchSize, maxRows, and timeout parameters. Although examples of input parameterization, dynamic queries, and parameter usage are provided, you can consult the SELECT operation documentation for more information.

Insert Example

Suppose there is a database schema named Products that has a table named electronic. electronic contains columns named id, name, description, price, and discount. The following SQL statement creates the table:

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

For an example of creating a table using the Database connector, check how to Execute DDL Statements.

Given that the table is already created and that a there is a connection configuration named dbConfig, the following operation inserts a new record in the electronic table:

<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>

Note that the id parameter is not passed in the query because it is an autoincrement key that is generated automatically by the database.

To benefit from the advantages of input parameters, structure the equivalent query as follows:

<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>

The insert operation outputs a Statement Result object that contains two fields: an affectedRows integer that indicates how many rows were affected by the query, and a generatedKeys map that contains the autogenerated keys. Given this example, the output is as follows:

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

  }
}

The generatedKeys map is empty, although a row is successfully added to the table. The map is empty because this information is disabled by default to avoid overhead. To activate the information and generate keys, add the autoGenerateKeys parameter, and then set it to true:

<db:insert config-ref="dbConfig" autoGenerateKeys="true">
   <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>

If the statement generates multiple keys per affected row, you can specify which columns should be returned by setting the parameter autoGeneratedKeysColumnNames to a list that contains the desired column names. For example, to ensure that only the id column is returned, set the parameter to a DataWeave list with a single string element:

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

Update Example

Given the table that is defined in the insert example, the operation to update the electronic table to set the discount to 10 percent for all items that have a price value above 100 is as follows:

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

The <![CDATA[…​]]> wrapper allows you to use special characters, such as > or ", in the query. Otherwise, you must use XML-escaped versions of those characters, such as &gt; and &quot;.

When you use the Anypoint Studio visual user interface for connectors instead of the XML code view, you can type special characters directly into the SQL Query Text box, and Anypoint Studio automatically changes the character to its escaped version in the XML view.

Delete Example

Given the table defined in the [_insert_example], the following operation deletes the record with id 1 from the electronic table:

<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>

Was this article helpful?

💙 Thanks for your feedback!

Edit on GitHub