Querying a Database and Attaching Results to an Email

Shows you how to query a MySQL database, aggregate the query results and use the Attachment transformer to attach it to an email in a csv format

Example

The JSON data containing employee names is sent to the application using the HTTP POST method. The Splitter component then splits the list of employees and queries the MySQL DB individually for employee details. The Collection Aggregator component aggregates all the employee information into a List. This List is then converted to a .csv file and attached to an email which is sent using SMTP.

Set up and run the example

1. Import the example project into your workspace.

2. Start the MySQL server on your machine and create a connection by navigating to your mysql home directory and using the following command:

    mysql  -u root -p

3. Now, run import_orders.sql which is placed under src/main/resources to create a DB table

4. Click on the SMTP connector and configure its properties as follows:

    Host=smtp.gmail.com
    Port=587
    User=senderemailid%40gmail.com
    Password=senderpassword
   
    To=receiveremailid@gmail.com
    From=senderemailid@gmail.com
    Subject=Export from Excel

5. Now,run the mule application

6. Make a POST request using Postman to your localhost with the following xml code as the message body:

    <root>
     <employees>
      <employee>Chava Puckett</employee>
      <employee>Quentin Puckett</employee>
      <employee>Mona Sosa</employee>
     </employees>
    </root>

7. Verify that you received an email with the attachment which is basically a csv file of the queried employee records.

Documentation

Read full documentation in GitHub