Advertisements

Microservices and SODA: JSON Data Stored in an Oracle Database Accessed through​ REST or Java


Simple Oracle Document Access

SODA (Simple Oracle Document Access) is a non-SQL style of storing/retrieving JSON data in an Oracle database.

cold summer alcohol cocktail
Photo by Pixabay on Pexels.com

It’s so easy to work with SODA! Let’s get started.

First, enable ORDS schema in your database.

Second, deploy ORDS in K8s (you can also deploy ORDS standalone in your laptop or in a container if you don’t have one, here you can find an example).

Now let’s try it:

Create a collection:

A collection is a table that transparently stores JSON data for you. Execute the following instruction remarked in bold:

curl -i -X PUT http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON

HTTP/1.1 201 Created

Date: Sat, 22 Dec 2018 19:15:26 GMT

X-Frame-Options: SAMEORIGIN

Cache-Control: private,must-revalidate,max-age=0

Location: http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/

Content-Length: 0

Insert JSON in the database:

Download this to a file called po.json and execute the following (please note the ID of the new doc created)

curl -X POST --data-binary @po.json -H "Content-Type: application/json" "http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/"

{"items":[{"id":"1C0C85A9B7084DAD88AC9155DC998578","etag":"D17DBB877A3B846EDB32789589F8CDB791634359D615376D938D167AAA3E1F46","lastModified":"2018-12-22T19:21:02.243934Z","created":"2018-12-22T19:21:02.243934Z"}],"hasMore":false,"count":1}

Just in case you haven’t noticed the magic of this, take a look at the following picture:

sodajson.png

Retrieve document by ID:

The JSON data is stored in a table. Now we can retrieve the document by the ID that was previously given as follows:

curl -X GET http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/1C0C85A9B7084DAD88AC9155DC998578

{"PONumber": 0,

"Reference": "ABANDA-20140803",

"Requestor": "Amit Banda",

...

"UPCCode": 85391181828},

"Quantity": 9.0}]

Search for documents:

So far, so good. But wait, there is more! Let’s search for the content of a JSON attribute, in this case, documents with PO number is 0:

curl -X POST --data-binary '{"PONumber":"0"}' -H "Content-Type: application/json" http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON?action=query

{"items":[{"id":"1C0C85A9B7084DAD88AC9155DC998578","etag":"D17DBB877A3B846EDB32789589F8CDB791634359D615376D938D167AAA3E1F46","lastModified":"2018-12-22T19:21:02.243934Z","created":"2018-12-22T19:21:02.243934Z","value":{"PONumber": 0,...]}}],"hasMore":false,"count":1}

Bulk insert:

Now, let’s insert a bunch of JSON’s, first download this content and save as POlist.json and execute the following in bold:

curl -X POST --data-binary @POlist.json -H "Content-Type: application/json" http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON?action=insert

{"items":[{"id":"0A6DF1E1A3A745D98DC3E3DD648732B2","...22T19:45:42.157916"}],"hasMore":false,"count":70,"itemsInserted":70}

As you can observe, we have inserted several objects with one shot.

Update a document:

Let’s update JSON with PO=0 we searched for before (take note of the ID):

curl -i -X PUT --data-binary '{"PONumber" : "99999"}' -H "Content-Type: application/json" http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/1C0C85A9B7084DAD88AC9155DC998578

HTTP/1.1 200 OK

Date: Sat, 22 Dec 2018 19:55:10 GMT

X-Frame-Options: SAMEORIGIN

Cache-Control: no-cache,must-revalidate,no-store,max-age=0

ETag: BC9497081F1E314EE19DD777F2ED7E8425F51C21E7EC1968CEA8110A684C3F05

Last-Modified: Sat, 22 Dec 2018 19:55:10 GMT

Location: http://130.61.67.88:8080/ords/hr/soda/latest/shopcartJSON/1C0C85A9B7084DAD88AC9155DC998578

Content-Length: 0

 

List records, delete records and so forth:

And so on… Take a look at the SODA REST, Java and whitepaper documentation.

Discover more features

Take a look at the documentation on how to create more complex queries and many other features.

Last, but not least, notice that the JSON object can be different in each row, there is no constraint that forces to a specific structure.

Microservices and SODA

As you probably have noticed, SODA is nice for CRUD operations with JSON data (such as the list of products in the shopping cart) in the database. In addition, you can utilize a relational model for the rest of your app. In brief, SODA is good for building microservices for the modern applications of the digital era.

And finally, by storing your data in an Oracle database, you get benefit of it’s inherent robustness, reliability, resilience, high availability, fault tolerant, distributed (sharding), in-memory, backup/recovery and more.

ordsarch

That’s all folks!

Enjoy 😉

 

 

IT

Advertisements

javier mugueta View All →

Me and my circumstance

1 Comment Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: