Query a Single Record from a Database Example | MuleSoft Documentation
# Query a Single Record from a Database Example
The Anypoint Connector for Database (Database Connector) **Query single** operation enables you to execute an SQL query that returns a single record (row) from a database and access the result payload without any additional transformations. This operation does not stream any of the returned fields. When the query is executed, all the values of the returned row are read into memory and returned in the payload.
You must be careful, however, because if your row is too large, you could run out of memory. Also, if you use connection pooling you do not have to worry about your application running out of connections, because this operation returns the connection to the pool immediately after the query is executed.
You invoke the **Query single** operation in the same way as you invoke the **Select** operation; however, you always get a single result using the **Query single** operation, regardless of the number of records returned by the actual SQL query.
The following example shows how to configure the operation in a Mule application that retrieves books information from a table in a MySQL database. To accomplish this example, you must:
  * Run an SQL statement that creates a table called `BOOK` and inserts seven records of book information (title, author, bookshopName, isbn, price).
  * Configure the SQL database connection.
  * Create the Mule application.
  * Run the application and then test it by using with curl commands.


Figure 1. Query a Single Record from a Database flow
## Create the Table in the Database
Before configuring the SQL database connection and creating the Mule app, run the following SQL statement to create a table called `BOOK` and insert seven book records. An incremental `id` is associated with each new book added into to table. For example, the first book added to the table has an `id` equal to `1`, the second book has an `id` equal to `2`, and so on.
```
USE SYS;
CREATE TABLE BOOK(
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  title VARCHAR(100),
  author VARCHAR(100),
  bookshopName VARCHAR(100),
  isbn VARCHAR(13),
  price SMALLINT,
  PRIMARY KEY (id)
);
INSERT INTO BOOK(title, author, bookshopName, isbn, price) VALUES('Good Omens', 'Terry Pratchett and Neil Gaiman', 'A.Z. Fell and Co.', '9780060853983', 50);
INSERT INTO BOOK(title, author, bookshopName, isbn, price) VALUES('A Wizard of Earthsea', 'Ursula K. Le Guin', 'A.Z. Fell and Co.', '9780547773742', 20);
INSERT INTO BOOK(title, author, bookshopName, isbn, price) VALUES('IT', 'Stephen King', 'A.Z. Fell and Co.', '9781508297123', 20);
INSERT INTO BOOK(title, author, bookshopName, isbn, price) VALUES('The Nice and Accurate Prophecies of Agnes Nutter', 'Agnes Nutter', 'A.Z. Fell and Co.', '000000000000', 200);
INSERT INTO BOOK(title, author, bookshopName, isbn, price) VALUES('Cujo', 'Stephen King', 'A.Z. Fell and Co.', '9781501192241', 20);
INSERT INTO BOOK(title, author, bookshopName, isbn, price) VALUES('Nation', 'Terry Pratchett', 'A.Z. Fell and Co.', '9780552557795', 30);
INSERT INTO BOOK(title, author, bookshopName, isbn, price) VALUES('The Ocean at the End of the Lane', 'Neil Gaiman', 'A.Z. Fell and Co.', '9780062459367', 30);
```

## Configure a MySQL Database Connection
After you create the table, go to Anypoint Studio to configure the MySQL database connection:
  1. Create a new Mule project in Studio.
  2. Navigate to the **Global Elements** view.
  3. Click **Create** to open the **Choose Global Type** view.
  4. In the **Filter** field, type `http`, select **HTTP Listener config** , and click **OK**. This configuration is for the HTTP **Listener** source that initiates the Mule application flow.
  5. In the **HTTP Listener config** window, complete the following fields:
     * **Protocol** : `HTTP (Default)`
     * **Host** : `All Interfaces [0.0.0.0] (default)`
     * **Port** : `8081`


  4. Click **OK**.
  5. In the **Global Elements** view, click **Create** to open the **Choose Global Type** view.
  6. In the **Filter** field, type `database`, select **Database Config** , and click **OK**.
  7. In the **Database Config** window, set the **Name** field to `dbConfig`.
  8. In the **Connection** field, select **MySQL Connection**.
  9. Click **Configure** to add the required MySQL JDBC driver and select one of:
     * **Add recommended library** Install the recommended library.
     * **Use local file** Install the library using a local file.
     * **Add Maven dependency** Install a Maven dependency to add to the project.
  10. In the **Connection** section, complete the following fields:
     * **Host** : `localhost`
     * **Port** : `3306`
     * **User** : `root`
     * **Password** : `mysql`
     * **Database** : `sys`
  11. On the **Advanced** tab, set the **Pooling profile** field to **Edit inline**.
  12. Set the **Max pool size** field to `5`.
  13. Click **Test Connection** to confirm that Mule can connect to the database.
  14. Click **OK**.


