salesforce
+

Create an OData v4 API

The OData MySQL example shows you how to use an HTTP-based data service to query a MySQL database.

Prerequisites

The following software is required to create and use an OData REST API with APIkit:

  • OData Plugin

  • Mule runtime engine 4.3.0 or later

  • Anypoint Studio 7.9.0 or later

Scaffold an OData v4 API Using APIkit for OData

  1. In Studio, select File > New > Mule Project.

  2. In the Project Name field, type the name of your Mule project.

  3. Click Finish.

  4. In src/main/resources/api, create a new file with a .csdl.xml extension.
    For example, odata-metadata.csdl.xml:

    <?xml version="1.0" encoding="UTF-8"?>
<edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx">
  <edmx:DataServices>
    <Schema xmlns="http://docs.oasis-open.org/odata/ns/edm" Namespace="odata4.namespace">
      <EntityType Name="Customers"> (1)
        <Key> (2)
          <PropertyRef Name="CustomerID" />
        </Key>
        <Property Name="CompanyName" Type="Edm.String" MaxLength="40" Unicode="false" />
        <Property Name="ContactName" Type="Edm.String" MaxLength="30" Unicode="false" />
        <Property Name="ContactTitle" Type="Edm.String" MaxLength="30" Unicode="false" />
        <Property Name="CustomerID" Type="Edm.String" Nullable="false" MaxLength="5" Unicode="false" />
        <NavigationProperty Name="Orders" Type="Collection(odata4.namespace.Orders)"/>
      </EntityType>
      <EntityType Name="Orders">
        <Key>
          <PropertyRef Name="OrderID" />
          <PropertyRef Name="ShipName" />
        </Key>
        <Property Name="Freight" Type="Edm.Decimal" Precision="19" Scale="4" />
        <Property Name="OrderDate" Type="Edm.DateTimeOffset" Unicode="false" />
        <Property Name="OrderID" Type="Edm.Int32" Nullable="false" Unicode="false" />
        <Property Name="CustomerID" Type="Edm.String" MaxLength="5"/>
        <Property Name="Price" Type="Edm.Single" Unicode="false" />
        <Property Name="Priority" Type="Edm.Int16" Unicode="false" />
        <Property Name="ShipAddress" Type="Edm.String" MaxLength="60" Unicode="false" />
        <Property Name="ShipName" Type="Edm.String" Nullable="false" MaxLength="40" Unicode="false" />
        <NavigationProperty Name="Customer" Type="odata4.namespace.Customers">
        	<ReferentialConstraint Property="CustomerID" ReferencedProperty="CustomerID" />
        </NavigationProperty>
      </EntityType>
      <EntityContainer Name="OData4EntityContainer"> (3)
        <EntitySet Name="Customers" EntityType="odata4.namespace.Customers"> (4)
        	<NavigationPropertyBinding Path="Orders" Target="Orders"/>
        </EntitySet>
        <EntitySet Name="Orders" EntityType="odata4.namespace.Orders"/>
      </EntityContainer>
    </Schema>
  </edmx:DataServices>
</edmx:Edmx>
    1 EntityType defines the type and its properties
    2 key defines which property or set of properties is the key for the entity.
    For example, the entity set Orders is composed of two keys OrderID and ShipName.
    3 EntityContainer defines the sets of exposed entities.
    In this example, Orders and Customers.
    4 EntitySet contains a name and EntityType.
    Both must be defined in the schema namespace.

  5. In the Package Explorer view, right-click your odata-metadata.csdl.xml file > Generate Mule OData 4 API:

    Window displays the previously mentioned path to get to the Generate Mule OData 4 API option.

  6. APIkit generates the corresponding flows based on your .csdl.xml metadata file.

    The three flows created from the csdl.xml metadata file are highlighted.
    1 The main flow that receives messages and routes the operation to the correct handler.
    2 Five flows with GET, PUT, POST, and DELETE operations for each entity.
    3 A flow with a GET operation to retrieve the collection of entities.

Handle OData System Query Options

Within each On Entity Request and On Entity Collection Request listener you can access a systemQueryOptions attribute that maps to each OData query option ($select, $orderBy, $count, etc.). For example, to access the $select value you use the following expression:

#[attributes.odataRequestAttributes.systemQueryOptions.select]
odataRequestAttributes is highlighted in the output tab, within the Mule message, under attributes.

Use The Transform to SQL Operation

APIkit for OData 4 contains a Transform to SQL Select operation to transform the input OData GET request to a SQL SELECT query. The operation is useful when combined with the select operation of the Database Connector. The output payload is a prepared statement and the attributes are a map of prepared statement parameters. This operation has been designed to work with the Mule DB connector to help the user expose databases with OData 4 easily.

