Technical Articles

Varone Daniele

March 12, 2020 8 minute read

SAP Cloud Application Programming Model (CAPM): Deep Insert with many to many associations

Before start a thanks to Vincent Buccilli for the help it give to me on creating this Demo.

Context

The scope of this blog post is to describe the implementation of a “Deep Insert build from a model with many to many associations”. It will be done through SAP Cloud Application Programming Model, this is a natural extension of the deep insert series of Carlos Roggan I have take it as the start example and I really suggest to read it before.

In the Carlos Roggan series he show a one-to-one relation between entity and in the deep insert the key of the association is manually created from the second entity and passed to the first one.This kind of approach is not really helpful in a master-detail association where the key of master need to be passed to all detail items.

For the deep insert, a code implementation is required to override the “basics” insert. In this demo, I’ll use JAVA implementation to make all the CREATE possible.

Here a useful link to CAPM Doc

Scenario

I am creating a Movie Catalog Service, this service will show associations between Movies, Actors, Castings, and Categories. Each entity Movie will be associated with an entity Category (one to one association) and composed by a list of actors grouped trough casting (many to many associations).

The OData service will display a list of each entity (Movies, Actors, Castings, Categories).

The movie creation will be done via a deep Insert where the user can pass the Movie, with associated actors into Casting, and described by one Category.

The list of Actors already presents at DB Side can be used to replace the Casting actor ID with the actor full name.

Project

On Web IDE a “SAP Cloud Platform Business Application” project is created as:

Project name : DeepInsertMTM
Java package : com.demo.deep
OData version : V2
Service : Java
Database : SAP HANA Database

CDS Model

namespace my.model;

// Main Entity where we show all principal association 
entity Movies {
  key ID : UUID;
  NameM : String;
  Category : Association to Categories;
  Casts : Composition of many Castings on Casts.Movie = $self;
}

// One-to-One association
entity Categories{
	key CategoryID : UUID;
	CategoryText : String;
}

// list of actors 
entity Actors {
  key ID : Integer;
  FirstName : String;
  LastName : String;
  linkMovies: Association to many Castings on linkMovies.Actor = $self;
}

// list of actors that perform in the Movie
entity Castings{
  key Movie : Association to Movies;
  key Actor : Association to Actors;
}

For all entity to be created a UUID identifier is used to have a universally unique identifier

Movie-Actor Keys in Castings are made to be a unique entry for each Movie.

Service

The projection will allow the user who is consulting the service to make CREATE/GET operation, Actors are in readonly.

using { my.model } from '../db/data-model';

service CatalogService {
  entity Movies as projection on model.Movies;
  entity Castings as projection on model.Castings;
  entity @readonly Actors as projection on model.Actors;
  entity Categories as projection on model.Categories;
}

Java

The main point of the overriding method is to create an EntityData by using the JSON that the user sent to the API.

To make it possible, a Maps to harvest the data is needed.

package my.project;

import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import com.sap.cloud.sdk.cloudplatform.logging.CloudLoggerFactory;
import com.sap.cloud.sdk.service.prov.api.*;
import com.sap.cloud.sdk.service.prov.api.annotations.*;
import com.sap.cloud.sdk.service.prov.api.exits.*;
import com.sap.cloud.sdk.service.prov.api.request.*;
import com.sap.cloud.sdk.service.prov.api.response.*;
import com.sap.cloud.sdk.service.prov.api.EntityData;
import com.sap.cloud.sdk.service.prov.api.ExtensionHelper;
import com.sap.cloud.sdk.service.prov.api.exception.DatasourceException;
import com.sap.cloud.sdk.service.prov.api.operations.Create;
import com.sap.cloud.sdk.service.prov.api.request.CreateRequest;
import com.sap.cloud.sdk.service.prov.api.response.CreateResponse;
import org.slf4j.*;

public class MoviesService {