The following screenshot shows the HTTP **Listener** global element configuration:
Figure 2. HTTP Listener configuration
The following screenshots show the Database global element configuration:
Figure 3. Database Config General configuration
## Create, Configure, Run and Test the Mule Application
After you configure the MySQL database connection, create, configure, run, and test the Mule app:
### Create a Flow by Configuring the HTTP Listener and Set Variable Component
To create the Mule flow:
  1. In the **Mule Palette** view, select the HTTP **Listener** source and drag it onto the canvas. The source initiates the flow by listening for incoming HTTP message attributes.
  2. In the **Connector configuration** field, select the `HTTP_Listener_config` global configuration.
  3. Set the **Path** field to `/select/book/{maxId}`. The `maxId` value parameter indicates how many books records to retrieve from the database. You can increment this value regardless of the number of maximum connections you configured in your database connection pool.
  4. In the **Mime Type** tab, set the **Mime Type** field to `application/json`.
  5. In the **Advanced** tab, set the **Allowed methods** field to `GET`.
  6. Drag a **Set Variable** component to the right of the **Listener** source. This component creates a new variable to save the database results that will be obtained from the **Query single** operation.
  7. Set the **Name** field to `bookCollection` and the **Value** field to `#[[]]`:
Figure 4. Set Variable configuration
### Configure the For Each Component, the Query Single Operation, and the Set Payload Component
Continue creating the Mule application using a **For Each** component to iterate the number of books requested by **HTTP Listener**. Then retrieve the book information using the **Query single** operation, and save the results in a payload variable:
  1. Drag a **For Each** component to the right of **Set Variable**.
  2. Set the **Collection** field to `#[1 to attributes.uriParams.maxId as Number]`. This expression iterates the collection of books in the table, from the first book (`1`) to the number of books set in the `maxId` parameter, when performing the HTTP request `/select/book/{maxId}`.
Figure 5. For Each configuration
  3. Drag the **Query single** operation into the **For Each** component.
  4. Set the **Connector configuration** field to `Database_Config` to connect to the MySQL database configuration.
  5. Set the **SQL Query Text** field to `SELECT id, title, author FROM BOOK WHERE id = :id`. This query selects the books information from the database.
  6. Set the **Input Parameters** field to `![CDATA[#[{'id': payload }]]]`. This expression maps the key parameter `id` (referenced in the previous **SQL Query Text** expression) to `payload` which is the result value of books information retrieved using the `SELECT` query.
Figure 6. Query single configuration
  7. Drag another **Set Variable** component to the right of the **Query single** operation.
  8. Set the **Name** field to `bookCollection` and the **Value** field to `#[vars.bookCollection ++ [payload]]`. The original variable `bookCollection` now saves the `payload` of the retrieved book information.
  9. Drag a **Set Payload** component to the right of the **For Each** component.
  10. Set the **Value** field to `#[vars.bookCollection]` to save the variable content as a new output payload:


Figure 7. Set Payload configuration
### Run and Test the Mule Application
To run and test the Mule application:
  1. Save the project in Studio.
  2. Test the app by running `curl localhost:8081/select/book/7` at the command line. This command selects the first seven books of the table, regardless of the maximum number of connections (`5`) configured in the pool.


## XML for Querying a Single Record from a Database
Paste this code into your Studio XML editor to quickly load the flow for this example into your Mule app:
```
<?xml version="1.0" encoding="UTF-8"?>
<mule xmlns:db="http://www.mulesoft.org/schema/mule/db"
	xmlns:http="http://www.mulesoft.org/schema/mule/http"
	xmlns="http://www.mulesoft.org/schema/mule/core" xmlns:doc="http://www.mulesoft.org/schema/mule/documentation"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd"></mule>
	<http:listener-config name="HTTP_Listener_config" doc:name="HTTP Listener config" doc:id="9501220e-fba8-440b-afdf-14b4ca010fe8" >
		<http:listener-connection host="0.0.0.0" port="8081" />
	</http:listener-config>
	<db:config name="Database_Config" doc:name="Database Config" doc:id="20db2231-3668-48d0-bb60-66f120fc99c8" >
		<db:my-sql-connection host="localhost" port="3306" user="root" password="mysql" database="sys">
			<db:pooling-profile maxPoolSize="5"/>
		</db:my-sql-connection>
	</db:config>
	<flow name="querysingleForeachFlow">
		<http:listener doc:name="Listener" config-ref="HTTP_Listener_config" path="/select/book/{maxId}" outputMimeType="application/json" allowedMethods="GET"/>
		<set-variable variableName="bookCollection" value="#[[]]" />
		<foreach collection="#[1 to attributes.uriParams.maxId as Number]">
			<db:query-single doc:name="Query single" config-ref="Database_Config">
				<db:sql>SELECT id, title, author FROM BOOK WHERE id = :id</db:sql>
				<db:input-parameters><![CDATA[#[{'id': payload }]]]></db:input-parameters>
			</db:query-single>
			<set-variable variableName="bookCollection" value="#[vars.bookCollection ++ [payload]]" />
		</foreach>
		<set-payload value="#[output application/json --- { books : vars.bookCollection }]" />
	</flow>
```

## See Also
  * Database Connector Examples
  * MuleSoft Help Center