SAP Cloud Platform SDK for service development: Create OData Service [15] Data Source OData V2: QUERY advanced

Welcome to our next round of playing ‘round with the SAP Cloud Platform SDK for service development, to expose OData v4 service, while using the data source API to consume data from an OData v2 service.

In the previous round we learned basic implementation for the 5 supported operations.
Let’s continue now with some more capabilities offered by the data source API for OData v2 consumption.
We’ll focus on the QUERY operation and we’ll make the call to the consumed a bit more sophisticated.

BTW, this blog is part of the series of tutorials for beginners.

Let’s skip the Prerequisites and Preparation section, it is the same like in previous tutorial.
Instead, let’s directly discuss what we want to do.

• We want to develop an OData v4 service which simplifies access to backend data
• In the backend we have BusinessPartners, Contacts and Products (among much more other data)
• We want to expose only those business partners who are suppliers.
• We want only few basic pieces of information
• Along with the suppliers, we want to expose their products
• From the products we expose only basic info
• In addition, we expose some data which we calculate ourselves
• Also, we want to expose the supplier’s main contact person, but only basic info

That’s the high-level description of our mission.
How it is technically realized?
We’ll see.

Let’s skip the Project Creation, it is always the same.
Let’s skip the model…? Oh no, we need a new model:

Model

In the present tutorial, we’re focusing on suppliers:

<EntityType Name="Supplier">
	<Key>
		<PropertyRef Name="SupplierIdentifier" />
	</Key>
	<Property Name="SupplierIdentifier" Type="Edm.String" />
	<Property Name="SupplierCompanyName" Type="Edm.String" />
	<Property Name="SupplierCity" Type="Edm.String" />
	<Property Name="SupplierCountry" Type="Edm.String" />
	<Property Name="ContactFirstName" Type="Edm.String" />
	<Property Name="ContactLastName" Type="Edm.String" />
	<Property Name="ContactGuid" Type="Edm.Guid" />
	<Property Name="ContactBirth" Type="Edm.Date" />
	<Property Name="ProductsToSupply" Type="Edm.String" />
	<Property Name="ProductsTotalAmout" Type="Edm.Int16" />
	<Property Name="ProductsContainAds" Type="Edm.Boolean" />
	<Property Name="ProductsTotalValue" Type="Edm.Decimal" Scale="2" Precision="8" />
</EntityType>

Implementation

As mentioned above, we want to focus on the QUERY operation, in order to learn about some interesting capabilities.

Let’s go through our task list.

Simplify the model

This is represented in our edmx file.
The 3 backend entities are merged into one entity and the property names are renamed

Below table tries to summarize the modifications:

Note:
We aren’t listing all those properties from backend service which we’re skipping

Merge data of BusinessPartner, Contact, Product entities

Our intention is to have only one entity which offers a short overview on the data which is interesting for our users.
So we want to have pieces of data of the BusinessPartner, the main Contact and its Products in one entity instead of three entities.
Since these 3 entities are related, they can be reached via navigation properties.
Means, when using the backend service, we can read one BusinessPartner and from there we can navigate via a navigation property to the related products.

<host>/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/BusinessPartnerSet(‘0100000010’)/ToProducts

Since a BusinessPartner is not meant to be a human, but rather a company, we can navigate to the (human) contact persons via the following URL:

<host>/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/BusinessPartnerSet(‘0100000010’)/ToContacts

Now, instead of following navigation properties in separate single calls, in OData, we can merge data of related entities by adding the parameter $expand to the URL

<host>/IWBEP/GWSAMPLE_BASIC/BusinessPartnerSet?$expand=ToProducts,ToContacts

Note:
When invoking this URL, you might need to be patient, due to possibly huge amount of data.
Remember that it is a public service and everybody is allowed to create additional entries

Note:
The service GWSAMPLE_BASIC, which we’re using as backend v2 service, allows to do a reset to the sample data.
It is the function import called “RegenerateAllData”, see here for instructions

Programmatically, the $expand is realized as follows:

