JDBC Example

On the client-side user interface on a browser (see image below), the user clicks the Load CSV Data button to submit a request to the server-side application. The request asks Mule to save CSV data to the database, then return the data in XML format. (For this example, the developer pre-populated the CSV Data to Load field with data to enable you to easily use a functional example.)

The request meets the JDBC example application and activates the CSVLoader flow. In this flow, the AJAX inbound endpoint “listens” for user requests on the /services/jdbc/loadCSV channel; that is to say, it waits for users to press the Load CSV Data. Note that this endpoint listens only for user requests to load CSV data, not for requests to retrieve, clean up or clone database records: one type of request, one flow to process it. Further, because it has a one-way message exchange pattern, this Ajax endpoint only receives requests from the end user; it does not return responses.

In addition to listening for, and receiving, a message, the Ajax endpoint also transforms the data format of the message from CSV data to Java map objects. To perform this transformation, the endpoint references a Global CSV to Maps Transformer to follow specific instructions on how to transform the data format. The image below, left, displays the Ajax endpoint’s properties panel in Studio; note the section outlined in red in which the endpoint references the global CSV to maps transformer. The image below, right, displays the global CSV to maps transformer’s properties panel in Studio.

After receiving and converting the message payload with the Ajax endpoint, the flow uses a request-response JDBC Endpoint to save CSV data to the database. In this case, the CSV data contains information about Mules that live on end user’s ranches, such as the Mule’s name, color and weight.

The JDBC endpoint in this flow performs two tasks:

To perform these tasks, the JDBC endpoint works in conjunction with two other things: a Global JDBC Connector and a SQL Query, or stored procedure.

The JDBC connector — a global element in the JDBC example application — helps the JDBC Endpoint with its tasks (listed above).

The JDBC endpoint in the CSVLoader flow simply references the connector information (see image below, left) and the SQL query (below, right) in the global JDBC connector. Note that you can store SQL queries on an individual JDBC endpoint in a flow and it will perform the procedures exactly the same as though referencing a global JDBC connector. The only reason to use a global JDBC connector to store your queries is to save time — if you anticipate using a query more than once in your application, you can efficiently write it once, in a global connector, then refer to it multiple times.

Because the end user expects a response in XML format, the CSVLoader flow uses a Maps to XML Transformer to convert the message payload’s data format. Recall that the end user sent a request in CSV data format, and the Ajax endpoint transformed the content to map objects so the JDBC endpoint could save the data to the database. Mule now converts the message a second time in order to comply with the demand for an XML response.

The next building block in the flow, the Property Transformer, sets a property on the message payload to specify the type of content it contains (for HTTP protocol). In this case, the Set Property property transformer sets the value of the property to text/xml.

Finally, the Ajax outbound endpoint returns a response to the end user. It directs its response — now a message payload which contains the CSV data in XML format — to the specific channel on which the client expects a response, /jdbc/reply.

After Mule processes the end user request, it returns a response to the end user as a message in the Response field in the client-side user interface on her browser. The CSVLoader flow returns a message that appears as an XML list of all the Mules in the database (see image below).

+ image::csv-loaded4.png[csv_loaded4]

Notice that the CSVLoader flow also contains a Rollback Exception Strategy.

If an error occurs in this flow, the rollback exception strategy catches the exception and performs two actions:

When rollback exception strategy handles an error, it ultimately returns a response to the end user’s browser.

On the client-side user interface on a browser, the user clicks the Clone Mules button to submit a request to the server-side application.

In this flow, the Ajax endpoint “listens” for user requests on the services/jdbc/clone channel.

Next, the request-response JDBC endpoint calls the callCloneStoredProc procedure. Like the JDBC endpoint in the CSVLoader flow, the endpoint itself does not contain an SQL query, it simply references a stored procedure — callCloneStoredProc — on the global JDBC connector. According to the procedure, Mule clones the contents of the database, then passes the new count of total records to the next message processor as a Java object.

The Object to JSON Transformer converts the data format of the message payload so that the content — a count of all the Mules in the database — becomes human-readable.

Lastly, the Ajax outbound endpoint returns a response to the end user. It directs its response to the specific channel on which the client expects a response, /jdbc/reply.

After Mule processes the end user request, it returns a response to the end user as a message in the Response field in the client-side user interface on his browser. The CSVLoader flow returns a message that appears as a simple record count (see image below).