---
schema_version: "1"
id: c80ff26dd060e9bbfc3525d0396d1e4e8db92d7a6ddbbc87d24b9d0a926e49e1
title: Execute Bulk Operations Examples
collection: mulesoft
version: current
locale: en-us
portal_url: https://docs.salesforce.com/mulesoft/current/en-us/db-connector/1.14/database-execute-bulk.md
canonical_url: https://docs.mulesoft.com/db-connector/1.14/database-execute-bulk
source_site: docs.mulesoft.com
content_type: documentation
content_hash: 54cb7f874190e141ab5b9b62cd017d82d259cd46a4049d01f60262b2215f4375
description: MuleSoft Documentation Site
release: "1.14"
latest: false
---
---
product: Database Connector
version: 1.14
---

# Execute Bulk Operations Examples

> For the full documentation index, see: https://docs.mulesoft.com/llms.txt

Anypoint Connector for Database (Database Connector) supports the **Bulk insert**, **Bulk update**,
and **Bulk delete** operations that enable you to manage data in bulk. This improves the performance
compared to executing a single insert, update, or delete operation at various times.

These operations are similar to their single counterparts, except that instead of receiving input
parameters as key-value pairs, the bulk operations expect a list of key-value pairs. Avoid
unnecessary steps by doing a bulk operation:

- Parse the query only once

- Require only one database connection since a single statement is executed

- Minimize network overhead

- RDBMS can execute the bulk operation

While some statements in the bulk operation can be successfully executed, some can result in an
error (for example, if the **Bulk insert** operation fails to insert 1 out of 100 rows, a single
exception is thrown describing what went wrong). It will be up to the driver to either stop the
execution immediately and ignore all the remaining operations, or continue to execute the remaining
statements. In both cases, whenever an error occurs, examine the Mule app logs that provide details
on what caused the failure.

The following example shows how to configure the operations in a Mule application that inserts,
updates, and deletes customers and products data into a MySQL database. To accomplish this example,
you must:

- Run an SQL statement that creates two tables called `CUSTOMERS` and `PRODUCTS`.

- Configure the SQL database connection.

- Create the Mule app.

- Run the application and then test it by using with curl commands.

![Bulk insert](/mulesoft/current/en-us/db-connector/1.14/_images/database-bulkoperations-flow.png)

## Create the Table in the Database

Before configuring the SQL database connection and creating the Mule app, run the following SQL
statement to create the tables called `CUSTOMERS` and `PRODUCTS` and insert 4 products into the
table `PRODUCTS`:

```sql
CREATE DATABASE DEMO;

USE DEMO;

CREATE TABLE CUSTOMERS (
ID bigint AUTO_INCREMENT PRIMARY KEY,
NAME varchar(30) NOT NULL,
LAST_NAME varchar(30) NOT NULL
);

CREATE TABLE PRODUCTS (
ID bigint AUTO_INCREMENT PRIMARY KEY,
PRICE numeric(8, 2) NOT NULL,
ITEM varchar(30) NOT NULL
);

INSERT INTO PRODUCTS (PRICE, ITEM) values (50.0, 'shampoo');
INSERT INTO PRODUCTS (PRICE, ITEM) values (150.5, 'olive oil');
INSERT INTO PRODUCTS (PRICE, ITEM) values (11, 'coconut oil');
INSERT INTO PRODUCTS (PRICE, ITEM) values (133, 'wine');
```

## Configure a MySQL Database Connection

After you create the table, go to Anypoint Studio to configure the MySQL database connection:

1.  Create a new Mule project in Studio.

2.  Navigate to the **Global Elements** view.

3.  Click **Create** to open the **Choose Global Type** view.

4.  In the **Filter** field, type `http`, select **HTTP Listener config**, and click **OK**.  
    This configuration is for the HTTP **Listener** source that initiates the Mule application flow.

5.  In the **HTTP Listener config** window, complete the following fields:

    - **Protocol**: `HTTP (Default)`

    - **Host**: `All Interfaces [0.0.0.0] (default)`

    - **Port**: `8081`

6.  Click **OK**.

7.  In the **Global Elements** view, click **Create** to open the **Choose Global Type** view.

8.  In the **Filter** field, type `database`, select **Database Config**, and click **OK**.

9.  In the **Database Config** window, set **Name** to `dbConfig`.

10. In the **Connection** field, select **MySQL Connection**.

11. Click **Configure** to add the required MySQL JDBC driver and select one of:

    - **Add recommended library**  
      Install the recommended library.

    - **Use local file**  
      Install the library using a local file.

    - **Add Maven dependency**  
      Install a Maven dependency to add to the project.

12. In the **Connection** section, complete the following fields:

    - **Host**: `localhost`

    - **Port**: `3306`

    - **User**: `root`

    - **Password**: `mulesoft`

    - **Database**: `demo`

13. Click **Test Connection** to confirm that Mule can connect to the database.

14. Click **OK**.

![The HTTP Listener global element configuration](/mulesoft/current/en-us/db-connector/1.14/_images/database-querysingle-example-1.png)

The following screenshot shows the Database global element configuration:

![The database general configuration setting the Host, Port, User, and Password fields.](/mulesoft/current/en-us/db-connector/1.14/_images/database-bulk-example-2.png)

## Create the First Flow

The first flow inserts in bulk the customers' items into the `CUSTOMERS` table, follow these steps
to create the flow:

1.  In the **Mule Palette** view, select the HTTP **Listener** source and drag it onto the canvas.  
    The source initiates the flow by listening for incoming HTTP message attributes.