ODataQueryResult result = ODataQueryBuilder
		.withEntity("/sap/opu/odata/IWBEP/GWSAMPLE_BASIC", "BusinessPartnerSet")
		.expand("ToContacts", "ToProducts")

Note:
In the backend service, a BusinessPartner can have multiple contact persons.
In our v4 service, we use only the first contact which we find.

Reduce amount of data

When comparing the metadata of our exposed model and the consumed model, it becomes obvious that we’re significantly decreasing the amount of information by reducing the number of properties.

See Appendix section for a comparison of the structure of our exposed v4 service and the consumed v2 service.

We’re reducing the amount of data in several aspects:
Less entities, no complex types
Less number of properties
Aggregation of a to-many navigation into a single property

When it comes to reducing the number of properties, it is easy, we did that in the previous blogs:
Fetch the data and pick only what is relevant for us.

Now, there’s a better way:
Why should we fetch data which we actually don’t want?
To avoid network overhead, we should only fetch the data which we need.

That can be achieved with $select.

We’ve covered the $select in terms of exposing/provisioning, however now we need it when calling/consuming the backend service.

Example of how we want to call the backend service:

<host>/<srv>/BusinessPartnerSet?$select=BusinessPartnerID,CompanyName,Address

Moreover, in our call to the backend, we not only want to apply a $select statement to the BusinessPartner, but also to the expanded Contacts and Products
This is possible.
We have to specify the qualified name of expanded property, by prefixing the navigation property name and a slash

<navigationPropertyName>/<propertyName>

In our example:

…&$select=BusinessPartnerID,ToProducts/ProductID

This is considered in the following full URL

https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/BusinessPartnerSet?$expand=ToProducts,ToContacts&$select=BusinessPartnerID,CompanyName,Address,ToProducts/ProductID,ToProducts/Price,ToProducts/TypeCode,ToContacts/FirstName,ToContacts/LastName,ToContacts/ContactGuid,ToContacts/DateOfBirth

Such long URL is hard to read?
Maybe try in following scroll-able formatting:

https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/BusinessPartnerSet?$expand=ToProducts,ToContacts&$select=BusinessPartnerID,CompanyName,Address,ToProducts/ProductID,ToProducts/Price,ToProducts/TypeCode,ToContacts/FirstName,ToContacts/LastName,ToContacts/ContactGuid,ToContacts/DateOfBirth

Note:
Applying $select to nested properties of a ComplexType is not supported in OData v2

In our code we add the $select statement as follows:

.select("BusinessPartnerID", "CompanyName", "Address", 
   "ToProducts/ProductID", "ToProducts/Price","ToProducts/TypeCode", 
   "ToContacts/FirstName", "ToContacts/LastName", "ToContacts/ContactGuid", "ToContacts/DateOfBirth") 

As usual in the API, it is a list of strings, representing the names of properties.

Note:
The above statement doesn’t show one aspect which I’d like to bring to your attention:
In case you have a URL which combines $expand and $select.
In such case, you have to make sure that the navigation properties which you use in your expand statement, must be listed in the $select statement as well.
It is obvious, but easy to forget: the framework can only consider expand-properties if they’re contained in the received portion of data, i.e. if they’re part of the $select.

Expose only Suppliers

In the backend service, the entity BusinessPartner is defined to be generic.
A business partner can be a customer or a supplier. Both have the same properties, so the backend service has been designed to not have to similar entities, but only one.
There’s a flag which indicates if it is a customer or a supplier: the property BusinessPartnerRole
The property is of type Edm.String and the possible values are stored in a separate entity set.

The entity set VH_BPRoleSet is kind of static, it cannot be modified (e.g. CREATE is not allowed), it is a so-called value help

We can invoke the following URL to find out which are the possible values:

https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/VH_BPRoleSet

and as result, we get 2 entries:
Role 01 with short text “Customer” and role 02 with short text “Supplier”

Like that, we can figure out how to identify the suppliers from the list of BusinessPartners:
We want only those BusinessPartner entities which have value 02 for the property BusinessPartnerRole

In OData, this requirement is realized with a filter statement:

