Nav

Lab 2: Connect the Product API to a MySQL Database

Overview

In this lab, we will implement the generated subflows by connecting to a MySQL database.

Step 1: Implement Get Products Flow

In this step, you will implement the get:/product flow. To get the products from the database, you will need to:

  • Retrieve the records from the database.

  • Transform the records to json format.

The following instructions will guide you through the process:

  1. Locate the get product flow.

    module7_lab2_delete_get_products_set_payload

    DOUBLE CHECK: Make sure you are working in the get:\product:api-config flow
  2. Now you will add the Database connector to the flow.

    You will see that the database connector is not in the palette. You need to add that module.

  3. Go to the Mule Palette

  4. Click Add Module

    module7 lab2 add module
  5. Select Database Connector.

  6. Drag and Drop Icon to the light blue panel.

    module7 lab2 add database module

    Going back to the Palette, you now will see the Database connector.

    module7 lab2 palette database connector
  7. Click on the database connector, and drag and drop the Select icon before the transformation.

    DOUBLE CHECK: Drop the Database Connector in the Process section, not the Source section.
  8. Double-Click on the Database icon and a configuration panel will be shown.

  9. Change its name to Get Products.

    module7_lab2_name_database_component_get_products

    A best practice in Mule is to parameterize connectivity information. To configure the database connector we will leverage this best practice.

  10. Create a folder called config in src/main/resources

  11. Create a file called configuration.yaml in the src/main/resources/config

    module7 lab2 config file
  12. Insert the following text into configuration.yaml file.

    mysql:
      host: "services.mythicalcorp.com"
      port: "3306"
      user: "product"
      password: "Mule1379"
      database: "products_test"

    N.B. The password can be encrypted using the Mule Credential Vault.

  13. Save the configuration.yaml file

  14. Go back to the api.xml file. Press the Global Elements tab.

    module7 lab2 global elements
  15. Press the Create button and search for Configuration Properties

    module7 lab2 configuration properties
  16. Press OK.

  17. in the file text box write config/configuration.yaml. You can also browse to the file.

    module7 lab2 configuration properties file
  18. Now that you have your properties defined, let’s add a new connector configuration.

    Go back to the database connector you dropped, click to open properties and click on the module7_studio_plus_button button on the Connector Configuration.

  19. In Name write Products_Database_Configuration

  20. In Connection select MySQL Connection.

    When you select the database connection it asks you to add the JDBC Driver.

  21. Press Configure…​ button. A new window will open.

  22. Select Add Maven depenency

  23. Complete with the following data:

    • Group id: mysql

    • Artifact id: mysql-connector-java

    • Version: 5.1.46

      module7 lab2 add jdbc dependency
  24. Click Finish.

  25. For our database configuration specify the following values for the properties:

    • Host: ${mysql.host}

    • Port: ${mysql.port}

    • User: ${mysql.user}

    • Password: ${mysql.password}

    • Database: ${mysql.database}

      module7_lab2_db_config_1

      Mule resolves the ${key} to the configuration properties that we put in the configuration.yaml file by default without any extra configuration needed.
  26. Click Test Connection to verify the connectivity

    module7_lab2_db_config_test_connection_success

  27. Click OK

  28. Click OK again to close the MySQL Configuration.

    Now that we have the Database configured, we will add the query.

    SELECT  p.id, p.name, p.description, p.product_number, p.manufactured, p.colors, p.categories, p.stock, p.safety_stock_level, p.standard_cost, p.list_price, p.size, p.size_unit_measure_code, p.weight, p.weight_unit_measure_code, p.days_to_manufacture, p.images,  p.modified_date, p.created_date
    FROM product p
    LIMIT 10
    We are limiting the query to retrieve only up to 10 items with the "LIMIT 10" parameter.

    module7_lab2_get_products_query

    Many customers use their favorite query tool (SQL Query Analyzer, TOAD, DBVisualizer, …) to craft the query they want and then paste it into textbox in Studio.

    Now we need to transform the records from the database to JSON format.

  29. Double-Click on the Transform Message icon. You will find the fields that come from the query on the left, and the fields that the API will return on the right. You can graphically map fields by dragging between fields.

    This Transform Message component uses MuleSoft’s universal DataWeave transformation language for transforming data from what format to another. There is no need to use xpath/XSLT for XML, code for JSON, code for CSV, etc. Use DataWeave for all transformations. Dataweave is a simple, powerful tool to query and transform data inside of Mule.

    module7_lab2_get_products_transform_illustration

  30. Remove the actual mapping and copy the mapping below into the text view of the dataweave transform.

    %dw 2.0
    output application/json
    ---
    payload map (product, index) -> {
    	id: product.id,
        categories: (product.categories default "") splitBy ",",
        colors: (product.colors default "") splitBy ",",
        createdDate: product.created_date as String {format: "yyyy-MM-dd"},
        modifiedDate: product.modified_date as String {format: "yyyy-MM-dd"},
        safetyStockLevel: product.safety_stock_level as Number,
        stock: product.stock as Number,
        daysToManufacture: product.days_to_manufacture,
        name: product.name,
        description: product.description,
        images: (product.images default "") splitBy ",",
        listPrice: product.list_price,
        manufactured: product.manufactured,
        productNumber: product.product_number,
        size: product.size,
        sizeUnitMeasureCode: product.size_unit_measure_code,
        standardCost: product.standard_cost,
        weightUnitMeasureCode: product.weight_unit_measure_code,
        weight: product.weight
    
    }
    Since colours, categories and images are arrays and in the database they are saved as comma-separated Strings, we added to add a function called Split By to the mapping that splits the single record into a list of them separated by ",". There are a number of functions available within Dataweave to support more complex mapping requirements. See Dataweave documentation for more information.

    module7_lab2_get_products_transform_complete

  31. This concludes this step. Click save in the transform message window.

  32. Now we are ready to test our API implementation.

    Follow the same steps from Lab 1.

    • Start the application.

    • Check the logs to make sure it is deployed successfully.

    • Test the Get Products using the GET method on the Product using the console, or Postman. You may need to set Content-Type and Accept headers to application/json. Ask your instructor for assistance if you receive error responses.

  33. Now you get the response from the API, the product list is being retrieved from the MySQL database and you can check that the information is different from the one you saw in Lab 1.

    module7_lab2_get_products_postman_results

    (Example result. Results may differ.)

    Now that you have confirmed this flow is working, move onto the next implementation. If you have any problems, call over the instructor before continuing.

