To Query a Database

This procedure uses a MySQL sample database that you can load using a script. Download the script using the following URL:

https://docs.mulesoft.com/mule-user-guide/v/3.9/_attachments/create.sample.db.sql

The script creates the company database having tables named employees and roles. The employees table contains employee first and last names, birth dates, gender, hire date, and employee number (the primary key). For example purposes, you configure the database connector using an expression in the SQL query that references the value of an input parameter:

#[message.inboundProperties.'http.query.params'.lastname]

You run the app, enter http://localhost:8081/?lastname=Smith in a client browser, and the database connector selects the record for Smith from the database. The object-to-JSON message processor converts the result to JSON.

In a real-world situation, you are responsible for testing input parameters to prevent SQL injection. Alternatively, you can use a stored procedure, which supports the :param syntax, to safely handle input parameters.

In Studio, create a new project. Drag an HTTP, Database, and Object-to-Json connectors to the canvas:
Configure the HTTP connector:
- In HTTP properties, click .
- Click OK to accept the defaults, and save.
  
  The error indicator on the HTTP connector extinguishes.
In Database properties, click , and set the following options:
- Host: The name of the host running MySQL.
- Port: 3306
- User and Password: Your database user name and password.
- Database: company
- MySQL Driver: Browse to the location of and select the driver that is compatible with your database.
  
  Test and save the settings.

In Database properties, set options according to the following table:

Parameter Value

Parameter	Value
Operation	`Select`
Query Type	`Parameterized`
Parameterized SQL Statement	`select first_name from employees where last_name = #[message.inboundProperties.'http.query.params'.lastname]`

Operation

Select

Query Type

Parameterized

Parameterized SQL Statement

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

Run the project as a Mule application.
Assuming your host is localhost, activate the flow by entering the following URL in a browser or in a curl command. For example:
```
curl http://localhost:8081/?lastname=smith
```
[{"first_name":"Mara"}] appears.

To Query a Database

See Also