Transform to SQL select element and tab are highlighted.
Select element and tab are highlighted, as well as the attributes.queryparameters in the input parameters field.
Serialized entity element and tab are highlighted, as well as the inbound content in the General section.

APIkit currently supports the following elements:

  • System query options:

    • $filter

    • $select

    • $search

    • $orderBy

    • $skip

    • $top

  • Single entity

  • Entity relation

  • Server-side pagination

APIkit does not currently support the following elements:

  • Parameter Aliases

  • Built-in Filter Operations:

    • has

    • divby

  • Built-in Query Functions, all but:

    • contains

    • startswith

    • endwith

  • $apply

  • $compute

  • $expand

How Does Transform to SQL Select Work in Odata 4?

The OData protocol defines a series of conventions that enable clients to query identified resources using URLs. The list of queryable entities, their fields, and the relationships between them are defined in a data model using simple HTTP messages. As OData shares some similarities with Java database connectivity (JDBC), the transform-to-sql-select operation takes advantage of such similarities to help the user create SQL queries for those resources. The resulting message of the operation is a prepared statement with the SQL select sentence as the payload along with the parameters name and values map as the attributes.

How Do I Implement the Transform to SQL Select Operation Using Apikit for Odata 4?

<apikit-odata:transform-to-sql-select> is the name of the XML element that represents this operation, which has the following parameters:

Parameter name

Description

Display name

This parameter configures the operation name.

Module configuration

Name of the related-module configuration.

Entity set name

The name of the entity set that you are trying to fetch #[attributes.entitySetName].

Page size

A number that defines the amount of results per page during server-side-pagination.

Entity type name

A string obtained from the OData request using #[attributes.odataRequestAttributes], which is specified by the entity data model.

Entity type fields

An array of strings obtained from the OData request using #[attributes.odataRequestAttributes], which provides the list of property fields from the entity type.

Entity type keys

An object obtained from the OData request using #[attributes.odataRequestAttributes], which provides the map of entity key names and values.

System query options

Parameter obtained from the OData request using #[attributes.odataRequestAttributes].

Search transformation enabled

Parameter to define the behavior of the transformation.

Strict search term match

Parameter to define the behavior of the transformation.

$filter Expressions in OData 4

​​The $filter expression in OData uses comparison, logical, arithmetic, and grouping operators. You can access the list operators with their corresponding meaning at the OData4 Specification. APIkit does not support has and divby operators.

Expressions Supported by Transform to SQL Select

OData supports a variety of expressions for filtering that you can find in its specification. The transform-to-sql-select operation supports the following subset of unary and binary operators, along with the following method operators.

Binary Operators in OData and in SQL

OData Operator

SQL Operator

add

+

and

AND

div

/

eq

=

ge

>=

gt

>

le

=<

lt

<

mod

%

mul

*

ne

<>

or

OR

sub

-

Unary Operators in OData and in SQL

OData Operator

SQL Operator

not

NOT

-

-

Method Operators in OData and in SQL

OData Operator

SQL Operator

contains

LIKE %value%

startswith

LIKE value%

endswith

LIKE %value

$search Support

The operation builds a where clause based on the $search expression provided by the system query option. The implementation translates the search expression to select all the elements that have property values that match the condition. By default, a term matches if the field value “contains” the term, but it does not match if it is set as “strict” in the configuration. The following parameters define the $search feature:

Parameter name

Description

Search transformation enabled

When set to true, it enables the transformation support for this option.

Strict search term match

When set to true, it specifies that there is a match only if the field value contains exactly the same terms as the search expression.

APIkit for OData 4 Implementation Example

Download the full apikit-odata4-example application and run it.

After Studio deploys the application, you can query your OData service using the following query examples:

GET Request Example

Request
curl --location --request GET 'localhost:8081/api/Customers('BLAUS')'
Response
{
  "@odata.context": "http://localhost:8081/api/$metadata#Customers/$entity",
  "CompanyName": "Blauer See Delikatessen",
  "ContactName": "Hanna Moos",
  "ContactTitle": "Sales Representative",
  "CustomerID": "BLAUS"
}

GET Request Example With Composite Key

Request
curl --location --request GET 'localhost:8081/api/Orders(OrderID=10315,ShipName='Island Trading')
Response
{
  "@odata.context": "http://localhost:8081/api/$metadata#Orders/$entity",
  "Freight": 41.76,
  "OrderDate": "1996-09-26T00:00:00Z",
  "OrderID": "10315",
  "Price": null,
  "Priority": 1,
  "ShipAddress": "Garden house Crowther Way",
  "ShipName": "Island Trading"
}

