OData – Microsoft Dynamics
The webpage contains a tutorial for exchanging data between a native Java application and a Microsoft Dynamics system.
‘Dynamics’ offers an open OData-interface for data-exchange with not only other Microsoft applications, but also a Java application can successfully be coupled to the Dynamics application, and make use of its structured datasets.
You need to be authorized to login in the Dynamics system. That is, you need to have valid user-credentials (username + password) in the Dynamics-application. Moreover, the WIndows-authentication server ADFS must be configured such that you can use a web-dialogue to login to Dynamics.
Important note: the solution presented here was developed for an on-premises installation of ‘Dynamics’. When ‘Dynamics’ runs in the cloud, under Azure, a similar authentication procedure is used.
OData-background
Essential to data exchange are the read and write operations that make up the interface. The OData-interface (OData tutorial) implemented by Dynamics, uses web-trafficing to query for JSON-objects (entities, or records), and to consume JSON-objects (with parts of entities/records). The JSON-objects are strongly typed within Dynamics, but looesly typed exchange via JSON is supported (i.e., no type declarations acompanying the fields), as is also strong field typing.
Under the surface, the Dynamics application uses a MS SQL-server database as dataengine. The Dynamics OData interface represents each database table as an EntitySet. An individual database record is called an EntityType.
The XML-tag “<EntitySet …” defines a set of entities.
The XML-tag “<EntityType…” defines one entity, and it inclused a primary key field (“<Key> …”).
The OData-standard uses http-uri-requests to perform database-queries (select from …, select from … where …). The OData-endpoint in return provides one or more records that match the select criteria. In case a query to an entity set is performed with the unique primary key, then the matching record is returned, as JSON-object. The primary key type is often of type GUID, in Dynamics (a UUid type).
You can also query on other fields than the primary key, and zero, one – or more records are returned, in the latter case as a JSON-list of JSON-objects.
Dynamics-Metadata
The Dynamics application contains an extensive description of all the data and data types that are available via the OData-endpoint of the Dynamics-system. The OData-endpoint typically has the format: https://mydynanamicsapp.myorg.org/api/data/v9.0/
The contents of the metadata page can be queried by:
https://mydynanamicsapp.myorg.org/api/data/v9.0/$metadata
which returns a lengthy XML-tagged webpage.
You can scroll through this XML-page with your browser.
Searching for occurences of the XML-tags <EntitySet and <EntityType yields the beginning of the entity definitions in the metadata XML-page.
You can also save this XML-page with all metadata as text-file and open this text-file with, for example, notepad++ or another text editor.
Start of a $metadata page (in XML-format):
<?xml version="1.0" encoding="UTF-8"?> <edmx:Edmx Version="4.0" xmlns:edmx="http://docs.oasis-open.org/odata/ns/edmx"> <edmx:Reference Uri="http://vocabularies.odata.org/OData.Community.Keys.V1.xml"> <edmx:Include Namespace="OData.Community.Keys.V1" Alias="Keys"/> <edmx:IncludeAnnotations TermNamespace="OData.Community.Keys.V1"/> </edmx:Reference> <edmx:Reference Uri="http://vocabularies.odata.org/OData.Community.Display.V1.xml"> <edmx:Include Namespace="OData.Community.Display.V1" Alias="Display"/> <edmx:IncludeAnnotations TermNamespace="OData.Community.Display.V1"/> </edmx:Reference> <edmx:DataServices> <Schema Namespace="Microsoft.Dynamics" Alias="msdyn" xmlns="http://docs.oasis-open.org/odata/ns/edm"> <EntityType Name="dynbaseentity" Abstract="true"/> <EntityType Name="accounts" BaseType="msdyn.dynbaseentity"> <Key> <PropertyRef Name="primary_key"/> </Key> <Property Name="primary_key" Type="Edm.Guid"> <Annotation Term="Org.OData.Core.V1.Description" String="The unique id of the possible customer for the account."/> </Property> <Property Name="leadid" Type="Edm.Guid"> <Annotation Term="Org.OData.Core.V1.Description" String=""/> <Annotation Term="Org.OData.Core.V1.Permissions"> <EnumMember>Org.OData.Core.V1.PermissionType/Read</EnumMember> </Annotation> </Property> ...
The metadata for one EntityType, in this example accounts, can with the following call be performed:
https://mydynanamicsapp.myorg.org/api/data/v9.0/EntityDefinitions(LogicalName=’accounts‘)
Literature links to Dynamics and OData:
The working of OData requests
The OData interface of ‘Dynamics’ is extensive. In general, https-GET-requests are used to query for sets of entities, pertaining to an entity set. This set may be empty, contain one entity (a unique match), or it can result in a list of entities. The request returns one JSON-object containing a list of records, in JSON-format (i.e., key-value pairs).
You can use the Java library OLingo to receive the JSON-output from the OData-request, as a list of Java-entities. OLingo is described below.
The ‘raw’ JSON-object returned can look as follows:
"{\n" + " \"@odata.context\":\"https://mydynanamicsapp.myorg.org/api/data/v9.0/$metadata#accounts\", + " \"value\":[\n" + " {\n" + " \"@odata.etag\":\"A/\\\"987654762\\\"\"," + " \"account_name\":\"General costs\"," + " \"primary_key\":\"77477040-802d-e611-80c7-0050569c18ec\"," + " \"statecode\":0," + " \"statuscode\":1," + " \"notification\":null\n" + " }\n" + " ]\n" + "}";
Lookup all entities in the entityset accounts
https://mydynanamicsapp.myorg.org/api/data/v9.0/accounts
which results in a list of JSON-objects, the entityset accounts.
Lookup specific entity from known primary key
https://mydynanamicsapp.myorg.org/api/data/v9.0/accounts(77477040-802d-e611-80c7-0050569c18ec)
where the primary key is of type Edm.Guid: 77477040-802d-e611-80c7-0050569c18ec
Note that for entity-field-updates, the OData-object to update must always be indicated with its primary key, in the (…-…) annotation, as shown in this paragraph.
Lookup matching entities from string field value
https://mydynanamicsapp.myorg.org/api/data/v9.0/accounts?$filter=account_name eq ‘General costs’
which results in a list of JSON-objects. This filter construct can be extended with wild-card constructions and substring operators.
Important note: the OData-uri calls above cannot be applied directly from within Java, via http-connect statements. You need to apply first:
String encodedUri = URLEncoder.encode(<odata-uri>);
as to encode the special characters correctly. The string encodedUri can be used from within Java, in combination with:
...
URL obj = new URL(encodedUri);
URLConnection conn = obj.openConnection();
...
OLingo – Java-library for OData object representation
The OData-Get-queries return JSON-objects via the output stream of the http-request. These JSON-objects have a specific format with a set of predefined tags, and field types. The data objects returned can be represented as OData-Java objects in your Java-code.
OData – Read
This Java-code below shows how an OData client entity set (OLingo version 4.5.0, earlier versions have other object names and types) is read from the OData-endpoint:
String oDataUrlQuery = "https://mydynanamicsapp.myorg.org/api/data/v9.0/accounts"; String primaryKey = "77477040-802d-e611-80c7-0050569c18ec"; oDataUrlQuery += URLEncoder.encode("(" + primaryKey + ")", StandardCharsets.UTF_8.name()); // URL-encoded version of "https://mydynanamicsapp.myorg.org/api/data/v9.0/accounts(77477040-802d-e611-80c7-0050569c18ec)" // Http-get query, see below ClientEntitySet oDataEntitySet = getQueryFromHttp(oDataUrlQuery); if (oDataEntitySet == null) { return null; } for (ClientEntity oDataEntity : oDataEntitySet.getEntities()) { for (ClientProperty currentProperty : oDataEntity.getProperties()) { String propertyContent = currentProperty.getName(),currentProperty.getValue().toString(); System.out.println("Property name: " + currentProperty.getName() + " Property value: " + propertyContent); } }
The ClientEntitySet contains a list of the returned entities. In case the http-query returns no objects, the list is empty. When a single object is returned, the list contains that single entity.
An ODataEntity is reprebsented by the type ClientEntity. An entity has one or more properties – each property corresponds to a table-field (a DB-column). Each property has also a type. For example. a String is in OData represented by the type Edm.String.
The ClientEntitySet is constructed using the OLingo client.
public ClientEntitySet getQueryFromHttp(String url) throws Exception { // Initialize ODataClient ODataClient client = ODataClientFactory.getClient(); client.getConfiguration().setDefaultPubFormat(ContentType.JSON_NO_METADATA); client.getConfiguration().setHttpClientFactory(new RequestRetryHttpClientFactory()); // Read JSON-object from OData-endpoint ('Dynamics'), using Http-get String jsonObjectRead = getViaHttp(url); InputStream inputStream = new ByteArrayInputStream(jsonObjectRead.getBytes(StandardCharsets.UTF_8)); return client.getReader().readEntitySet(inputStream, ContentType.APPLICATION_JSON); }
The http-get is defined as follows:
public String getViaHttp(String url) throws Exception { URL obj = new URL(url); URLConnection conn = obj.openConnection(); conn.setUseCaches(false); conn.setRequestProperty("Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8"); conn.setRequestProperty("Accept-Language", "en-US,en;q=0.5"); conn.setRequestProperty("Authorization", OAuth2AccessToken.BEARER_TYPE + " " + getBearerToken()); if ("gzip".equals(conn.getContentEncoding())) { Reader reader = new InputStreamReader(new GZIPInputStream(conn.getInputStream())); String jsonRawOutput = ""; while (true) { int ch = reader.read(); if (ch == -1) { break; } jsonRawOutput += (char) ch; } return jsonRawOutput; } else { BufferedReader in = new BufferedReader(new InputStreamReader(conn.getInputStream())); String jsonRawOutput = ""; while (true) { int ch = in.read(); if (ch == -1) { break; } jsonRawOutput += (char) ch; } return jsonRawOutput; } }
We use authentication via a Bearer token, obtained from the AFDS-sts service, which is the Microsoft authentication server.
A bearer token is one long character string. ADFS and the MS Dynamics application need to be configured as to support login via a bearer-token request, that is, via the OAuth2-protocol.
OAuth2 – via JavaSpring
There are three ways that can be used to authenticate via ADFS and establish communication with ‘Dynamics’:
- Web-browser authentication (username and password)
- SAML-authentication
- OAuth2 authentication
The web-browser authentication eventually provides the http-reply message with a valid cookie, which needs to be added to the sucessive http-query requests via the OData-interface (see the section ‘The working of OData requests’, above). The web-browser authentication cannot be used to write data, or to alter data, in the Dynamics-system.
The SAML-authentication is used by Microsoft in their own Dynamics-web-screens. The messages are complex. Moreover, Microsoft has indicated that the SAML-protocol will in due course be phased out, as means for communication with the Dynamics-system.
The third means is OAuth2 authentication, where ADFS is configured to provide a bearer-token when requested so with the correct login credentials. This valid bearer token subsequently needs to be added to the http-get and http-patch request in order to read and alter data, in the dynamics entity-sets.
The following code fragment below obtains the correct bearer token, when the credentials are correct, and ADFS is configured as to provide the resource uri, here this is: https://mydynanamicsapp.myorg.org. Spring OAuth2 is being used to establish and perform the OAuth2 request (see the Maven-dependency below, on this page).
public String getBearerToken() { ResourceOwnerPasswordResourceDetails resourceObj = new ResourceOwnerPasswordResourceDetails(); resourceObj.setAccessTokenUri("https://sts.myorg.org/adfs/oauth2/token"); resourceObj.setId("https://mydynanamicsapp.myorg.org"); headersMap.put("Cache-Control", Arrays.asList("no-cache")); headersMap.put("Content-Type", Arrays.asList("application/x-www-form-urlencoded")); AccessTokenRequest atr = new DefaultAccessTokenRequest(); Map<String, List<String>> headersMap = new HashMap<String, List<String>>(); resourceObj.setGrantType("password"); atr.add("Bearer Token", "Access_Token"); atr.add("client_Id", "ab762727-144c-......"); atr.add("Version", "v9.0"); atr.add("resource", "https://mydynanamicsapp.myorg.org"); atr.add("username", "WINDOMAIN\\dynamics_user"); atr.add("password", "dynamics_password"); OAuth2ClientContext context = new DefaultOAuth2ClientContext(atr); OAuth2RestTemplate restTemplate = new OAuth2RestTemplate(resourceObj, context); OAuth2AccessToken token = restTemplate.getAccessToken(); return token.toString(); }
If ADFS is not configured to support OAuth2-authentication for ‘Dynamics’, you will be denied a bearer token by ‘sts’. Consult then a certified Microsoft consultant with the required knowledge of how to configure ADFS for OAuth2-authentication and http-redirect.
OData – Update Entity fields
It is also possible to alter field-values in existing entities, via the OData-interface. Notice that an Apache Http client is required to perform the correct http-patch operation (http-Patch is similar to http-Post, but is a different operation). The options presented on the web for using the java.net http-client will not work (i.e., using an “X-HTTP-Method-Override” -> “PATCH” header field will NOT work).
Let’s say we want to update the field account_name in the entity accounts with the primary key 77477040-802d-e611-80c7-0050569c18ec.
String jsonObject = "{\n" + "\"account_name\":\"General overhead costs\"\n" + "}";
So this JSON-object (jsonObject) contains the field name (‘account_name’) and the updated string value (‘General overhead costs’) with the update value, pertaining to the accounts-object with primary key 77477040-802d-e611-80c7-0050569c18ec. The primary key is indicated via the url of the http-patch call.
So the update-uri of the http-patch becomes:
String changeUrlWithKeyEncoded = "https://mydynanamicsapp.myorg.org/api/data/v9.0/accounts" + URLEncoder.encode("(77477040-802d-e611-80c7-0050569c18ec)", StandardCharsets.UTF_8.name());
The Patch-operation is implemented via the Apache http client:
public boolean updateObject(String changeUrlWithKeyEncoded, String jsonObject, String bearerToken) { try { CloseableHttpClient httpClient = prepareClient(); HttpPatch request = new HttpPatch(changeUrlWithKeyEncoded); StringEntity params = new StringEntity(jsonObject, ContentType.APPLICATION_JSON); request.setEntity(params); request.addHeader(org.apache.http.HttpHeaders.CONTENT_TYPE, MediaType.APPLICATION_JSON_VALUE); request.addHeader(HttpHeaders.ACCEPT, MediaType.APPLICATION_JSON_VALUE); request.addHeader(HttpHeaders.AUTHORIZATION, OAuth2AccessToken.BEARER_TYPE + " " + bearerToken); HttpResponse response = httpClient.execute(request); if (response.getStatusLine().getStatusCode() != HttpStatus.NO_CONTENT.value()) { log.error("updateObject: update object of [" + changeUrlWithKeyEncoded + "] failed, with http status code [" + response.getStatusLine().getStatusCode() + "] giving response message [" + response.getStatusLine().toString() + "]"); } return response.getStatusLine().getStatusCode() == HttpStatus.NO_CONTENT.value(); } catch (Exception e) { log.error("updateObject: update object of [" + changeUrlWithKeyEncoded + "] failed because of [" + e + "]"); } return false; } public CloseableHttpClient prepareClient() { try { SSLContext sslContext = SSLContexts.custom().loadTrustMaterial(null, new TrustSelfSignedStrategy()).useTLS().build(); HttpClientBuilder builder = HttpClientBuilder.create(); SSLConnectionSocketFactory sslConnectionFactory = new SSLConnectionSocketFactory(sslContext, SSLConnectionSocketFactory.ALLOW_ALL_HOSTNAME_VERIFIER); builder.setSSLSocketFactory(sslConnectionFactory); Registry<ConnectionSocketFactory> registry = RegistryBuilder.<ConnectionSocketFactory>create() .register("https", sslConnectionFactory) .register("http", new PlainConnectionSocketFactory()) .build(); HttpClientConnectionManager ccm = new BasicHttpClientConnectionManager(registry); builder.setConnectionManager(ccm); return builder.build(); } catch (Exception ex) { return null; } }
Take notice of the fact that SSL is being used in the Apache-patch http-call.
After the successful Patch-call, the OData-service returns http-code 204, NO_CONTENT. You have successfully updated the field account_name in the accounts entity with primary key 77477040-802d-e611-80c7-0050569c18ec.
All other http-response codes than 204 indicate that the update attempt was unsuccessful.
Construct new entities and entity updates with OLingo
When you need to update several fields (properties) in a record, or you want to create a new record in Dynamics using OData, OLingo offers handy and typesafe code.
final String DYN_ROOT_NAMESPACE= "msdyn"; String fieldName = "account_name"; String fieldValue = "General overhead costs"; FullQualifiedName fullQualifiedName = new FullQualifiedName(DYN_ROOT_NAMESPACE + "." + "accounts"); ClientEntity newEntity = ODataClientFactory.getClient().getObjectFactory().newEntity(fullQualifiedName); ClientPrimitiveValue oDataValue = new ClientPrimitiveValueImpl.BuilderImpl().setValue(fieldValue).build(); ClientProperty property = ODataClientFactory.getClient().getObjectFactory().newPrimitiveProperty(fieldName, oDataValue); newEntity.getProperties().add(property); InputStream inputStream = client.getWriter().writeEntity(newEntity, ContentType.JSON); String jsonUpdateObject = IOUtils.toString(inputStream, StandardCharsets.UTF_8); /* String jsonUpdateObject: "{\n" + "\"account_name\":\"General overhead costs\"\n" + "}"; */
This Java-code yields the Json-object to use in the http-Patch-call, as described earlier in this tutorial.
Maven dependencies
The following Maven dependencies were used, in the code fragments in this tutorial:
<dependency>
<groupId>org.springframework.security.oauth</groupId>
<artifactId>spring-security-oauth2</artifactId>
<version>2.3.5.RELEASE</version>
</dependency>
<properties>
<odata.version>4.5.0</odata.version>
</properties>
<dependency>
<groupId>org.apache.olingo</groupId>
<artifactId>odata-client-api</artifactId>
<version>${odata.version}</version>
</dependency>
<dependency>
<groupId>org.apache.olingo</groupId>
<artifactId>odata-client-core</artifactId>
<version>${odata.version}</version>
</dependency>
<dependency>
<groupId>org.apache.olingo</groupId>
<artifactId>odata-commons-api</artifactId>
<version>${odata.version}</version>
</dependency>
<dependency>
<groupId>org.apache.olingo</groupId>
<artifactId>odata-commons-core</artifactId>
<version>${odata.version}</version>
</dependency>
Further applications – connect with SAP-Hana
The OLingo Java-code presented here can also be used to connect with SAP-Hana thereby providing an OData-interface.
See the technical explanation of the OLingo / Java-to-SAP connection, as explained in this link.