Step 2: Implement Get Specific Product Flow

In this step, you are going to implement the get:/product/{id} flow.

To get the product from the database, you must:

  • Retrieve the records from the database.

  • Check if it was found.

  • If it was found, transform the record to json format.

  • If it was not found, create a "Not found" response.

The following instructions will guide you through the process:

  1. Remove all the Transform icons from the get:/product/{id} flow. To do that you just right-click on the icon and select Delete. Or select it and hit Delete key.

  2. Drag & Drop a Select component from the database connector.

  3. Double Click on the icon to open the configuration tab.

  4. Complete with the following parameters:

    • Display Name: Get Product

    • Connector configuration: Select Products_Database_Configuration

    • SQL Query Text:

      SELECT p.id, p.name, p.description, p.product_number, p.manufactured, p.colors, p.categories, p.stock, p.safety_stock_level, p.standard_cost, p.list_price, p.size, p.size_unit_measure_code, p.weight, p.weight_unit_measure_code, p.days_to_manufacture, p.images,  p.modified_date, p.created_date
      FROM product p
      WHERE p.id = :id
  5. Let’s add the Input Parameters. Click on the Transformation Icon.

    module7 lab2 press transform button

    A new window will appear. Similar to a dataweave transformation

  6. Search for id. It should be in Attributes → uriParams → id.

  7. Drag and drop the id from input to the output

    module7 lab2 select transform
  8. Press module7_lab2_done_button button

    Here we are using a parametrized query. You define the variable in the query and then you complete with the parameters on the other text box.
    module7 lab2 select by id configuration

    We are going to validate if the product was found. In case it doesn’t exist we are going to throw an exception. To do that we need to add the Validations Module

  9. Go to the Mule Palette and add the Validation Module.

  10. Select the Validation connector and drag & drop is not empty collection.

    The Flow should look similar to this:

    module7 lab2 add validator

    This validator component is going to throw an Exception of type VALIDATION:EMPTY_COLLECTION What we want to do is to catch that Exception and send a 404 (Not Found) response.

  11. To do that go to the Error Handling that is defined in the api-main flow.

  12. Find the component On Error Propagate type: APIKIT:NOT_FOUND

    module7 lab2 error handling
  13. Click on it.

    You will see the configuration properties.

  14. Click on the Type textbox.

    module7 lab2 click type

    A checkbox list will open with all the possible errors that can be catched.

  15. Select VALIDATION:EMPTY_COLLECTION

    module7 lab2 validation empty collection
    If the Error is not listed write the error name manually.
  16. Press Save icon on the top right side of the panel.

    Now we are going to transform the response.

  17. Add a Transform component at the end of the flow.

    copy this script into the text view of the DataWeave transform:

    %dw 2.0
    output application/json
    var product = payload[0]
    ---
    {
    	id: product.id,
    	name: product.name,
    	description: product.description,
    	manufactured: product.manufactured,
    	productNumber: product.product_number,
    	colors: (product.colors default "") splitBy "," ,
    	categories:(product.categories default "") splitBy "," ,
    	safetyStockLevel: product.safety_socket_level,
    	standardCost: (product.standard_cost default "0.0") as String {format: "##.##"} as Number,
    	listPrice: (product.list_price default "0.0") as String {format: "##.##"} as Number,
    	stock: product.stock,
    	safetyStockLevel: product.safety_stock_level,
    	daysToManufacture: product.days_to_manufacture,
    	size: product.size,
    	sizeUnitMeasureCode: product.size_unit_measure_code,
    	weight: product.weight,
    	weightUnitMeasureCode: product.weight_unit_measure_code,
    	daysToManufacture: product.days_to_manufacture,
    	images: (product.images default "") splitBy ",",
    	modifiedDate: (product.modified_date default "") as Date {format: "yyyy-MM-dd"},
    	createdDate: (product.created_date default "") as Date {format: "yyyy-MM-dd"}
    }
  18. Transform Message will transform the DB structure to the JSON format defined for the response. Database selects always return a list of rows, so one method to obtain a single result is to take the first one.

    module7_lab2_get_product_transform_config

    Finally we are going to add a Custom Business Event to register the product found.

  19. Go to the Palette and search for Custom Business

    module7 lab2 add custom business event
  20. Setup the Custom Business Event parameters to record the queried product’s information for future business analysis:

    • Display Name: Found Product Detail Event

    • Event Name: Found Product Detail Event

    • In the Key Performance Indicators table, add these two attributes:

      Name Expression / Value

      Product Name

      #[payload.name]

      Product ID

      #[payload.id]

      module7_lab2_get_product_event_config

  21. Now you can run the API as in the preceding step, and check calling GET on "product/{id}" using an existing and a non-existing ID.

  22. Stop the Mule Runtime using the console view after the test.

Now that you have confirmed this flow is working, move onto the next implementation. If you have any problems, call over the instructor before continuing.

Step 3: Implement Create Product Flow

In this step, you will implement the post:/product flow. To create the product in the database, you must:

  • Insert the product in the DB

  • Return the product plus the id as a response.

The following instructions will guide you through the process:

  1. Remove the Transform component from the post:\product:application\json:api-config flow. To do that you just right-click on it and select Delete or select it and hit the Delete key.

  2. Using the Palette, drag & drop the necessary components to create a flow like this:

    module7_lab2_post_product_initial

  3. Start by configure the insert component. Set the parameters as follows to save the product into the database:

    • Display Name: Insert Product

    • Connector configuration: Products_Database_Configuration

    • SQL Query Text:

      INSERT INTO product(name, description, product_number, manufactured, colors, categories, stock, safety_stock_level, standard_cost, list_price, size, size_unit_measure_code, weight, weight_unit_measure_code, days_to_manufacture, images, modified_date, created_date)
      VALUES(:name, :description, :productNumber, :manufactured, :colors,  :categories, :stock, :safetyStockLevel, :standardCost, :listPrice, :size, :sizeUnitMeasureCode, :weight, :weightUnitMeasureCode, :daysToManufacture, :images,CURDATE(), CURDATE() );
  4. Click on module7_lab2_transform_button button to open a dataweave panel.

  5. Complete the Transformation with the following values:

    %dw 2.0
    output application/java
    fun getManufacturedCode(value) = if (value == true) 1 else 0
    ---
    {
            categories: (payload.categories default []) joinBy ",",
            colors: (payload.colors  default []) joinBy ",",
            daysToManufacture: payload.daysToManufacture,
            description: payload.description,
            images: (payload.images  default []) joinBy ",",
            listPrice: payload.listPrice,
            manufactured: getManufacturedCode(payload.manufactured),
            name: payload.name,
            productNumber: payload.productNumber,
            safetyStockLevel: payload.safetyStockLevel,
            size: payload.size,
            sizeUnitMeasureCode: payload.sizeUnitMeasureCode,
            standardCost: payload.standardCost,
            stock: payload.stock,
            weight: payload.weight,
            weightUnitMeasureCode: payload.weightUnitMeasureCode
    }

    module7_lab2_post_product_database_config

    Look that in Dataweave you can declare functions and use if else structures. This is used to transform the manufactured value from boolean to integer.

    As this operation creates a new product, you will need to obtain the newly generated id. How this is implemented can vary with different database engines' drivers.

  6. In the case of MySQL, go to "Advanced" in Database configuration.

  7. Find Auto Generated Keys section.

  8. Set Auto generate keys to True

  9. In the *Auto generated keys columns names" select Edit inline.

    1. Add value id in the table below.

      The configuration will be like this: module7_lab2_post_product_database_config_2

      We don’t want to loose the original payload, so we are going to save the generated key in a variable.

  10. Go to the Advanced tab. Scroll down till you see the Output values.

  11. Complete with the following values:

    • Target Variable: generated_key

    • Target Value: #[payload.generatedKeys.GENERATED_KEY]

      module7 lab2 advance panel

      Now we are going to configure the response Transform Message. In this step we need to merge the original request payload with the returned ID from the database. The next script will do the trick. We are getting the original request payload and adding a field called "id".

  12. Add the following transformation:

    %dw 2.0
    output application/json
    ---
    payload ++
    id: vars.generated_key
  13. Finally let’s configure a Custom Business Event. Set the parameters to record the created product’s information for future business analysis:

    1. Display Name: New Product Created Event

    2. Event Name: New Product Created Event

    3. In the Key Performance Indicators table, add these two attributes:

      Name Expression / Value

      Product ID

      #[payload.id]

      Product Name

      #[payload.name]

      module7_lab2_post_product_event_config

  14. Now you can run the API as in the preceding steps, and check it is working by calling POST on "products".

    The productNumber must be unique, you need to change the productNumber with a random value. If you have problems with this, ask your instructor to provide a value by querying the database directly to check for existing records

    Stop the Mule Runtime using the console view after the test.

    Now that you have confirmed this flow is working, move onto the next implementation. If you have any problems, call over the instructor before continuing.