2.  In the **Connector configuration** field, select the `HTTP_Listener_config` global
    configuration.

3.  Set **Path** to `/insert`.

4.  Drag a **Bulk insert** operation to the right of the **Listener** source.

5.  For **Connector configuration**, select `Database_Config`.

6.  In **Input Parameters**, add the following expression:

    `[{'id': 2, 'name': 'George', 'lastName': 'Costanza'}, {'id': 3, 'name': 'Cosmo', 'lastName': 'Kramer'}]`

7.  In **SQL Query text**, add the following:

    `INSERT INTO CUSTOMERS (NAME, LAST_NAME) values (:name, :lastName)`

## Create the Second Flow

The second flow updates in bulk the price of the products in the `PRODUCTS` table, follow these
steps to create the flow:

1.  In the **Mule Palette** view, select the HTTP **Listener** source and drag it below the first
    flow.  
    The source initiates the flow by listening for incoming HTTP message attributes.

2.  In the **Connector configuration** field, select the `HTTP_Listener_config` global
    configuration.

3.  Set **Path** to `update`.

4.  Drag a **Set Payload** component to the right of the **Listener** source.

5.  In **Value** add the following expression:

    ```text
    [{
    	discountRate: 0.1,
    	price: 50
    },
    {
    	discountRate: 0.2,
    	price: 150.5
    }]
    ```

6.  Drag a **Bulk update** operation to the right of the **Set Payload** component.

7.  For **Connector configuration**, select `Database_Config`.

8.  In **SQL Query text**, add the following expression:

    `UPDATE PRODUCTS SET PRICE = PRICE * :discountRate WHERE PRICE = :price`

## Create the Third Flow

The third flow deletes in bulk products from the `PRODUCTS` table, follow these steps to create the
flow:

1.  In the **Mule Palette** view, select the HTTP **Listener** source and drag it below the first
    flow.  
    The source initiates the flow by listening for incoming HTTP message attributes.

2.  In the **Connector configuration** field, select the `HTTP_Listener_config` global
    configuration.

3.  Set **Path** to `/delete`.

4.  Drag a **Set Payload** component to the right of the **Listener** source.

5.  In **Value** add the following expression:

    ```text
    [{
    	price: 11
    },
    {
    	price: 133
    }]
    ```

6.  Drag a **Bulk delete** operation to the right of the **Set Payload** component.

7.  For **Connector configuration**, select `Database_Config`.

8.  In **SQL Query text**, add the following expression:

    `DELETE FROM PRODUCTS WHERE PRICE = :price`

## Run and Test the Mule App

To run and test the Mule app:

1.  Save the project in Studio.

2.  Click **Run > Run** to deploy the app.

3.  In your internet browser, test the app by running the following CURL commands:  
    `curl [http://localhost:8081/insert](http://localhost:8081/insert)`  
    `curl [http://localhost:8081/update](http://localhost:8081/update)`  
    `curl [http://localhost:8081/delete](http://localhost:8081/delete)`

## XML for Mule App Example

Paste this code into your Studio XML editor to quickly load the flow for this example into your Mule
app:

```xml
<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:db="http://www.mulesoft.org/schema/mule/db" xmlns:http="http://www.mulesoft.org/schema/mule/http"
	xmlns="http://www.mulesoft.org/schema/mule/core"
	xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd">
	<db:config >
		<db:my-sql-connection host="localhost" port="3306" user="root" password="mulesoft" database="demo" />
	</db:config>
	<http:listener-config name="HTTP_Listener_config" >
		<http:listener-connection host="0.0.0.0" port="8081" />
	</http:listener-config>
	<flow name="dbcon-demoFlow">
		<http:listener config-ref="HTTP_Listener_config" path="/insert"/>
		<db:bulk-insert config-ref="Database_Config">
			<db:bulk-input-parameters ><![CDATA[#[[{'id': 2, 'name': 'George', 'lastName': 'Costanza'}, {'id': 3, 'name': 'Cosmo', 'lastName': 'Kramer'}]]]]></db:bulk-input-parameters>
			<db:sql ><![CDATA[INSERT INTO CUSTOMERS (NAME, LAST_NAME) values (:name, :lastName)]]></db:sql>
		</db:bulk-insert>
	</flow>
	<flow name="dbcon-demoFlow1" >
		<http:listener config-ref="HTTP_Listener_config" path="update"/>
		<set-payload value="#[[{
	discountRate: 0.1,
	price: 50
},
{
	discountRate: 0.2,
	price: 150.5
}]]" doc:name="Set Payload" />
		<db:bulk-update config-ref="Database_Config">
			<db:sql ><![CDATA[UPDATE PRODUCTS SET PRICE = PRICE * :discountRate WHERE PRICE = :price]]></db:sql>
		</db:bulk-update>
	</flow>
	<flow name="dbcon-demoFlow2" >
		<http:listener config-ref="HTTP_Listener_config" path="/delete"/>
		<set-payload value="#[[{
	price: 11
},
{
	price: 133
}]]" doc:name="Set Payload" />
		<db:bulk-delete doc:name="Bulk delete" config-ref="Database_Config">
			<db:sql ><![CDATA[DELETE FROM PRODUCTS WHERE PRICE = :price]]></db:sql>
		</db:bulk-delete>
	</flow>
</mule>
```

## See Also

- [Database Connector Examples](https://docs.mulesoft.com/db-connector/1.14/database-connector-examples)