    private static final Logger LOG = CloudLoggerFactory.getLogger(MoviesService.class.getName());
        //Annotation to precise that this methos is called when we want to create Movie entity
	@Create(entity = "Movies", serviceName = "CatalogService")
	public CreateResponse createMoovie(CreateRequest createRequest, ExtensionHelper extensionHelper) throws DatasourceException{
                // Get all the json's data
		Map<String, Object> mapForCreation = createRequest.getData().asMap();

                // Generate uuid for Movie Entity
		UUID movieGuid = UUID.randomUUID(); 
		mapForCreation.put("ID", movieGuid);
                
                // For each entity Casting, bring the association to the movie ID wich has been created
		ArrayList<Object> cast = (ArrayList<Object>)mapForCreation.get("Casts");
		for(Object c : cast){
			Map<String,Object> current = (Map<String,Object>) c;
			current.put("Movie_ID", movieGuid);
		}
		
        // Get the association to the category and attached a new UUID to this entity
        Map<String, Object> inlineCategoryMap = (Map<String, Object>)mapForCreation.get("Category");
        if(inlineCategoryMap != null) {
    		UUID categoryUUID = UUID.randomUUID();  
        	inlineCategoryMap.put("CategoryID", categoryUUID);
        	mapForCreation.put("Category_CategoryID", categoryUUID);
        }
	
        // New map that will contain the key to compose the entities 
        Map<String, List<String>> keyMap = new HashMap<String, List<String>>();
        keyMap.put("Movies", Collections.singletonList("ID"));        
        keyMap.put("Categories", Collections.singletonList("Category"));                
        List<String> lis = Collections.singletonList("Casts");
        keyMap.put("Castings", lis);               
        
        // EntityData type enable to create entities
        EntityData entityDataToCreate = EntityData.createFromDeepMap(mapForCreation, keyMap, "CatalogService.Movies");              
        EntityData result = extensionHelper.getHandler().executeInsertWithAssociations(entityDataToCreate, true);// true to return created entity
        return CreateResponse.setSuccess().setData(result).response();
	}
}

CSV

In order to import data for Actors from CSV file, I have followed the step described in this link :

ID,FIRSTNAME,LASTNAME
1,Bradley,Cooper
2,Jennifer,Lopez
3,Lady,Gaga
4,Al,Paccino
5,Robert,Deniro

Project Build

After building DB and SRV on Web IDE the link to test the Service should be present on console header. Now I’m able to check the service Metadata, naturally Movie and Casting are empty, service returns nothing.

Service with data :

Create Movie

To create the Movie entity with Casting and Category, I have used POSTMAN

Http Verb : POST
URL : https://…DeepInsertmtm-srv…/odata/v2/CatalogService/Movies
Body-type : JSON

Here below as to resemble the JSON for the DeepInsert :

{
	"NameM" : "The Godfather Part II",
	"Category" :{
		"CategoryText" : "Crime"	
	},
	"Casts":[
		{
			"Actor_ID" : 4
		},
		{
			"Actor_ID" : 5
		}
		
	]
}

POSTMAN result :

last but not least a double check on service to verify that the entry is correctly inserted, here below the Movie single entry for “The Goodfather part II” :

XSL 1.0
<entry xml:base="https://.../odata/v2/CatalogService/" xmlns="http://www.w3.org/2005/Atom" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices">
<id>https://.../odata/v2/CatalogService/Movies(guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85')</id>
<title type="text">Movies</title>
<updated>2020-03-12T10:04:35.879Z</updated>
<category term="CatalogService.Movies" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<link href="Movies(guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85')" rel="edit" title="Movies"/>
<link href="Movies(guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85')/Category" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Category" title="Category" type="application/atom+xml;type=entry"/>
<link href="Movies(guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85')/Casts" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Casts" title="Casts" type="application/atom+xml;type=feed"/>
<content type="application/xml">
<m:properties>
<d:ID>3f3e2831-0cde-4fca-8d78-2a917d68ce85</d:ID>
<d:NameM>The Godfather Part II</d:NameM>
<d:Category_CategoryID>0fde3620-05ef-4d03-99bf-e3ecbf0970f3</d:Category_CategoryID>
</m:properties>
</content>
</entry>

Navigation to Category from Movie:

XSL 1.0
<entry xml:base="https://.../odata/v2/CatalogService/" xmlns="http://www.w3.org/2005/Atom" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices">
<id>https://.../odata/v2/CatalogService/Categories(guid'0fde3620-05ef-4d03-99bf-e3ecbf0970f3')</id>
<title type="text">Categories</title>
<updated>2020-03-12T10:07:29.744Z</updated>
<category term="CatalogService.Categories" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<link href="Categories(guid'0fde3620-05ef-4d03-99bf-e3ecbf0970f3')" rel="edit" title="Categories"/>
<content type="application/xml">
<m:properties>
<d:CategoryID>0fde3620-05ef-4d03-99bf-e3ecbf0970f3</d:CategoryID>
<d:CategoryText>Crime</d:CategoryText>
</m:properties>
</content>
</entry>

