Technical Articles
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.
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;
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?
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?
Hello Nandan,
I am also facing same issue.
Were you able to fix it?
Thanks & Regards,
Shubham
Found a solution.
Thanks Gregor Wolf
https://github.com/sapmentors/cap-community/tree/master/examples/deep-insert