&$filter=BusinessPartnerRole eq ‘02’

Or

<host>/<srv>/BusinessPartnerSet?$filter=BusinessPartnerRole%20eq%20%2702%27

The full URL:

https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/BusinessPartnerSet?$filter=BusinessPartnerRole%20eq%20%2702%27&$expand=ToProducts,ToContacts&$select=BusinessPartnerID,CompanyName,Address,ToProducts/ProductID,ToProducts/Price,ToProducts/TypeCode,ToContacts/FirstName,ToContacts/LastName,ToContacts/ContactGuid,ToContacts/DateOfBirth

Note:
As of OData specification, there’s a blank after the property name and an inverted comma around the value.
However, since in HTTP, the URL is based on ASCII, the special characters have to be encoded. The browsers automatically does hat.
However, we need to be aware of the encoded URL, some REST clients require it. Also, when programmatically specifying URLs. That’s why it is printed above.
(you may find more info here)

Example:

&$filter=BusinessPartnerRole%20eq%20%2702%27

Luckily, when using the SAP Cloud Platform SDK for service provisioning, that is handled by the framework.

As follows:

.filter(ODataProperty.field("BusinessPartnerRole").eq(ODataType.of("02")))

Add some new data

In addition to above tasks, we show that in our OData service implementation we can add some new properties, which aren’t there in the backend service, and we fill those properties with values which are calculated based on backend values.

What we’re doing are just little examples to show what is possible and how easy it is to achieve.

The idea is about products:
For each supplier, we can follow the navigation property to see the list of products belonging to him.
In our above implementation we do it within one call with a $expand statement.
Since it is a one-to-many navigation, the result is a list.
In our v4 service, we want to flatten the list, to make it simpler, e.g. to just have a rough overview.
As such, we take the list and count the number and put the value into a new property called “ProductsTotalAmout”
Furthermore, we take the property “ProductID” from each product in the list, then we concatenate all IDs into one string and put the value into a new property “ProductsToSupply”
Similarly, for each product we check the value of the type code.
What type code?
Background: a product has a property “TypeCode” and the possible values can be inspected via the value help collection

<host>/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/VH_ProductTypeCodeSet

Possible values are AD (Advertisement) or PR (Product)
Our intention is to have in our service a boolean property (ProductsContainAds) indicating if the supplier has advertisements in his list of products.
What is it useful for?
For nothing.
And the last self-calculated property is simple, it sums up the price of all supplied products to a new property ProductsTotalValue

For this task, there’s no additional URL parameter, we only have to make sure that we get the data which we need for our calculations.

Even if we don’t expose them, the relevant properties must be included in the $select statement:

&$select= ToProducts/ProductID,ToProducts/Price,ToProducts/TypeCode

We have covered that in the above section.

With respect to implementation, this is pure Java homework.
So no need to give a code snippet here.
These calculations are done in small helper methods, you’re anyways used to find the full java code at the end of this page.

Some more technical aspects

You might have wondered why we have the birthday in our simplified model, which contains only the most relevant data for overview…
The reason:
There’s a difference in the OData specification between v2 and v4
In OData v2, the data type Edm.DateTime was used, which has been removed in v4
See What’s new in v4 (vs v2)
So we need some type mapping, but we don’t need to care much.

As shown in the model chapter above, we’ve specified the birth date as Edm.Date

<Property Name="ContactBirth" Type="Edm.Date" />

Whereas in the v2 service it is Edm.DateTime

<Property Name="DateOfBirth" Type="Edm.DateTime"

In our java code, when we’re doing the mapping, we just pass the value we get from the backend contact into our supplier property

supplierEntity.put("ContactBirth", contactsMap.get("DateOfBirth"));

We don’t need to care about the data type, as long as we’re using HashMaps to store the values.

For those of you using POJOs, the following info should be useful:

The Java type which is used by the framework for the DateOfBirth property is java.util.GregorianCalendar
See documentation

By the way, this is a good example why it makes sense to replace the Edm.DateTime by Edm.Date:
The backend service represents the birthday as Edm.DateTime, which includes the time. However, hours and seconds are just ignored in the backend storage, the time is always zero.
Example payload:

