Contact Us 1-800-596-4880

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.

  1. In Studio, create a new project. Drag an HTTP, Database, and Object-to-Json connectors to the canvas:

    http database object-json
  2. Configure the HTTP connector:

    • In HTTP properties, click add.

    • Click OK to accept the defaults, and save.

      The error indicator on the HTTP connector extinguishes.

  3. In Database properties, click add, 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.

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

    Parameter Value

    Operation

    Select

    Query Type

    Parameterized

    Parameterized SQL Statement

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

  5. Run the project as a Mule application.

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

View on GitHub