Navigation to Casts from Movie :

XPath1/Parse
XSL 1.0
<feed xml:base="https://.../odata/v2/CatalogService/" xmlns="http://www.w3.org/2005/Atom" xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices">
<id>https://.../odata/v2/CatalogService/Castings</id>
<title type="text">Castings</title>
<updated>2020-03-12T10:08:25.816Z</updated>
<author>
<name/>
</author>
<link href="Castings" rel="self" title="Castings"/>
<entry>
<id>https://.../odata/v2/CatalogService/Castings(Movie_ID=guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85',Actor_ID=4)</id>
<title type="text">Castings</title>
<updated>2020-03-12T10:08:25.816Z</updated>
<category term="CatalogService.Castings" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<link href="Castings(Movie_ID=guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85',Actor_ID=4)" rel="edit" title="Castings"/>
<link href="Castings(Movie_ID=guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85',Actor_ID=4)/Movie" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Movie" title="Movie" type="application/atom+xml;type=entry"/>
<link href="Castings(Movie_ID=guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85',Actor_ID=4)/Actor" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Actor" title="Actor" type="application/atom+xml;type=entry"/>
<content type="application/xml">
<m:properties>
<d:Movie_ID>3f3e2831-0cde-4fca-8d78-2a917d68ce85</d:Movie_ID>
<d:Actor_ID>4</d:Actor_ID>
</m:properties>
</content>
</entry>
<entry>
<id>https://.../odata/v2/CatalogService/Castings(Movie_ID=guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85',Actor_ID=5)</id>
<title type="text">Castings</title>
<updated>2020-03-12T10:08:25.816Z</updated>
<category term="CatalogService.Castings" scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme"/>
<link href="Castings(Movie_ID=guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85',Actor_ID=5)" rel="edit" title="Castings"/>
<link href="Castings(Movie_ID=guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85',Actor_ID=5)/Movie" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Movie" title="Movie" type="application/atom+xml;type=entry"/>
<link href="Castings(Movie_ID=guid'3f3e2831-0cde-4fca-8d78-2a917d68ce85',Actor_ID=5)/Actor" rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Actor" title="Actor" type="application/atom+xml;type=entry"/>
<content type="application/xml">
<m:properties>
<d:Movie_ID>3f3e2831-0cde-4fca-8d78-2a917d68ce85</d:Movie_ID>
<d:Actor_ID>5</d:Actor_ID>
</m:properties>
</content>
</entry>
</feed>

Conclusion

On the implemented Deep insert with many-to-many associations inside an override method using JAVA is done, the Master Key is manually created and passed to all detail items.

Of course, the schema can be bewildered and completed with many other associations.

Troubleshooting

On Web IDE the java code completion is not working that make difficult write the code specially, my case, for a ABAP developer with basic java knowledge. I find difficult also found for all SAP Java methods the documentation.

The use of Web IDE debugger to consult the sent data by service and check the code is really helpful navigation on call stack is not really clear.

5 Comments

You must be Logged on to comment or reply to a post.

Diego Fernandes Silva

April 25, 2020 at 10:04 pm

Congrats Varone Daniele !

For the project compile, at least in Web IDE, a correction is necessary in this line:

entity Actors @readonly as projection on model.Actors;

Nandan Chaturvedi

June 1, 2020 at 1:44 pm

Thanks a lot for sharing this blog. Really useful and informative.

I was following this and just want to know if you can share how the metadata looks for the project. Because, while creating the data for Movies and Actors, should I have actor_ID and Movie_ID respectively in their data?

If yes, I get an error "no column named actor_ID" in Movies

And if no, how am I supposed to maintain the data with reference to each other?

Many thanks in advance?

Nandan Chaturvedi

June 2, 2020 at 2:32 am

Dear Varone Daniele !

I am getting the following error as well:
"Deep insert with to-many Associations is not allowed". Do you think there is something i am doing wrong?

Shubham Dehariya

October 23, 2020 at 4:40 pm

Hello Nandan,

I am also facing same issue.

Were you able to fix it?

Thanks & Regards,
Shubham