<d:DateOfBirth>1970-01-03T00:00:00</d:DateOfBirth>

Full Query implementation

Bringing it all together.
Please refer to the Appendix section for the full source code which includes the helper methods

@Query(serviceName = "DemoService", entity = "Suppliers")
public QueryResponse getSuppliers(QueryRequest queryRequest) {
	String[] select = { "BusinessPartnerID", "CompanyName", "Address",   
			"ToProducts/ProductID", "ToProducts/Price", "ToProducts/TypeCode", 
			"ToContacts/FirstName", "ToContacts/LastName", "ToContacts/ContactGuid", "ToContacts/DateOfBirth" }; 
		
	try {
		List<Map<String, Object>> v2BusinessPartnerList  = ODataQueryBuilder
				.withEntity("/sap/opu/odata/IWBEP/GWSAMPLE_BASIC", "BusinessPartnerSet")
				.select(select)
				.expand("ToContacts", "ToProducts")
			        .filter(ODataProperty.field("BusinessPartnerRole").eq(ODataType.of("02"))) 
				.build()
				.execute("ES5")
				.asListOfMaps();

		// convert the v2 map to v4 map, including all the modifications
		List<Map<String, Object>> v4SupplierList = convertV2BusinessPartnersToV4Suppliers(v2BusinessPartnerList);
			
		return QueryResponse.setSuccess()
				.setDataAsMap(v4SupplierList)
				.response();
	} catch (ODataException e) {
		return QueryResponse.setError(ErrorResponse.getBuilder()
				.setMessage("Error occurred while calling OData V2 data source.")
				.setStatusCode(HttpStatus.SC_INTERNAL_SERVER_ERROR)
				.setCause(e)
				.response());
	}
}

Run

After build and deploy, we can run the service and view the Suppliers list

https://demoproject.cfapps.eu10.hana.ondemand.com/odata/v4/DemoService/Suppliers

And we can compare the result to the corresponding call to the v2 backend service, including expanded contacts and products

https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/BusinessPartnerSet?$filter=BusinessPartnerRole%20eq%20%2702%27&$expand=ToProducts,ToContacts&$select=BusinessPartnerID,CompanyName,Address,ToProducts/ProductID,ToProducts/Price,ToProducts/TypeCode,ToContacts/FirstName,ToContacts/LastName,ToContacts/ContactGuid,ToContacts/DateOfBirth&$format=json

Appendix 1

Source code of manifest file: manifest.yml

---
applications:
  - name: DemoProject
    memory: 512M
    buildpack: sap_java_buildpack
    path: target/DemoProject-0.0.1-SNAPSHOT.war  
    services:
    - demoxsuaa
    - demodestination
    env:
      ALLOW_MOCKED_AUTH_HEADER: 'true'

Appendix 2

Source code of model file: DemoService.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 Namespace="demo" xmlns="http://docs.oasis-open.org/odata/ns/edm">
			<EntityType Name="Supplier">
				<Key>
				    <PropertyRef Name="SupplierIdentifier"/>
				</Key>
				<Property Name="SupplierIdentifier" Type="Edm.String"/>  
				<Property Name="SupplierCompanyName" Type="Edm.String" />
				<Property Name="SupplierCity" Type="Edm.String"/> 
				<Property Name="SupplierCountry" Type="Edm.String"/> 
				<Property Name="ContactFirstName" Type="Edm.String"/>
				<Property Name="ContactLastName" Type="Edm.String"/>
				<Property Name="ContactGuid" Type="Edm.Guid"/>  
				<Property Name="ContactBirth" Type="Edm.Date" />
				<Property Name="ProductsToSupply" Type="Edm.String"/>
				<Property Name="ProductsTotalAmout" Type="Edm.Int16"/>
				<Property Name="ProductsContainAds" Type="Edm.Boolean"/>
				<Property Name="ProductsTotalValue" Type="Edm.Decimal" Scale="2" Precision="8"/>
			</EntityType>
			<EntityContainer Name="EntityContainer">
				<EntitySet Name="Suppliers" EntityType="demo.Supplier"/>
			</EntityContainer>
		</Schema>
	</edmx:DataServices>
