Oracle RDBMS as a NoSQL JSON document store

Support for storing and querying JSON has recently been added to the Oracle Database, and is covered extensively by my colleague Beda Hammerschmidt in this blog. Developers can store and query JSON using Oracle SQL enhanced with the new JSON operators over traditional APIs such as OCI and JDBC.

Now, there's also a sparkling new family of APIs called SODA (Simple Oracle Document Access) that are built on top of the JSON functionality, and deliver the experience of a NoSQL document-store with the Oracle RDBMS.

Initially two implementations of SODA are available: SODA for REST, and SODA for Java. This blog entry will focus on how to get started with SODA for Java.

With the SODA for Java API, instead of accessing tables via SQL/JSON queries over JDBC, the developer works with collections of documents, and performs CRUD (create, read, update, delete) operations on these collections. Under the covers, document collections are stored as Oracle RDBMS tables with JSON contents, and SODA transparently translates CRUD operations into SQL over JDBC. So you get the ease of use of a NoSQL document store, with all the benefits of the Oracle RDBMS, such as transactions.

Let's build a simple app to show SODA for Java in action.

Prerequisites:

To work with SODA for Java, you must have Oracle Database 12.1.0.2, and you must install a patch.

Oracle Database 12.1.0.2 is available at this URL: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

For the patch, go to Oracle Support (https://support.oracle.com). Select tab Patches & Updates. Search for the patch number 20885778, or access it directly at this URL: https://support.oracle.com/rs?type=patch&id=20885778

Download the patch appropriate for your platform, and install it following the instructions in the README available with the patch.

Finally, you also need to grant the SODA_APP role to the Oracle Database schema that will be used to store document collections (i.e. actual RDBMS tables that back these collections). So connect as sysdba to your instance, and, assuming your schema name is "scott", issue:

grant SODA_APP to scott;

To run SODA for Java, you need the following jars:

  • ojdbc6.jar that ships with Oracle Database 12.1.0.2, available here
  • javax.json-1.0.4.jar. This is the JSR353 implementation, download it from here
  • orajsoda.jar. The SODA for Java jar. Download the latest here

These three jars, javax.json-1.0.4.jar, orajsoda.jar, and ojdbc6.jar, must be in the classpath to compile and run SODA for Java apps.

Now let's write a simple app that:

  • Creates a new collection
  • Inserts a few documents into the collection
  • Retrieves the first inserted document by its auto-generated key
  • Retrieves documents matching a query-by-example, or QBE

Here's the code (read the comments in-line to understand what the code is doing):

import java.sql.Connection;
import java.sql.DriverManager;

import oracle.soda.rdbms.OracleRDBMSClient;

import oracle.soda.OracleDatabase;
import oracle.soda.OracleCursor;
import oracle.soda.OracleCollection;
import oracle.soda.OracleDocument;
import oracle.soda.OracleException;

import java.util.Properties;

public class testSODA {
  public static void main(String[] args) {
   
    // SODA works on top of a regular JDBC connection.
    // Set up the connection string: replace hostName, port, and serviceName
    // with the info for your Oracle RDBMS instance
    String url = "jdbc:oracle:thin:@//hostName:port/serviceName";

    Properties props = new Properties();

    // Replace with your schemaName and password
    props.setProperty("user", "schemaName");
    props.setProperty("password", "password");

    Connection conn = null;

    try {
        // Get a JDBC connection to an Oracle instance
        conn = DriverManager.getConnection(url, props);

        // Get an OracleRDBMSClient - starting point of SODA for 
        // Java application
        OracleRDBMSClient cl = new OracleRDBMSClient();

        // Get a database
        OracleDatabase db = cl.getDatabase(conn);

        // Create a collection with the name "MyJSONCollection".
        // Note: Collection names are case-sensitive.
        // A table with the name "MyJSONCollection" will be
        // created in the RDBMS to store the collection
        OracleCollection col = db.admin().createCollection("MyJSONCollection");

        // Create a few JSON documents, representing
        // users and the number of friends they have
        OracleDocument doc1 =
          db.createDocumentFromString(
            "{ \"name\" : \"Alex\", \"friends\" : \"50\" }");

        OracleDocument doc2 =
          db.createDocumentFromString(
            "{ \"name\" : \"Mia\", \"friends\" : \"300\" }");

        OracleDocument doc3 =
          db.createDocumentFromString(
            "{ \"name\" : \"Gloria\", \"friends\" : \"399\" }");

        // Insert the documents into a collection, one-by-one.
        // The result documents contain auto-generated 
        // keys, among other documents components (version, etc).
        // Note: SODA provides the more efficient bulk insert as well
        OracleDocument resultDoc1 = col.insertAndGet(doc1);
        OracleDocument resultDoc2 = col.insertAndGet(doc2);
        OracleDocument resultDoc3 = col.insertAndGet(doc3);

        // Retrieve the first document using its auto-generated
        // unique ID (aka key)
        System.out.println ("* Retrieving the first document by its key *\n");

        OracleDocument fetchedDoc = col.find().key(resultDoc1.getKey()).getOne();

        System.out.println (fetchedDoc.getContentAsString());

        // Retrieve all documents representing users that have
        // 300 or more friends. Use the following query-by-example:
        // {friends : {$gte : 300}}.
        System.out.println ("\n* Retrieving documents representing users with" +
                            " at least 300 friends *\n");

        OracleDocument f = db.createDocumentFromString(
         "{ \"friends\" : { \"$gte\" : 300 }}");

        OracleCursor c = null;

        try {
          // Get a cursor over all documents in the collection
          // that match our query-by-example
          c = col.find().filter(f).getCursor();

          while (c.hasNext()) {
            // Get the next document
            fetchedDoc = c.next();

            System.out.println (fetchedDoc.getContentAsString());
          }
        }
        finally {
          // Important: you must close the cursor to release resources!
          if (c != null) {
            c.close();
          }
        }

        // Drop the collection, deleting the table backing
        // it and collection metadata
        if (args.length > 0 && args[0].equals("drop")) {
          col.admin().drop();
          System.out.println ("\n* Collection dropped *");
        }
    }
    catch (Exception e) {
      e.printStackTrace();
    }
    finally {
      if (conn != null) {
        try {
          conn.close();
        }
        catch (Exception e) {
        }
      }
    }
  }
}

Copy and paste this code into a file called testSODA.java. Then modify the "url" String at the beginning of the program
with connection info for your Oracle RDBMS instance. Also "user" and "password" properties set at the beginning of the program need to be modified with the schema name which will contain the table backing the collection, and the password for that schema. Note that you must grant the SODA_APP role to this schema name, as described above.

Compile and run testSODA.java, making sure the necessary jars are in the classpath.  For example, assuming you're in the directory where the jars are located, do:

javac -classpath "orajsoda.jar" testSODA.java
java -classpath "orajsoda.jar:ojdbc6-12.1.0.2.0.jar:javax.json-1.0.4.jar:." testSODA

You should see the following output:

  * Retrieving the first document by its key *

  { "name" : "Alex", "friends" : "50" }

  * Retrieving documents representing users with at least 300 friends *

  { "name" : "Mia", "friends" : "300" }
  { "name" : "Gloria", "friends" : "399" }

This example illustrates two ways of retrieving documents from the collection: by using unique document keys, or by using QBEs. To find all users
with at least 300 friends, the following QBE was used in the code above:

  {"friends" : {"$gte" : 300}}

As you can see, a QBE is a JSON document with a structure similar to the JSON document it's trying to match. Various operators can appear
inside the QBE. In this case, $gte operator is used to find all documents where the "friends" field is set to greater than or equal to 300.

To check out the table backing this collection, connect to the schema associated with your JDBC connection in the example above, using SQLPlus or another similar tool, and do:

desc "MyJSONCollection"
You should see:
SQL> desc "MyJSONCollection"

 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID					   NOT NULL VARCHAR2(255)
 CREATED_ON				   NOT NULL TIMESTAMP(6)
 LAST_MODIFIED				   NOT NULL TIMESTAMP(6)
 VERSION				   NOT NULL VARCHAR2(255)
 JSON_DOCUMENT                                      BLOB

As you can see a table has been created with the following columns:

 ID  Stores the auto-generated key
 JSON_DOCUMENT  Stores the document content
 CREATED_ON  Stores the auto-generated created-on timestamp
 LAST_MODIFIED  Stores the auto-generated last-modified timestamp
 VERSION  Stores the auto-generated document version

This table schema corresponds to the default collection configuration, but SODA collections are highly configurable. For example, the timestamp and the version columns are optional, there are many possible ways of generating the IDs or versions, etc. Custom collection configuration is covered in the documentation, and will be covered in future blog entries as well. Although most users should be fine with the defaults, custom collection configuration might be useful in some cases, such as mapping an existing table to a new collection.

To drop the collection, removing the underlying table and cleaning up the metadata persisted in the database, run the example again, but this time with the "drop" argument at the end:

java -classpath "orajsoda.jar:ojdbc6-12.1.0.2.0.jar:javax.json-1.0.4.jar:." testSODA drop

The output will now be different from before, since the same three documents will be inserted again. But, at the end, the collection will be dropped, and the underlying table removed.

Note: do not drop the collection table from SQL.  Collections have metadata stored in the Oracle RDBMS, so must be properly dropped by using the drop() method. See the line: col.admin().drop() in the code.

This concludes a very brief intro to SODA for Java, with more content to follow.

More info:

SODA for Java documentation: http://docs.oracle.com/cd/E63251_01/index.htm
SODA for Java Javadoc: http://download.oracle.com/otndocs/java/javadocs/soda/soda6r/index.html

Oracle Database as a Document Store homepage: http://www.oracle.com/technetwork/database/application-development/oracle-document-store/index.html

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed
About

This blog serves as a news, discussion and support platform for people who want to process JSON data in the Oracle database. About the authors: Beda Hammerschmidt: "I joined Oracle in 2006 from Germany and worked on various projects in XML and more recently JSON land; for example I implemented the path engine that evaluates path expressions in the new SQL/JSON operators. I love to hear from developer how they use the product and what extensions they would like to see in future releases!"

Search


Categories
Archives
« March 2017
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 
       
Today