Querying a DB and attaching results to an Email

This example application 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 use case The XML 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 Aggregrator 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. Open the Example project in Anypoint Studio from Anypoint Exchange.

2. In your application in Studio, click the Global Elements tab. Double-click the HTTP Listener global element to open its Global Element Properties panel. Change the contents of the port field to required HTTP port e.g. 8081

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

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

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

    Host=smtp.gmail.com
    Port=587
    User=senderemailid%40gmail.com
    Password=senderpassword
   
    [email protected]
    [email protected]
    Subject=Export from Excel
   

6. In Global Elements tab click on the Generic Database Configuration and configure its properties as follows:

    URL=jdbc:mysql://localhost:3306/company?user=user&password=password
   

7. Now,run the mule application

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

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

###Go further

• Read about the Database Connector here
• Read about the Attachment Transformer Reference here
• Learn more about Anypoint DataWeave here