</edmx:Edmx>

Appendix 3

Source code of Java file: ServiceImplementation.java

package com.example.odata.DemoProject;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

import org.apache.http.HttpStatus;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.sap.cloud.sdk.odatav2.connectivity.ODataException;
import com.sap.cloud.sdk.odatav2.connectivity.ODataProperty;
import com.sap.cloud.sdk.odatav2.connectivity.ODataQueryBuilder;
import com.sap.cloud.sdk.odatav2.connectivity.ODataType;
import com.sap.cloud.sdk.service.prov.api.operations.Query;
import com.sap.cloud.sdk.service.prov.api.request.QueryRequest;
import com.sap.cloud.sdk.service.prov.api.response.ErrorResponse;
import com.sap.cloud.sdk.service.prov.api.response.QueryResponse;

public class ServiceImplementation {

	Logger logger = LoggerFactory.getLogger(ServiceImplementation.class);

	@Query(serviceName = "DemoService", entity = "Suppliers")
	public QueryResponse getSuppliers(QueryRequest queryRequest) {
		
		// compose $select statement
		String[] select = { "BusinessPartnerID", "CompanyName", "Address",   
				"ToProducts/ProductID", "ToProducts/Price", "ToProducts/TypeCode", 
				"ToContacts/FirstName", "ToContacts/LastName", "ToContacts/ContactGuid", "ToContacts/DateOfBirth" }; 
		
		try {
			// call the backend v2 service
			List<Map<String, Object>> v2BusinessPartnerList  = ODataQueryBuilder
					.withEntity("/sap/opu/odata/IWBEP/GWSAMPLE_BASIC", "BusinessPartnerSet")
					.select(select)
					.expand("ToContacts", "ToProducts")
					.filter(ODataProperty.field("BusinessPartnerRole").eq(ODataType.of("02"))) 
					.build()
					.execute("ES5")
					.asListOfMaps();

			// convert the result (v2) map to v4 map, including all the modifications
			List<Map<String, Object>> v4SupplierList = convertV2BusinessPartnersToV4Suppliers(v2BusinessPartnerList);
			
			return QueryResponse.setSuccess()
					.setDataAsMap(v4SupplierList)
					.response();
		} catch (ODataException e) {
			return QueryResponse.setError(ErrorResponse
					.getBuilder().setMessage("Error occurred while calling OData V2 data source. "
							+ e.getODataExceptionType() + " - " + e.getMessage())
					.setStatusCode(HttpStatus.SC_INTERNAL_SERVER_ERROR)
					.setCause(e)
					.response());
		}
	}
	
	
	// HELPER
	
	private List<Map<String, Object>> convertV2BusinessPartnersToV4Suppliers(List<Map<String, Object>> v2BusinessPartnerListExpanded){
		List<Map<String, Object>> v4supplierList = new ArrayList<Map<String, Object>>();
        if (v2BusinessPartnerListExpanded != null && v2BusinessPartnerListExpanded.size() > 0) {
            for (Map<String, Object> businessPartnerExpanded : v2BusinessPartnerListExpanded) {
                Map<String, Object> supplierEntity = convertV2BusinessPartnerToV4Supplier(businessPartnerExpanded);
                v4supplierList.add(supplierEntity);
            }
        }
		return v4supplierList;
	}
	