Step 4: Error Handling

The Product Number in a product, is unique. So when we do an insert we could get an error. We are going to throw a 409 status code with the details of the error.

  1. First insert a try component at the beginning of the post flow

  2. Put the insert component inside. You should see something similar to this

    module7 lab2 try flow
  3. Click on the Error Handling pointer

  4. Add an Error Propagate component. You should see something like this

    module7 lab2 propagate

    We only want to catch the Query Execution error, so that’s what we are going to configure.

  5. Click on the Error Propagate component and in Type write DB:QUERY_EXECUTION. You can also select from the drop down list.

    module7 lab2 propagate config

    Now, we are going to raise a new exception with the DB:QUERY_EXECUTION message.

  6. Drag and drop Raise Error component inside the On Error Propagate component.

    module7 lab2 raise error
  7. Configure with the following values

    Name Value

    Type

    APP:CONFLICT

    description

    #[error.description]

    module7 lab2 raise error config

    Now we need to go to the API Kit Error Handling and add a new On Error Propagate Handler to return 409.

  8. Go to the Error Handling in the product-api-main flow and add a new On Error Propagate Handler

  9. Click inside and configure APP_CONFLICT in Type.

    module7 lab2 api kit propagate
  10. Add a Transformation Component inside

    We are going to return a payload and a httpStatus variable with the error code.

  11. In the payload configuration we are going to return the error description, so copy and paste the following script:

    %dw 2.0
    output application/json
    ---
    {message: error.description}
    module7 lab2 error desc transform

    Now let’s add a variable.

  12. Click module7_lab2_add_target button. A new window will appear.

  13. Select Variable from the drop down list and set httpStatus as the Variable Name.

    module7 lab2 add variable menu
  14. Write 409 in the script box.

    module7 lab2 add variable output

    At the end you will have something similar to this:

    module7 lab2 api kit conflict handlerpng

    Now you can run the API as in the preceding steps, and check it is working by calling POST on "products" with an existing product number.

    You should see something like this:

    module7 lab2 409 console

Stop the Mule Runtime using the console view after the test.

+ Now that you have confirmed this flow is working, move onto the next implementation. If you have any problems, call over the instructor before continuing.

Step 5: Implement Update Product Flow

In this step, we are going to implement the put:\product{id} flow. To update the product in the database, we just need to call the database.

