Contact Us 1-800-596-4880
  1. Homepage
  2. Database Connector (1.11)

  3. Database Connector Examples

All Connectors

Database Connector Examples - Mule 4

Anypoint Connector for Database (Database Connector) examples help you configure database connections, query a database, execute stored procedures, execute DDL statements, execute scripts, execute database transactions, use bulk operations, and configure custom data types:

Execute Bulk Operations

The insert, update, and delete operations can be used for the cases in which each input parameter can take only one value. Alternatively, bulk operations allow you to run a single query using a set of parameters values.

You can avoid unnecessary steps by doing a bulk operation so that:

  • The query is parsed only once.

  • Only one database connection is required since a single statement is executed.

  • Network overhead is minimized.

  • RDBMS can execute the bulk operation atomically.

For these use cases, the connector offers three operations: <db:bulk-insert>, <db:bulk-update> and <db:bulk-delete>.

These operations are similar to their single counterparts, except that instead of receiving input parameters as key-value pairs, the operations expect them as a list of key-value pairs.

For example:

<db:bulk-insert config-ref="dbConfig" >
  <db:bulk-input-parameters>
    #[[{'id': 2, 'name': 'George', 'lastName': 'Costanza'}, {'id': 3, 'name': 'Cosmo', 'lastName': 'Kramer'}]]
  </db:bulk-input-parameters>
  <db:sql>
    insert into customers (id, name, lastName) values (:id, :name, :lastName)
  </db:sql>
</db:bulk-insert>

If you don’t use bulk operations, when performing a delete operation, many rows could match the criteria and get deleted if only one criteria (POSITION = X) is provided. The same concept applies for update. If you use UPDATE PRODUCTS set PRICE = PRICE * 0.9 where PRICE > :price, you may want to apply a 10% discount on many products, but the price input parameter accepts only one value.

If you want to apply different discount rates on products that have different prices, you can execute many operations.

The following example is a payload that is a list of objects of the following structure { price : number, discountRate: number}:

<foreach>
  <db:update config-ref="dbConfig">
    <db:input-parameters>
     #[
      {
        'discountRate' : payload.discountRate,
        'price' : payload.price,
      }
    ]
    </db:input-parameters>
    <db:sql>
      UPDATE PRODUCTS set PRICE = PRICE * :discountRate where PRICE > :price
    </db:sql>
  </db:update>
</foreach>

The previous operation accomplishes the task but is inefficient. For each element in the list, one query must be executed for each element of the operation:

  • The query is parsed.

  • Parameters are resolved.

  • A connection to the database is acquired (either by getting one from the pool or establishing a new one).

  • All the network overhead is paid.

  • The RDBMS processes the query and applies changes.

  • The connection is released.

If an error arises while executing one of the operations (for example, if bulk insert fails to insert 1 out of 100 rows), a single exception is thrown.

It may happen that while some statements in the bulk operation can be successfully executed, some may result in an error. When this occurs, it will be up to the driver to either:

  1. Stop execution immediately and ignore all remaining operations, or

  2. Continue to execute the remaining statements.

In both cases, whenever an error occurs you can examine your application logs to see which caused the failure. When this occurs a single exception is thrown describing what went wrong.

See Also

MuleSoft Help Center

Back to top
View on GitHub