Create Example

In this example, the client provides the key (or keys) to create a new entity:

POST Customer Request
curl --location --request POST 'http://localhost:8081/api/Customers' \
--header 'Content-Type: application/json' \
--data-raw '{
   "CompanyName": "Mulesoft",
   "ContactName": "Customer 123",
   "CustomerID": "NW123"
}'
POST Order
curl --location --request POST 'http://localhost:8081/api/Orders' \
--header 'Content-Type: application/json' \
--data-raw '{
   "Freight": 32.38,
   "OrderID": 100000,
   "ShipAddress": "Unknown St. 123",
   "ShipName": "Order 100000"
}'

Update Example

OData v4 recommends using PATCH to update entities because it only changes the provided fields:

PATCH
curl --location --request PATCH 'http://localhost:8081/api/Customers('\''BOTTM'\'')' \
--header 'Content-Type: application/json' \
--data-raw '{
   "ContactName": "James Bottom"
}'
curl --location --request PATCH 'http://localhost:8081/api/Orders(OrderID=10251,ShipName='\''Victuailles en stock'\'')' \
--header 'Content-Type: application/json' \
--data-raw '{
   "ShipAddress": "Unknown Av. 1234"
}'

OData v4 discourages the usage of PUT to update your entities because it replaces the entire entity with a new one risking you a possible data loss.

PUT
curl --location --request PUT 'http://localhost:8081/api/Customers('\''LONEP'\'')' \
--header 'Content-Type: application/json' \
--data-raw '{
   "CompanyName": "New Lonesome Pine Restaurant",
   "ContactName": "Fran C. Wilson",
   "CustomerID": "LONEP"
}'
curl --location --request PUT 'http://localhost:8081/api/Orders(OrderID=11056,ShipName='\''Eastern Connection'\'')' \
--header 'Content-Type: application/json' \
--data-raw '{
   "Freight": 27.52,
   "OrderDate": "1998-05-28T00:00:00",
   "OrderID": 11056,
   "Priority": 2,
   "ShipAddress": "45 King George",
   "ShipName": "Eastern Connection"
}'

Delete Example

To delete the entity:

DELETE
curl --location --request DELETE 'http://localhost:8081/api/Customers('\''NW123'\'')'
curl --location --request DELETE 'http://localhost:8081/api/Orders(OrderID=11056,ShipName='\''Eastern Connection'\'')'

Client-Side Pagination in APIkit for OData 4

You can configure your client to request the page using the HTTP query parameters $skip and $top.

  • The $top system query parameter specifies a non-negative integer n that limits the number of items returned from a collection.

  • The $skip system query parameter specifies a non-negative integer n that excludes the first n items of the queried collection from the result.

For example:

curl -X GET ‘localhost:8081/api/Customers?$skip=1&$top=5’

Returns five records starting from the second position.

Server-Side Pagination in APIkit for OData 4

Server-side pagination is a way of controlling a subset of data requests that were fetched from a client-side server using a fixed pageSize parameter. It divides the response data set into discrete pages to improve data readability. The server defines the page size, which is the maximum number of records returned by the server in a request.

How Server-Side Pagination Works in OData 4

OData defines a @odata.nextLink to indicate that a response is only a subset of the requested collection of entities. As such, @odata.nextLink contains a URL that enables the client to retrieve the next subset of the requested collection. The URL contains a query parameter $skiptoken that indicates the server where the next page starts.

How to Implement This Feature in Apikit for OData 4

<apikit-odata:request-entity-collection-listener> enables you to define a set of fields in the Responses tab, in Anypoint Studio, to implement the server-side pagination.

alt text:"pageSize set to ${service.orders.pageSize} in the On Entity Collection Request tab."

After it is populated, APIkit creates the XML representation of the previous fields.

<apikit-odata:request-entity-collection-listener config-ref="odata-metadata-config" path="/Orders" method="GET" >
        <apikit-odata:collection-success-response >
            <apikit-odata:entity-collection-success-response pageSize="${service.orders.pageSize}"/>
        </apikit-odata:collection-success-response>
    <apikit-odata:request-entity-collection-listener>

Page Size or Token Fields for Server-Side Pagination

Page size is a fixed number that represents the maximum number of records the request-entity-collection listener can retrieve for each request. In case you cannot use a numeric pagination, set a token in the Token field, so a developer can generate it in the corresponding flow. APIkit for OData includes this token value in the @odata.nextLink URL with a query parameter $skiptoken=<Token_Value>.

Was this article helpful?

💙 Thanks for your feedback!

Edit on GitHub