Querying a MySQL Database

Illustrates how to use the database connector to connect to a MySQL database. After reading this document and creating and running the example in Mule, you should be able to leverage what you have learned to create an application that connects to a MySQL database.

Example

In the Mule application, an inbound HTTP connector listens for HTTP GET requests with the form: http://:8081/?lastname=. The HTTP connector passes the value of as one of the message properties to a database connector. The database connector is configured to extract this value and use it for the SQL query listed below.

select first_name from employees where last_name = #[message.inboundProperties['lastname']]

As you can see, the MEL expression in the SQL query references the value of the parameter passed to the HTTP connector. So if the HTTP connector receives http://localhost:8081/?lastname=Smith, the SQL query will be select first_name from employees where last_name = Smith.

The database connector instructs the database server to run the SQL query, retrieves the result of the query, and passes it to the object-to-JSON message processor which converts the result to JSON. Since the HTTP connector is configured as request-response, the result is returned to the originating HTTP client.

Set Up and Run the Example

1. Download and setup the MySQL driver in it's default location. You can even check out some of the YouTube videos for assistance with this step.

2. Start the MySQL server from System Preferences

1. Creating the MySQL Database : Visit this page and click on View the script for copy-paste to copy the script. Navigate to the MySQL driver on the command terminal and then paste the script to create a MySQL database called Company which has tables for employees and roles. The script also creates a password protected user and grants it access to the database.

    username: generatedata; password:generatedata

2. Open the Mule Example project in the Anypoint studio interface.

3. Open database-to-json.xml file located in src/main/app directory. Set url attribute of the db:generic-config element to:

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

4. Configuring the Database Connector for this Example:In this example, the database connector retrieves data from a MySQL database listening on port 3306, the default for MySQL. Ensure that MySQL_Configuration points to the local jdbc MySQL server on your machine. You may watch this video for assistance with this step.

1. Run the example application in Anypoint Studio or Standalone

2. Got to your web browser and type in the following request:

    http://localhost:8081/?lastname=Puckett

   and you should get the following JSON response:

    [{"first_name":"Chava"},{"first_name":"Quentin"}]

Documentation

Read full documentation in GitHub

Examples to Try Next