The following instructions will guide you through the process:

  1. Remove the components from the put:\product{id} flow. To do that you just right-click on each and select Delete or select it and hit Delete key.

  2. Using the Palette, drag & drop the needed components to create a flow like this:

    module7_lab2_put_product_initial

  3. Configure the Database with this parameters:

    • Display Name: Update Product

    • Connector configuration: Products_Database_Configuration

    • SQL Query Test:

      update product
      set name = :name, description = :description, product_number = :productNumber, manufactured = :manufactured, colors = :colors,
      categories= :categories, stock = :stock, safety_stock_level = :safetyStockLevel, standard_cost = :standardCost, list_price = :listPrice, size = :size,
      size_unit_measure_code = :sizeUnitMeasureCode, weight = :weight, weight_unit_measure_code = :weightUnitMeasureCode, days_to_manufacture = :daysToManufacture,
      images = :images,  modified_date = CURDATE()
      where id = :id
  4. Click on module7_lab2_transform_button button to open a dataweave panel.

  5. Complete with the following script

    %dw 2.0
    output application/java
    fun getManufacturedCode(value) = if (value == true) 1 else 0
    ---
    {
            id: attributes.uriParams.id,
            categories: (payload.categories default []) joinBy ",",
            colors: (payload.colors default []) joinBy ",",
            daysToManufacture: payload.daysToManufacture,
            description: payload.description,
            images: (payload.images default []) joinBy ",",
            listPrice: payload.listPrice,
            manufactured: getManufacturedCode(payload.manufactured),
            name: payload.name,
            productNumber: payload.productNumber,
            safetyStockLevel: payload.safetyStockLevel,
            size: payload.size,
            sizeUnitMeasureCode: payload.sizeUnitMeasureCode,
            standardCost: payload.standardCost,
            stock: payload.stock,
            weight: payload.weight,
            weightUnitMeasureCode: payload.weightUnitMeasureCode
    }

    Like we did in the previous step we are going to log the number of records updated

  6. Focus on the logger component

  7. Press the module7_lab2_fx_button to add a dataweave expression.

  8. Add the following dataweave expression:

    "Number of affected rows: " ++ ((payload.affectedRows default -1 )as String)
    module7 lab2 logger update config

    Since we are updating the product number, you can add the Try component and raise a APP:CONFLICT exception like we did in the Creation flow. The Flow should be similar to this:

    module7 lab2 put raise error
  9. Now you can run the API as in the preceding steps, and check it is working by calling PUT on "products/{id}" for an id you know exists.

    Note that if you have more time you can extend the implementation as in the GET /products/{id} flow to respond with a not found response if the record does not exist, however note that in the restful style, it is usual to invoke a create operation on the resource if it does not already exist.

    Stop the Mule Runtime using the console view after the test.

    Now that you have confirmed this flow is working, move onto the next implementation. If you have any problems, call over the instructor before continuing.

Step 6: Implement Delete Product Flow

In this step, we are going to implement the delete:\product{id} flow. To delete the product in the database, we just need to call the database.

The following instructions will guide you through the process:

  1. Remove all the components from the delete:\product{id} flow. To do that you just right-click on each component and select Delete or select a component and hit the Delete key.

  2. Using the Palette, drag & drop the needed components to create a flow like this:

    module7_lab2_delete_product_initial

  3. Configure the Database component as follows:

    • Display Name: Delete Product

    • Connector configuration: Products_Database_Configuration

    • SQL Query Text:

      delete from product where id=:id
    • Input Parameters:

      id:attributes.uriParams.id

      We will add a logger to see how many records where deleted.

  4. Focus on the logger component

  5. Press the module7_lab2_fx_button to add a dataweave expression.

  6. Add the following dataweave expression:

    "Number of records deleted: " ++ (payload as String)
    module7 lab2 logger config

    We are returning a 204, so there will be no message response.

    Now you can run the API as in the preceding steps, and check it is working by calling DELETE on "products/{id}" for an id you know exists (such as the one created in the previous step).

    Note that if you have more time you can extend the implementation as in the GET /products/{id} flow to respond with a not found response if the record does not exist.

    Stop the Mule Runtime using the console view after the test.

    If you have any problems, call over the instructor before continuing.

    Now that we have a working implementation, in the next lab we will test it end to end and add unit tests.

Summary

In this module we implemented the System API against the MySQL database. We ran and tested the API on the local Mule Runtime which comes embedded in Anypoint Studio.

You completed the following steps:

  • Implement Get Products Flow

  • Implement Get Product by ID Flow

  • Implement Create Product Flow

  • Implement Delete Product Flow

  • Implement Update Product Flow

You were introduced to the use of Dataweave, a powerful tool to transform messages from a database record to json format in this case. You also saw how simple accessing a database can be using the Database Connector.

It is important to note that the implementation of an API is a real Mule application. Building integration applications and exposing APIs do not require additional knowledge, making it easy for Mule developers to work on both integrations and APIs.

See the Dataweave documentation for more information.

See the Database Connecter documentation for more information.

Please proceed to Lab 3: Run and Debug the API