Mule ESB Tutorials: Building Restful API

In this article, we will build, run, and test a Mule Restful Application which would connect to a database using the database connector.

Here is the demonstration of this article in youtube.

Create a Mule project
1. Open Anypoint Studio.
2. Select File > New > Mule Project.
3. Set the Project Name to muledj.restapiops.crud.
4. Ensure the Runtime is set to the latest version of Mule.
5. Click Finish.
Create an HTTP connector endpoint to receive requests, in the Mule Palette, select the Connectors tab. Drag an HTTP connector from the Mule Palette to the canvas.In the HTTP properties view that opens at the bottom of the window, click the Add button next to
connector configuration.
In the Global Element Properties dialog box, change the port and base path as shown and click OK.

In the HTTP properties view, set the path to /InsertDept.Set the allowed methods to POST. Click the Apply Changes button; the errors in the Problems view should disappear.

In order to connect to Microsoft SQL Server database, we will add the sqljar to the build path by Right-clicking on the project and select Build Path > Add to External Archives; you should now see the JAR file in the project's Referenced Libraries.

Let's add a Database connector endpoint, in the Mule Palette, select the Connectors tab. Drag a Database connector to the process section of the flow. Double-click the Database endpoint.

In the Database properties view, click the Add button next to connector configuration. In the Choose Global Type dialog box, select Connector Configuration > Generic Configuration and click OK.

On the Generic Database Configuration, choose Database URL and set URL and browse for SQL Server Driver in Driver Class Name as shown.

Click the Test Connection button; you should get a successful test dialog box. Make sure the connection succeeds before proceeding.

In the Database properties view, set the operation to INSERT. Add a query to Insert the data to the Department table as shown

In the Mule Palette, select the Transformers tab, drag a Set Payload transformer from the Mule Palette into the process section of the flow. Configure the Set Payload transformer

In the Set Payload properties view, set the value as shown.

Drag a property from mule palette and set property name as the Content-Type and value as application/json

Test the application, Run the project. In the Save and launch dialog box, select Always save resources before launching and click OK.

Watch the console, and wait for the application to start. Once it has started, return to Postman. In Postman, make a post request; you should get the response message

Return to Anypoint Studio. Stop the project.

Now let's enhance to read the data from the post payload, instead of hard-coded values.

Drag a JSON to Object transformer from the Mule Palette and drop it after the HTTP listener, this allows as to access the body using mule expression as shown below. Let's Run the project and validate the application.

In Postman, make a post request with json body as shown; you should get the response message. Return to Anypoint Studio and stop the project.

Now let's add GET Operation to our project.Drag an HTTP connector from the Mule Palette to the canvas.In the HTTP properties view that opens at the bottom of the window, use the same connector configuration used for HTTP post and n the HTTP properties view, set the path to /FetchDept.Set the allowed methods to GET.

Bring in database connector and in the Database properties view, choose the same MS Generic DB Config and set the operation to SELECT. Add a SELECT query to fetch data from the Department table as shown.

Use object to JSON transform to respond to the client in JSON format. Let's run the project and test it. In postman, try to do a get operation as shown, this would return all the records available in the table.

Let's modify parameterize the SELECT query with query parameter of Get Operation. Get Request would contain a HTTP query param and we would use it in the where clause of the select statement as shown below,

In Postman, make another request to; you should now see the info for the Department with ID of 1 and 2.

Now let's make new flows to handle PUT and DELETE request. As before, configure the HTTP listener for PUT and DELETE flows and use appropriate DB operation to achieve its functionality. Below screenshots reflects the flow design and HTTP and DB configurations.
Flow Design: