Working with JSON documents in Oracle database with the document access Java API with no trace of SQL code at all


Oracle database has native JSON support, including a JSON datatype. Simple Document Access API is provided for several programming languages such as Java, Node, C, REST, Phyton, PL/SQL

In this post we are working with an Autonomous Database in OCI, but same work applies to on-prem databases, Exadata Cloud at Customer and other flavours.

STEP 0: clone repo

git clone this repo for your convenience

Please note that jdbc drivers are located in the root directory of the repo, anyway, you can download them from here

STEP 1: create or grab a db instance

Create an ATP/AWD or Autonomous JSON instance in OCI

STEP 2: Install oracle client

Download and Install OracleInstanClient in client machine in a directory <oracleinstantclientinstalldir>

STEP 3: download connection wallet

Download the autonomous database connection wallet and unzip it to <oracleinstantclientinstalldir>/network/admin

STEP 4: set env variables

Export variable TNS_ADMIN

export TNS_ADMIN=<oracleinstantclientinstalldir>/network/admin

STEP 5: test db connection

(base) mbpj:instantclient_19_3 javiermugueta$ export TNS_ADMIN=/Users/javiermugueta/instantclient_19_3/network/admin
 (base) mbpj:instantclient_19_3 javiermugueta$ ./sqlplus invictus@dbpre_tp
 

 SQL*Plus: Release 19.0.0.0.0 - Production on Fri Jan 15 03:29:07 2021
 Version 19.3.0.0.0
 

 Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 

 Enter password: 
 Last Successful login time: Fri Jan 15 2021 03:28:42 +01:00
 

 Connected to:
 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
 Version 19.5.0.0.0
 

 SQL> 
 

STEP 6: mavenize it all

Install maven if you dont have it

STEP 7: create project from archetype

Note: you dont need to do this step beacuse project is already created in the git repo, here is how it was created and pom updated with needed dependencies

mvn archetype:generate -DgroupId=javiermugueta.blog -DartifactId=jsontest -DarchetypeArtifactId=maven-archetype-quickstart -DarchetypeVersion=1.4 -DinteractiveMode=false

Modify pom file with the dependency highlighted in bold down here:

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    <dependency>
    <groupId>com.oracle.database.soda</groupId>
    <artifactId>orajsoda</artifactId>
    <version>1.1.4</version>
  </dependency>
  </dependencies>

STEP 8: Locate testSoda.java class source code

Locate source code testSoda.java in cloned git repo

STEP 9: adapt testSoda.java to your environment

(highlighted in bold here below)

public class testSoda
{
  public static void main(String[] arg)
  {
      String url = "jdbc:oracle:thin:@dbpre_tp";
      Properties props = new Properties();
      props.setProperty("user", "youruser");
      props.setProperty("password", "yourpasswd");
      Connection conn = null;
      try

STEP 10: package code

Locate in project directory and maven package

(base) mbpj:soda4java javiermugueta$ pwd
 /Users/javiermugueta/Library/Mobile Documents/com~apple~CloudDocs/INTERCHANGE/TUTORIALS/soda4java
 (base) mbpj:soda4java javiermugueta$ cd jsontest/
 (base) mbpj:jsontest javiermugueta$ mvn package
 [INFO] Scanning for projects…
 [INFO] 
 [INFO] --------------------< javiermugueta.blog:jsontest >---------------------
 [INFO] Building jsontest 1.0-SNAPSHOT
...
 [INFO] --- maven-jar-plugin:3.0.2:jar (default-jar) @ jsontest ---
 [INFO] Building jar: /Users/javiermugueta/Library/Mobile Documents/com~apple~CloudDocs/INTERCHANGE/TUTORIALS/soda4java/jsontest/target/jsontest-1.0-SNAPSHOT.jar
 [INFO] ------------------------------------------------------------------------
 [INFO] BUILD SUCCESS
 [INFO] ------------------------------------------------------------------------
 [INFO] Total time:  3.219 s
 [INFO] Finished at: 2021-01-15T03:46:01+01:00
 [INFO] ------------------------------------------------------------------------
 (base) mbpj:jsontest javiermugueta$ 

STEP 11: Test artifact

(base) mbpj:jsontest javiermugueta$ cd ..
 (base) mbpj:soda4java javiermugueta$ pwd
 /Users/javiermugueta/Library/Mobile Documents/com~apple~CloudDocs/INTERCHANGE/TUTORIALS/soda4java
 (base) mbpj:soda4java javiermugueta$ ll
 total 22360
 drwxr-xr-x@  15 javiermugueta  staff      480 Jan 15 03:49 .
 -rw-r--r--@   1 javiermugueta  staff     6148 Jan 15 03:49 .DS_Store
 drwxr-xr-x@   6 javiermugueta  staff      192 Jan 15 03:46 jsontest
...
 -rw-r--r--@   1 javiermugueta  staff   265130 Dec 21 09:43 xdb.jar
 -rw-r--r--@   1 javiermugueta  staff  1933747 Dec 21 09:43 xmlparserv2.jar
 drwxr-xr-x  110 javiermugueta  staff     3520 Nov 19  2018 ..

(base) mbpj:soda4java javiermugueta$ java -cp jsontest/target/jsontest-1.0-SNAPSHOT.jar:*:. javiermugueta.blog.testSoda

If you leave the code as it is, a JSON document with well known ticketId is created, then 1000 JSON docs are created with random ticketID, then all the documents are full scanned (but skipped after first occurrence) and, finally, a search by specific ticketId is performed and result shown in stdout.

...

        // a new ticket document with fixed ticketId 
        OracleDocument doc = db.createDocumentFromString("{\"ticketId\" : \"" + "000001"  + "\", \"ref\" : \"0095454353\", \"color\" : \"r023\",  \"price\" : \"99.00\"}");
        col.insert(doc);

        // a bucnh of ticket documents with random ticketId 
        for (int i = 0; i < 1000; i++){
          String ticketId = UUID.randomUUID().toString();
          doc = db.createDocumentFromString("{\"ticketId\" : \"" + ticketId  + "\", \"ref\" : \"0095454353\", \"color\" : \"r023\",  \"price\" : \"99.00\"}");
          //OracleDocument doc = db.createDocumentFromString("123", "{ \"ref\" : \"0095454353\", \"color\" : \"r023\",  \"price\" : \"99.00\"}");
          col.insert(doc);
        }

        // Find all documents in the collection.
        OracleCursor c = null;
        try 
        {
          c = col.find().getCursor();
          OracleDocument resultDoc;
          while (c.hasNext())
          {
            resultDoc = c.next();
            System.out.println ("Key:         " + resultDoc.getKey());
...
            System.out.println ("\n");
            break; // dont wanna lose my time waiting.. 🙂
          }

          // search by json attribute
          OracleDocument filterSpec =db.createDocumentFromString("{ \"ticketId\" : \"000001\"}");
          c = col.find().filter(filterSpec).getCursor();
 
          while (c.hasNext()) {
              resultDoc = c.next();
              System.out.println ("Document key: " + resultDoc.getKey() + "\n" +
                        " document content: " + resultDoc.getContentAsString());
             break; // dont wanna lose my time waiting.. 🙂
          }
...

More info here and in many other places…

Hope it helps!!! 🙂

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.