	@SuppressWarnings("unchecked")
	private  Map<String, Object> convertV2BusinessPartnerToV4Supplier(Map<String, Object> v2BusinessPartnerExpanded) {
    	Map<String, Object> supplierEntity = new HashMap<String, Object>();
        supplierEntity.put("SupplierIdentifier", v2BusinessPartnerExpanded.get("BusinessPartnerID"));
        supplierEntity.put("SupplierCompanyName", v2BusinessPartnerExpanded.get("CompanyName"));
        
        // Address property is a ComplexType, non-nullable 
        Map<String, Object> v2BusinessPartnerAddress = (Map<String, Object>)v2BusinessPartnerExpanded.get("Address");
     	supplierEntity.put("SupplierCity", v2BusinessPartnerAddress.get("City"));
    	supplierEntity.put("SupplierCountry", v2BusinessPartnerAddress.get("Country"));
        
    	// ToContacts is an expanded navigation property, here we flatten it into the supplier
		List<Map<String, Object>> contactsMapList = (List<Map<String, Object>>)v2BusinessPartnerExpanded.get("ToContacts");
		if(contactsMapList!= null && ! contactsMapList.isEmpty()) {
			//in v2 backend, one BusinessPartner can have many contacts. Here, for simplification, we take only the first
			Map<String, Object> contactsMap = contactsMapList.get(0);
			supplierEntity.put("ContactGuid", contactsMap.get("ContactGuid"));
			supplierEntity.put("ContactFirstName", contactsMap.get("FirstName"));
			supplierEntity.put("ContactLastName", contactsMap.get("LastName"));
			supplierEntity.put("ContactBirth", contactsMap.get("DateOfBirth"));
		}

    	// ToProducts is an expanded navigation property, here we use it to calculate new fields
		List<Map<String, Object>> productsList = (List<Map<String, Object>>)v2BusinessPartnerExpanded.get("ToProducts");
		if(productsList!= null && ! productsList.isEmpty()) {
			supplierEntity.put("ProductsToSupply", calculateProductsAsString(productsList));
			supplierEntity.put("ProductsTotalAmout", calculateProductsCount(productsList));
			supplierEntity.put("ProductsContainAds", calculateProductsContainAds(productsList));
			supplierEntity.put("ProductsTotalValue", calculateProductsValue(productsList));
		}

		return supplierEntity;
	}
	
	/** @return the number of product entries */
	private int calculateProductsCount(List<Map<String, Object>> productsList) {
		return productsList.size();
	}
	
	/** @return true if among the given product list there's an entry of type Advertisement */
	private boolean calculateProductsContainAds(List<Map<String, Object>> productsList) {
		return productsList.stream()
				.<String>map(map->(String)map.get("TypeCode"))
				.collect(Collectors.toList())
				.contains("AD");
	}
	
	/** @return all product IDs concatenated as one string */
	private String calculateProductsAsString(List<Map<String, Object>> productsList) {
		return productsList.stream()
				.<String>map(map->(String)map.get("ProductID"))
				.collect(Collectors.joining(", ")); // need <String> to compile fine
	}
	
	/** @return the sum of all products in the given list */
	private BigDecimal calculateProductsValue(List<Map<String, Object>> productsList) {
		return productsList.stream()
				.<BigDecimal>map(m->(BigDecimal)m.get("Price"))
				.reduce(BigDecimal.ZERO, (lhs, rhs) -> lhs.add(rhs), BigDecimal::add);
	}
}

Appendix 4

Creating DateOfBirth value

Unfortunately, we usually don’t find entries in the sample data of ES5 system, containing values for DateOfBirth property of the contacts.
Such that we cannot compare the backend value with our v4 service result.

But that’s not a problem, we just need to send an UPDATE to the backend service, to add some value to the entries we want to compare.
However, we cannot just update an arbitrary contact, it should be one which we see in our supplier list.

And this is how it is done:

Example request:

URLS:

use a guid from your supplier, e.g.

https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/ ContactSet(guid’0050568c-901d-1ed8-96c7-e9e794e4a0fa’)

HTTP Verb:

PATCH

Headers:

(Please refer here for explanation about the csrf token)

x-csrf-token: <your fetched token>
Content-Type:    application/json

Request body:

{
    "DateOfBirth": "\/Date(990316800000)\/"
}

(corresponds to 5/20/2001, 2:00:00 AM)

Alternative format:

{
    "DateOfBirth": "1988-12-22T21:33:11"
}

Note;
If you’re sending this payload to the backend service, make sure to enter any arbitrary data for the time. Remember that the data type is Edm-DateTime, so the time cannot be omitted.

Note:
You might find this converter tool useful.